Monthly Archives: July 2009

Custom Visibility Toggling in SSRS

Posted on by
29

I can see clearly now

The visibility property of Report items can be a little gem. You can set the initial visibility of an item to hidden/visible , use another report item (a parent group in a table or matrix) and set the initial appearance of the toggle icon (+/-) either absolutely or using expressions. By using expressions, you can dynamically determine whether or not the item should be hidden (or collapsed in the case of a table/matrix grouping), and subsequently set the initial appearance of the icon.

In the example below, textbox8 is my toggle item for the group “Priority“. When the report loads I want to show the “critical” items in an expanded state, and all other priorities in a collapsed state. By right-clicking ontextbox8 and choosing properties I get the window below. Select Expression for the Initial appearance of the icon and enter a formula that will determine whether the initial appearance of the icon is expanded (’true‘) or collapsed (’false‘). What you use in your expression to evaluate whether or not the toggle item is expanded or collapsed is up to you.

Note. The above assumes that the Group “Priority” has already had it’s visibility set using an appropriate expression to show the “Critical” items expanded and all other collapsed when the report is run. It also assumes that the visibility of the group “Priority” is toggled by another item (textbox8).

Fig 1. Text Box Property dialog, showing the Visibility Tab

Fig 1. Text Box Property dialog, showing the Visibility Tab

The result is shown in Fig 2.

Fig 2. Result of setting custom visibility for a table group
Fig 2. Result of setting custom visibility for a table group
-luke

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

Posted on by
39

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.