I have data that is grouped by plan within each client.
I want to end up with something that looks like this:
I was able to create the client group, but I can’t figure out how to create the second level (plan) grouping. I feel like I am missing something easy, but it’s just not working… Does it have to use a subreport or can it be done in a single report?
I tried using nested Tablixes (Tablixi?) but then I get a complaint that the detail row can’t contain anything except static members. Using a single Tablix, the best I can do is this:
Where it repeats the client name for each plan within the plan group.
Here is some sample data for testing (as requested):
CREATE TABLE #t (ClientId INT, ClientName VARCHAR(250), PlanId INT, PlanName VARCHAR(250), EntryDate DATE, [User] VARCHAR(50), Invoice VARCHAR(15), [Value] DECIMAL(8,2))
INSERT INTO #t (ClientId, ClientName, PlanId, PlanName, EntryDate, [User], Invoice, [Value])
VALUES
(1,'Client One',10,'Plan Ten','3/1/24','Bob',NULL,123),
(1,'Client One',10,'Plan Ten','3/5/24','Frank',NULL,124),
(1,'Client One',10,'Plan Ten','3/28/24','Bob','1123',125),
(1,'Client One',11,'Plan Eleven','3/1/24','Joe',NULL,123),
(1,'Client One',11,'Plan Eleven','3/6/24','Joe',NULL,122),
(1,'Client One',11,'Plan Eleven','3/8/24','Frank',NULL,124),
(1,'Client One',11,'Plan Eleven','3/28/24','Joe','1124',128),
(1,'Client One',12,'Plan Twelve','3/2/24','Anna',NULL,125),
(1,'Client One',12,'Plan Twelve','3/8/24','Frank',NULL,126),
(1,'Client One',12,'Plan Twelve','3/28/24','Anna','1126',127),
(2,'Client Two',13,'Plan Thirteen','3/1/24','Frank','1125',123),
(2,'Client Two',14,'Plan Fourteen','3/1/24','Joe',NULL,123),
(2,'Client Two',14,'Plan Fourteen','3/6/24','Joe',NULL,122),
(2,'Client Two',14,'Plan Fourteen','3/8/24','Marie',NULL,124),
(2,'Client Two',14,'Plan Fourteen','3/28/24','Joe',1127,128),
(2,'Client Two',15,'Plan Fifteen','3/2/24','Anna',NULL,125),
(2,'Client Two',15,'Plan Fifteen','3/8/24','Marie',NULL,126),
(2,'Client Two',15,'Plan Fifteen','3/28/24','Anna',1128,127)
4
You can’t do exactly what you want but you can get close. The problem is the Client Name that you have spanning columns. You cannot merge text boxes from different row groups so the Client name would have to appear directly above the plan name, aligned horizontally the same.
Start by adding a table and then add all the details columns that you need (Date; Value; User; Invoice). Then add a parent row group to the default details row group, grouped by plan id, select ‘Add header’ when you do this. Then repeat by adding a parent group to the plan id row group, this time selecting ‘client id’ and again selecting the header option.
Then choose the the client name and plan name in the respective textboxes, and merge with the columns to the right of each.
Here’s a two minute GIF showing you how I did this (I skipped the dataset query part but it’s just the data you supplied)
For some reason SO won’t let me upload this (even though it’s only 1.2MB) so here’s a link to it on my OneDrive shares
https://1drv.ms/i/s!Al1Kq21dFT1ilthpIqx79ObdBl4Lbg?e=SqETP6
1