Tag Archives: Reporting Services

Sorting on Multiple Columns in SSRS

Posted on by

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

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

The InScope() Function and the key to formatting subtotals in SSRS Matrix Objects

Posted on by

What it does

The InScope(<named-scope>) function checks to see if the current item is in the specified scope. To find out more about Scope please see my post on Understanding Scope

Example – So for the simple matrix report in Fig 1.:

  • The Green cells fall out-of-scope of the ColumnGroup, and In-Scope for the RowGroup
  • The Grey cells fall out-of-scope of the RowGroup, and In-Scope for the ColumnGroup
  • The Light blue cells fall In-Scope of both the ColumnGroup and the RowGroup.
  • The Dark blue cell falls out-of-scope of both the ColumnGroup and the RowGroup.

What this means

By incorporating an If statement into the party we can decipher which cells should get particular formatting. For example:

Fig 1. A Matrix Object

InScopeFunctionExample

Application

Now let’s apply the above theory. The object is to format the background colour of the cells as shown above:

  • Select the data cell.
  • Navigate to the background colour property
  • Select Expression from the drop down list.
  • Apply the expression:

=IIf(InScope(“ColumnGroup”),

IIf(InScope(“RowGroup”),”LightBlue”, ”Grey”),

IIf(InScope(“RowGroup”),”DarkGreen”, “DarkBlue”)

)

  • Run the report. You should get a colour scheme similar to Fig1.

Now this is just a simple example. If you have multiple column and row groups you will have to expand the logic to cater for what is In Scope and what is Out of Scope. The If statements will begin to get a bit messy, but if you use an indented format to initially get the formula out and then reduce it, it becomes easier.

You can apply this logic to format any property of the cells. You can also use the logic to change the data shown in the cells. If, for example, you wanted to show an average in the subtotal column instead of a data value, then you would substitute the colour above for the appropriate data expression

=IIf(InScope(“ColumnGroup”),

IIf(InScope(“RowGroup”),Fields!Total.Value, SUM(Fields!Total.Value)),

IIf(InScope(“RowGroup”),SUM(Fields!Total.Value)/Count(Fields!Month.value), Sum(Fields!Total.Value)/Count(Fields!Month.Value)

)

There is another way to format the colour (or other property) of the Subtotal columns/rows. Using this method will apply the same formatting to all cells in the subtotal column/row, including the Grand Total. So, if you want a simple way to blanket format the subtotal of a column or row:

  • Select the Subtotal column/row cell.
  • Click the little green arrow that appears in the top right corner of the cell (watch out, it’s a tricky little bugger)
  • Now amend the property that you wish to update.
  • Done.