Sales Dashboards in SSRS

Andrew Smith Posted on

Whilst SSRS (pre-2016) will not typically be the first choice for creating dashboards, it can still be a viable option for organisations that have an existing investment in SSRS.

SSRS’s applicability will still very much be subject to the number, complexity and presentation specifications of the dashboards required, but given the right circumstances well presented, useful dashboards can be created relatively quickly.

For this post I’ve used SSRS to see how closely I can recreate the Sales Dashboard presented in Stephen Few’s book Information Dashboard Design (Second Edition). This is my version:

dashboard4

I’ve reduced the number of widgets from Few’s version, partly because of the limited data available in the AdventureWorksDW database that I was using, and partly to keep it small enough to present a legible screenshot here. Some of the controls are different too, since Few used some presentational attributes that are not available in SSRS.

The end result is still very useful though, and captures many of Few’s principles regarding effecting dashboard design. E.g. the dashboard should:

  • include a comprehensive set of information
  • highlight important information – here using the red indicators to highlight under-performance
  • use graphics wherever possible, since graphics are faster for the eye to interpret than text
  • use appropriate graphics (e.g. that use screen real estate efficiently – no huge automotive-style dials!)
  • have good support for comparisons, e.g. between territories or products
  • be visible in its entirety without scrolling or paging
  • have data that’s easy to interpret
  • be scalable, i.e. it won’t break if more sales territories or products are added
Technical Aspects

There wasn’t anything too complicated here with regards the SSRS implementation, although the presentational aspects did take some time to lay out, due to the number of controls and the need for precision in alignment and use of whitespace.

I used 1 dataset for each widget, where the only slight complexity was with regards the Key Metrics YTD widget. This is because whereas the other 2 YTD widgets could just rely on monthly data per territory/product from the database (which could be used directly by the sparklines and aggregated via SSRS row grouping for the numeric totals), this approach wasn’t possible for the key metrics.

The reason for this is the presence of averages – having SSRS aggregate monthly averages would be wrong – a simple average was required – not an average of averages.

So I used a T-SQL “GROUP BY ROLLUP” in the query to give me both the monthly data and the aggregate data (average or sum depending upon the metric), where the aggregate data has a null month number e.g.:

dashboardquery

I then used an expression filter in the sparkline category group properties to ignore the aggregate rows:

=IIF(IsNothing(Fields!MonthNumberOfYear.Value), false, true)

and an expression in the numeric totals to ignore the monthly rows:

=Sum(IIF(IsNothing(Fields!MonthNumberOfYear.Value), Fields!Amount.Value, nothing))

Summary

Whilst the dashboard layout above was a little fiddly to produce, the flexibility of SSRS did allow a great deal of control over the look and feel of the end result.

Note that the graphics used here entirely consists of sparklines, data bars and indicators – these visualisations provide much bang for their buck – quickly conveying useful information whilst taking up little space.

So as stated in the introduction to this post, given the right circumstances, SSRS can be a useful choice for implementing dashboards.