Tag Archives: Matrix

Sorting on Multiple Columns in SSRS

Posted on by
3

There have been several cases lately where people have asked the question “Can I sort on multiple columns in my Table/Matrix report?” More often than not the answer given is an emphatic “No”. The fact is that you CAN sort on multiple columns by simply incorporating use of  the Shift key.

Assuming you have already set up interactive sorting on the columns you want to be able to sort by; run/preview your report. As you are already aware, the columns which can be sorted on have a two tiny arrows right-aligned in the column header. To sort on any one column just click on the arrows. Click on the arrow again to sort in the other direction.

MultipleColumnSorting

To sort on multiple columns, hold down the Shift key whilst clicking the arrows icons of the columns that you want to sort on. Start with the column you want to sort on first and then move to the next sort order, etc, etc. Click on any column arrow icon twice to change the direction (continue to hold down the Shift key until you have sorted on all the columns you wish to sort by).

Dataset sorted by Year (asc), Quarter (asc), Total (desc)MultipleColumnSorting_2

And there you have it. Multiple Column sorting in SSRS!

T-SQL Tuesday #016 – Aggregations

Posted on by
3

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)”)

image

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.

image

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.

image

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.

image

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.

image

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.

image

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”)

image

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”)

image

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”)

image

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”)

image

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”).

image

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”).

image

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”).

image

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">image

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

Creating Subtotals in a Matrix in SSRS (2005/2008)

Posted on by
10

This is a quick look at how to create and use subtotals in your Matrix objects.

Subtotals 101

SQL Server Reporting Services makes it relatively easy to add totals to your columns and rows in a matrix object. In short, it’s as easy as right-click>Subtotal. Seriously, that’s it. Well, unless you want to format them, but that’s another issue entirely (an not quite as straight forward).

Let’s do this step-by-step :

  1. Open or create a new SSRS project
  2. Add a report item to your project.
  3. Add a dataset
  4. Add a matrix object
  5. Configure matrix to desired specification
  6. Add totals to outer groups by right-clicking the outer column/row group for which you require the subtotals and select subtotal.
  7. Add subtotals for inner groups by right-clicking the inner column/row group for which you require the subtotals and select subtotal.
  8. Tidy up by hiding the inner group line items initially – by right-clicking the inner group and selecting Properties>Visibility and then setting the properties accordingly.

Here’s a quick 2 minute screen cast of how to create subtotals in a Matrix object in SSRS 2005. It illustrates creating row group Totals & subtotals, but applies to column groups as well:

Here’s a link to another version if you are having any trouble: http://www.screencast.com/t/OGE1MmI4