1

I have a main table (Table A). It has separate many-to-many relationships to two other tables (Table B and Table C).

I would like to create an SSRS report that has one row per record in Table A, but then displays a sub-table for each Table B and Table C in one of the columns.

I believe that I can do this by creating a sub-report for Table B and Table C, by passing the Table A ID to each of the two sub-reports. However, I'm wondering if this is possible to do without creating sub-reports.

Below is an example of the data structure and what I would like the report to look like.

Data Structure

Desired report format

kralco626
  • 8,456
  • 38
  • 112
  • 169
  • You might be able to pull this off if you are able to get all of the data into a single dataset. The main issue with nesting tablixes inside other tablixes is that the dataset usually doesn't match. – Steve-o169 Nov 27 '19 at 17:20
  • So, I can get this all in the same dataset, but the data doesn't exactly make sense. If the record in Table A relates to 2 records in Table B and 3 records in Table C then my full dataset provides 6 records. Which I guess wouldn't technically be a problem if I could figure out how to group them properly, which I can't. If we omitted table B or table C, then this works, but with the two seperate many-to-many, I can't get the grouping right to display the data. Anything I try has extra rows, because of the 2x3=6 problem... – kralco626 Nov 27 '19 at 17:25
  • So if I'm visualizing your data correctly, there are 6 rows -- some should have `NULL` values for `C ID` and others for `B ID`... It may be possible to filter into the sub tables based on that. For sub Table B - `IIF(IsNothing(Fields!C_ID.Value), Nothing, Fields!B_ID.Value)` or something to that effect. Or just hide rows where `C_ID` is `NULL`. – Steve-o169 Nov 29 '19 at 13:40

1 Answers1

0

This is a little long winded as I didn't have any sample data to work with. I created come sample data as follows.

CREATE TABLE TableA(aID int, a1 varchar(20), a2 varchar(20))
GO
CREATE TABLE TableB(bID int, b1 varchar(20), b2 varchar(20))
GO
CREATE TABLE TableC(cID int, c1 varchar(20), c2 varchar(20))
GO
INSERT INTO TableA VALUES (1,'Red', 'Green'), (2,'Blue', 'Purple')

INSERT INTO TableB VALUES (1,'Dave', 'Bob'), (1,'Geoff', 'Harry'), (2,'Jane', 'Mary'), (2,'Anne', 'Sue')

INSERT INTO TableC VALUES (1,'Dog', 'Cat'), (2,'Goat', 'Cow'), (2,'Sheep', 'Lamb'), (2,'Donkey', 'Horse'), (2,'Lizard', 'Frog')

I then created a query that joins the tables together, it includes a 'TableName' column to indicate which table it came from, we will use this to arrange the columns in a matrix. It also contains a row number for each row per table. The query looks like this.

SELECT 
    * 
    , RowN = ROW_NUMBER() OVER(PARTITION BY aID, TableName ORDER BY ID, FirstColumn, SecondColumn)
    FROM (
SELECT 
        a.*
        , 'Table B' as TableName
        , bID as ID, b1 as FirstColumn, b2 as SecondColumn
    FROM TableA a 
        JOIN TableB b on a.aID = b.bID
UNION ALL
SELECT 
        a.*
        , 'Table C' as TableName
        , c.*
    FROM TableA a 
        JOIN TableC c on a.aID = c.cID
    ) u

In the port designer, I added a matrix control. I set the row groups to group by aID and Rown and added a column group by TableName .

Finally I set the first three columns Hide duplicates property to DataSet1

The report design looks like this.. enter image description here

The final output looks like this...

enter image description here

It's not perfect but it should be enough to get you going.

Alan Schofield
  • 19,839
  • 3
  • 22
  • 35