Pie Charts on SSRS Map Reports

Share this...

Twitter

Linkedin

Facebook

Pinterest

Reddit

Ok, I think I am getting a few puzzled looks now. All of you must be wondering why I am talking about pie charts (isn’t pie chart the untouchable outcast in the family of visualization techniques?). Also, you must be thinking that there is no possible way you can make pie charts in SSRS map reports (unless I am referring to Power View instead of SSRS). Well, to answer your questions, I am not recommending pie charts but ever since Power View gave the feature for displaying pie charts in map reports (read section III in Creating Maps in Excel 2013 using Power View), a lot of people have been asking whether it is possible to replicate the same in SSRS. You do have the ability to display bubble charts in SSRS map reports but there is no default way of achieving pie charts in SSRS map reports. That is when yours truly decided to take a shot at it and came up with this workaround (oh yes, you heard it right – there is a workaround!)

To demonstrate this workaround, I am using the data from 2000 US Presidential Election (which I got using the online search feature in Data Explorer). Follow the steps below:-

1) Create a new report and then create a datasource pointing to your SQL database. After that, create a new dataset query like shown below

SELECT     State, Gore, Bush, Others, TotalVotes FROM EData

EData is the table which contains the election data that I pulled up before. This table contains the State name, the percentage of votes for Gore, percentage of votes for Bush, percentage of vote for all Others and finally the total number of votes.

2) The key to getting the pie charts on the SSRS map reports is to make use of the Pie Charts feature in Google chart. You can get an image of a pie chart as long as you pass in the required parameters in an url format. The basic syntax of the url we are going to use is

http:chart.googleapis.com/chart
?chs=300×225 — size of the image
&cht=p — type of chart, in this case, pie chart
&chd=t:20,30,50 — Data to be used in pie chart
&chco=FF0000|00FF00|0000FF — color to be used in pie chart

To get a pie chart for each row / state, we need to construct the url for each row in the table. This can be done by the following query

SELECT State,
Gore,
Bush,
Others,
TotalVotes,
‘http://chart.googleapis.com/chart?chs=300×300&cht=p&chd=t:’ + CAST (Gore AS VARCHAR (20)) + ‘,’ + CAST (Bush AS VARCHAR (20)) + ‘,’ + CAST (Others AS VARCHAR (20)) + ‘&chco=06A2CB|DD1E2F|EBB035’ AS MarkerUrl
FROM   EData

3) Create a map report of US State from the map gallery and then choose the map visualization as Color Analytical Map. Choose the analytical dataset as Edata and then map the statename in the spatial dataset with the state field in the analytical dataset.

Click on Next and then finish to create a basic map report.

4) Even though it is not part of this requirement, I decided to color the states as blue or red based on whether Gore or Bush had got more votes respectively. For this, right click on the Polygon in Map Layers and then click on Polygon Color Rule.

Write a simple expression to display blue or red based on who’s got more votes like shown below.

Now your map report should look like this when previewed

5) Right click on the Polygon layer and then enable the Show Center Points option.

6) Now select the Center Point Marker Rule.

Now choose the Marker Type as Image, image source as External and the image as the Marker Url field as shown below.

7) Also, we need to change the size of the pie chart based on Total votes field. For this, change the Center Point Size Rule.

Change the size rule option as shown below.

Now we should get the basic version of the end result as shown below.

8) After deleting the legends as well as modifying the colors so that my eyes don’t get blinded, this is what I came up with.

So aren’t you amazed at the flexibility SSRS gives you? As usual, comment and let me know what your thoughts are on this technique. Meanwhile, feel free to download the completed SSRS 2012 report that I made from here.