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, including:

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