Month: June 2013

Pie Charts on SSRS Map Reports

Pie Charts on SSRS Map Reports

June 28, 2013

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

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

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

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.

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, 7 comments
Split a Delimited Row into Multiple Rows using DAX Queries

Split a Delimited Row into Multiple Rows using DAX Queries

June 11, 2013

Recently, I got a question from one of my readers on whether there is a way to split a single delimited row into multiple rows using DAX. I had kind of demonstrated the opposite to Group Multiple Rows to Single Delimited Row in PowerPivot here and this was another interesting challenge. Read on for the solution.

Split a Delimited Row into Multiple Rows using DAX Queries

Business Requirement

Suppose there is a table which has two columns – StudentName and Subject.

Source Table

The end result should be

Required end result

Solution

We will be using DAX queries to generate a table that will have the desired output. For this demonstration, I will be creating a PowerPivot model and running DAX queries in DAX Studio on top of the Excel PowerPivot model.

1) Create a linked table for the Student.

Student table

2) Create a table called DummyTbl which is just a list of running numbers from 1 till n. The value of n should be equal to the max number of subjects that a student can have. In this demo, I am assuming that a student can have a max of 10 subjects, so my Dummy table will consist of numbers 1 – 10.

Dummy table

3) Now let us create the DAX queries in DAX Studio. First, we will create a computed column called SubCnt which will give the number of subjects for each row.

EVALUATE
SUMMARIZE (
    Student,
    [StudentName],
    [Subject],
“SubCnt”,
    1 + Len ( [Subject] ) – Len ( Substitute ( [Subject], “,”, “” ) )
  )

find number of subjects

4) The only way we can increase the number of rows of a table is by using the function CrossJoin. So let us CrossJoin the Student table with the DummyTbl.

EVALUATE
Crossjoin (
SUMMARIZE (
      Student,
      [StudentName],
      [Subject],
“SubCnt”,
      1 + Len ( [Subject] ) – Len ( Substitute ( [Subject], “,”, “” ) )
    ),
    DummyTbl
  )

crossjoined tables

5) Now filter the resultant table where SubCnt is less than or equal to Dummy column. Now we have got the exact number of rows needed for the end result.

EVALUATE
Filter (
Crossjoin (
SUMMARIZE (
        Student,
[StudentName],
[Subject],
“SubCnt”,
1 + Len ( [Subject] ) – Len ( Substitute ( [Subject], “,”, “” ) )
      ),
      DummyTbl
    ),
DummyTbl[Dummy] <= [SubCnt]
  )

filtered table

6) The only thing left is to split the delimited subjects to the single subject for each row. And the only way to split delimited values is to use the PATHITEM function. To use the PATHITEM function, we should substitute the commas (,) with the pipe (|) symbol.

EVALUATE
ADDCOLUMNS (
FILTER (
CROSSJOIN (
SUMMARIZE (
          Student,
          [StudentName],
          [Subject],
“SubCnt”,
          1 + Len ( [Subject] ) – Len ( Substitute ( [Subject], “,”, “” ) )
        ),
        DummyTbl
      ),
      DummyTbl[Dummy] <= [SubCnt]
    ),
“SubName”,
PATHITEM (
SUBSTITUTE ( Student[Subject], “,”, “|” ),
      DummyTbl[Dummy]
    )
  )

splitting delimitted subjects to individual subjects

7) Now all we need to do is to select the two required columns.

EVALUATE
SUMMARIZE (
ADDCOLUMNS (
FILTER (
CROSSJOIN (
SUMMARIZE (
            Student,
            [StudentName],
            [Subject],
“SubCnt”,
            1 + Len ( [Subject] ) – Len ( Substitute ( [Subject], “,”, “” ) )
          ),
          DummyTbl
        ),
        DummyTbl[Dummy] <= [SubCnt]
      ),
“SubName”,
pathitem (
Substitute ( Student[Subject], “,”, “|” ),
        DummyTbl[Dummy]
      )
    ),
    [StudentName],
    [SubName]
  )
ORDER BY [StudentName]

end result

Hopefully this post will help you when you encounter a similar situation!

Posted by SQLJason, 3 comments