SSRS Dynamic Columns that change on Row Groups

Question

First Question: let me know how I messed this up. I am trying to make a Budget vs Actual report. I have a SQL Stored Proc that returns data based on a few parameters, such as Customer and Project Number. The data comes out as Invoice data by date, and may have multiple lines for each Service type and I have the budgeted amount for that service type at each line. I then take the data and using the SSRS Report Builder Tablix pivot the data. I then display the Date and Invoice number on the rows, and use Dynamic Columns for each of the Service Types. Lots of totals and the Budgeted amounts later I get a report.

My problem is the users want to run this report for the same Customer for more than one Project. It works for one Project but when I try two project numbers the Report shows all columns. For instance one project has 10 Service Types and the second project has 15, with only a couple of repeats. But my report for each Project shows 23 columns when it should be just the number of columns for that study. The report page breaks on Project just fine, but it doesn’t seem to group the Columns like I need.

In the picture the 4th column belongs to the Previous project but still shows up as to 8 or so others.

Answer 1

You can use nesting to achieve this output eg a matrix inside a table (or list)

The table(or list) will have a group (rowgroup for table) by project that will contain the matrix with the data.

In the picture below the table has two cells, the left containing the group value (project) and the right the matrix with the data