Search code examples
visual-studiossrs-2012reportbuilder3.0reportbuilder

Indicators in a matrix

I'm trying to add some indicators into a matrix to show an increase or decrease from the previous year

This is my design view

enter image description here

And this is how it's viewed in the report

enter image description here

I would obviously like my up and down arrows to appear in the currently empty columns. Also I'm wondering if there is a way of deleting the first empty column after 2014/15 as there is no year to compare it to.

Solution

  • SSRS supports a function called Previous, which will be useful in your scenario. Assuming that the name of your column group is "Fiscal", the difference of two years can be calculated as

    =Count(Fields!IDNUMBER.Value) - Previous(Count(Fields!IDNUMBER.Value), "Fiscal")

    Because you can't know the range of the numbers in advance, I suggest to use a "numeric" expression like

    =Sign(Count(Fields!IDNUMBER.Value) - Previous(Count(Fields!IDNUMBER.Value), "Fiscal"))

    for the indicator, so the ranges can be defined by single values -1, 0 and 1:

    Indicator Properties

    To hide the first indicator column, right-click the column header in design view and click Column Visibility..., then in the cnfiguration dialog specify to hide based on an expression like

    =IsNothing(Previous(Fields!Fiscal.Value, "Fiscal"))

    Here's a screenshot of my design view:

    Design view