Skip to main content

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


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

SELECT        CAST(DatePart(Month,@MAXMONTH) as NVARCHAR(2)) + '-' + CAST(DatePart(Year,@MAXMONTH) as NVARCHAR(4)) Month,6 as Sort, @MAXMONTH as MonthDate
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
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
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
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
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

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:

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,",")


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


Popular posts from this blog

#projectserver2013 VIEW FAILURE: The view failed to load. Press OK to reload this view with the default settings. Press cancel to select another view.

** UPDATE ** includes notes relating to secondary bug where Timesheet is created without Administrative tasks.

Does this ring any bells?

This has been bugging me for months, but finally I have a repro for this:

Issue Summary:  When a task is deleted from a plan that is approved into a previous or current timesheet - even when there are no actuals on the task - you can no longer view the timesheet

The following repro has been proven:
- Setup system with Single Entry Mode, with enforced Status Approval before Timesheet Approval
- Create resource as own timesheet manager
- Create new project
- Create two tasks in the same week, starting monday with 5 days duration:  1) Task to assign actuals, 2) Task to delete post submission
- Assign Resource to tasks
- publish project
- as Timesheet User, go to the appropriate timesheet period for the tasks created
- Assign actual work to one task (task 1), leaving task 2 with no actual work
- Submit timesheet
- as Project Status Manager, approve the time on task 1 …

What to do when your application server goes bang

What happens when someone kills your Application Server?
So imagine the scenario:
Three Server Solution - SQL - SharePoint 2010 and Project Server 2010 Application Server (Central Admin Host) - Wfe/ReportServer
We wake one bleary Monday morning to find that some numpty has killed the application server and the users are baying for blood.
Well surprisingly SharePoint handles this disaster recovery scenario particularly well.  Well.  Better than I thought it would to be honest.
Rough steps:
- quick SQL backup to be safe - Rebuild your application server - reinstall pre-reqs - reinstall SP, PS, SPFSP1, SPS+PSSP1, Cumulative Update and other stuff you usually put on there. - Run your configuration wizard to reattach to the Farm, and select Host CA Site
The last step was what I was VERY wary of.  Would the server simply reattach to the Farm, even when there is no CA server available?  
Bingo your back.... almost.... you are going to get errors a-gogo in your event log as things just aren't quite back…

Reporting from Project Server 2016 - multiple sites and userviews

Just a quickie...
I've been interested in how MS have handled the "multiple PWA sites in a Content DB" thing since I read that this was their new approach.  Most of my reporting is via SSRS so i am reliant (still... in 2016) on DB queries rather than OData feeds (tsk) and this "querying a PWA DB with more than one PWA site in it is unsupported" quote was worrying me.

So it looks like what is happening is this.

When you create the first PWA site in a Content DB it hard-codes the SiteID into the _Userview view design elements.  This means that your first PWA Site is the default.  All the data for subsequent sites are still held in the tables against separate SiteID's but you cannot utilise the OOTB _Userview components (see below)

SELECT        ProjectFields....
FROM            pjrep.MSP_TVF_EpmProject('FF19B767-CA6D-4C4C-B123-C0B5AE5354D6') AS MSP_EpmProject