Search code examples
reporting-servicesrdlcssrs-2012ssrs-tablix

Total by group value not included in data

Imagine the following data, which includes a column "Color". Possible values are Red, Blue and Yellow.

Other fields    Color   A  B  C
--------------------------------
   ...          Red     1  3  0
   ...          Red     0  1  6
   ...          Yellow  3  4  5

This is shown on a Tablix component. After it, I want to summarize by color, including all possible colors:

Color    A  B  C
-----------------
Red      1  4  6
Blue     0  0  0  <-- how to create this row?
Yellow   3  4  5

I insert a new Tablix component, group by Color, and hide the Details group, leaving the totals. But of course, that doesn't include colors missing in the data.

How can I do it?

Restrictions to potential solutions:

  • I can create another dataset with just the color names, if necessary.
  • I can't modify the original query (which retrieves A, B and C quantities).
  • I can't create a new dataset with a query joining a subquery with the color names and the one which retrieves the quantities. The latter is quite expensive and I'd rather not have the users wait twice the time...
  • If VB code is needed, it can only be in the Report Code.
Solution

  • You can do a query to return a list of all potential colours. Make your tablix link to that dataset, and group by the colour column. You can then do a lookupset() along the lines of lookupset(Colour, Colour, ThingToSummarise, "Dataset"). This will return an array/object of all the rows where the colours match. You will then need to use VB to aggregate it. See https://social.msdn.microsoft.com/Forums/sqlserver/en-US/609759d2-15e4-4837-86dd-30bb420db116/sum-array-values-returned-by-lookupset-ssrs-2008-r2 to sum it.