Thursday, February 24, 2011

Bubble Heatmap in SSRS Map Reports

Nowadays, the project that I am working on is keeping me quite busy and all the documentation work is driving me nuts (you wouldn’t believe me if I said I hate documentation Smile). But somehow, I do find time to respond to the steady flow of queries that keep coming  to me. And today, when one of my colleagues asked me how to implement bubble heatmap in SSRS map reports, I couldn’t resist the temptation to try it out at home and blog it down.

Bubble Map Report

For demonstrating the solution, I have downloaded a map of Africa along with some statistics on AIDS. Follow the steps below to implement the solution:-

1) Import the shapefile of Africa into the database (Read the first part of Import Shapefiles into SQL Server and Aggregate Spatial Data (Geometry) for more details). This is not a necessary step, but I find it easier to deal with spatial data in a table rather than reading from the shapefile at runtime or embedding it within the report.

[Shape2SQL for geometry shapefile conversion[9].png]

2) For the demonstration purpose, I have created two datasets in BIDS – one which will give the statistics on AIDS and the other one which will return the spatial data from the database.

1 Prepare Datasets

3) Now drag and drop a Map item from the toolbox and select the source of spatial data as SQL Server spatial query. Click Next.

2 Choose source of spatial data

4) Choose the dataset which has the spatial data and click on next.

3 Choose spatial dataset

5) In the next screen, the spatial field should automatically be detected and we can click on next straightaway.

4 Choose spatial data

6) Choose Color Analytical Map as the visualization technique and click next.

5 Choose map visualization

7) Choose the analytical dataset containing the statistics in the screen that comes and then click on next.

6 choose analytical dataset

8) Specify the common field in the spatial and analytical dataset which would be used for linking the map and the statistics. In this case, it is the Country field. Click on next.

7 Specify match fields

9) In the next screen, choose any of the option as we will soon be disabling the visualization in the next step. Click on Finish.

8 Choose color theme

10) Click twice on the map and press F4 to bring the MapPolygonLayer properties. Now disable polygon color rule as shown in the image below

9 Disable polygon color rule

11) Right click on the map and select the Show Center Points option

10 Select Show Center Points

12) Now, on right clicking the map again, the Center Point properties should be enabled and we can change the Center Point Color rule and Center Point Size rule.

11 Center Point size and color rule

13) After the above step and bit more messing up with the aesthetics, we end up with the final end result as shown below

Bubble heatmap

I am writing this post well past my bed time and so if I have missed out any vital steps, feel free to ask them in the comments section. I should be more than happy to detail them out for you. Time to catch a few winks now…Sleepy smile

Update (15/03/2011)

I had forgot to mention that this approach is for polygon based maps. If you have got a point based map, then the wizard itself will give you options to set bubble size and colour.

Thursday, February 17, 2011

Microsoft Community Contributor Award 2011

Just around a week ago, I had blogged about completing one year of active participation in the Microsoft communities. Today morning, I was pleasantly surprised to see the following mail in my inbox

MCCA letter

I am deeply humbled (and a little worried on where I would place the MCCA logo on my already overcrowded sidebar Smile) on receiving this and hopefully, the access to complimentary resources will act as an extra motivational factor for me to continue my activities. Well, enough of the boring acceptance speech and time to flaunt my latest logo


MCCA certificate

Saturday, February 12, 2011

PowerPivot tutorial videos

Waking up early morning for a technical webinar is so not me (I really love to have my sleep in the morning!); but then I was very interested in the PowerPivot webinar which was going to be taken by Rupesh Patric (Microsoft Partner Technical Consultant and an accomplished artist) exclusively for people from my company, MindTree Ltd. As I was the only one from UK, the time was adjusted for the majority people in India which meant I had to wake up early. Ofcourse, I knew I wouldn’t regret losing my beauty sleep from the very moment the presentation started and overall, it was a good introduction to PowerPivot. To top it all, Rupesh sent a goodie pack containing some video tutorials for PowerPivot and I thought it would be too selfish of me if I didn’t share it with you guys.



1.     Deck : SQL Server 2008 R2 Overview

2.    Book : Introducing SQL Server 2008 R2

3.     Hands On Lab - PowerPivot in SQL Server 2008

Set 1

1.     Presentation What's New in the Excel 2010 Client

2.     Demo What's New in the Excel 2010 Client

3.     Presentation Loading and Preparing Data for Analysis in SQL Server PowerPivot for Excel 2010

4.     Demo Loading and Preparing Data for Analysis in SQL Server PowerPivot for Excel 2010

5.     Presentation Creating Reports with SQL Server PowerPivot for Excel 2010

6.     Demo Creating Reports with SQL Server PowerPivot for Excel 2010

7.     Presentation Defining DAX Calculations with PowerPivot for Excel 2010

8.     Demo Defining DAX Calculations with SQL Server PowerPivot for Excel 2010

Set 2

1.     Part 0 Installing the PowerPivot Add-In for Excel

