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:



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

[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:

[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

[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

[ September 25, 2003, 04:51 AM: Message edited by: Grant Perkins ]

[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.