4 Replies Latest reply: May 15, 2014 9:54 AM by Richard Corby RSS

    Selecting first of repeating summary data

    Richard Corby

      I have a long report that has summary customer data followed by the detail data.  It then repeats for the next customer and so on.

       

      If the detail spans several pages, as it does for many customers, each page begins with the same summary data.  If I try to trap that row as detail, I end up getting that data several times in my table view.

       

      How can I trap it just once?  Sample below:

       

      [font="courier"] 

      AGING OF ACCOUNT RECEIVABLE   (PERIOD DATE  : 03/09/23)                 SAB-21021    PRODUCED ON  03/09/23  AT 21:35:14   PAGE  12                                                                               

      CREDIT AGENT : BIBI RICKETTS                                                                               

      REQUIRED INVOICES : 0 days and more.                                                                               

      CD  ST AGING      0-10     11-20     21-30     31-60     61-90     91  +                         %          

      -


                  

       

      BDC400199    SAMPLE CUSTOMERX & CO. CDA LTD                                                                 BALANCE :  560,858.55             

      37823        Oakville (Ontario), Fax: 905 855-5611                                                          IN PROG.:  403,503.61 48          

      TINA, 201 847-5020                            111,842.24          151,768.74                0.00            LIMIT  : 2,000,000.00             

                                                              297,247.57                0.00                0.00  TERMS   :         999 dy          

      Last remittance: 3,840.73 on 03/09/23                                                                               

      Avg payment : 31 day,  "99K" ACCT:5000149                                                                               

      EJ0519 3.221604 58412544  03/08/25 1        29                       5,769.85                                                                 

      EK2090 3.195663 58412096  03/08/27 1        27                       6,633.82                                                                 

      EK2091 3.196026 58412459  03/08/27 1        27                       6,478.90                                                                 

      EK2134 3.221650 58412590  03/08/27 1        27                      18,204.17                                                                 

      EK2137 3.221678 58412618  03/08/27 1        27                       4,774.24                                                                 

      EK2139 3.221691 58412631  03/08/27 1        27                       7,452.61                                                                 

      EK2150 3.222030 58412968  03/08/27 1        27                      14,101.57                                                                 

      EK2151 3.222041 58412979  03/08/27 1        27                       9,732.00                                                                 

      EK2246 3.241573 69060355  03/08/27 1        27                       9,545.38                                                                 

      EL2726 3.221648 58412588  03/08/29 1        25                      12,674.44                                                                 

      EL2730 3.222097 58413035  03/08/29 1        25                       5,674.13                                                                 

      EL2731 3.222115 58413053  03/08/29 1        25                       6,639.28                                                                 

      EL2769 3.241651 58413172  03/08/29 1        25                       6,462.86                                                                 

      EL5526 3.222095 58413033  03/08/29 1        25                       6,602.07                                                                 

      EL5564 3.241772 58413293  03/08/29 1        25                      13,196.81                                                                 

      EL5565 3.241773 58413294  03/08/29 1        25                       4,103.16                                                                 

      EL5535 3.230620 65237070  03/08/29 1        25                       5,585.16                                                                 

      EL7925 3.241854 58413375  03/09/02 1        21                       8,138.29                                                                 

      EN4216 3.222114 58413052  03/09/05 1        18             5,962.07                                                                           

      EN4228 3.242090 58413608  03/09/05 1        18             5,014.62                                                                           

      EN4230 3.242203 58413721  03/09/05 1        18            15,093.98                                                                           

      EN4291 3.270633 58413802  03/09/05 1        18             5,218.91                                                                           

      EN4292 3.270735 58413904  03/09/05 1        18             5,704.41                                                                           

      EN4380 3.285721 61680509  03/09/05 1        18             4,473.81                                                                           

      EP0080 3-923018 3-923018  03/09/08 1        15                33.17                                                                           

      EP0094 3-945898 3-945898  03/09/08 1        15                53.01                                                                           

      EP0234 3.288663 54579094  03/09/08 1        15                44.60                                                                           

      EP0253 3.293298 54579271  03/09/08 1        15                33.17                                                                           

      EP0357 3.308424 54579658  03/09/08 1        15                33.17                                                                           

      EP0107 3-966460 3-966460  03/09/08 1        15               132.36                                                                           

      EP8698 3-960848 3-960848  03/09/09 1        14                49.44                                                                           

      EP6389 3.224839 54576416  03/09/09 1        14             4,422.85                                                                           

      EP6393 3.242948 54576281  03/09/09 1        14               318.14                                                                           

      EP6394 3.243017 54576090  03/09/09 1        14             2,340.33                                                                           

      XXX XXX, INC.                                                                               

      AGING OF ACCOUNT RECEIVABLE   (PERIOD DATE  : 03/09/23)                 SAB-21021    PRODUCED ON  03/09/23  AT 21:35:14   PAGE  12                                                                               

      CREDIT AGENT : BIBI RICKETTS                                                                               

      REQUIRED INVOICES : 0 days and more.                                                                               

      CD  ST AGING      0-10     11-20     21-30     31-60     61-90     91  +                         %          

      -


                  

      BDC400199    SAMPLE CUSTOMERX & CO. CDA LTD                                                                 BALANCE :  560,858.55             

      37823        Oakville (Ontario), Fax: 905 855-5611                                                          IN PROG.:  403,503.61 48          

      TINA, 201 847-5020                            111,842.24          151,768.74                0.00            LIMIT  : 2,000,000.00             

                                                              297,247.57                0.00                0.00  TERMS   :         999 dy          

      Last remittance: 3,840.73 on 03/09/23                                                                               

      Avg payment : 31 day,  "99K" ACCT:5000149                                                                               

      EP6402 3.254264 54576555  03/09/09 1        14               517.13                                                                           

      EP6403 3.263499 54576805  03/09/09 1        14                35.10                                                                           

      EP6404 3.263670 54576813  03/09/09 1        14               158.51                                                                           

      EP6380 3.086430 58409419  03/09/09 1        14            45,411.66                                                                           

      EP6411 3.270802 58413971  03/09/09 1        14            14,877.54                                                                           

      EP6412 3.270803 58413972  03/09/09 1        14             8,702.21                                                                           

      EP6414 3.270969 58414133  03/09/09 1        14             6,875.10                                                                           

      EP6415 3.271016 58414180  03/09/09 1        14               887.12                                                                           

      EP6418 3.271080 58414244  03/09/09 1        14             8,925.66                                                                           

      EP6572 3.298138 58414566  03/09/09 1        14             9,651.90                                                                           

      EP7787 3.329315 59058947  03/09/09 1 T151   14                21.40                                                                           

      EP7788 3.331891 59058954  03/09/09 1 B2A    14                21.40                                                                           

      EP7789 3.328327 59058945  03/09/09 1 T151   14                21.40                                                                           

      EP6493 3.285808 61680596  03/09/09 1        14            13,477.39                                                                           

      EP6430 3.272173 65238081  03/09/09 1        14             6,597.32                                                                           

      EP6583 3.300816 69060686  03/09/09 1        14             5,331.05                                                                           

      EQ6800 3.298612 54579385  03/09/10 1        13             5,560.91                                                                           

      EQ6881 3.311858 54579763  03/09/10 1        13               177.16                                                                           

      EQ6882 3.311940 54579772  03/09/10 1        13                73.97                                                                           

      EQ6888 3.313537 54579858  03/09/10 1        13               151.86                                                                           

      EQ6890 3.313656 54579863  03/09/10 1        13               305.42                                                                           

      EQ3122 3.298217 58414643  03/09/10 1        13            22,376.50                                                                           

      EQ3076 3.272747 61679987  03/09/10 1        13             2,615.93                                                                           

      EQ3077 3.273102 61680341  03/09/10 1        13             4,452.69                                                                           

      EQ9134 3.001131 3.001131  03/09/11 1        12                40.23                                                                           

      ER3041 3-989164 3-989164  03/09/11 1        12                53.01                                                                           

      ER3042 3.007155 3.007155  03/09/11 1        12                38.22                                                                           

      ER3043 3.007165 3.007165  03/09/11 1        12                64.39                                                                           

      EQ9123 3-966505 3-966505  03/09/11 1        12                45.16                                                                           

      EQ9125 3-969451 3-969451  03/09/11 1        12                65.56                                                                           

      EQ9132 3-992284 3-992284  03/09/11 1        12                41.03                                                                           

      ER3022 3-908457 3-908457  03/09/11 1        12                73.77                                                                           

      ER3024 3-924864 3-924864  03/09/11 1        12                38.69                                                                           

      ER3025 3-925857 3-925857  03/09/11 1        12               109.94                                                                           

      ER3026 3-928319 3-928319  03/09/11 1        12                42.30                                                                           

      ER3027 3-931942 3-931942  03/09/11 1        12                41.13                                                                           

      ER3028 3-959062 3-959062  03/09/11 1        12                53.34                                                                           

      ER3029 3-959461 3-959461  03/09/11 1        12                33.17                                                                           

      ER3030 3-960906 3-960906  03/09/11 1        12               111.89                                                                           

      ER3031 3-962134 3-962134  03/09/11 1        12                36.25                                                                           

      ER3032 3-962857 3-962857  03/09/11 1        12                44.86                                                                           

      ER3033 3-966253 3-966253  03/09/11 1        12                38.22                                                                           

      ER3034 3-966279 3-966279  03/09/11 1        12                45.76                                                                           

      XXX XXX, INC.                                                                               

      AGING OF ACCOUNT RECEIVABLE   (PERIOD DATE  : 03/09/23)                 SAB-21021    PRODUCED ON  03/09/23  AT 21:35:14   PAGE  13                                                                               

      CREDIT AGENT : BIBI RICKETTS                                                                               

      REQUIRED INVOICES : 0 days and more.                                                                               

      CD  ST AGING      0-10     11-20     21-30     31-60     61-90     91  +                         %          

      -


                  

      BDC400199    SAMPLE CUSTOMERX & CO. CDA LTD                                                                 BALANCE :  560,858.55             

      37823        Oakville (Ontario), Fax: 905 855-5611                                                          IN PROG.:  403,503.61 48          

      TINA, 201 847-5020                            111,842.24          151,768.74                0.00            LIMIT  : 2,000,000.00             

                                                              297,247.57                0.00                0.00  TERMS   :         999 dy          

      Last remittance: 3,840.73 on 03/09/23                                                                               

      Avg payment : 31 day,  "99K" ACCT:5000149                                                                               

      ER3035 3-966458 3-966458  03/09/11 1        12                59.59                                                                           

      ER3036 3-968601 3-968601  03/09/11 1        12                41.03                                                                           

      ER3037 3-971067 3-971067  03/09/11 1        12                52.48                                                                           

      EQ9142 3.222085 58413023  03/09/11 1        12             8,115.80                                                                           

      EQ9205 3.298044 58414472  03/09/11 1        12             8,631.22                                                                           

      EQ9208 3.298190 58414618  03/09/11 1        12            22,011.95                                                                           

      ER3038 3-983606 3-983606  03/09/11 1        12                59.93                                                                           

      ER3039 3-987233 3-987233  03/09/11 1        12                37.32                                                                           

      ER3040 3-988939 3-988939  03/09/11 1        12                60.44                                                                           

      ER4915 3.270922 58414086  03/09/12 1        11             3,846.59                                                                           

      ER4916 3.270923 58414087  03/09/12 1        11            13,448.62                                                                           

      ER5044 3.319764 58414998  03/09/12 1        11            21,565.01                                                                           

      ER3992 3.328497 59058946  03/09/12 1 T151   11                77.59                                                                           

      ER3993 3.329402 59058948  03/09/12 1 T151   11                33.31                                                                           

      ER3994 3.330498 59058949  03/09/12 1 T151   11                52.85                                                                           

      ER3995 3.330621 59058950  03/09/12 1 T151   11                33.31                                                                           

      ER4966 3.302969 61682250  03/09/12 1        11             4,320.74                                                                           

      ER4982 3.304413 64404780  03/09/12 1        11             1,212.66                                                                           

      ER5112 3.332918 69060878  03/09/12 1        11             6,415.84                                                                           

      ER9969 3-987583 3-987583  03/09/15 1         8      59.74                                                                               

      ES0529 3.342508 54580757  03/09/15 1         8     118.03                                                                               

      ER9972 3-992334 3-992334  03/09/15 1         8      34.57                                                                               

      ER9966 3-986290 3-986290  03/09/15 1         8      40.06                                                                               

      ES4997 3.011143 3.011143  03/09/15 1         8      48.10                                                                               

      ES4998 3.011200 3.011200  03/09/15 1         8      39.36                                                                               

      ES8252 3.332610 17364834  03/09/16 1         7     211.77                                                                               

      ET1800 3.347835 52171820  03/09/16 1         7     342.57                                                                               

      ES8166 3.319740 58414974  03/09/16 1         7   8,556.87                                                                               

      ES8168 3.319750 58414984  03/09/16 1         7   2,185.51                                                                               

      ET1601 3.320043 58415276  03/09/16 1         7   6,732.37                                                                               

      ET1616 3.320162 58415395  03/09/16 1         7   3,767.86                                                                               

      ET1617 3.320164 58415397  03/09/16 1         7  20,678.76                                                                               

      ET1807 3.348716 58415624  03/09/16 1         7   5,913.57                                                                               

      ET1665 3.329603 64405409  03/09/16 1         7   1,846.08                                                                               

      ET1652 3.324168 65239519  03/09/16 1         7     360.34                                                                               

      ES8295 3.341795 69060915  03/09/16 1         7  11,271.68                                                                               

      ET7156 3.362758 54581423  03/09/17 1         6   4,606.74                                                                               

      EU1791 3.364841 54581512  03/09/18 1         5     440.62                                                                               

      EV0850 3.365004 54581532  03/09/22 1         1      33.17                                                                               

      EV4619 3.226160 3.226160  03/09/22 1         1      60.29                                                                               

      EV2650 3.320013 58415246  03/09/22 1         1     607.09                                                                               

      EV2701 3.348927 58415835  03/09/22 1         1   7,838.26                                                                               

      EV2755 3.356810 64406658  03/09/22 1         1   1,812.11                                                                               

      XXX XXX, INC.                                                                               

      AGING OF ACCOUNT RECEIVABLE   (PERIOD DATE  : 03/09/23)                 SAB-21021    PRODUCED ON  03/09/23  AT 21:35:14   PAGE  14                                                                               

      CREDIT AGENT : BIBI RICKETTS                                                                               

      REQUIRED INVOICES : 0 days and more.                                                                               

      CD  ST AGING      0-10     11-20     21-30     31-60     61-90     91  +                         %          

      -


                  

      BDC400199    SAMPLE CUSTOMERX & CO. CDA LTD                                                                 BALANCE :  560,858.55             

      37823        Oakville (Ontario), Fax: 905 855-5611                                                          IN PROG.:  403,503.61 48          

      TINA, 201 847-5020                            111,842.24          151,768.74                0.00            LIMIT  : 2,000,000.00             

                                                              297,247.57                0.00                0.00  TERMS   :         999 dy          

      Last remittance: 3,840.73 on 03/09/23                                                                               

      Avg payment : 31 day,  "99K" ACCT:5000149                                                                               

      EV2661 3.328435 69061069  03/09/22 1         1   1,035.28                                                                               

      EV2820 3.365950 69061061  03/09/22 1         1   4,226.31                                                                               

      EV0466 3.082630 3.082630  03/09/22 1         1      47.70                                                                               

      EW0170 3.376353 57060458  03/09/23 1            12,710.33                                                                               

      EW0037 3.348985 58415889  03/09/23 1             9,895.41                                                                               

      EW0038 3.348986 58415890  03/09/23 1             6,321.69                                                                                /font[/quote]

        • Selecting first of repeating summary data
          Grant Perkins

          Richard,

           

          Just to be sure I understand this;

           

          You are not interested in the individual transactions lines (mostly beginning with E in the report) as detail but only want the information in the summary section which repeats on each page.

           

          The repeat is not so much of a problem within the table produced but you only want to see one one set of the data in the final output and therefore need to filter the data collected, either in the table or in a summary, so that each customer only appears once.

           

          Is a fair and complete summary (no pun intended) of your needs?

           

          Grant

           

          Originally posted by Richard Corby:

          I have a long report that has summary customer data followed by the detail data.  It then repeats for the next customer and so on.

           

          If the detail spans several pages, as it does for many customers, each page begins with the same summary data.  If I try to trap that row as detail, I end up getting that data several times in my table view.

           

          How can I trap it just once?  Sample below:

          /b[/quote]

          • Selecting first of repeating summary data
            Richard Corby

            Grant..you are correct.  For this report, I do not care about the detail lines (I do use them in another report).  I just want the Summary Data at the beginning of each client.

             

            So bring it into a table and filter out there?  How?

             

            Thanks

            • Selecting first of repeating summary data
              Grant Perkins

              Richard,

               

              I can't remember if we discussed which version of Monarch you have but I have assumed at least V5.

               

              In which case one solution is to treat the summary section you need as the detail and add some input from the Page header as Page Header (though append looks like it would work in this case). Make sure you get the Page number - I suspect you may have to do some clever things with that on a long report since the space available seems limited. I guess the word 'PAGE' shifts left if you get more than 999 pages?

               

              Create a summary using the Agent Ref number and/or name as key fields and whatever else you want to report.

               

              In the Summary area include count (mainly for reference and validity checking) and your Page Number field.

               

              Right Click on the Page Number in the summary window and set the Properties >> Calculation Tab >>field calculation to  MIN or MAX according to choice.

               

              That should give you a single line entry for each higher combination of keys and the count will tell you how many total selections exist in the data table.

               

              Thats the quick response but I am wondering if some of the newer features in V7 offer other approaches which I have not recognised yet so I'm off to play with it (and maybe even V6) and will report back on anything excitingly different.

               

              Hope this helps.

               

              Grant

               

              Edit.

               

              I have this feeling that there may be other clever ways to reduce the duplicate rows using V6.

               

              However the best way by far if you want to filter the TABLE rather than go to the Summary solution is to use the extended Advanced filtering capability of V7 which allows you to filter out non-unique entries by single or multi-field combinations. It is exactly what you need in this case, as far as I can tell.

               

               

                Originally posted by Richard Corby:

              Grant..you are correct.  For this report, I do not care about the detail lines (I do use them in another report).  I just want the Summary Data at the beginning of each client.

               

              So bring it into a table and filter out there?  How?

               

              Thanks /b[/quote]

               

              [size="1"][ September 25, 2003, 04:51 AM: Message edited by: Grant Perkins ][/size]

              • Selecting first of repeating summary data
                Richard Corby

                Actually, I am using v7 and it is quite simple...I just use the advanced properties and there is a check box just for this. 

                 

                Oh well, that is another one solved...thanks.