A Sample SSRS Dashboard and some Tips & Tricks

And I’m Speaking at the PASS Summit 2013!
May 27, 2013
Split a Delimited Row into Multiple Rows using DAX Queries
June 11, 2013
Show all

A Sample SSRS Dashboard and some Tips & Tricks


The other day, I was browsing for some sample SSRS dashboards and one of the top images that came up made me cringe. You might wonder what could be so bad in a dashboard as to make me cringe. Well, apart from the factor that the dashboard was openly flouting all the best practices for visualizations and that it would have misled countless people in virtue of being one of the top five images for the search term – ‘SSRS Dashboard’, it was made by ME. To be precise, this dashboard was made by me around 3.5 years back, a time when I believed more in ‘eye-candy’ rather than effective visualizations. That was when I felt that I owed a proper SSRS dashboard to the community.

SSRS Sample dashboard and some tips and tricks

First of all, let me start by saying that this dashboard was inspired (a polite way of saying – a complete rip-off Smile) from this Dundas Dashboard example. Personally, I felt that this was a very good dashboard example and I decided to reproduce it in SSRS. The result is given below:-

SSRS Sample dashboard

Let me tell you some of the things that I like in this dashboard:-

1) The colours are carefully chosen. Note how the dashboard is not a kaleidoscopic combination of different colours like it’s predecessor. Light tones of grey are used to depict the axis as well as the gridlines, while the charts are more darker which gives them more attention.Also, in spite of having 4X times more information than the previous dashboard, the new one doesn’t look crowded (thanks to the right choice of colours)

2) The dashboard focuses on the three main KPIs but also has an executive summary on the right. I also like the way each line represents a story for that KPI.

3) I particularly like the KPI boxes on the left. Each box has a current value (which is highlighted by the size), difference from YTD (which is the small box on the top right), a sparkline on the bottom as well as conditional formatting to show whether the KPI is above or below the target. I think it gives a sexy eye-candy sort of feel to the dashboard without bending the visualization best practices. Also, I prefer the faded red and green colours to the normal traffic light red and green colours that we see in usual dashboards.

4) The labels are all formatted appropriately (for eg, 3M instead of 3,000,000). This ensures that we don’t waste a lot of space for labels. With that said, let me go ahead and show you some of the tips and tricks I employed for making this dashboard:-

I) Alternating Background for the sparkline


You can see the above sparkline (which is for the four quarters) has an alternating background for the four quarters. By default, you can have alternating colours in SSRS chart background if you set the background color of the chart area to a color (say light grey) and the InterlacedColor property of the X axis to another color (say dark grey). Now the problem is that the line charts will start at the meeting point of the two colours (like shown below) instead of at the centre.

Normal alternating background for sparklines

One of the ways you can get this done is by adding 4 striplines to the X-axis. For the first and third stripline, ensure that the BackgroundColor is the same (lets say light grey) and the second and fourth should have the same BackgroundColor (which is dark grey). All the striplines should have a Stripwidth of 1. The IntervalOffset property should be 0.5, 1.5, 2.5, 3.5 respectively for the four striplines.

Stripline property

Click on the Y axis and press the delete button to remove it. For the X axis, click on it and set the LineStyle property to None, Major TickMarks–>LineStyle to None and HideLabels to True. This will give us the required result below. For use in the dashboard, remove the chart border also.

required alternate background for sparklines

II) Pretty KPI boxes

KPI boxes

The pretty KPI boxes are just a combination of textboxes and the sparkline we made above. This is how it actually looks in design mode

KPI boxes in design mode

Right now, I have hardcoded most of the stuff. But you can change it as required. For eg, to get the conditional formatting, you can write a simple expression in the Color property of the textbox. The only other thing is to have different font sizes / font styles for the $ and numeric value. To demonstrate that, I pull in a textbox and type $ in it. After that, I drag and drop a field from my dataset (SAC) to the textbox.

ssrs textbox

