Sum report item in a column group (SSRS 2008)

Question

I have the following payroll table in an SSRS 2008 (R2) report:

Payroll Table

The dataset returns labor transactions consisting of the following fields:

STARTDATE
STARTTIME
FINISHDATE
FINISHTIME
REGULARHRS (difference between finish and start)
REFWO (such as "Travel", "Holiday", "Work", etc and is used to sort into the categories shown in the table above)
TIMEWORKED (0/1 flag that indicates whether or not it counts towards "Time Worked" category)

I have a column grouped on STARTDATE so that it displays each day of the week (our weeks go Mon through Sun). Everything down to "Unpaid Time Off" is a simple expression (usually just in the format Sum(IIF(something,A,B)) in both the daily column and the weekly (Totals) column. In the "Interim Regular" box (for the day grouping), I have the following expression:

=IIF(Weekday(Fields!startdate.Value)=1 
     OR Weekday(Fields!startdate.Value)=7 
     OR ReportItems!Holiday.Value>0,
    0,
    ReportItems!TimeWorked.Value-ReportItems!Holiday.Value-ReportItems!Bereave.Value)

Basically what I'm doing is saying: If STARTDATE is a Saturday, Sunday, or Holiday, the regular hours would be 0 since it would fall into OT1.5 (overtime, time and a half), otherwise I calculate the regular hours worked by subtracting Holiday time and Bereavement time from the Time Worked (since both are included as part of Time Worked). This part works great!! But when I try to sum up the total for the week using =Sum(ReportItems!InterimRegularDaily.Value) it tells me that I can't use an aggregate on a report item and that aggregate functions can be used only on report items contained in page headers and footers. I've done extensive googling to see if there is a solution, but everything seems to involve writing custom functions. I can't believe it would be THAT hard to simply sum up a group calculation in an outer group!

Any and all help would be greatly appreciated!!

Thanks!

You are summing the report item (object). Try wrapping your IIF() statement in a SUM() which should provide the SUM() of those hours for the group. — D.S.

Strawberryshrub Strawberryshrub · Accepted Answer · 2018-11-28T05:37:39

You can add the scope the your Sum() expression to reverence the the whole dataset or a group:

'Returns the sum of the whole DataSet
=Sum(Fields!TestField.Value, "YourDataSetName")

'Returns the sum of a group
=Sum(Fields!TestField.Value, "YourGroupName")

You can also stack them:

=Sum(Sum(Fields!TestField.Value, "Level3GroupName"), "Level2GroupName")

Sum report item in a column group (SSRS 2008)

1 Answers