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 Logged