Now I can individually select $ and give it a different font style / font size and also do the same for SAC (whose expression I modified to =Sum(Fields!SAC.Value, “G1_B1”) from =Fields!SAC.Value)

ssrs textbox - selected text properties

III) Formatting the labels

To format your labels in thousandss, you can use the format – 0,’K’

Label format - thousands

Similarly, if you want to format in millions, you can use – 0,,’M’. Note that you are just increasing a comma.

Label format - millions

IV) Correlated column charts

correlated column charts in SSRS

For this, add a normal column chart with 3 measures.

column chart

Now change the PointWidth property of the second measure to 0.6 and third measure to 0.3.

PointWidth property

Now right click on the Chart Area, and click on Properties. Check the ‘Enable 3D option and set the rotation, inclination and wall thickness to 0.

3D option

After that, set the BackgroundColor property of the chart area to No Color. Now you should have the required correlated column chart.

final correlated chart

Well, there are a few more tips in the dashboard that I made (even though I have hardcoded a LOT of stuff, since this was just a demo) and if you want, you can download the rdl file of this report from here. Hopefully, I have redeemed myself by creating a better dashboard than the original one! Smile


  1. Unknown says:

    I LOVE this blogpost. I think there is an evolution for report designers where we learn what the technology can do and then we learn report/dashboard design best practices for conveying information. I know I have some reports and dashboards from earlier in my career that I would like to redesign. Thank you for explaining in detail how you built the new dashboard. The KPI boxes are great. I hope that as we design more dashboards that both provide actionable information and are visually appealing, clients will see them and request them more than just eye candy.

  2. Braga says:

    The co-related chart looks very similar to the bullet chart.

    btw have you read this ?

  3. Tom Martens says:

    This is one of the most compelling Reporting Services dashboards I've seen. Once again I'm impressed what is possible if an expert does his work. There is just one thing I would have done slightly different.

    The "correlated column charts", I get accustomed to bullet graphs that were "invented" by Stephen Few some time ago and therefore I expect some different meaning, looking at columns that overlap. So, trying to discover the inherent meaning of one particular arrangement, I lost some time, reading the dashboard. Even if Stephen Few (one of my personal heroes according to visualization) describes this kind of charting method to show correlations between values in the 1st edition of "show me the numbers", it seems that he tends to use a different visualization technique in his subsequent work. If bars have to be used to show some kind of correlation: use the table lens.

    Maybe this would also suit the case (just a little example), at the moment I like the color darkred, for this particular dashboard darkgrey would also works well 🙂


    • SQL_Jason says:

      I have been getting a lot of comments on the correlated charts, and looks like it might be slightly confusing for this particular example. Thanks for taking the time to give an alternate recommendation! Personally, I still have a soft corner for the correlated column charts as it allows you to make comparisons no matter if your eyes move horizontally or vertically. In other visualizations like the table lens, you might have to make use of data labels for either of the one direction (depending on whether the bars are horizontal or vertical).

  4. Unknown says:

    Hi Jason,

    Your dashboard looks great.

    Just wondering could you please share the sample database? I can open the RDL file but missing the data source. I wish I could run this report to have a real feeling.


    • SQL_Jason says:

      You can run the report. Just point the datasource to your local database, and it should work (as all the data is coming from hardcoded SQL queries).

  5. Unknown says:

    Great Work. Really impressive. You're my personal SSRS hero 🙂


  6. Paras Doshi says:

    That's one great looking dashboard! Thanks for posting.

  7. Simeon says:

    Jason, this is a great set of sample dashboard components! Have you come across "easy" ways of creating tree charts in SSRS?

  8. Really nice post, thanks for this one. This is the first real nice SSRS dashboard I have found on the web. I succesfully implemented some pieces at a client, with succes. Are there any updates for the design available from your side?

    • SQL_Jason says:

      Hmmm, no updates maybe but I might come out with another example. Have some ideas in mind but will have to find time to implement 🙂 Hopefully soon!

    • sree harsha says:

      Hi Jason I am new for the SSRS I am still learning this course Can U plz explain about the dashboards from da starting in detail.It will be better not only for me but also the people who r learning these course…I hope u can explain these things.
      Thanks in advance

  9. John says:


    Appreciate if you could also share the data source details in the .rdl file. Looks like to uses a shared data source and the details are missing.


    • SQL_Jason says:

      Just point the datasource to your local database, and it should work (as all the data is coming from hardcoded SQL queries). Editing the Datasource should work.

  10. Steve Talbot says:

    Very good 24 Hrs of SQL Pass presentation last night on the SSRS Dataviz and Dashboards. I never really considered SSRS to make a dashboard since we are focused on Performance Point with some SSRS components. I really like the potential of SSRS Dashboards with drill-down, drill-through potential and much easier to deploy. Thanks.

  11. Mamata says:

    Hi Jason,

    Very good article. Could please let me know from where can I download the above explained dashboard.

    Thanks in Advance.

  12. Tommy Bell says:

    Hello Jason

    Do you by any chance offer a download by this?
    Also, is the data based on adventure works or something similar?

    • SQL_Jason says:

      The download link is at the end of the post 🙂 and you just need to point to a sql server instance, it should work as most of the data is encapsulated in the select statements.

  13. Connie says:

    This looks great — one of the nicest looking dashboards I've seen. I'd like to use SSRS to do some dashboards, but we don't have SharePoint, and a lot of the SSRS dashboards that I have seen seems to us SSRS + Sharepoint. Do you have to use SharePoint to do this complex sort of dashboard with SSRS?

  14. Halle Bose says:

    Quite frankly, this happens to most people, once you are growing. Even with something as simple as choosing email providers. Being able to look back and improve on work from the past is a sign of improvement.

  15. Craig says:

    I like the subdued colors. For me it is still a lot of information to consume on one screen, so a bit overwhelming. Would 3 different tabs make this dashboard even better?

  16. Dave Howard says:

    what database are we supposed to point this dashboard too? I know a local one, but what database name is this? anyone have any idea?

  17. name & DOB (Age)

    James & 01/12/1987 (26)—- Hear three fileds is their i want display age with in the brrackets

    Rock & 05/10/1986 –Hear age not their so i dnt want show "() "like this if age no value means

    how to write expression in ssrs

    am writen sample expression

    =Fields!name.Value & " " & format(Fields!dateofbirth.Value,"dd/MM/yyyy") & " " & iif(Fields!age.Value <> " ", "(" & Fields!age.Value & ")",nothing )

    if any changes required let me know.

  18. Jo John says:

    I have an SSRS report that groups report dates by week. The default view collapses the report dates and only shows the week value. I am trying to set up navigation to pass the report date to another report when a data cell is clicked.

    Currently, the report successfully passes a single report date fine, if the week is expanded. But if the week is collapsed, it only passes the first value. The destination report is set up to receive a multi-value parameter for the report date.

    Is it possible to in the SSRS 2012, help needed. Thanks in Advance

  19. Jacque says:

    Hi Jason,
    Thank you very much for the explanation of your process and the ability to download the sample dashboard. I really appreciate it.

    Thanks again,

  20. Sven says:

    Hello Jason,

    thank you for this post !

  21. David says:

    Hi Jason,

    I absolutely loved this design and spent ages creating an interactive version with drill downs similar to this. However when you go to view the report in Report Manager it turns into an absolute mess. Running it in Visual Studio is fine, exporting it to PDF from VS is fine, exporting it from IE or Chrome to PDF is all good, but viewing it in Report Manager it turns into about 10 pages of unreadable, overlapping crud with all of the graphs being duplicated everywhere. I tried it with yours thinking I was doing something wrong but it is the same. This is not your fault in the least, but I was wondering if anyone else had tried this and had the same result, and if there was a fixable reason for this.

    Again, loved the post!

  22. Usama says:

    This is aswsome. Many thanks.

    Can I ask how do you remove the border from the series from the bar chart without individually setting hte border colour of each series?

Leave a Reply

Your email address will not be published. Required fields are marked *