Summarizing Top10 and Top15 values

[varadha72]

Hi,

I have data that is having 20 rows of data. Now I need to display total of the Top5 rows and the summary of the Top10 rows. What I mean is-
I have 3 columns, ID, Amount, and Name.
1 10 A
2 5 B
3 6 C
4 9 D
5 20 E
6 15 F
7 13 G
8 12 H
9 17 I
10 22 J
11 33 K
12 27 L
13 31 M
14 2 N
15 7 O
Now I need to show in the report in the Report Footer, 2 values- i.e. 1. Total/Sum of Top5 Amounts. 2. Sum of Top10 Amounts.
Can someone help.

 

[BettyJ]

Is it possible to sort it by amount? (or do you have any group or other sort criteria alreadY?)

 

[BettyJ]

If you can sort by amount, then select Sort Descending.

In RH, add a formula
@initTotal5
[tt]whileprintingrecords;
numbervar totalamount5:=0;[/tt]
Suppress the formula.

In Details
@TotalTop5
whileprintingrecords;
[tt]numbervar totalamount5;
numbervar i;
if recordnumber in [1,2,3,4,5] then
(totalamount5:=totalamount5+{TableName.Amount};
);
totalamount5;[/tt]

In RF,
@DispTotalTop5
[tt]whileprintingrecords;
numbervar totalamount5;
totalamount5;[/tt]

Similarly you can do a Top 10 count and use
[tt]if recordnumber in [1,2,3,4,5,6,7,8,9,10][/tt]

Hope this helps.

 

[varadha72]

Thanks Betty, just came in, will try it out and let you know. I cannot use Top10 function, because I need to display the data in original order only. TopN will change the display order.

 

[BettyJ]

If you cannot change the order, then you cannot use my solution listed above.

 

[BettyJ]

Create a subreport with the required data.(one similar to or same as your main report). Sort that in descending order of amount.

To get sum of top 5 amounts, in the subreport, create the following formulas.
In RH, add a formula
@initTotals
[tt]whileprintingrecords;
numbervar totalamount5:=0;
numbervar totalamount10:=0;[/tt]

In Details,
@TotalTop5
[tt]whileprintingrecords;
numbervar totalamount5;
numbervar i;
if recordnumber in [1,2,3,4,5] then
(totalamount5:=totalamount5+{TableName.Amount};
);
totalamount5;[/tt]

In Details,
@TotalTop10
[tt]whileprintingrecords;
numbervar totalamount10;
numbervar i;
if recordnumber in [1,2,3,4,5,6,7,8,9,10] then
(totalamount10:=totalamount5+{TableName.Amount};
);
totalamount10;[/tt]

In RF,
@DispTotalTop5
[tt]whileprintingrecords;
numbervar totalamount5;
totalamount5;[/tt]

In RF,
@DispTotalTop10
[tt]whileprintingrecords;
numbervar totalamount10;
totalamount10;[/tt]

Suppress all sections except Report Footer.
Place the subreport in the Page Footer of the main report.
Hope this helps. Any questions, please feel free to ask.

 

[ianoctdec]

Hi,

Try to insert crosstabs in the report footer, one for top5 and one for top10; delete the rows and only show the columns totals.

Dana