Report Design
 Crystal Reports Forum : Crystal Reports for Visual Studio 2005 and Newer : Report Design
Message Icon Topic: Multiplying summary fields Post Reply Post New Topic
Author Message
bazzerp
Newbie
Newbie


Joined: 23 Jan 2009
Location: United Kingdom
Online Status: Offline
Posts: 9
Quote bazzerp Replybullet Topic: Multiplying summary fields
    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.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet 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.
IP IP Logged
bazzerp
Newbie
Newbie


Joined: 23 Jan 2009
Location: United Kingdom
Online Status: Offline
Posts: 9
Quote bazzerp Replybullet 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.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet 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.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.