How to have a child group span across three columns?

Question

What I'm trying to do

In my report, I am trying to get some basic data in a tablix. In this tablix there is one main summary row and detail rows inside it. What I want to do is put the details in the child row but split into three columns.

For example my tablix looks like this right now

Row11| Row12| Row13 |
1    | 5    |   4   |
 | Column1 | Column2|
 |   1     |    4   |
 |   2     |    5   |
 |   3     |    6   |
2    | 20   |   25  | 
  Column1  Column2  |
 |   7     |   8    |
 |   9     |   5    |
 |   3     |   6    |

(This is just a demo table. The number of columns in my application is not necessarily this number and it should be irrelevant anyway)

How I want it to look like:

Row11| Row12| Row13 |
1    | 5    |   4   |
 | Column1 | Column2| Column1 | Column2| Column1 | Column2|
 |   1     |    4   |   2     |    5   |   3     |    6   |
2    | 20   |   25  | 
 | Column1 | Column2| Column1 | Column2| Column1 | Column2|
 |   7     |   8    |   9     |   5    |   3     |   6    |

I just want to split the detail table into three columns. I have tried various approaches but in vein.

What approaches have I tried?

1. Attaching a sub report method. I attached a sub report and divided the report into three separate tables and split the columns in this order. This works except that it is terribly slow when trying to get large amount of data. Really do not want to do this.
2. The method mentioned here. Did not work.
3. I have been experimenting with the SQL itself as well but SQL does not look like to be an issue here.
4. Tried with Matrix instead of tablix too trying to push my limits but did not succeed.

Side note:

If it matters I am using SSRS SDK for PHP and grabbing the PDFs from the Report Server and using Visual Studio to design the reports.

This seems such a simple thing but I am stuck with this. Has anybody in a situation like this before?

Please let me know if you need more clarifications.

Answer 1

For what it's worth (I see you have already accepted an answer), I think this could be done primarily within SQL if you wished.

Assuming your raw data looks like this:

/-------------------------------------------\
| Row11 | Row12 | Row13 | Column1 | Column2 |
|-------+-------+-------+---------+---------|
|   1   |   5   |   4   |    1    |    4    |
|   1   |   5   |   4   |    2    |    5    |
|   1   |   5   |   4   |    3    |    6    |
|   2   |  20   |   25  |    3    |    6    |
|   2   |  20   |   25  |    7    |    8    |
|   2   |  20   |   25  |    9    |    5    |
\-------------------------------------------/

Let's create demo data to illustrate:

CREATE TABLE data (
    Row11 INT,
    Row12 INT,
    Row13 INT,
    Column1 INT,
    Column2 INT
)

INSERT INTO data
SELECT 1,5,4,1,4
UNION
SELECT 1,5,4,2,5
UNION
SELECT 1,5,4,3,6
UNION
SELECT 2,20,25,7,8
UNION
SELECT 2,20,25,9,5
UNION
SELECT 2,20,25,3,6

You could aggregate each summary and detail row like this:

SELECT DISTINCT d.Row11, 
                d.Row12, 
                d.Row13,
                dfirst.Column1,
                dfirst.Column2,
                dsecond.Column1,
                dsecond.Column2,
                dthird.Column1,
                dthird.Column2
FROM data d
CROSS APPLY
(
    SELECT TOP 1 Column1, Column2
    FROM data d1
    WHERE d1.Row11 = d.Row11 AND d1.Row12 = d.Row12 AND d1.Row13 = d.Row13
    ORDER BY 1,2
) dfirst
CROSS APPLY
(
    SELECT Column1, Column2
    FROM
    (
        SELECT Column1, Column2, ROW_NUMBER() OVER (ORDER BY Column1, Column2) AS rownumber
        FROM data d1
        WHERE d1.Row11 = d.Row11 AND d1.Row12 = d.Row12 AND d1.Row13 = d.Row13
    ) drows
    WHERE rownumber = 2
) dsecond
CROSS APPLY
(
    SELECT TOP 1 Column1, Column2
    FROM data d1
    WHERE d1.Row11 = d.Row11 AND d1.Row12 = d.Row12 AND d1.Row13 = d.Row13
    ORDER BY 1 DESC,2 DESC
) dthird

Which gives the results:

/-----------------------------------------------------------------------------------\
| Row11 | Row12 | Row13 | Column1 | Column2 | Column1 | Column2 | Column1 | Column2 |
|-------+-------+-------+---------+---------+---------+---------+---------+---------|
|   1   |   5   |   4   |    1    |    4    |    2    |    5    |    3    |    6    |
|   2   |  20   |  25   |    3    |    6    |    7    |    8    |    9    |    5    |
\-----------------------------------------------------------------------------------/

It should then be relatively trivial to group this in the table in your SSRS report by Row11, Row12, Row13, placing the values for Row11, Row12 and Row13 into the Group Header row and the values for all 6 Column1 and Column2 values into the detail row:

Design:

Results:

Note: this only works for 3 (or fewer) pairs of Column1/Column2 values per tuple of Row11, Row12, Row13 values.

Answer 2

I had to tackle a same problem once and the way I did it is by "Inserting a TABLIX inside a TABLIX". I believe if you follow the link below that shuold resolve what you are looking for:

http://www.sqlcircuit.com/2012/03/ssrs-how-to-show-tablix-inside-tablix.html

Waht I have additionaly done in my report to increase the width of the nested tablix so that it does not affect the width on the main tablix is: 1) On the the row above the insertted tablix I have created a column and kept it empty and merged the cell below it where the nested tablix should be. 2) now you can increase the size of the empty column (make the borders invisible) to what ever the width of the inserted TABLIX you would like.

Hope this helped.

Answer 3

Create three detail tables, adjust which rows get shown in each, and put them in a List.

This solution works on the assumption that your raw data looks something like this:

Add a table report item and add the Column1 and Column2 data to it, leaving the grouping as just the details. Right-click the detail row, and go to Row Visibility.

Switch this to 'Show or hide based on an expression', and add this expression:

=IIF(RowNumber("tblFirstColumn") MOD 3 = 1, False, True)

This will make only the first, fourth, seventh etc. record show in that table. Paste two copies of this table next to the first, and adjust the row visibility expression on each:

=IIF(RowNumber("tblSecondColumn") MOD 3 = 2, False, True)
=IIF(RowNumber("tblThirdColumn")  MOD 3 = 0, False, True)

Next add a List item. Change the row grouping of the list to group by Row11, add each row field to the top of this list (as text boxes or a non-grouped table), and move the three detail tables into the bottom of the list.

This should perform better than using subreports. I understand that when using subreports the datasets will be queried with every instance of that subreport. With all the design in one report, the queries should only run once.

Answer 4

Method 1: main tablix = three columns with TWO detail rows. In the 2nd detail row merge the three columns together. Create a new tablix for the detail information and put it inside the merged detail cell.

Method 2: main tablix = six columns and two detail rows. In the 1st detail row merge cells 1/2, 3/4, and 5/6 together.