SSRS Charts with Data Tables (Excel Style)

Ok, this post was supposed to come out a lot earlier but I was just too lazy. Complacency is a struggle we all have to fight and I would be lying if I say that I am immune to all these. This post is a direct result of a few forum posts where people said that charts with data tables are not possible in SSRS (unless you add a tablix beneath the chart and spend umpteen hours trying to align both of them!). Working with SSRS is like making love to a woman - you need to know the woman and what makes her click well for a fabulous time. Well, you can still manage without knowing her well enough, just that it wont be that awesome!

For people who are familiar with excel charts, adding a data table to a chart is a one-click affair. It is nice to see the data table just beneath the chart and it also serves as a legend. A simple example is shown below:-

In SSRS, having a tablix beneath the chart serves as an ok workaround as long as the number of fields in the category is fixed. When the number of fields change change (lets say, when the value of a report parameter changes), the alignment goes for a toss. Here, I will be demonstrating a much more elegant way to add the data tables than trying to align the tablix and chart.

1) Create a simple dataset from the AdventureWorks cube, displaying the Month, Category and Internet Sales Amount. Add a parameter for Calendar year with a default value of CY 2007. 

2) Drag and drop a matrix to the designer pane and then add the Category field on the row, Month on the column and Internet_Sales_Amount on the data as shown below

3) Add two rows outside group above the category row as shown below.

4) Now you can delete the top row (but not the associated group) containing the Category and the Month Header as shown below

5) Once that is done, drag and drop the month field to the textbox above the data value. Here, I just want to display the first three letters of the month (instead of having the month followed by year), so I am using the expression below directly:-

=left(Fields!Month.Value,3)

You might also want to do some quick formatting like hiding the textbox borders for the top row.

6) Once this is done, the only part remaining is to make a chart, format it and then place it on the top right textbox. For that, drag and drop a stacked column chart outside of the matrix, and then drag Internet_Sales_amount on the Values, Month on the Category Groups and Category on the Series Groups as shown below.

7) Right click on the vertical axis, select the Vertical Axis Properties and then modify the expression for Maximum from Auto to the expression written below

=1.2 * max(Fields!Internet_Sales_Amount.Value, "DST_Main")

8) Delete the axis titles, chart titles and the horizontal axis from the chart

9) Hide the labels for the vertical axis.

10) We need to place this chart within the tablix and make it repeat for each of the month. But before that, we need to make sure that there are no white spaces within the chart area, and hence change the following properties for the Chart Area.

Also set the BorderStyle property for the Chart as None and disable the MajorTickMarks for the Vertical Axis. This will also help in making the charts look continuous.

11) Now as a last step, we need to ensure that the vertical axis is only displayed for the first chart, Hence, set the LineStyle property for the vertical axis as shown below

=iif(Fields!Month.Value=First(Fields!Month.Value, "DST_Main"),"Solid","None")

12) Drag and drop the chart into the tablix cell

13) To add a color as a column next to the Category, we can use the Custom Code technique for consistent colors described here. Then ensure that the BackgroundColor property for the column as well as the series color for the chart is the expression below

=Code.GetColor(Fields!Category.Value)

14) Now preview the report and you will get the intended result. Change the parameter value from 2007 to 2008, and you will still see that the alignment is proper (even though the number of bars have changed)

You can download the completed rdl file from here. Now you know which post to point to incase someone asks on how to create charts with data tables in SSRS

Difference from Maximum of Running Totals in PowerPivot

I don’t know how many of you are Formula1 fans here, but this season is pretty good. Sebastian Vettel and Fernando Alonso are going neck and neck for the title and there are just 4 races remaining. I try to follow F1 as much as I can, but had missed watching the last couple of races and there were some pivotal moments as Vettel took the lead from Alonso. I really missed knowing how they were doing at the end of each race and how far the other drivers were from the race leader. That is when I decided to relive the experience by mocking up the data in PowerPivot.

So what I want to do is to get a running total of the points for each driver across the races., so that I can see how each driver was doing throughout the season. Also, it would give me a better idea of their performance if I could get the difference of the points for each driver from the race leader at the end of that race. Interesting huh?

 

Source

The source for our exercise can be downloaded from here and contains the following data:-

1. RaceFact

This table contains the positions for each driver across all the races in this season. The columns are Driver, Race, Position and a flag whether they crashed or not.

2. RaceOrder

This table contains all the completed races in this season and the order in which they happened. The columns are Race and Order.

3. Points

This table contains the points for each position. The columns are Position and Points.

4. Driver

This table contains the list of all drivers and just has one column - Driver.

 

Requirement

1. The running total of the points for each driver should be displayed race by race as shown below.

2. The difference of each driver from the leader (as of that race) should be displayed.

Implementation

1. Build the PowerPivot model for the RaceData as shown in the diagram above. For simplicity, I have already created the model in the source file available for download here.

2. For creating the running total across the races, we will be following the technique detailed in my previous blog. For that, create 2 calculated columns in the RaceFact table called Points and Order respectively.

=calculate(sum(Points[Points]))

=calculate(sum(Races[Order]))

Notice that the foreign keys in the fact table (Driver, Race, Position) are hidden as it is a best practice to hide the foreign keys. This way, the users will not get confused whether to use the columns in the master tables or the fact tables.

3. For finding the running total, we can create a new measure with the below formula

RT_Points:=calculate(sum(RaceFact[Points]), filter(Allexcept(RaceFact,Driver[Driver]), RaceFact[Order]<=Max(RaceFact[Order])))

When this measure is used against the drivers on rows and races on columns, it will display the running total of the points for each driver across the races.

4. Now for getting the difference of each driver from the leader, we need a measure which will give us the points of the race leader at the end of every race. This measure would be equal to the max of the running total that we just created and that should be the same for all drivers for a particular race. Create the measure with the DAX formula given below

MaxRT_Points:=calculate(maxx(values(Driver[Driver]), [RT_Points]), All(Driver[Driver]))

The result is shown below

5. Now all we have to do to get the final requirement is to subtract the MaxRT_Points measure from the RT_Points measure as shown below

DiffFromLeader:=[RT_Points] - [MaxRT_Points]

The result is shown below

6. With a bit of formatting and adding a chart for the top 6 drivers, I can now clearly see how the battle for the top place has been going

The final version of the PowerPivot file can be downloaded from here. It is interesting to see that Fernando Alonso has been the most consistent driver, as can be seen from the above chart, even though he is in second place. With this information, I am going to bet my money on Alonso winning the Drivers championship (and not because I am a biased Ferrari fan :P). What do you reckon?