Pie Charts on SSRS Map Reports

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

Pie charts on SSRS Map reports

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.

Create dataset

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

?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

Google 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

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.

map state name

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.

polygon color rule

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

Color polygon rule

Now your map report should look like this when previewed

polygon color rule result

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

show center points

6) Now select the Center Point Marker Rule.

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.

change marker type rules

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.

Center Point size rule

Change the size rule option as shown below.

change size rule

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

end reult - alpha version

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.

end result - final version

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.

Posted by SQLJason




Christopher Chamberlain

Hi Jason,
After reading a dozen of yor blogs, I am simply amazed by your talents. I just wanted to express that and consult your blogs as I expand my BI experience.
Cheers, Chris Chamberlain

Theron Knapp

Very Impressed. Nice chart/report.

Amazing feature, who would have thought Microsoft and Google would collaberate like this. Next bold question would be of course if you can call a subreport using the markers. For instance a chart or table data. Any suggestions?? Performance might be an issue, but might be worth a try.

Paul Playing with BI

Hi Jason,

thanks so much for taking the time to post this nice solutions. I just want to ask you what alternative could be use for the google charts, because this project is deprecated and for long term solutions is not usable. I am trying to embed tables in my report instead of pie charts, without success at the moment.

Hi Jason,

nice blog post.
As Paul mentioned, it seems like google changed the API. Do you have any suggestions how to combine the explained proceeding with googles 'new' API (e.g. for column chart: https://google-developers.appspot.com/chart/interactive/docs/gallery/columnchart).
I'm looking for another way than adding java script to the report.
Many thanks in advance.

Ok found it, the image charts are now deprecated but you can still use them. Here is the link:

Hi Jason,
Thank you for your blog. It helps me a lot. But image charts cannot be used now, could you please tell us how to generate pie chart with SQL Server from the javascript google provided? Thanks in advance.

Man, it’s 2019 and I’m stuck!!! could you please tell us how to do it from current google api?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.