0

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. BudgetVsActual

Dijkgraaf
  • 11,049
  • 17
  • 42
  • 54
BBackSoon
  • 1
  • 1
  • Behaviour as expected for a Matrix report. Is there a limit to the number of projects? If there was you could just create that number of data-sets for the max number of projects the report is to handle. And just copy and paste your matrix and attach each one to a different data set. That way you won't get columns that belong to other projects. Either that or your row needs to have the matrix embeded in it (so it does the matrix per project row) – Dijkgraaf Jun 22 '17 at 02:33
  • If you want each project shown separately, I suggest you keep your single project report, this will be your subreport. Then create another report with a simple one cell table. This table should point to a dataset that contains a list of all projects the user selected. In the single cell, add your subreport and pass the projectid as a parameter. Essentially you'll run the main report will run the subreport 'n' number of times but without the user having to run them individually. If you need more help let me know and I'll post a full detailed answer. – Alan Schofield Jun 22 '17 at 14:18
  • Had some trouble with the sub report, but I did get a Linked Stub report to work. Is that the same thing? I have the full list of projects and a link shows the report for each one at a time. – BBackSoon Jun 22 '17 at 21:46

1 Answers1

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

enter image description here

enter image description here

niktrs
  • 9,858
  • 1
  • 30
  • 30
  • I think that is what I want to do but so far have not been able to find a more detailed explanation. I know this sounds weird, but one of the biggest things I needed was the Key Words for what I was wanting to do. I do have a Matrix, but it is stand along, are you suggesting I use a sub report or is this something different? – BBackSoon Jun 22 '17 at 21:51
  • 1
    Its not a subreport. It's a table with a single row and two cells. The row is grouped by the project field. The right cell of the table contains a matrix instead of some field value. Due to this layout, for each project a new matrix is created with only the project data - the row group acts as a filter for the contained matrix, so it returns only current project columns. – niktrs Jun 23 '17 at 01:55
  • That's awesome, *exactly* what I was looking for! – Mathieu Guindon Sep 08 '17 at 14:26