How to span row across all column groups in SSRS

Question

I have a report with a very specific format that I'm having trouble implementing in SSRS (2016). It's supposed to have a row that spans across all column groups. Here is a rough mockup I made in Word to demonstrate the desired layout:

So the basic structure is it's a list of projects, and each project has one or more transactions, and each transaction has funding amounts for a set of fiscal years.

The part I'm having trouble with is the project title can be long, and they want it to span the entire row.

I was able to get most of the report structure implemented in SSRS using a matrix as follows:

So there is a parent row group for each project, then a child row group for each transaction. There is a single column group for the Fiscal Year, and the detail cell contains the Amount.

The output of the above report appears like this:

So it's mostly correct, but the project title does not span the entire row. What I'd like to be able to do is to merge the bolded [ProjectTitle] cell with the empty cell to the right, but when I select both cells, there is no option to merge them. From what I understand, you can't merge cells across a column group in SSRS?

Is there a workaround to achieve the desired structure?

Here is the SQL query I was using for the above sample:

SELECT * FROM (VALUES
  (1, 'Project Title which can be really long', 1, 'Transaction 1', 2019, 2.25),
  (1, 'Project Title which can be really long', 1, 'Transaction 1', 2020, 7.25),
  (1, 'Project Title which can be really long', 1, 'Transaction 1', 2021, -2.25),
  (1, 'Project Title which can be really long', 1, 'Transaction 1', 2022, 0.55),
  (1, 'Project Title which can be really long', 1, 'Transaction 1', 2023, 6.25),
  (1, 'Project Title which can be really long', 1, 'Transaction 1', 2024, 4.5),
  (1, 'Project Title which can be really long', 1, 'Transaction 1', 2025, 1.25),
  (1, 'Project Title which can be really long', 1, 'Transaction 1', 2026, 1.5),
  (1, 'Project Title which can be really long', 2, 'Transaction 2', 2019, 1.75),
  (1, 'Project Title which can be really long', 2, 'Transaction 2', 2022, 2.25),
  (1, 'Project Title which can be really long', 2, 'Transaction 2', 2024, 0.75),
  (1, 'Project Title which can be really long', 2, 'Transaction 2', 2025, 2.55),
  (1, 'Project Title which can be really long', 2, 'Transaction 2', 2026, 1.5),
  (1, 'Project Title which can be really long', 3, 'Transaction 3', 2020, 2.35),
  (1, 'Project Title which can be really long', 3, 'Transaction 3', 2021, 11.5),
  (1, 'Project Title which can be really long', 3, 'Transaction 3', 2022, -0.55),
  (1, 'Project Title which can be really long', 3, 'Transaction 3', 2023, -0.75),
  (1, 'Project Title which can be really long', 3, 'Transaction 3', 2024, 1.05),
  (2, 'Another Project', 1, 'Transaction 1', 2019, 2.86),
  (2, 'Another Project', 1, 'Transaction 1', 2020, 1.25),
  (2, 'Another Project', 1, 'Transaction 1', 2021, 0.75),
  (2, 'Another Project', 1, 'Transaction 1', 2022, -0.55),
  (2, 'Another Project', 1, 'Transaction 1', 2023, 2.05),
  (2, 'Another Project', 1, 'Transaction 1', 2024, -3.28),
  (2, 'Another Project', 1, 'Transaction 1', 2025, 0.75),
  (2, 'Another Project', 1, 'Transaction 1', 2026, -0.5),
  (2, 'Another Project', 2, 'Transaction 2', 2019, 0.25),
  (2, 'Another Project', 2, 'Transaction 2', 2022, 1.25),
  (2, 'Another Project', 2, 'Transaction 2', 2024, 2.25),
  (2, 'Another Project', 2, 'Transaction 2', 2025, -1.55),
  (2, 'Another Project', 2, 'Transaction 2', 2026, 2.5)
) AS Funding(ProjectId, ProjectTitle, TransactionId, TransactionTitle, FiscalYear, Amount);

Alan Schofield Alan Schofield · Accepted Answer · 2019-06-15T23:24:52

I'm not sure there is a way round this exactly. As you already stated, you cannot merge across these cells as they are not in the same scope.

The way I would go about this, and it will NOT give you exactly what you wanted but close, is to create a subreport that takes a ProjectID as a single value parameter.

The dataset query would be something similar to this..

SELECT * FROM myTable WHERE ProjectId = @ProjectID

The sub report I created just moved the title to a text box above the matrix. The design looks like this...

The main report just has a dataset with a distinct list of ProjectIDs (in this sample the dataset only contains two values, 1 and 2).

I added a list control, set the dataset to point to the only dataset.

In the only 'cell' I inserted a subreport, pointed it at the subreport created earlier and set the parameter to be ProjectID from the dataset.

The main report design looks like this...

The final rendered output looks like this...

How to span row across all column groups in SSRS

1 Answers