Building a dashboard with SSRS 2008 Nov CTP

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:

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:

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

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

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):

select the indicator type you wish:

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

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

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!

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:

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

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

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 !