Making Dynamic and Static columns play nicely in Matrix in SSRS 2005

Question

I have a report that shows financial data by year along with a few other bits of static data: every time I try to put in a Column group, it does either above or below the static data:

FiscalYear
StaticData1 | StaticData2 | StaticData3 | StaticData4

Or: StaticData1 | StaticData2 | StaticData3 | StaticData4
FiscalYear

What I want is that I get the static data and then the dynamic data (which may be 1 or 20 additional columns) right beside it with the corresponding data below How do I get the report to look something like: StaticData1 | StaticData2 | StaticData3 | StaticData4 | FiscalYear | FiscalYear+1 ...

Thanks much!

Answer 1

See Dynamic Grouping to learn how to make your matrix groupings dynamic based on report parameters.

Wait a second. I think that doesn't answer your question.

It sounds like you're asking how to make more columns appear to the right of your static data at the same level of grouping. The only thing I can recommend is to modify your recordset to return a row for each final matrix cell. This usually involves a partial unpivot.

You have something like:

Row1 Static1 Static2 Year Row1 Static1 Static2 Year

instead, you need this:

Row1 'Static1' Static1Value
Row1 'Static2' Static1Value
Row1 'Year' YearValue
Row2 'Static1' Static1Value
Row2 'Static2 Static2Value
Row2 'Year' YearValue

I hope that makes sense. I don't have a lot of time to go into much detail.

One other thing that might help you is to experiment with dropping a Rectangle into one of the matrix detail cells. Once you have that, you can drop as many text boxes into the Rectangle as you want and position them as desired. This is one of the ways to get extra values to show even though you only want one grouping level.