RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Advanced Matrix Reporting Techniques

Learn how to build robust Matrix-style reports in SQL Server Reporting Services. These advanced reporting techniques make it easier to build a fully-functional report.

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.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date