0

SSRS matrix table is a great way to generate dynamic fields as long as values exist.

However, is there a way to "always" show these dynamic fields even if a value doesn't exist for them? The report field locations varies based on data availability and users have to add missing columns in Excel manually.

Dynamic fields go from 3 to up to 30 (at least for now based on run by values). Adding these values manually would make the report hard to maintain.

NonProgrammer
  • 1,337
  • 2
  • 23
  • 53

2 Answers2

3

The way I have handled for this is in the SQL. I build a table of all the values I will always want, I cross join that table to my final output table and update/insert values where they need to exist. That way I guarantee the rows, and eventually columns in the matrix, exists even if they end up being null.

Does that make sense?

Jesse
  • 865
  • 10
  • 18
  • 1
    `LEFT JOIN` works well for this. You can also `UNION` distinct values to the results if you want to avoid issues with records not fitting into predefined combinations. – StevenWhite Jun 13 '17 at 21:12
  • This makes sense. Very ideal solution. I'd rather maintain it in SQL than in SSRS. – NonProgrammer Jun 14 '17 at 13:41
1

Jesse's solution is a good one, but if for whatever reason you can't or prefer not to change the SQL you can do it in SSRS by forcing a blank value in the cell with a expression like this:

=iif(IsNothing(Fields!.xxx.Value)," ",Fields!.xxx.Value)
Jayvee
  • 10,670
  • 3
  • 29
  • 40