-1

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.
angus
  • 2,305
  • 1
  • 15
  • 22
  • Does your dataset include the rows for Blue? – BishNaboB Jan 19 '17 at 09:45
  • @BishNaboB The dataset doesn't include the rows for Blue, that's why I have trouble including it in the summary. – angus Jan 19 '17 at 09:57
  • But it *could* include them and another color could be missing, depending on the parameters, of course. I don't know exactly which colors are going to be missing when I run the report. – angus Jan 19 '17 at 09:59
  • 1
    My preferred method for this would be to generate the colours in the SQL. So generate a list and left join to your actual data so that "Blue" (or whatever colours) are there with nulls for the rest of the columns. – BishNaboB Jan 19 '17 at 10:17
  • @BishNaboB Unfortunately I can't do that (I edited the question to make it more clear). The solution needs to be mainly inside the report. – angus Jan 19 '17 at 10:41
  • What exactly is the reason to not do a join to a list of colours? – BishNaboB Jan 19 '17 at 10:55
  • @BishNaboB - It's explained. **(a)** I can't modify the original query (it is ran and the resultset is bound to the report by means I don't control); **(b)** if I were to create a new query, duplicating the original, adding a join with the link of colors, the report would take twice as long. It's an expensive query. – angus Jan 19 '17 at 11:14
  • Can we have a look at the original query. I shouldn't have thought that adding a left outer join would cause it to run twice as long... – Paul Bambury Jan 19 '17 at 11:37
  • In that case the original query would run for the first Tablix and then the modified one for the summary. Can we just forget about that approach? Isn't there a client-side solution? – angus Jan 19 '17 at 11:41
  • 1
    @angus the new left joined query would replace the whole query. That way you get a row per colour per result, which can then be aggregated in the tablix to get the summary. There's not a client side approach unless you do a new query of just colours and do lookups / aggregated lookupsets for each column to get your summary information – BishNaboB Jan 19 '17 at 12:38
  • @BishNaboB "do a new query of just colours and do lookups / aggregated lookupsets for each column". That might be the solution. Can you elaborate? – angus Jan 19 '17 at 13:09
  • 1
    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://msdn.microsoft.com/en-GB/library/ee240819.aspx for lookupset, 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. – BishNaboB Jan 19 '17 at 13:13
  • 1
    This is a slower and clunkier way though, significantly worse than the original suggested. – BishNaboB Jan 19 '17 at 13:13
  • @BishNaboB Believe me, if I could have done it in the query, this question wouldn't exist. And if I needed help writing the query, this question would have different tags. Your solution with LookupSet works; do you want to put it as an answer? – angus Jan 19 '17 at 14:30
  • @angus Done and done! – BishNaboB Jan 19 '17 at 18:31

1 Answers1

1

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.

BishNaboB
  • 1,047
  • 1
  • 12
  • 25