How to use a sum of numbers that are evaluated at a later time

[arodri]

Hello,

I have a Commissions report that I'm trying to finish but there is one final percentage that I'm having trouble with.

Groups in the report:
Group#1: Region
Group#2: EmployeeName
Group#3: SiteName

All employees only work in one region, but there are times when one employee works at more than one site in that region. So the report lists the employee, and then all of their sales, percentages, etc. for each site that that employee worked at during a given time period.

The calculation I'm having trouble with is a percentage on how much they sold for each site, of the total they sold (total for all sites they were at). I'm trying to display this in GroupFooter#3 (SiteName Group), but the total sum gets calculated in GroupFooter#2. Below is what it looks like:

Region#1
Employee#1 (Header)

Site#1 2,000 %I need in this group = 2,000/12,000
Site#2 10,000 %I need in this group = 2,000/12,000

Employee#1 (Footer): 12,000

The 2,000, 10,000, and 12,000 are all running totals. Obviously the 2,000 and 10,000 get reset on change of group #3 (SiteName), and the 12,000 running total resets on change of group#2(EmployeeName).

How do I calculate the 2,000/12,000 percentage when the 12,000 is not calculated until later? I need to display this percentage in the SiteName group.

Thank you!!

 

[hilfy]

Is there a reason why you're using running totals instead of sums? If you use sums, you should be able to do this. For example:

if sum({sales field}, {region field}) > 0 then
sum({sales field}, {site field}) / sum({sales field}, {region field})
else 0

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com