Repeat formula in group hedaer band to achieve total

[bhodgson]

I am using the following formula in a databand but I need to achieve the total for a group. I have 3 levels of grouping and in each group header band I want to alter the formula to achieve group totals at each level. Can you please help:

Here is the formula:

Code: Select all

{Format("{0:C}", Round(
Fields.PAY.ULTIMATE_PAY_RATE
*
(
Fields.DETACH_STATION == Fields.HOME_STATION ?
Truncate(DateDiff(Fields.END_DATE,Fields.START_DATE).TotalHours) + (DateDiff(Fields.END_DATE,Fields.START_DATE).Minutes / 60.0):
Truncate(DateDiff(Fields.END_DATE,Fields.START_DATE).TotalHours) +2 + (DateDiff(Fields.END_DATE,Fields.START_DATE).Minutes / 60.0)
)
,2)
)}

I have tried copying this formula into a group header band and adding a SUM() in various different places but it always seems to display an error.

The data looks similar to below (it is grouped per person):
HOME STATION --- DETACH STATION --- HOURS --- COST
Washington DC ----- New York- --------------- 8 ------------ £100
Washington DC ----- Washington DC -------- 8 ------------ £80

To explain further, each person is paid an hourly rate, this is called the Fields.PAY.ULTIMATE_PAY_RATE which is in the code above. In the data above the hourly rate is £10, but, as happens in the formula above if the home station is different to the detach station then an extra 2 hours is added on. So in the example above the cost of the Washington DC - New York record costs £20 extra. This is captured in the IF statement from the databand but I am having trouble using this IF statement as part of a SUM function in the group header band.

Could somebody please tell me what I can use in the group header band to achieve a total for that group. In the above example the total would be £180.

Thanks,
Ben

[Alex K.]

Hello,

Please try to use the Totals.Sum() function.

Thank you.

[bhodgson]

Thanks for your response, I have altered my formula as per below:

Code: Select all

{Format("{0:C}", Round(
(decimal)Fields.PAY.ULTIMATE_PAY_RATE
*
(
Fields.DETACH_STATION == Fields.HOME_STATION ?
Truncate(Sum(DateDiff(Fields.END_DATE,Fields.START_DATE).TotalHours)) + (Sum(DateDiff(Fields.END_DATE,Fields.START_DATE).Minutes / 60.0)):
Truncate(Sum(DateDiff(Fields.END_DATE,Fields.START_DATE).TotalHours)) +2 + (Sum(DateDiff(Fields.END_DATE,Fields.START_DATE).Minutes / 60.0))
)
,2)
)}

However it is not parsing the IF statement and therefore never adding the extra 2 hours when an detach station is different to the home station. So in the data in my above post it is returning £160 instead of £180.

How can I get around this? How can the group header band know to process the IF statement on the data from within the group header band?

Regards,
Ben

[bhodgson]

Just in case my message above wasn't clear, the formula in my group header band seems to provide a total based on every data item being evaluated as TRUE in my IF statement even though there are rows that are false and therefore should have an extra 2 hours added on. From my data above it is therefore classing the first record as having a total of £80 and the overall total is £160.

Regards,
Ben

[Alex K.]

Hello,

Can you please send us your report with test data for analysis.

Thank you.