Tallan's Technology Blog

Tallan's Top Technologists Share Their Thoughts on Today's Technology Challenges

SSRS Summing Problem Solved

Micael George

While working on an SSRS Report for a client, I ran into a problem surrounding the summing of a particular field in my dataset, called “TotalAmount”. It seemed that the SSRS Sum function was returning double the amount that I was expecting. This was a result of my view returning multiple rows for items that had different values for a particular field; in this case the field was named “AllocationPercentage”. The “AllocationPercentage” field was from a temp table that was joined to the view, which caused several rows for a single item. I knew the view was correct, but I wasn’t quite sure how to get the SSRS Sum function to only sum particular rows.

To get around the summing issue, a couple steps were taken to solve the problem.

1)      I added a column for the row number, which was partitioned by a unique key field.

Ex:

ROW_NUMBER() OVER (PARTITION BY BillableComponentId ORDER BY BillableComponentId) AS ‘RowNumber’

2)      An expression was added in the “Total” column of the report that only takes the first row’s value to sum and converts all other rows to zero

Ex:

=SUM(IIF(Fields!RowNumber.Value=1,Fields!TotalAmount.Value,0))

After doing the steps above, the “TotalAmount” summed correctly and the invoice returned expected results.

2 Comments. Leave new

IT GOT #ERROR ON ME

Thanks so much. Your post saved me a lot of time and thinking.

,ROW_NUMBER() OVER (PARTITION BY PO.POID ORDER BY PO.POID) AS RowNumber

,CASE
WHEN ROW_NUMBER() OVER (PARTITION BY PO.POID ORDER BY PO.POID)>1
THEN 0
ELSE PO.POAMOUNT
END AS PO_TOTAL

I then used as expression to sum up the PO_TOTALS as
=SUM(Fields!PO_TOTALS.Value)

Thanks so much

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>