2.     Part 1 Using the PowerPivot Add-in to Import Data into Excel 2010

3.     Part 2 Import data from a SQL Server Database

4.     Part 3 Import Data from an Access Database

5.     Part 4 Using Copy-Paste to Import Excel data

6.     Part 5 Using a Linked Excel table to Import data

7.     Part 6 Compressed data in PowerPivot for Excel

8.     Part 7 Handling big data in PowerPivot for Excel

9.     Part 8 Importing SAP data in PowerPivot for Excel

10.  Part 9 Reports as data sources in PowerPivot for Excel

11.  Relationships - 1 Overview

12.  Relationships - 2 Creating Relationships

If you are interested in Business Intelligence video tutorials, I would recommend you to visit Microsoft’s Channel 9, plenty more of the good stuff there.

Update (18/03/2011)

Another great Microsoft Technet resource – PowerPivot for Excel (Business Intelligence) was brought to my notice by Michele Hart (Microsoft). This site serves as a one-stop place for your PowerPivot requirements, be it articles, whitepapers, blogs, samples, videos or information about PowerPivot books. But the best part is the Virtual Lab where you can actually work and get your hands dirty on a system. Check it out!

Wednesday, February 9, 2011

1 year of activity in MSDN Forums

Even though I had joined the MSDN Forums way back in 2009, I started using it regularly only from 2010 February. Looking back, it’s been a wonderful journey and I should say I have learned a lot from all of you wonderful guys contributing there. I didn’t do pretty bad myself as I raked in 6556 points from 1158 posts with 392 answers, all in the past one year.

MSDN Profile

My activity at the forums inspired me to take my career to the next level and even my blogs and networking are a direct result of this. In the past few months, my activity has reduced considerably but I intend to catch up soon and make up for it. I am also planning on getting more involved in the user group activities and taking some sessions, but more on it later.

Monday, February 7, 2011

Display Current Page Total in Footer

There is one interesting story about NASA floating around the internet for quite some time now. The story goes like this - NASA spent millions to develop a pen that could write in space under zero gravity while the Soviet cosmonauts just used a pencil. Even though this is just a myth, I found it pretty interesting as sometimes even I tend to overdo things and come up with complicated answers when simple solutions exist. One of my latest blunders was on how to display current page totals of a multi-paged report in the footer.

Page totals of current SSRS report

For demonstrating the solution, I have made a dataset which will return a 1 or 0 based on whether all the students of a state has passed for a particular subject. Since the number of rows returned would be pretty large, the report will span over multiple pages. The requirement is to display the total number of pass and fails of the current page in the report footer. The steps are given below:-

(I) Overkill Solution

Thinking there is no straight forward way of doing this, I tried to devise the solution by applying the concepts of limiting rows in a page and custom code.

1) In the design mode of BIDS, create a matrix with State, Sector and the Pass/Fail measure. Then use the technique detailed in this blog by Melissa Coates to limit rows on a page.

SSRS Limit rows in a page

The expression that I have used for this demo is


as I need to limit 12 rows in my page. This can be modified as per your requirements.

2) Click on Report on the top menu and then select Report Properties. Navigate to the Code section and paste the custom code given below

Dim public Sales as Integer
Dim public Shared FirstMember as Integer
Dim public Cnt as Integer=0

Public Function FindFirst(ByVal Sales AS Integer ) AS Integer
if (Cnt mod 12) = 1 then 
End If
return Sales
End Function

This code will sum up the values which are passed as input to the function and keep the result in FirstMember variable. Since we know that only 12 rows will be displayed in one page, a counter variable is used to reset the value of FirstMember once the 13th row or multiples of it are reached. Thus it is ensured that the sum of the values in each page can be obtained by accessing the FirstMember variable.

3) Edit the measure expression in the matrix and paste the following code there


This expression will invoke the function and pass the measure value each time a row is displayed.

Using function in expression

GHE is the name of my measure and Fields!GHE.Value should be substituted by your measure name.

4) Now you can directly use the following expressions in a textbox in your footer to get the number of pass and fail in each page.

="No of Pass in current Page =" & Code.FirstMember

="No of Fail in current Page =" & 12-Code.FirstMember

If the number of rows that you are using is something other than 12, then it should be substituted accordingly in the expression for number of fails.

Preview of report

(II) Simple Solution

1) Make a simple matrix with the required columns and measure (Here, the columns are State and Sector & the measure is GHE)

2) Paste the following expression in a textbox in the footer

="No of Pass in current Page =" & Sum(ReportItems("GHE").Value)

="No of Pass in current Page =" & Sum(iif(ReportItems("GHE").Value=0,1,0))

The GHE in ReportItems("GHE").Value is the name of the textbox in which the measure is placed. The name of the textbox can be got by right clicking on the textbox and then pressing F4.

Textbox name

3) Now the report can be previewed and the same result can be viewed.

Moral of the blog is to make sure that you do your research properly and then get it reviewed by your peers before coming up with complex solutions.