adding a multi row tablix to an existing matrix within matrix report

Question

hi we run 2014 enterprise. i've inherited a matrix within matrix report that i've anonymized and dumbed down big time in the attached ddl and mock up. the 3 data regions you see to the left of the mockup are actually tablix's inside a matrix that breaks on camp name and create date. there is a one to one between session id and the combo of camp name and create date.

currently, the inner tablix's are of a fixed size. so really the columns in one row of the select on matrixwithinmatrix map one to one with the inner tablix cells for "this break".

i'm faced with adding a true matrix (shown to the right of "i want to add this to the report". from what i remember, the scope of an inner matrix cant be different than the scope of the outer matrix. so i'd better not think about a different dataset. i'm thinking about concatenating the date, max count pairs into the single row currently extracted for any one session and somehow splitting it into the new tablix. i'd hate to redesign the current report's matrix within atrix approach. does the community have any ideas?

USE [research]  
GO  
  
SET ANSI_NULLS ON  
GO  
  
SET QUOTED_IDENTIFIER ON  
GO  
--drop table matrixwithinmatrix  
CREATE TABLE [dbo].[matrixwithinmatrix](  
	[Id] [int] IDENTITY(1,1) NOT NULL,  
	SessionId int,  
	CampId Int,  
	CampName varchar(100),  
	CreateDate datetime2(7),   
	CamperCount_type1 int,  
	CamperFees_type1 decimal,  
	CamperCount_type2 int,  
	CamperFees_type2 decimal,  
	CounselorCount_type3 int,  
	CounselorSalaries_type3 decimal,  
	CounselorCount_type4 int,  
	CounselorSalaries_type4 decimal,  
 CONSTRAINT [PK_matrixwithinmatrix] PRIMARY KEY CLUSTERED   
(  
	[Id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]  
GO  
insert matrixwithinmatrix  
select 1,1,'Rolling Hills','1/1/2021',5,5000,10,10000,  
                                      2,1000,2,10001  
union all  
  
									  
select 2,1,'Rolling Hills','2/1/2021',6,6000,11,11000,  
                                      3,1500,3,1501  
union all  
select 3,2,'no hills','3/1/2021',7,7000,12,12000,  
                                 4,2000,4,2001  
union all  
select 4,2,'no hills','4/1/2021',8,8000,13,13000,  
                                 5,2500,5,2501  
  
select * from matrixwithinmatrix order by campname,createdate--<---think of this as the current report dataset  
  
  
  
  
USE [research]  
GO  
--drop table matrixwithinmatrixboatschedule  
SET ANSI_NULLS ON  
GO  
  
SET QUOTED_IDENTIFIER ON  
GO  
--drop table matrixwithinmatrix  
CREATE TABLE [dbo].[matrixwithinmatrixboatschedule](  
	[Id] [int] IDENTITY(1,1) NOT NULL,  
	SessionId int,  
	starttime datetime2(7),  
	endtime datetime2(7),  
	maxcampers int,  
 CONSTRAINT [PK_matrixwithinmatrixboatschedule] PRIMARY KEY CLUSTERED   
(  
	[Id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]  
  
insert matrixwithinmatrixboatschedule  
select 1,'2021-01-02 10:15:00.000','2021-01-02 10:45:00.000',5  
union all  
select 1,'2021-01-02 11:15:00.000','2021-01-02 11:45:00.000',6  
union all  
select 1,'2021-01-03 11:15:00.000','2021-01-03 11:45:00.000',9  
union all  
select 2,'2021-01-02 10:15:00.000','2021-01-02 10:45:00.000',7  
union all  
select 2,'2021-01-02 11:15:00.000','2021-01-02 11:45:00.000',8  
union all  
select 3,'2021-01-02 10:15:00.000','2021-01-02 10:45:00.000',9  
union all  
select 3,'2021-01-02 11:15:00.000','2021-01-02 11:45:00.000',10  
union all  
select 4,'2021-01-02 10:15:00.000','2021-01-02 10:45:00.000',11  
union all  
select 4,'2021-01-02 11:15:00.000','2021-01-02 11:45:00.000',12  
  
select * from matrixwithinmatrixboatschedule order by sessionid,starttime  
  
  
select sessionid,cast(starttime as date) starttime,sum(maxcampers) maxcampers--<--think of this as the desired but probably not legal dataset   
from matrixwithinmatrixboatschedule  
group by sessionid,cast(starttime as date)  
order by sessionid,starttime  
  

Answer 1

thx Joy. unfortunately what i've shown is all that can be shown because of confidentiality matters.

maybe "cascaded tablix" is a better term.

a good discussion on cascaded tablix's can be seen here http://www.sqlcircuit.com/2012/03/ssrs-how-to-show-tablix-inside-tablix.html. notice they are for situations where there is summary (like to the left in the excel image) info and detail (the new grid) desired in the same report.

i found a solution and feel more lucky than anything else that it worked. i did consider adding a single column to the original query with line feeds after each boat date and max pair but instead tried this...

1. I added a character(1) field to the end of the query called summary. It can have a Y or N value. the existing sources (one row per program) of grid info were given a Y value.
2. I added a union all to the query for the detail info. these records were given an N in the summary column, contained the key info and required the addition of a boatdate and boatmax column on both sides of the union all. on the summary side, the 2 columns contained nulls.
3. I put a filter in the existing tablix's to read only records whose summary=Y
4. I added a tablix over the same dataset with a filter to read only summary=N. its own sort is on boat date.

so essentially the single dataset evolved into this...

select *,'Y' summary,cast(null as date) boatdate,cast(null as int) boatmax
from matrixwithinmatrix
union all
select b.SessionId ,
b.CampId ,
b.CampName ,
b.CreateDate ,
null,null,null,null,null,null,null,null,
'N',
cast(starttime as date),
sum(maxcampers)
from matrixwithinmatrixboatschedule a
join matrixwithinmatrix b
on...
group by b.SessionId ,
b.CampId ,
b.CampName ,
b.CreateDate ,
cast(starttime as date)
order by campname,createdate

Answer 2

i'm not sure if this should be in a comment or answer but attached here is an image of the 2 selects, one being the original dataset, and the other being the desired new dataset. session id can easily be used to join these 2 tables, i'm more concerned about a differently scoped dataset with an outer dataset.

Answer 3

Hi @db042190 ,
What is "matrix within matrix"? What does your report in VS or ReportBuilder look like at this time? Please take a screenshot to show it.
Please show a complete schematic of the effect you want.
The query statement you gave is indeed as shown in the image you attached after execution, but the Dataset cannot generate a matrix in VS or ReportBuilder.
Best Regards,
Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.