Top 25 and Next N Report in CR 8.5 2

[antonx]

Hi,

In CR 8.5, how can you create a summary page in the Report Header as follows:

Top 25 Premiums 9999.99
Next 50 Premiums 9999.99
Next 100 Premiums 9999.99
Balance 9999.99
---------
Total 9999.99

The input is 2 fields: Policy Number and Premium.

Currently, the report is grouped by Policy Number to get the total Premium of the Policy Number.

I'm hoping not to use subreports. The report already uses subreports to do other things and adding more subreports will take too long.

Thanks

 

[MisterMo]

what database are using and most importantly do you have free access to it?

one option would be to write a SP and do all the work there using temp tables.

Mo

 

[Madawc]

There is a 'Top N' function for ordering groups. But I've no idea whether you could sum them.

Note that Crystal has its own cycle which you have to work within.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[MisterMo]

Here is a way

create a group for the amount value and make sure that is descending so that the highest value is top

then create a formula like this @Counter

if groupnumber> 0 and groupnumber <= 25 then
1
else if groupnumber> 25 and groupnumber <= 75 then
2
else if groupnumber> 75 and groupnumber <= 175 then
3
else
0

on the section expert where you display the data

click on the x-2 button where it sday suppress and add

the condition

@Counter = 0

this is only partly tested and might have some bugs still but its a start

Mo

 

[GJParker]

The simple answer here is that it can't be done without precalculating the totals on the database. otherwise you have 2 options.

1. Inserta asubreport in the report header.
2. Display the summary in the report footer.

If you need any further help then let us know which way you wish to proceed.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[lbass]

Just a note that MisterMo's solution does work in 11.0, although not in 8.5.

-LB

 

[Madawc]

I've just thought of a way. Group, and list the groups in premium sequence. Use a running total as a count, once per group. Have four other running totals, which accumulate when the count is 1 to 25, 26 to 100 etc.

That's assuming you can test a running total from a running total, which I am not sure of. If it won't, you could do something similar using variables.

You'd also need to suppress the displays and just show the totals at the end, in the report footer.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[MisterMo]

I think at this point we ought to wait for a response from antonx

Mo

 

[lbass]

To clarify my earlier comment, I meant to say that MisterMo's formula could be used in a crosstab placed in the report header if V.11 were available and if you first did a topN/group sort in the main report.

-LB

 

[antonx]

Thanks all and I appreciate it. I can't use SP because you must go thru hell to have DB2 dba approve it.

I used GJParker's suggestion 1) Insert as a subreport in the report header.

In the subreport, I created 5 formulas: (one formula for each total, below is one of the five formulas, called @AccumPremTop25.

global numbervar Top25PremiumAccum;

if GroupNumber >= 1 and GroupNumber <= 25 then
Top25PremiumAccum := Top25PremiumAccum + Sum ({@LastDay_Premium}, {NEW_BUS_DM.CONTRACT_NUMBER})
else Top25PremiumAccum := Top25PremiumAccum;

Top25PremiumAccum;

The subreport is grouped by Policy Number to get the total Premium of the Policy. In the GH, I inserted all the 5 formulas for display. Then I inserted the subreport in the RH of the main report. It now take 2 times longer to run this report but I guess I had no other choice.

Antonx.