Linking and Brushing Visualization with SSRS

Share this...

Twitter

Linkedin

Facebook

Pinterest

Reddit

In spite of being in the US for more than 8 months now, I still haven’t lost touch with the English Premier League (football). For those who don’t know me, I am a big fan of Manchester United and hence by default, loathe Manchester City. They are like our biggest rivals and won the Premier League last season, and that too in the last minute of the last game! I never thought that the day would come when I would even mention them in my blog, let alone praise them. But then last week, they started a new crowdsourced analytics project called MCFC Analytics and I can’t stop thinking about what a fantastic idea it is. Needless to say, I was among the first set of people to request the data from them and have been playing around with it this week. I was also reading about the brushing and linking visualization techniques, and hence thought of using this data for illustrating this concept within SSRS.

Before I go further, let me explain what brushing and linking is. “Brushing lets the user selects data points that get highlighted in one or more views of the same data. When several views are involved, the fact that all of them highlight the same data points is commonly referred to as linking (and the views are called coordinated multiple views)” – Robert Kosara. For demonstrating this concept in SSRS, I am using 5 bar charts – Goals by Venue, Goals by Goals From, Goals by Substitutions, Goals by Team and  Goals by Opposition. Follow the steps below to reproduce the report:-

1) Download the data from http://www.mcfc.co.uk/mcfcanalytics and then import it to a table in your SQL Server database called Players.

2) Create a new report called MCFC, and point the datasource to your database containing the Players table.

3) Create 2 hidden report parameters – RP_Field and RP_FValue with no available values and All as the default value. The RP_Field will be used for determining the series field of the chart (can be Venue, GoalType, Starts, Team or Opposition) and the RP_FValue will actually pass the field value which is being clicked.

4) Make a new dataset called DST_Main and then use the query below as your dataset query

SELECT [Player Surname],
       [Player Forename],
       [Team],
       [Opposition],
       [Venue],
       [Starts],
       GoalType,
       [Gls],
— Field used to dynamically calculate the series field
       CASE
WHEN @RP_Field = ‘All’ THEN ‘0’
WHEN @RP_Field = ‘Venue’ AND @RP_FValue = [Venue] THEN ‘1’
WHEN @RP_Field = ‘Venue’ AND @RP_FValue <> [Venue] THEN ‘0’
WHEN @RP_Field = ‘Starts’ AND @RP_FValue = [Starts] THEN ‘1’
WHEN @RP_Field = ‘Starts’ AND @RP_FValue <> [Starts] THEN ‘0’
WHEN @RP_Field = ‘GoalType’ AND @RP_FValue = [GoalType] THEN ‘1’
WHEN @RP_Field = ‘GoalType’ AND @RP_FValue <> [GoalType] THEN ‘0’
WHEN @RP_Field = ‘Team’ AND @RP_FValue = [Team] THEN ‘1’
WHEN @RP_Field = ‘Team’ AND @RP_FValue <> [Team] THEN ‘0’
WHEN @RP_Field = ‘Opposition’ AND @RP_FValue = [Opposition] THEN ‘1’ ELSE ‘0’
END AS Srs,
— Field used to dynamically sort the charts
       CASE
WHEN @RP_Field = ‘All’ THEN [Gls]
WHEN @RP_Field = ‘Venue’ AND @RP_FValue = [Venue] THEN [Gls]
WHEN @RP_Field = ‘Venue’ AND @RP_FValue <> [Venue] THEN ‘0’
WHEN @RP_Field = ‘Starts’ AND @RP_FValue = [Starts] THEN [Gls]
WHEN @RP_Field = ‘Starts’ AND @RP_FValue <> [Starts] THEN ‘0’
WHEN @RP_Field = ‘GoalType’ AND @RP_FValue = [GoalType] THEN [Gls]
WHEN @RP_Field = ‘GoalType’ AND @RP_FValue <> [GoalType] THEN ‘0’
WHEN @RP_Field = ‘Team’ AND @RP_FValue = [Team] THEN [Gls]
WHEN @RP_Field = ‘Team’ AND @RP_FValue <> [Team] THEN ‘0’
WHEN @RP_Field = ‘Opposition’ AND @RP_FValue = [Opposition] THEN [Gls] ELSE ‘0’
END AS SrsGls
FROM   [MCFC].[dbo].[Players]
UNPIVOT ([Gls] FOR GoalType IN ([Goals from penalties], [Goals Open Play], [Goals from Set Play], [Goals from Corners], [Goals from Direct Free Kick], [Goals from Throws])) AS [Gls]

5) Make 5 stacked bar charts with [sum(Gls)] as Values and [Srs] as the Series Group. The category for the five charts are Venue, GoalType, Starts, Team and Opposition respectively.

6) Change the Color expression of all the five charts as below =iif(Fields!Srs.Value=”0″,”Silver”,”00AFEF”) This is done so that we can see the brushing effect in the bars.

7) Add a textbox on the top of the report with RESET as the value. Set the action properties of the textbox as shown below

8) Set the action properties of the Venue chart as shown below

Similarly, set the action properties of the other 4 charts.

9) Set the sorting property for the category group as

=iif(Parameters!RP_Field.Value=”All” or Parameters!RP_Field.Value=”Starts”,sum(Fields!Gls.Value),Sum(Fields!SrsGls.Value)) 

You can also add the category group (for eg, the Venue chart will have Venue field) as a secondary sort option, as shown below.

This sorting will make it easier for us to understand the data.

10) Now preview the report and you should see it as shown below

11) Click on any of the bars, and you shall see the report getting the brushing effect. For eg, you can see it as shown below when Manchester City is clicked in the Team chart.

Through this chart, we can easily see that Man Utd had more home wins than away, scored more goals through open play and scored most against Arsenal. If you want to see which team scored most through Corners, click on the corners bar in Goals From chart.

Very easily, you can see that Manchester city scored the most and Blackburn Rovers conceded the most goals through corners. You can already see the power of linking the charts instead of keeping it as individual unlinked ones.

12) Click on the Reset button to bring the report to the original state in step 10. This post has been written keeping an experienced SSRS developer in mind. If you felt that the explanations are not enough, feel free to download the report file and check yourselves.

As always, I am interested in hearing your views and opinions about this. Feel free to comment on the MCFC Analytics project as well as this brushing concept.

Note: I am part of the core team that is organizing a SQL Saturday in Charlotte, NC on October 27. If you are around the North Carolina region, go and register yourselves for the event here. If you are far from us and can’t come for the event, spread some love on twitter and other social media sites using the hash tag #sqlsat174.