Advanced Matrix Reporting Techniques

David Leibowitz wrote a great article on some of the more advanced issues when creating Martix reports. View the article here.

The following six points are discussed in the article:

1. Dynamic dataset creation – Rather than provide a simple select statement or reference to a stored procedure, you’ll be able to dynamically modify the dataset source code at runtime.
2. Query-based dynamic grouping – My last article showed you how to dynamically change grouping levels on your report output (the layout tab). But those were based upon a static list of values in the Parameters collection. This time, we’ll use a query to illustrate that you can provide dynamic grouping based upon a dynamically changing dataset.
3. Dynamic column names – Keeping in spirit with the whole dynamic nature of this article, we’ll change the actual column headings depending upon the type of data our user has selected.
4. The cells-in-cells technique – Otherwise known as the "rectangle inside a textbox" technique, this helps us achieve a bit more usability with the somewhat limited framework of the SSRS Matrix control by allowing us to add as many subcolumns within a column as we’d like
5. Custom matrix aggregates – The heart of this article, and probably the very reason you are still reading. Going beyond the standard SUM, we’ll use the powerful inspection expression InScope() to provide nearly limitless calculations.
6. Custom chart coloring – We’ll add some data visualization to the report, but we’ll modify the chart coloring at runtime.