Let’s imagine we’re producing some simple reports and we only can track the number of pies returned across a whole despatch and not for each despatch type.
We have a single query whose results breakdown each DespatchID into the various methods – Premier, Delayed and Urgent. Each DespatchID may or may not have any of the methods. We also have the number of PiesSold per DespatchType but the number of PiesReturned is a value over the whole DespatchID.
In reporting services we use a group in Reporting Services to get the value (for example in our data we are displaying PiesReturned) for each DespatchID.(i.e. DespatchID 744 has 7 pies returned)
Then we need to calculate the total of the group.
We cannot simply sum the PiesReturned value as this would give us duplicates and the incorrect answer in the example of 46. We cannot sum the reporting services group as it goes out of scope
There is no SUM DISTINCT available in Reporting Services 2005 so we can’t get the distinct value that way.
Since the query may not return a particular Despatch Type we cannot use that as a filter.
The solution found was to add a column of the row number within a windowed set partitioned by the DespatchType like this
ROW_NUMBER() OVER (PARTITION BY DespatchID ORDER BY DespatchID) AS ‘RowNumber’
This gives us the amended query output below:
Then in the reports’ footer total column we put an expression that only takes the first row’s value to sum and converts all other rows to zero in that windowed set.
=SUM(IIF(Fields!RowNumber.Value=1,Fields!PiesReturned.Value,0))
The end result is we end up with the correct total of 26 pies returned
Sum of a group in Reporting Services