It’s T-SQL Tuesday time again! If you are not sure what T-SQL Tuesday is about then go and read this article written by Adam Machanic (Twitter | Blog). This month’s T-SQL Tuesday is brought to us by Jess Borland (Blog | @grrl_geek) and the topic is “Aggregation”. So let’s get started.
Matrix objects in SQL Server Reporting Services lend themselves to aggregation. With row and column groups prevalent in so many report requirements, it is inevitable that there will be subtotals at many different levels. Understanding Scope will enable you to refine your matrix object by applying different aggregate functions (or formatting options) to specific cells, or set of cells.
This is a post that I have been meaning to write for quite some time. In fact, ever since I did my original post on The InScope() Function and the key to formatting Matrix objects, I’ve been threatening to do it. This stems from all the comments and questions (via email) that I get relating to that post. Most of which are due to a lack of understanding of “scope”. So, while this may seem a trivial subject to a few of you, there are a lot of folks who just aren’t quite there yet. Also, this post is aimed at Reporting Services 2005 – I know that we are well into 2008 R2 by now, but I am aware that there are still many many companies out there who have yet to make that leap. Thankfully Reporting Services 2008 R2 deals with matrix subtotals in a much more user-friendly way.
Scope can be defined as (care of the Free Dictionary):
The area covered by a given activity or subject
For example: Applying this to a Matrix object, we can say that all the cells covered by a particular Column (or Column Group) header are in-scope of that particular Column (or Column Group) header.
I’ll use the matrix layout below as a template to attempt to explain this in a little more detail. This matrix has the following details:
- A row detail [group] named “Countries”
- A row group named “Regions”
- A subtotal on the row details (“Region [1/2] Subtotal”)
- A [Sub]total on the row group (“Grand Total (Region)”)
- A column detail [group] named “Quarters”
- A column group named “Years”
- A subtotal on column details (2009/2010 Subtotal)
- A [Sub]total on the column group (“Grand Total (Years)”)
Fig 1. Matrix object
For each of the figures below I have included a description of the scope that is being highlighted as well as an expression that can be used to generate the background colour for the cells that fall in-scope of the named group.
Fig 2. below shows the scope of the Row Details Group (“Countries”). All cells that fall within these rows (including the subtotals & grand totals) are within the scope of the Row Details Group.
Fig 2. Matrix object – Row Details (“Countries”) Scope
=IIF(InScope(“Countries”), “Green”, “White”)
Fig 3. below shows the scope of the Column Details Group (“Quarters”). All cells that fall within these columns (including the subtotals & grand totals) are within the scope of the Column Details Group.
Fig 3. Matrix object – Column Details (“Quarters”) Scope
=IIF(InScope(“Quarters”), “Green”, “White”)
Fig 4. below shows all cells that fall within the scope of both the Row & Column Detail Groups only.
Fig 4. Matrix object – Cells “in-scope” of both Row Details (“Countries”) & Column Details (“Quarters”)
=IIF(InScope(“Countries”), IIF(InScope(“Quarters”), “Green”, “White”), “White”)
Fig 5. below shows the scope of the Row Group (“Regions”). All cells falling within these rows are within the scope of the Row Group (“Regions”). Notice how this includes the Region 1/2 Subtotals – these fall outside of the scope of the Row Detail Group (“Countries”), see fig 2.
Fig 5. Matrix object – Row Group (“Regions”) Scope
=IIF(InScope(“Regions”), “Green”, “White”)
Fig 6. below shows the scope of the Column Group (“Years”). All cells falling within these columns are within the scope of the Column Group
(“Years”). Notice how this includes the 2009/2010 Subtotals – these fall outside of the scope of the Column Detail Group (“Quarters”), see fig 3.
Fig 6. Matrix object – Column Group (“Years”) Scope
=IIF(InScope(“Years”), “Green”, “White”)
Fig 7. below shows all the cells that fall outside of the scope of the Row Detail Group (“Countries”), but within the scope of the Row Group (“Regions”)
Fig 7. Matrix object – Cells “out-of-scope” of Row Details (“Countries”), but “in-scope” of Row Group (“Regions”)
=IIF(InScope(“Regions”), IIF(InScope(“Countries”), “White”, “Green”), “White”)
Fig 8. below shows all the cells that fall outside of the scope of the Column Details Group (“Quarters”), but within the scope of the Column Group (“Years”)
Fig 8. Matrix object – Cells “out-of-scope” of Column Details (“Quarters”), but “in-scope” of Column Group (“Years”)
=IIF(InScope(“Years”), IIF(InScope(“Quarters”), “White”, “Green”), “White”)
Fig 9. below shows all cells that fall outside of the scope of both the Row Details Group (“Countries”) and the Row Group (“Regions”)
Fig 9. Matrix object – Cells “out-of-scope” of Row Details (“Countries”) AND “out-of-scope” of Row Group (“Regions”)
=IIF(InScope(“Regions”), “White”, “Green”)
Fig 10. below shows all cells that fall outside of the scope of both the Column Details Group (“Quarters”) and the Column Group (“Years”)
Fig 10. Matrix object – Cells “out-of-scope” of Column Details (“Quarters”) AND “out-of-scope” of Column Group (“Years”)
=IIF(InScope(“Years”), “White”, “Green”)
Fig 11. below shows all the cells that fall outside of the scope of both the Row Details Group (“Countries”) and the Column Details Group (“Quarters”), but inside the scope of both the Row Group (“Regions”) and the Column Group (“Years”).
Fig 11. Matrix object – Cells “out-of-scope” of Row Details (“Countries”) AND Column Details(“Quarters”), but “in-scope” of Row Group (“Regions”) AND Column Group (“Years”)
=IIF(InScope(“Regions”), IIF(InScope(“Countries”), “White”, IIF(InScope(“Years”), IIF(InScope(“Quarters”), “White”, “Green”)), “White”)
Fig 12. below shows all the cells that fall outside of the scope of the Row Details Group (“Countries”) and the Column Details Group (“Quarters”) and the Column Group (“Years”), but inside of the scope of the Row Group (“Regions”).
Fig 12. Matrix object – Cells “in-scope” of Row Group (“Regions”), but “out-of-scope” of Row Details (“Countries”) AND Column Details (“Quarters”) AND Column Group (“Years’”)
=IIF(InScope(“Regions”), IIF(InScope(“Countries”), “White”, IIF(InScope(“Years”), “White”, “Green”)), “White”)
Fig 13. below shows all the cells that fall outside of the scope of the Row Details Group (“Countries”) and the Row Group (“Regions”) and the Column Details Group (“Quarters”), but inside of the scope of the Column Group (“Years”).
Fig 13. Matrix object – Cells “in-scope” of Column Group (“Years”), but “out-of-scope” of Column Details (“Quarters”) AND Row Details (“Countries”) AND Row Group (“Regions”)
=IIF(InScope(“Years”), IIF(InScope(“Quarters”), “White”, IIF(InScope(“Regions”), “White”, “Green”)), “White”)
Fig 14. below shows the cell (there can be only one) that falls outside of the scope of any named group in the matrix object
ef="http://lukehayler.com/wp-content/uploads/2011/03/image13.png">
Fig 14. Matrix object – Cells “out-of-scope” of all named Row/Column Groups
=IIF(InScope(“Regions”), “White”, IIF(InScope(“Years”), “White”, “Green”))
Using the knowledge & understanding of the above combinations with SSRS expressions (to define values/formats) will enable you to refine your matrix-based reports and give the end user the required information.
Not sure how to get subtotals in your matrix objects? See my post on Creating Subtotals in SSRS 2005
Last Thursday saw the first of many regular SQL Server Social events.Held at the Sherlock Holmes Inn on Collins Street, it was a great event with around 25-30 people in attendance. A lot more than I imagined considering a last minute change to the date. 
