calculations based on 2 largest volume and price in a table 1

[thiagarr]

Hi all,

Greetings.

I have a subreport in a report with volume and price. I would like to get the total of the top 2 volume * price based on the usage. Sample data and required result is as follows:
Period volume Price Rank vol*Price
1 100 7 6
2 200 8 5
3 400 9 2 3600
4 300 8 3
5 500 9 1 4500
6 250 7 4
Total 5100

I need only the highest and second highest volume and I need the total (5,100 in the above example) to be used in further calculations.

I created formula called rank in which I used formulas to calculate the maximum (volume) and nthlargest(2, volume) to identify the two highest volumes and calculated the vol * Price. But I am not able to insert a summary to sum the two values (and also not able to do a running total of the vol*price field.

Any help on how I can achieve this is very highly appreciated.


Thanks and regards,

TR

 

[thiagarr]

Hi,

My apologies.

I use CR 9.2 with Oracle 9i database.

Thank you.

Thanks and regards,

TR

 

[lbass]

Use a variable. Let's say your formula for vol*price is called {@cost}. Then create a formula like the following and place it in the same section as {@cost}:

whileprintingrecords;
numbervar sumcost := sumcost + {@cost};

Then in the report footer, use a formula like:

whileprintingrecords;
numbervar sumcost;

If you want the summary at a group level instead, place the last formula in the group footer, and then add a reset formula in the group header:

whileprintingrecords;
numbervar sumcost;
if not inrepeatedgroupheader then
sumcost := 0;

It is unclear where the subreport is, or what its relevance is to the problem. If {@cost} is in a subreport linked to the main report, then you would need to use shared variables, and the solution would be a little different. Please clarify.

-LB

 

[thiagarr]

LB,

That is great and worked as required.

I also understand that this is sort of a workaround for a running total or summary (which was what I was trying to achieve) where for some reason, CR does not allow running totals or summary in some instances. I am sure this idea will be used by me in many more reports to come.

Yes, this is in a subreport had no relevance to the issue I posted here.

I really appreciate your help on this and especially on a Sunday morning (where I am).

Thank you very much.

Thanks and regards,

TR