SSRS 2016 Dashboard UX Part 1 - Slicers

Dashboard navigation is the phrase i am using to describe the process of being able to interrogate and drill down into reports without the necessity for using parameters.  Let's face it, parameters are really ugly in SSRS - even with the ability to now lay them out - and the world would be a happier place if there was such a concept as slicers and other report UI elements natively in the SSRS universe.

Firstly a note:  yes, I know Power BI does a lot of this stuff.  However, for on-premise customers, this isn't necessarily a cost-effective solution.  Most of my customers are SharePoint and Project Server users and - lets face it - most Project Server environments don't end up sitting on SQL Enterprise Edition so Power BI requires $/£ on top of the existing customer investment, which just isn't gonna fly in most cases.

So here are a few concepts I have played with in the last few weeks to spoof such navigation tricks.

Part 1) Single-select slicers using Action buttons in a Matrix

Part 2) Tree drill-down navigation

Part 3) Navigation buttons and pulling it all together

Slicers

We all know and love slicers from our Excel days.  A simple list of attributes in a dataset that allow you to filter charts or tables quickly with a click.  Great stuff!  So why can't I do this in SSRS?  Well, you [kinda] can, with the caveat that it works for only one selection, not multiple.

The key elements of making this work are:

a) your primary dataset that appears in the report

b) an additional dataset that includes a DISTINCT select of your filterable attribute to appear in the slicer

c)  a matrix using the dataset (b)

d) some hidden parameters

e) a reset button to "unfilter"

d) some formatting shenanigans to make it feel like a button.

Here is the result on an "hours booked by category" report using a Month drill down on a six month rolling dataset report:

 figure 1:  all months selected

 figure 2:  Feb-18 selected

The primary dataset

This is a summary of booked hours in Project Server timesheets by category.  This particular report looks at "chargeable" projects and other Administrative Tasks.  The key data elements are:

> resource name

> Resource Team
> Booking category

> month (using the first of the month as an aggregate point)

> monthstring (an nvarchar string to describe Month above) - i've done this as a short cut as I am lazy sometimes

> Sum of Actual Hours

This is filtered to show only the last six months.

Secondary filter dataset 

The dataset for the month selection matrix is basically a list of the months that will appear in the primary dataset.  Yes I know it would be better to use a CTE to recursively build this but i was in a hurry...

DECLARE @MAXMONTH DATE = DATEADD(MONTH,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0))
SELECT        CAST(DatePart(Month,@MAXMONTH) as NVARCHAR(2)) + '-' + CAST(DatePart(Year,@MAXMONTH) as NVARCHAR(4)) Month,6 as Sort, @MAXMONTH as MonthDate
UNION ALL
SELECT        CAST(DatePart(Month,DATEADD(MONTH,-1,@MAXMONTH)) as NVARCHAR(2)) + '-' + CAST(DatePart(Year,DATEADD(MONTH,-1,@MAXMONTH)) as NVARCHAR(4)) Month, 5 as Sort
,DATEADD(MONTH,-1,@MAXMONTH)
UNION ALL
SELECT        CAST(DatePart(Month,DATEADD(MONTH,-2,@MAXMONTH)) as NVARCHAR(2)) + '-' + CAST(DatePart(Year,DATEADD(MONTH,-2,@MAXMONTH)) as NVARCHAR(4)) Month, 4 as Sort
,DATEADD(MONTH,-2,@MAXMONTH)
UNION ALL
SELECT        CAST(DatePart(Month,DATEADD(MONTH,-3,@MAXMONTH)) as NVARCHAR(2)) + '-' + CAST(DatePart(Year,DATEADD(MONTH,-3,@MAXMONTH)) as NVARCHAR(4)) Month, 3 as Sort
,DATEADD(MONTH,-3,@MAXMONTH)
UNION ALL
SELECT        CAST(DatePart(Month,DATEADD(MONTH,-4,@MAXMONTH)) as NVARCHAR(2)) + '-' + CAST(DatePart(Year,DATEADD(MONTH,-4,@MAXMONTH)) as NVARCHAR(4)) Month, 2 as Sort
,DATEADD(MONTH,-4,@MAXMONTH)
UNION ALL
SELECT        CAST(DatePart(Month,DATEADD(MONTH,-5,@MAXMONTH)) as NVARCHAR(2)) + '-' + CAST(DatePart(Year,DATEADD(MONTH,-5,@MAXMONTH)) as NVARCHAR(4)) Month, 1 as Sort
,DATEADD(MONTH,-5,@MAXMONTH)

Slicer Matrix

Now we create a matrix to contain this dataset.  Simply create a new Matrix and add the Month column to the Column Groups section and the data region.  Once done  you can delete all rows and columns except the data region so you end up with this:

Parameters

So far so meh! now for the magic.

Firstly we create two parameters, once to drive the report, and one to act as a filter reset.  I've gone for Months and MonthsReset as the names.  These are:

> Type = Text

> Selection = Multi value

> Visibility = Hidden

> Available and Default Values = Month column in the secondary dataset (b)

Next we place an action on the matrix of months that does the following:

> Go to Report -> This report (so it is basically resending the users to the current report

> Pass Parameters -> Month -> Value -> [Month] from dataset (b)

This means when clicking the month value in the matrix, it executes the report but passes the Month value into the Month parameter.

Now we can apply a filter to the chart where  [MonthString] (dataset a) IN [@Month] so by default the report shows all six month periods, but clicking the above defined action passes one specific month, overriding the default Month parameter.

Reset button

Once you have this you then need a way of resetting the parameter.  I used a Text Box to hold the reset action.  This follows a similar approach of using the Go To Report action and applies the values in the MonthReset parameter (all six months) to the Month parameter.

In addition I can make the reset button only appear when the filter is applied using this on the Text Box Visibility option:

Expression for hidden:

=Join(Parameters!Month.Value,",") = Join(Parameters!MonthReset.Value,",")

Formatting

So once you have clicked on a slicer and the report is filtered, we then need to show the user what they have selected.  I did this using shades of grey Font (Dark for selected, light for not selected) and Border expressions (Border on selected, no border for not selected). 

Text formatting - I used this expression to show Filtered for as Dim Grey and Filtered out as Silver:

=IIF(Array.IndexOf(Parameters!Months.Value, Fields!Month.Value) > -1,"DimGray","Silver")

Full disclosure:  Google to the rescue here!  as far as i can work out, this looks in the values of the Parameter "Month" and looks for where the Value "Month" in the Matrix appears.  If it is NOT in the parameter then it gets -1, otherwise it gets a value from 0-n.

Border Formatting - Same as above, simply select Text Box Properties, and use the fx to enter the expression above (replacing Silver for White) and select the bottom border.

BE AWARE:  This doesn't get saved anywhere so if you go back and apply border again you will need to re-enter your expression or you will end up with a Black border again.

There you go, a working slicer.  Of course all this constant navigation to the same report is a little inefficient but ensuring your queries are optimal and use of snapshots instead of live data can speed things up.

Next:  Dashboard Tree Navigation