Print Page | Close Window

Multiplying summary fields

Printed From: Crystal Reports Book
Category: Crystal Reports for Visual Studio 2005 and Newer
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=5310
Printed Date: 17 Apr 2025 at 12:23pm


Topic: Multiplying summary fields
Posted By: bazzerp
Subject: Multiplying summary fields
Date Posted: 23 Jan 2009 at 5:19am
Hi. I have a report which shows averages in a group footer (call these avg1, avg2, avg3, etc.) and an average of those in the next footer. All good so far. However I need to create another summary in this next footer using a formula similar to this:
 
( (1 + (avg1/100)) * (1 + (avg2/100)) * (1 + (avg3/100)) etc. etc.)
 
I'm still a CR novice and I can't see how I can do this. I can't see how to access the individual averages. It would be easy enough to sum the averages but to multiply them in this way is currently beyond me!
 
Please help. Thanks.


Replies:
Posted By: lockwelle
Date Posted: 27 Jan 2009 at 7:08am
since I don't know how the averages are being calculated, I am guessing you are using the built in summary function.  If so, access them is actually pretty easy.
 
Create a formula, and in the field panel, under the Report Fields, look for the summing icon (sigma) for the values that you want...it will say something like 'Report Area: Avg of...' and then build the formula that you describe for as many averages.
 
On the other hand, this appears to be in a group footer, so there are potentially an infinite number.  As long as you can access the 'current' average, I would create formula that uses the average for the current group and appends it the rest to get your multiplicative total.  Something like:
shared numbervar x := x * (1+(avg1/100);
 
the caveat is what to set x to initially.  maybe this would be better:
shared numbervar x
if x = 0 then
  x:=(1+(avg1/100))
else
  x := x * (1+(avg1/100)
 
remember to set x back to some value when the group you are reporting for changes.
 
Hope this helps...or points you to a solution.

Posted By: bazzerp
Date Posted: 28 Jan 2009 at 3:11am

Thanks for that.

Ideally I am trying to do this within a cross-tab report.

I have resorted to doing the compound calculation within a stored procedure which supplies the data to the report. This is fine up to a point. In the cross-tab I use a sum of the compounded results which works because I only populate the column when there is a strategy change. However when the cross-tab summarises for a group change it simply sums these compound amounts rather than re-calculates for the group.

I'll try to give an example:
 
Strategy Group Strategy     Q1       Q2      Q3     Q4   Compound Value
 
      A                      1            4.38    12.77                              17.71
                              2            4.35      4.88    7.22   -0.61        16.64
                              3           -4.36                                            -4.36
 
Change of Group Totals:    4.37      8.83    7.22   -0.61        34.35

The value 34.35 is simply a sum of 7.71 + 16.64 which is not correct.
It should be 100 * (((1.0437)*(1.0883)*(1.0722)*(0.9689)) - 1) = 18.00
 
The actual compound formula is 100*(((1+avg1/100)*(1+avg2/100)*
                                                      (1+avg3/100)*(1+avg4/100)) -1)
 
Apologies for the complexity but it is what I am having to do!
 
It would be great if I could customise my own summary function to use instead of 'sum' in the cross-tab.

Posted By: lockwelle
Date Posted: 28 Jan 2009 at 6:41am
Sorry, I have no experience with Cross Tabs in Crystal.  You might try a function in Crystal which cuts out the pasting, but again I haven't needed cross tabs so don't really know.


Print Page | Close Window