Just a quick post on something that I recently had to remember how to do and thought would be useful to write down and share.
The Problem
I have a dataset which duplicates some values, something like this:
Here I’ve got two product lines that each have two narratives associated with them. I am required to SUM the price column for a totals rows in a table displaying the product lines, so that I get an output in my report something like this:
By default, SSRS will use a SUM expression for the ‘Price’ column (SUM(Fields!Price.Value)), but in this instance it will give the wrong answers (21.00, 41.98, 62.98) because the product lines are being doubled up by their narratives. Ordinarily you might break the narratives out into their own data set, but sometimes that is not possible. So how do I get the correct answer?
The Solution
The expressions in the ‘Price’ column should be set to:
=SUM(IIF(Fields!NarrativeNum.Value = 1, Fields!Price.Value, 0))
This expression checks for the value of NarrativeNum on the current row and only SUMs the Price value if NarrativeNum == 1, otherwise we substitute a 0. In effect, the SUM sees our dataset like this:
Summary
We’ve seen how we can use a combination of functions to prevent SSRS from counting duplicate values. This is a simple technique but can be very useful.
