atrix components are great for visualizing data that you might normally export to Excel, or in a Pivot Table (year-over-year analysis is a good example). But if an analyst wants to view—for example—sales of a Month year-over-year, then some custom sorting has to be added so the months line up as expected. In this article I will cover the question that drives us, Neo. It’s the question that brought you here. You know the question, just as I did. Specifically, how do you get custom aggregates on a matrix report? That, and a few other advanced reporting techniques are covered so you won’t be so quick to grab for that blue pill and escape from working with a matrix component.
I remember the first time I used a Matrix component in SQL Server Reporting Services (SSRS). I right-clicked the detail row, selected Subtotal and was amazed that RS “knew” that I wanted to sum the values of the rows. Without any need to specify a formula, I had aggregates with a simple mouse click. That amazement wore off soon after I realized just how difficult it might be to generate aggregates based on values other than a standard SUM.
Covered here are six advanced techniques for building more robust Matrix-style reports in SQL Server Reporting Services,
- Dynamic Dataset Creation
- Query Based Dynamic Grouping
- Dynamic Column Names
- Cells in Cells
- Custom Matrix Aggregates
- Custom Graph Colors
All of the necessary steps to build a fully functional report are included, which illustrates:
- Key concepts for dynamic dataset creation
- Custom matrix aggregates
- Custom chart coloring
- Dynamic query-based grouping with workarounds explained with the ‘textbox-inside-a rectangle inside-a-textbox’ technique.