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.
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
After doing the steps above, the “TotalAmount” summed correctly and the invoice returned expected results.