How to group this SSRS report?

Question

I have an SSRS report. It looks like this:

Its definition looks like this:

As you can see it's just a bunch of numbers in different columns A to G. Those numbers were grouped by A then B then C. For each row in the report, column D,E,F,G are my detailed data. The problem is, for each group (I mean grouped by A,B,C), I need all rows for the D&E column. For F&G column, I don't want the duplicates. So for example, for the very first group, we can see for column F&G there are duplicates, the data 20, 30 appeared twice. Essentially what I want is something like this (note the duplicates were removed compared to the aforementioned report result):

Take a look at the data with yellow background, that's the part where I want duplicates removed. I don't know how to implement this in SSRS. Hope someone can point me to the right direction. You can download the report I'm testing at https://www.dropbox.com/s/qxvloaeuj31m0mj/Report1.rdl?dl=1. This simple report was created to isolate the problem I met in a more complex report. I can't change the logic and have to find a way to implement this. Thanks in advance.

Answer 1

Here is my go at this. I am making an assumption that what you want is mostly like the last screenshot you included in the question.

I pulled down the RDL you made available. Thanks for that! It can make this sort of thing that much easier.

The SQL in the report is mock data, which is good for what we are trying. I assume that the stored procedure that you are sourcing the data from now gives you something similar. I made a comment about getting the results into a temp table to work with, changing/shaping the data, and then returning it to the report. That is the tact I am going with in this answer.

Here is the updated SQL from the RDL, with a few line left out for brevity.

SELECT *
INTO #Report
FROM (VALUES(1,4,1,1,10,20,30),
(1,4,1,2,11,20,30),
…
(22,666,7,14,23,150,6)
) AS t(A, B, C, D,E,F,G);

SELECT A.A, A.B, A.C, A.D, A.E  -- selecting columns A thru E
FROM #Report A 

UNION ALL

SELECT DISTINCT B.A, B.B, B.C, B.F, B.G  -- union in columns F and G
FROM #Report B
ORDER BY 1, 2, 3

DROP TABLE #Report

All this really does is use a SELECT INTO to create a temp table with the original results. Then, I split the results, and UNION it all back together with only the columns I need stacked on top of each other; also using a DISTINCT where you want to get rid of duplication.

To use this in the RDL you provided, you will have to remove the F and G columns from the Tablix, and replace the SQL with this. This is what the Tablix would look like in design mode.

Now, you mentioned that there is a stored procedure that you cannot change. To get results from a stored procedure into a temp table you need to do 2 things.

First, create the temp table in the same shape at the results from the procedure. The tables doesn’t really have to be a temp table; you can make it a non-temp table and drop it later, too. In the temp table case, the DDL would look something like this:

CREATE TABLE #Report (A int, B int, C int, D int, E int, F int, G int)

Then you insert the results of the stored procedure into that temp table.

INSERT INTO #Report
EXEC usp_MyStoredProcedure

And no, there is no simple SELECT INTO option with a stored procedure, in case you were wondering. A lot of people wish there was.

So, if this is the way you end up going, here is what the SQL for the report could be like.

CREATE TABLE #Report (A int, B int, C int, D int, E int, F int, G int);

INSERT INTO #Report
EXEC usp_MyStoredProcedure;

SELECT A.A, A.B, A.C, A.D, A.E  -- grabbing columns A thru E
FROM #Report A 
UNION ALL
SELECT DISTINCT B.A, B.B, B.C, B.F, B.G  -- union in columns F and G
FROM #Report B
ORDER BY 1, 2, 3

This is what the report would look like in preview mode:

Hope this helps you out.