Building a dashboard with SSRS 2008 Nov CTP

By | November 14, 2009

SSRS got a few very nice new visual controls in the November CTP like the Indicator and databar control. In this blog post we create a visual dashboard with the new SSRS.

We start as usually by opening the BIDS and create a new reporting services project (nothing new here). I couldn’t get data while using the report wizard after creating the dataset the wizard seems to lose its data (CTP bug i guess). So I just create a new empty report and creating a shared datasource.

Next up is creating a KPI dashboard that shows whether the sales per sales region are on quota. We start with creating a dataset to get our data, the first thing we notice is the possibility of selecting a shared dataset:

shareddata

This could be very time saving and efficient, since you can share a dataset to your entire project. I regularly have to copy and paste a dataset from one report to the other.

Ok i created a dataset with sales amount, sales quota and sales region, as you can see you can convert your dataset to a share dataset:

shareddataset

It will now be available in your project for all other reports:

shareddataset2

On on to our dashboard, i create a Tablix to contain our values and eventually the indicator:

tablix

Running this gives us the percentage of sales compared to the quota. Now i want to include the indicator in there, insert the indicator on the report (check out the new controls available):

controls

select the indicator type you wish:

types

create a new column in the tablix, paste the indicator on the tablix:

tablixind

Now all we have to do is set up the indicator:

setup

I use the percentage of quota versus amount to determine the color of the indicator, two things to notice when your value is out of bounds you won’t have an indicator, i feel a greater or smaller then value should be available. The other thing is the States Measurement unit setting, i couldn’t find out what it exactly meant, you can select numeric or percentage there but I couldn’t get it to work. But this is all it takes to create a indicator, very easy!

inidicator

Now i want to show my dashboard which sales per product group are selling the best, with the possibility to drill down, we start again by creating the dataset and a tablix:

tablix2

Now i want to use the data bar to show the amount of products sold in comparison the the other products. Insert a new column into the tablix and drag the databar control onto the tablix, you can click the bar to set the values (i had to search for that ..):

databar

as you can see you can even set more bars by category or series, we don’t need that now.

Putting the value in should do it, but of course since we want to see the values in comparison to the rest, the bars have to have all the same size, by default it will be as wide as the value. So we use the Max salesamount value to determine the width. This will result in the following

databar2

As you can see, the new reporting services controls make creating a more visible dashboard a lot easier. The one thing that would make the combination SSAS, SSRS as real killer app is the addition of time intelligent function. But i think we are getting there !

  • Rahul Rajput

    Hi,
    This is an excellent article. I want your help to display reports in a dashboard. For example, I have 50 reports and I want user to choose and run reports. How can I do that? How can I show all available reports to the user in attractive manner?

    Thanks
    Rahul

  • Rahul Rajput

    Hi,

    As I’m working on this article but in toolbox ‘Indicator Control, Databar Control’ is not visible. How can I get it. I’m using Sql server 2008.

    Please help.

    Thanks
    Rahul