SSRS - How to total a row

Question

Report Month         Booked Month          Hours     Available Hours    Reported Hours
2015-12-01 00:00:00 2015-10-01 00:00:00     3.5       97                   172
2015-12-01 00:00:00 2015-10-01 00:00:00     0.75      97                   172
2015-12-01 00:00:00 2015-10-01 00:00:00     6.85      97                   172
2015-12-01 00:00:00 2015-10-01 00:00:00     3         97                   172
2015-11-01 00:00:00 2015-10-01 00:00:00     3.5       97                   185
2015-11-01 00:00:00 2015-10-01 00:00:00     0.75      97                   185
2015-11-01 00:00:00 2015-10-01 00:00:00     6.85      97                   185
2015-11-01 00:00:00 2015-10-01 00:00:00     3         97                   185

In my SSRS report i want to total the Reported Hours Column. This person booked 172 hours in December and 185 hours in November so his total for the year should be 185 + 172 = 357.

In my current report it adds together all the rows. How do i get it to just pick one row of reported hours per month?

Thanks

Hannover Fist Hannover Fist · Accepted Answer · 2016-03-31T20:49:57

There's not a set way to do this in SSRS.

Usually you would wrap your query in another query that uses ROW_NUMBER and group by months. Then only add the hours if the row_numnber = 1.

How do I use ROW_NUMBER()?

Unfortunately that's not always possible. I wrote some code to add up unique records based on another field. I made it to get check amounts for various vendors.

For the expression, you would use:

=Code.SUMVendors(Report_Month & "|" & Fields!Reported_Hours.Value)

Add the Visual BASIC to the CODE of the report:

FUNCTION SUMVendors(BYVAL ITEMS AS OBJECT()) AS DECIMAL
'Returns Sum of unique vendors. Use as Code.SUMVendors(Vendor & "|" & Amount)
IF ITEMS IS NOTHING THEN 
    RETURN NOTHING
    GOTO EXITSUB
END IF

DIM TEMP AS STRING
DIM VENDORS AS STRING = ""
DIM VENDOR AS STRING = ""
DIM VALUE AS DECIMAL 

FOR EACH ITEM AS OBJECT IN ITEMS

    TEMP = CONVERT.TOSTRING(ITEM)

    VENDOR = "|" & LEFT(TEMP, InStr(TEMP, "|"))

    VALUE = CDEC(MID(TEMP, InStr(TEMP, "|") + 1, LEN(TEMP)))

    IF InStr(VENDORS, VENDOR) = 0 Then  
        VENDORS = VENDORS + VENDOR 
        SUMVendors = SUMVendors + VALUE
    End If

NEXT

EXITSUB:
END FUNCTION

SSRS - How to total a row

1 Answers