How to make Map Reports in SSRS

How to make Map Reports in SSRS

I was going through some of my old documents and found this article on map reports which I had written for a SSRS book (which didn’t go through because of some time related issues). I thought it might be useful to those who are looking to explore map reports in SSRS. So here it goes:- Map reports have been a part of SQL Server Reporting Services from SSRS 2008 R2. However, they are usually a grey box for developers and hence, not been used to their full potential in most of the organizations. This chapter will introduce you to the basic concepts involved in map reports and then take you through some of the common scenarios involved while building map reports.

Basic Concepts

1) Geometry and Geography: These are the two spatial data types that are available in SQL Server 2008 and above. Geometry imagines that the world is a flat rectangle while Geography takes the curvature of the spherical earth into account. Depending on your application, you can choose between the two data types for your spatial purposes. Geography data types would be more precise over long distances as the earth is actually spherical, but it would require more overhead in setting up the spatial data as additional parameters have to be provided to account for the roundness of the earth. Geometry on the other hand is easier to setup but might not give you the precision over long distances.

2) Source for Map Reports: SSRS 2008 R2 and above allows two main map sources – ESRI Shapefiles and SQL spatial. Shapefiles are a popular geospatial vector data format which can be used in a lot of geographic information softwares. A shapefile will consist of at least 3 mandatory files (of extension .shp, .shx and .dbf) to store core data and might have some other files for improving performance. Apart from shapefiles, the spatial data can be stored in SQL Server database using the data types geography or geometry and used as a source for SSRS map reports. SSRS also has a predefined set of maps in the Map Gallery, which is essentially spatial data embedded in reports and this can also be used as a source for your map reports.

3) Layer Types in Map Reports: There can be 4 types of layers in SSRS Map reports namely Polygon, Line, Point and Bing Map. Polygon layers are used for displaying outlines of areas like administrative boundaries for countries, states, etc. Line layers are used for displaying the lines for routes, roads, paths, etc. Point layers are usually used for displaying the markers for point locations. The Bing Map layer adds a background of Bing Map tiles that correspond to the spatial data available in the map viewport.

Prerequisites To recreate the scenarios explained in this chapter, you would require the following:-

1. SQL Server 2012. You can download an evaluation copy from

2. AdventureWorksDW2012 Database. You can download it from

3. AdventureWorks Multidimensional Models for SQL Server 2012. You can download it from

4. Some sample shapefiles for the United Kingdom, which can be downloaded from

Initial Setup

1. Start by creating a new report project using SQL Server Data Tools (SSDT).

2. Design a new shared data source based on the Microsoft SQL Server provider that connects to the sample AdventureWorksDW2012 database and name it DS_SQL.

3. Design a new shared data source based on the Microsoft SQL Server Analysis Services provider that connects to the sample AdventureWorks Multidimensional database and name it DS_SSAS.

I) Creating Map Reports from Shapefiles

This section will demonstrate how to create a map report from a shapefile and visualize it with the measures present in your relational or multidimensional database.

1. Add a new report to the project and name it ‘1 Report from Shapefiles

2. Create a new data source which points to the shared data source, DS_SQL

3. Design a dataset called DST_Analytical_UK_C and populate it with the SQL query below

SELECT ‘England’ AS Country, 500 AS Sales


SELECT ‘N Ireland’ AS Country, 300 AS Sales


SELECT ‘Scotland’ AS Country, 350 AS Sales


SELECT ‘Wales’ AS Country, 200 AS Sales

4. Drag and drop a map report item from the toolbox to open the Map Wizard. In the first screen, select ESRI Shapefile as the source of spatial data. Browse to the folder which has the saved maps for the sample UK shapefiles and select GBR_adm1.shp. Click on Next.

5. You should be able to view the map and change its resolution or crop it in this screen. You can also add a Bing Maps layer in this screen if needed. Click on Next.

6. Choose the Map Visualization in this screen. Since we have chosen a polygon shapefile, we get the options for Basic Map (which just displays the map), Color Analytical Map (which visualizes the data by varying color) and Bubble Map (which visualizes the data by bubbles of varying sizes). The last two options would require a dataset for getting the measure information to visualize data. Choose the Color Analytical option for now and click on Next.

7. In the next screen, select the analytical dataset DST_Analytical_UK_C that we had created in Step 3.

8. In this screen, we will have to specify the match fields in the spatial and analytical data. Select the checkbox for NAME_1 in the spatial dataset field and select Country in the dropdown for analytical dataset field. You can see the selected fields getting highlighted in the window panes below. Click on Next.

9. Choose an appropriate theme and select [sum(sales)] from the dropdown list of the Field to visualize option. Choose the Color Rule as Red-Yellow-Green. You can choose to display the labels also if you want. Click on Finish. 10. Now you can preview the report and see your first map report.


FAQs regarding Map Reports from Shapefiles

1) Why is that some of my map regions are not getting visualized and are being displayed in white color?

This is a clear indicator that the names in the match fields for the spatial and analytical data are not the same. An example is present in our current example where DST_Analytical_UK_C has a field value of ‘N Ireland’ while the shapefile has a field value of ‘Northern Ireland’. You can change the value to Northern Ireland in the analytical query but this might not be a valid scenario in most of the cases where the data comes from the Data Warehouse and we would not want to tamper with the Data Warehouse. In such cases, we can change the field value of the shapefile by going to the design mode for the report, right clicking on the polygon region (in this example, Northern Ireland) in the map report item and selecting Embedded Polygon Properties. Now the NAME_1 field can be changed from ‘Northern Ireland’ to ‘N Ireland’ in the popup window and all the regions should be visualized now.


2) Why is it that I am getting a system exception when I try to deploy some of my map reports?

This happens normally when you are working with shapefiles in excess of 4MB. By default, the maximum size of a rdl report which can be deployed is 4MB. This can be easily changed by replacing <httpRuntime executionTimeout = “9000” /> with <httpRuntime executionTimeout = “9000” maxRequestLength=”1000000″ /> in the Web.config file which by default is found in C:Program FilesMicrosoft SQL ServerMSRS11.SQL2012Reporting ServicesReportServer. The reporting services might need to be restarted for the change to be reflected.

3) OK, I have succeeded in deploying my report to the ReportServer. However the report takes a long time to render. Why is that?

This should come as no wonder if you are working with large shapefiles. Most of the shapefiles that are available online exceed 8MBs and it would be prudent to trim them down the size by changing the resolution in the map wizard. For example, I was able to trim down the size for the current report using this method from 8.5MB to less than a MB without much difference to the map outlines. Another alternative would be to upload the Shapefiles to the ReportServer and then use them as the source for your map reports as is shown in this link This could help in decreasing the rendering time as well as making the shapefiles available for other reports.

4) Why is it that I see only the maps for USA in the Map Gallery? How can I add my own maps there?

Currently, only the maps for USA are there in the Map Gallery. However, you can add your own maps by following the instructions in this link

5) Where do I find some sample shapefiles to start exploring map reports? There are plenty of sites which offer free shapefiles for at least the country level administrative boundaries. One of them is For more detailed level shapefiles (like zip codes for Netherlands), you might need to buy it from some vendors or make your own shapefile using tools like ArcGIS.

II) Creating Map Reports from SQL Spatial Data

This section will demonstrate how to create a map report from spatial data stored in SQL Server and visualize it with the measures present in your database.

1. Create a new table called dbo.GBR_adm2 in the database which corresponds to the shapefile GBR_adm2 in the sample. (Read the FAQs below on how to import spatial data to the database).

2. Add a new report to the project and name it ‘2 Report from SQL Spatial

3. Create a new data source which points to the shared data source, DS_SQL

4. Design a dataset called DST_Spatial_UK_D and populate it with the SQL query below


FROM dbo.GBR_adm2

Note that the table dbo.GBR_adm2 contains the equivalent spatial data of the GBR_adm2 shapefile in our samples.

5. Design a dataset called DST_Analytical_UK_D and populate it with the SQL query below


FROM dbo.GBR_adm2

6. Drag and drop a map report item from the toolbox to open the Map Wizard. In the first screen, select SQL Server Spatial Query as the source of spatial data. Click on Next.

7. Choose DST_Spatial_UK_D as the spatial dataset in the screen and click on Next.

8. You should be able to view the map and change its resolution or crop it in this screen. You can also add a Bing Maps layer in this screen if needed. Click on Next.

9. For the purpose of exploring a new map visualization, select Bubble Map and click on Next.

10. Choose DST_Analytical_UK_D as the analytical dataset and click on Next.

11. Select NAME_2 as the matching field for both spatial and analytical datasets and click on Next.

12. Select the checkbox for Use bubble sizes to visualize data and choose the Data Field as [Sum(Sales)]. Click on Finish.

13. Now you can preview the report and see the result.


III) Parameterizing Map Reports

Most of the times, it is necessary to add report parameters to filter out the data so that we can focus better on a particular region. This section will provide a walkthrough on how to add parameters to a map report

1. Open the 2 Report from SQL Spatial report and add a new dataset, DST_RP_Country with the query given below.



2. Add a new report parameter, RP_Country with available values from the DST_RP_Country dataset.

3. Modify the query of the DST_Spatial_UK_D dataset as shown below



WHERE (NAME_1 = @RP_Country)

4. Modify the query of the DST_Analytical_UK_D dataset as shown below


FROM dbo.GBR_adm2

WHERE (NAME_1 = @RP_Country)

5. It would be good idea to add a Bing Map layer to the background so that we can understand the location better. For this, click on the polygon layer to display the Map Layers panel on the right hand side. Then click on the Add Layer icon and select the Tile Layer option from it.


6. Click on Preview and you can see that the map changes with the selected parameter value.


IV) Implementing Drill Down in Map Reports

This section will provide details on how you can implement the drill down feature in map reports. For the same, we will be making use of the two reports that we have already created – 1 Report from Shapefiles as the parent report and 2 Report from SQL Spatial as the child report.

1. Open the report – 1 Report from Shapefiles in design mode and click on map report item to bring the Map Layers panel on the right hand side.

2. Right click on the polygon layer and select the Polygon Properties.

3. Select the Action tab on the property popup window, and select the Go to Report radio button.

4. Specify the 2 Report from SQL Spatial report as the child report and map the RP_Country parameter with the Country field as shown in the image below. Then click on OK.


5. Once this is done, you can preview the report and see that the second report gets displayed with the appropriate filtered data on clicking a particular country in the first report.

V) Aggregating Spatial Data in Map Reports

Just like we aggregate our normal relational data, there might be scenarios where we would like to aggregate spatial data and display it in a report. An example would be to aggregate custom sales regions of an organization at a country level. This example would take you through such a similar implementation.

1. Create a new table dbo.GBR_adm1 with spatial data which corresponds to the shapefile GBR_adm1 in the samples.

2. Add a new column called Sales_Region to the table dbo.GBR_adm1 and give a value of North to Scotland and Ireland, and South to England and Wales.

3. Create a new report 3 Aggregate Spatial Data and add a dataset called DST_Spatial_AggUK with the query below

SELECT SalesRegion, geography::UnionAggregate(geom) AS geom


GROUP BY SalesRegion

Note that geography::UnionAggregate and geometry::UnionAggregate are two new spatial functions introduced in SQL Server 2012 to aggregate geography and geometry data respectively.

4. Now make a Basic Map report with the above dataset and you can see the result below when you preview the report


The same effect can also be done by aggregating the spatial data in the reports instead of in the query.

1. Create a dataset called DST_Spatial_UK with the query below

SELECT SalesRegion, geom


2. Create a Basic Map report with this dataset as the source.

3. Click on the map viewport to bring the Map Layers panel on the right. Click on the polygon layer, and then press F4 to bring the property panel.

4. Modify the VectorData property as =Union(Fields!geom.Value)

5. Click on the Group property and add the SalesRegion field as shown in the image below. Click on OK.


6. Preview the report now and you should see the map of UK aggregated by SalesRegion as in the example above.

FAQs regarding Map Reports from SQL Spatial Data

1) All of my map reports are visualized in a single color even when I have not chosen the Basic Map option. Why is that?

SSRS has some color distribution options which govern the way colors are visualized in your map reports. By default, it is kept to Optimal option which create ranges that automatically adjust distribution to create balanced sub ranges. You might want to change it to EqualInterval option (which creates ranges that divide the data into equal range intervals), EqualDistribution option (which creates ranges that divide that data so that each range has an equal number of items) or Custom option (where you can specify your own number of ranges to control the distribution of values). This can be done by right clicking on the map layer and selecting Layer Color Rule option.

2) How do I import my shapefiles to SQL Server?

Currently, SQL Server does not have a built-in option to import shapefile data to SQL Server. One of the most popular ways to import shapefile data to SQL Server is by using the free tool Shape2SQL. This has a single screen, easy to use GUI and can be downloaded from


There are a few bugs with this tool and from my personal experience, in case you are not able to see the imported table in the database, it might be worthwhile to create the table structure beforehand and then run the software again

3) How do I add my own custom locations as a point layer in map reports?

There are many sites like which will return the latitude and longitude if you enter your address. This can be converted to spatial data using the function STPointFromText(). An example is shown below

1. Create a sample table

CREATE TABLE [dbo].[MyHangouts](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Location] [varchar](255) NULL,

[Latitude] [float] NULL,

[Longitude] [float] NULL,

[PostCode] [varchar](20) NULL,

[GeoL] [geography] NULL


2. Insert some data with the latitude and longitude

INSERT INTO [dbo].[MyHangouts] ( [Location], [PostCode], [Latitude], [Longitude] )

VALUES (‘Home’,’NC 28210′,35.1537875,-80.8502022),

(‘Office’,’NC 28211′,35.1493742,-80.8272008),

(‘PetSmart’,’NC 28217′,35.1385661,-80.8764557),

(‘The EpiCentre’,’NC 28202-2538′,35.225324,-80.842187),

(‘Library’,’NC 28211′,35.1513557,-80.8225257)

3. Update the spatial data column using the STPointFromText function

UPDATE [MyHangouts]

SET [GeoL] = geography::STPointFromText(‘POINT(‘ + CAST([Longitude] AS VARCHAR(20)) + ‘ ‘ + CAST([Latitude] AS VARCHAR(20)) + ‘)’, 4326)

geography::STPointFromText(‘POINT(‘ + CAST([Longitude] AS VARCHAR(20)) + ‘ ‘ + CAST([Latitude] AS VARCHAR(20)) + ‘)’, 4326)

4) Can I create my own custom shapes in the map reports?

Yes, you can create your own custom shapes in the map reports as long as you are able to provide the location points for it. You can use online image mapping tools like to get the point locations and then use the STPolyFromText() to convert it into spatial data. For eg, I have used a spatial query to get the result below


The query is available in if you want to have a further look. Conclusion SSRS spatial features take up the charting possibilities to a whole new level. In addition to the normal geo-spatial reporting capabilities, we can also use the spatial features to basically create any shape or chart that we require and visualize it with data. All we need are the locations of the shape as well as a little bit of creativity.

Posted by SQLJason, 9 comments
Power Map Preview – Exploring the NEW features!

Power Map Preview – Exploring the NEW features!

Woot! Microsoft just announced the pre-release availability of the refreshed builds for Power Map with some really cool new features and boy, I must say that I am PUMPED! For folks that know me, I have been doing quite a number of presentations on Power Map (and geospatial analytics in general) and there were a couple of features that I really really wished for in Power Map. Well, looks like the Power Map team heard my requests and I feel this release is a big step forward.

Power Map preview for Excel - new features

First of all, here are some useful links (including the download link for the refreshed bits):-

Now let’s see some of the new features that has come up since my last post on Power Map.

I) Recognizing geographic data and Plotting them on launching Power Map On launching Power Map, the columns with geographic data are automatically recognized and plotted. For example, I had some data of the population of North Carolina by zip code as shown below

sample data

Now when I launched Power Map, this is what I saw

recognizes geo data and plots them

The best part is that the geographic columns seemed to be recognized correctly even if the they are named something other than City / State / Zip Code, etc. For eg, I renamed the City to CVDS and still was able to see it correctly mapped to City.

works with different column names also

And anyways, you still have the option to change it from the drop down. All in all, great feature and saves me a couple of unnecessary clicks.

II) Support for Flat Map

I know, I know, all of you would be thinking – what’s so special about this? After all, what made Power Map unique was that it had the ability to do 3-D geospatial analysis. Well, that is pretty cool but there are certain times when only a flat map would do. For eg, when you are looking at a worldwide data like population by countries, you want to see both sides of the globe. Earlier in Power Map, it used to look like shown below:-

3D map - power map

We could see only one half of the globe and had to rotate to see the other half. Now we have this icon to convert the 3D map to a flat map (and back to 3D if required!)

Flat map - Power Map

This is REALLY cool! Open-mouthed smile

III) Support for Regions Visualization (Chloropeth Mapping)

Now this is the most awesome enhancement according to me – support for chloropeth mapping or shading a geographical area (and I had actually requested as a Connect suggestion during the beta release of GeoFlow). Let me show you how the visualization looks for the population data for North Carolina

Region Visualization - Power Map

Best part is that it is damn right simple – just change the visualization from Column to Region (and yes, that also means that we have a fourth visualization type now – apart from Column, Bubble and HeatMap)

new visualization type in Power Map - Region

The Region visualization is only present at a Country / Region, State / Province, County or Zip / Postal Code level (which means City is not available) and is available across all countries (not just the USA). If you try to use a geographical level which is not one of the above, you will receive the following error and the chart type will be changed to Column.

Power Map - Region charts work with Country/Region, State/Province, County, or Zip/Postal Code data

I LOVE this feature and more than that, adore the way how simple it is for users to geocode at that level (no need to bring in complex shapefiles to geocode county/zip code level data like in SSRS!). I can already see a lot of use for it.

IV) Change Color for Data Series

Now this is another simple but very useful new feature – the ability to change colors for data series. You can see this new option in the Layer options.

change color for data series

You can also see that the display options of the Themes button has changed and now the colors also show up.


V) Exporting Tours as Videos

A question that consistently makes the rounds when I present on Power Map is – “How do I “unlock” the tours from the Excel workbook? Is it possible to share it through any other medium?” Well, the answer has been No until now. The current build allows you to export your tours as a video and now this can be shared across all your channels. For creating videos, there is a new Create Video button on the ribbon and then you can select the video quality also.


To demonstrate the feature, I have exported a tour from one of my recent presentations. This tour is around 5 mins long and investigates the myth – Are Refugees flooding Industrialized countries? It took quite some time for me to export the tour as a video even in the 360p mode, but that is understandable I guess.

Power Map Tour exported as a video


Overall, I feel this is a build with some really awesome features. Apart from the new features I described, there are also some UI changes but these changes are simple enough to understand, so I am not going over them. There are still some really basic functionalities that are desired like filtering your data and drilling down on the geographic levels (and I found out at least one bug which I will be filing on Connect soon), but we should not forget that this is not the final version. So I am happy and I would encourage everyone to try out the new build.

Posted by SQLJason, 11 comments
Designing Bullet Graphs in SSRS – Part 3

Designing Bullet Graphs in SSRS – Part 3

The concepts in this 3 part blog series are heavily borrowed from the book Information Dashboard Design, authored by the data visualization guru – Stephen Few and is going to concentrate on the following topics:-

I) How to make Bullet Graphs in SSRS
II) Deciding the Color
III) Multiple points of comparison
IV) Alternatives to bar
V) Differentiating measures that need to be read differently
VI) Positive and negative values
VII) Values within a distribution
VIII) Future Projections Designing Bullet Graphs in SSRS - Part 3 In my previous post (Designing Bullet Graphs in SSRS – Part 2), I showed how to make alternatives to bar, how to differentiate measures and how to show positive and negative values in bullet graphs developed in SSRS. This post will touch upon the final 2 topics. VII) Values within a distribution Sometimes the value displayed in a bullet graph needs to be compared to the distribution of an entire set of values. Lets say, a teacher needs to see how individual students performed with respect to the class average. Values within a Distribution - Bullet Graphs For that, I have made a sample dataset shown below sample dataset results L1, L2, L3 and L4 are limits of the three ranges, Score is the score for that student, Avg is the average for that Subject in the class. Now let’s see how to make bullet charts for the data shown above. 1) Drag and drop a table in the report layout. Put Subject field in the Rows and then put a bullet chart in the data field. Select Score as the LinearPointer1 and Avg as LinearPointer2. basic bullet graph 2) Add 3 ranges as shown in step 5 of How to make Bullet Graphs in SSRSDesigning Bullet Graphs in SSRS – Part 1. The upper and lower limits should be L1 & L2, L2 & L3 and L3 & L4 respectively for the three ranges. Add ranges 3) Change the Scale properties to a minimum of 40 and maximum of 100 (we are hardcoding it now but ideally you should use expressions). Also change the Number format to #,00 instead of percentage. Scale properties 4) Change the bar to a cross mark as per the technique shown in Alternatives to BarDesigning Bullet Charts in SSRS – Part 2. Now the result should be similar to the image shown below bullet graphs with the empty spaces 5) Add 2 more ranges so that the empty spaces (for eg, 40 – 60 for English) can be filled. For that, right click on the scale and then select the Add Range option. Add ranges to cover empty spaces 6) Select the Start range as 40 and End range as L1. Set the Placement relative to scale option to Cross. Also, change the Fill color to No color. Range properties Repeat the same for the second range but set the Start range as L4 and End range as 100. Now, with a bit of the formatting techniques described in the previous posts, you should get the desired result. VIII) Future Projection With the normal bullet chart, it is easy to compare a measure with another measure, say Sales against Target. However, there are scenarios where you would want to track a measure against a future target, say YTD Sales against a yearly target. In such scenarios, it would be better to project the measure and then display it in a bullet chart as shown below Future Projection - Bullet graph In the above bullet chart, you can see that the Sales is 65, Target is 245 and Projected is 270. But more importantly, you get an idea that the current sales is on track to meet the target. For creating this type of bullet chart, I am using the following sample dataset sample dataset result Follow the steps below to achieve the same:- 1) Make a bullet chart with Projected as Pointer1 and Target as Pointer2. Change the color for the LinearPointer1 to #608fc7 (or whatever you prefer). Adding projection and target Also, set the scale properties and ranges appropriately. I used 0 to L1, L1 to L2 and L2 to L3 as the Start Range and End Range for the three ranges. 2) Now right click on the scale and add a third pointer. Add new pointer 3) Click on the properties for LinearPointer3 and select the value as Actual. Also change the color appropriately. Ensure that he Type and Width properties are set as shown in the image below (so that the marker is a bar instead of the default rectangle). LinearPointer3 properties Now we should be able to see the desired graph on previewing the report. With this, we have come to the end of the three blog series on bullet charts. Hopefully, this will come in handy when you are faced with a design related questions while developing bullet charts in SSRS.

Posted by SQLJason, 1 comment
Designing Bullet Graphs in SSRS – Part 2

Designing Bullet Graphs in SSRS – Part 2

The concepts in this 3 part blog series is heavily borrowed from the book Information Dashboard Design, authored by the data visualization guru – Stephen Few and is going to concentrate on the following topics:- I) How to make Bullet Graphs in SSRS
II) Deciding the Color
III) Multiple points of comparison
IV) Alternatives to bar
V) Differentiating measures that need to be read differently
VI) Positive and negative values
VII) Values within a distribution
VIII) Future Projections Designing Bullet Graphs in SSRS Part 2 In my previous post (Designing Bullet Graphs in SSRS – Part 1), I started off with how to make a bullet graph in SSRS and lightly touched upon some design related topics. This post will carry on from where I left. IV) Alternatives to Bar The primary measure of the Bullet Graph is typically depicted as a bar. Whenever we use a bar to encode a quantitative value, the scale should start at zero as per visualization best practices. For accurate comparisons, it is sometimes useful to narrow the quantitative scale of the bullet graph, which means not starting the scale at zero (for eg, in cases where we know that the values is always going to be between a particular range). In such cases, we can use a mark to encode the primary measure instead of the bar. Follow the steps below to reproduce the same:- First, let us see how a completion rate of 97.5% would look in a bullet graph against a target of 98 where the completion rate (primary measure) is visualized as a bar. Normal Bullet Graph You can see that it is very hard to notice the difference. However, see the difference below when we visualized the bar as a marker. Bullet Graph with Marker instead of Bar 1) To achieve the results, change the bar to a X symbol using the same technique as discussed in the previous post (see Multiple Points of Comparison) Pointer properties 2) Also change the scale properties as appropriate by using expressions (for this example, I hardcoded it as between 90 and 100, however the best practice is to use expressions to make it dynamic). Change Scale Properties V) Differentiating Measures that need to be read differently Consider the chart below where the Revenue and Expenses are placed next to each other. These measures need to be read differently as we know that having more of Revenue and less of Expense is good. The background colors help in a way to distinguish this as we can see that the light colors (which denotes good) are there at the end for Revenue and at the beginning for Expenses. Expenses and Revenue visualized as normal Bullet Graphs However, a better way to differentiate them would be to display them as below:- Reversing the scale direction for Expense Now straightaway we can see that Expenses is to be read differently than Revenue by the direction of the scale. To do this, follow the steps below:- 1) Right Click on the scale and select Scale Properties. Select Scale Properties 2) Check the tick box to Reverse Scale Direction. Reverse Scale Direction Now we should be able to get the desired result. VI) Positive and Negative Measures Bullet graphs which display both positive and negative values are especially useful when we try to compare variances between 2 measures (E.g., Sales vs. Target for customers). image To achieve the same, follow the steps below:- 1) For this demo, I am using a dataset that returns Customers, Sales, Target and the Low, Medium, High values of the qualitative ranges image 2) Drag and drop a matrix to the report layout. Now, add Customer to the Rows and a bullet graph to the column as shown below. Also add Sales as LinearPointer1, 0 as LinearPointer2 and Target as LinearPointer3. image I also made some modifications like changing the bar color, reducing the width & height of LinearPointer2 and changing the color to Gray (as it is supposed to be the 0 line and hence need to be differentiated). 3) I also changed the max and min value of the scale to 30 & -30 respectively. Scale properties 4) The 3 ranges are also changed as 0 – Low, Low to Medium and Medium to high. LinearRange1. Do for the other 2 ranges also Now we should get the desired results. The rest of the topics will be covered in my next post – Designing Bullet Graphs in SSRS – Part 3.

Posted by SQLJason, 0 comments
Designing Bullet Graphs in SSRS – Part 1

Designing Bullet Graphs in SSRS – Part 1

Two weeks back, I bought myself the second edition of  Information Dashboard Design, authored by the data visualization guru – Stephen Few. I had read the first edition but was intrigued by the 6 new chapters in the second edition. Hence, I decided to buy it and I must say that I am really happy with my decision. One of the new chapters that particularly struck me is on designing Bullet Graphs, a simple invention that Stephen Few had invented specifically for dashboards. There are a whole lot of posts on how to make the bullet graphs in SSRS, but most of them (if not all) just talk about how to develop a basic bullet graph. That is when I decided to write a blog series on how to design and develop advanced bullet graphs in SSRS. The concepts in this series is heavily borrowed from the book and is going to concentrate on the following topics:- I) How to make Bullet Graphs in SSRS
II) Deciding the Color
III) Multiple points of comparison
IV) Alternatives to bar
V) Differentiating measures that need to be read differently
VI) Positive and negative values
VII) Values within a distribution
VIII) Future Projections Designing Bullet Graphs in SSRS Part 1 I) How to make Bullet Charts in SSRS 1) For this demo, we are using a very simple dataset having Sales (primary measure), Target (comparative measure) and SalesPrevYear (another comparative measure)  as the resultant columns. Also, there are 3 other columns – Low, Medium and High, for denoting the upper limits of the ranges. Drag and drop a Gauge report item from the Toolbox and select the Bullet Graph. SSRS Select Gauge Type 2) Drag and drop Sales and Target into LinearPointer1 and LinearPointer2 respectively. Add pointers It is advisable to set the ToolTip property also for each of the Values. 3) Change the FillColor and FillGradientColor property for LinearPointer1 and LinearPointer2 as Black. Change colors 4) Change the FrameStyle property to None for the gauge. Set frame style to none 5) Right-click on the gauge, select Gauge Panel –> LinearRange1 Properties and then set the Start Range as 0 and End Range as [Sum(Low)] (selected from the dropdown). Set range properties Repeat the process for LinearRange2 (where the Start Range = [Sum(Low)] and End Range = [Sum(Medium)]) and LinearRange3 (where the Start Range = [Sum(Medium)] and End Range = [Sum(High)]). It is also advisable to change the default colors of the ranges (as per the guidelines given in the next section) using the Fill tab as well as set the Border to None. 6) Click on the Scale to set the format string to #,0 and MaximumValue property to =Sum(Fields!High.Value) Set Scale properties It is advisable to set the Font also to something smaller, usually between 8 and10 pt instead of the default 12 pt. 7) Most people developing Bullet Graphs in SSRS stop with the previous step. However, it is very important to resize the graph, as there is a lot of white space around the graph in SSRS (remember, one of the main reasons we use bullet graphs is because it is so space efficient). You can modify that by going to the Gauge properties and setting the X Position, Y Position, Width and Height as shown below. Resize bullet graph to utilize whitespace 8) You are ready to preview the Bullet Graph. Final Bullet Graph II) Designing the color 1) For the primary measure, the color must stand out and hence it is usually kept as Black. However, other colors can also be used (although I tend to exclude red and green) as is shown below. Primary Measure color 2) For the secondary measure, I usually keep it as black or dark grey (if dark grey is being used, care must be taken that it stands out from the background colors of the qualitative ranges). 3) For the background colors of the qualitative ranges, the overarching principle is to vary the colors by intensity rather than hue so that color blind viewers can also make distinctions. The common practice is to use varying intensities of black depending on the number of qualitative ranges (Two: #A6A6A6 and #E6E6E6, Three: #9A9A9A, #C0C0C0 and #E6E6E6, Four: #808080, #A6A6A6, #CDCDCD and #E6E6E6, Five: #808080, #A6A6A6, #CDCDCD, #E6E6E6 and #F6F6F6, although keeping it to around 3 would be optimal as more colors would make the chart difficult to understand). However other colors can also be used like shown below Background color for qualitative ranges III) Multiple Points of Comparison Sometimes there are more than one measures to compare against the primary measures. The bullet graph can accommodate one more measure gracefully by using another symbol. Any more than two comparison measures will complicate and slow perception. Follow the steps below to achieve the same in SSRS:- 1) Click on the scale, right click to bring up the Scale Properties and select the Add Pointer option. Add new Pointer 2) Add SalesPrevYear as LinearPointer3. Add SalesPrevYear 3) The default symbol is a rectangle for the pointer. However, it is better to use a X mark as the second pointer symbol as it is big emough to be noticeable and also we can find the centre of intersection. For that, I took an image of the x mark, uploaded it to the report and named it X. Change the Pointer Image properties – MIMEType as png, Source as Embedded, Value as =”X” and Width as 25. Add image for pointer 4) Preview the bullet graph. Preview bullet graph with 3 Pointers While I was showing this to my colleague Javier Guillen, he gave a feedback that the intersection point of the X symbol will not be visible if it is lesser than the value of the primary measure. For the same reason, I would recommend to change the color of the bar to something other than black and keep the X symbol to the black color. Change color fo primary measure This is it for now in this post, but do come back soon to read Designing Bullet Graphs in SSRS – Part 2  and Designing Bullet Graphs in SSRS – Part 3 for the rest of the topics.

Posted by SQLJason, 0 comments
Drill-down Behaviour in Power View

Drill-down Behaviour in Power View

I usually don’t write a lot of posts asking for favours from my readers (in fact, this is the first one), but an incident happened today at my client site which has aggravated a pet peeve that I have with Power View – drill-down behaviour in Power View. The drill-down behaviour in Power View is really unintuitive and this is at least the the third client of mine that has raised this issue of drill-down. That is when I decided to raise my voice and ask for your help too in letting Microsoft know about this issue, so that they can consider changing this in a future release. For those of you who don’t have the time to read this post of mine, I would really appreciate if you could vote this Connect issue that Leonard Murphy has raised. For those of you who have the time, follow my post below to see the current drill-down behaviour in Power View and why I feel that this is not right.

Drill down in Power View

For this demonstration, I am using Power View in Excel 2013 (although this behaviour can be reproduced in Power View for SharePoint also) and a very simple table (which shows the sales by countries and states) as my source.

Source Data

I) How to Implement Drill-Down in Matrix

1) Open Power View and then select the three columns to make a table.

Simple table visualization

2) Click on the Table icon and select the Matrix option. Now you can see that the table has been converted into a matrix.

Convert table to Matrix

3) Now select the Show Levels icon and click on Rows – Enable Drill Down One Level at a Time.

Enable drill down

4) You can see that we have achieved the drill down functionality in the matrix. Click on US and see the results.

Drill down in matrix - Power View

Also note the drill down and drill up icons.

II) How to Implement Drill-Down in Chart

1) As in Step 1 of previous section, select all three columns to make a table. Then click on the Bar Chart option and select Stacked Bar. Ensure that the Country and State are in the Axis and Sales is on Values.

Chart in Power View

2) You can see the drill-down functionality by clicking on Canada. Also note the drill-up icon on the top right of the chart once you have drilled down.

Drill Down in Chart - Power View

III) Unintuitive Cross-filtering behaviour

1) Everything looks fine when you just have one chart in the canvas. Now let’s see what happens when we have 2 visualizations – lets say, a bar chart with drill-down and a table which just has State and Sales.

Chart and table in Power View

2) Now when you single click on a country (say India) in the chart, the table gets cross filtered for the states of the selected country.

Chart cross-filters table

3) Next, see what happens when you double click on a country (say India).

Double clicking in chart removes cross-filter in table

You can see the bar chart has drilled down into the states for the selected country as expected but look what has happened to the table. The table is now showing states for all the countries and not just the states of the selected country. This is so unintuitive as a person seeing the report will expect to see only the sales of the three states of India. This can get even more confusing if the states are not displayed on the table, and there will be no way of knowing what has been cross-filtered on the table.

4) To add to the confusion, I can press CTRL and then click on all three states. Look at the image below for the results.

CTRL+Click on the three bars -> table is cross-filtered

You can see that the bar chart looks exactly the same as the previous image, but the table looks different. In short, the report looks exactly the same and there is no way to differentiate whether the data in the table is correct and if some cross-filter has been applied or not. What I would expect is that the drill-down always cross-filters the rest of the visualizations. Honestly, I can’t think of a scenario where I would not want the cross-filtering to happen and I would love to hear your views as comments.

IV) Call for Action – Vote up the Connect issue

If you agree with me that the cross-filtering behaviour is so non-intuitive, please vote up the connect issue. You can do so by clicking on this link, log-in and then clicking on the green up arrow.

Click to vote

Hopefully we will have enough votes for Microsoft to look into the issue seriously!

Posted by SQLJason, 11 comments
Power BI for Office 365

Power BI for Office 365

Great news!!! Today, at the Worldwide Partner Conference, Microsoft announced a new offering – Power BI for Office 365. Quoted from Microsoft – “Power BI for Office 365 is a cloud-based business intelligence (BI) solution that enables our customers to easily gain insights from their data, working within Excel to analyze and visualize the data in a self-service way. It works with Office 365 to help customers share insights, find answers and stay connected to their data from their favorite mobile devices.” Announcing Power BI for Office 365 For those of you who have the time, I would recommend going through the links that I have collected below. And for those who don’t have the time, let me do a very quick summary:- 1) Discover, analyze, and visualize with Power BI for Office 365, which includes Power Query (formerly known as Data Explorer), Power Map (formerly known as GeoFlow), Power Pivot (formerly known as PowerPivot – notice the space between the words) and Power View (for a change, I like that the names are standardized with a space after Power). 2) Create Power BI Sites to share live interactive reports. Power BI Sites are dedicated collaborative BI workspaces in Office 365 for sharing data and insights with colleagues. 3) Ask questions and get back instant answers with the new Q&A feature that uses natural language query technology to provide you with immediate answers in the form of interactive charts and graphs based on the questions you type into the speech bubble. 4) Browse Excel and Power View reports with HTML 5. Stay connected with the touch-optimized Power BI mobile app. BI users can access and receive live updates on their reports through their browser with HTML5 or through a mobile application designed for their tablet or touch-enabled device, either Microsoft Power BI for Windows or Microsoft Power BI for iPad. 5) Refresh your data on demand or define a schedule. The Data Management Gateway allows your IT department to enable data refresh from Office 365 to your favourite on-premises data sources. 6) No announcement on pricing or preview date (except for “later this summer”). The natural language feature looks pretty impressing even though I doubt whether the experience is going to be as seamless as shown in the demo. Also, the bubble chart shown in the demo (also shown in the image below) looked extremely jazzy but may end up just being eye-candy in real life. I would love to finally see the Mobile (and ofcourse iPad) solution come through with Power BI, although I am a tad bit disappointed that this feature is not there for the SharePoint version. Bubble chart from Amir Netz keynote demo - Power BI There are a couple of official posts on this offering and I have collated them below:- 1) What powers Power BI in Office 365?
2) Introducing Power BI for Office 365
3) Announcing Power BI for Office 365
4) Register for Power BI Preview Watch the Day 1 Vision Keynote from WPC 2013 on demand here! You can also read some of the initial reactions to Power BI below:- 1) Some Thoughts About Power BI – Chris Webb
2) Microsoft Office 365 Cloud Power BI – Jen Underwood
3) Microsoft Announces Power BI for Office 365 – Andrew Brust
4) Power Business Intelligence for Office365 – Jen Stirrup Exciting times indeed for Microsoft BI and Self Service BI in general. I can’t wait to get my hands on the preview.

Posted by SQLJason, 2 comments
Pie Charts on SSRS Map Reports

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

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

Split a Delimited Row into Multiple Rows using DAX Queries

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


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.

    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.

Crossjoin (
      1 + Len ( [Subject] ) – Len ( Substitute ( [Subject], “,”, “” ) )

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.

Filter (
Crossjoin (
1 + Len ( [Subject] ) – Len ( Substitute ( [Subject], “,”, “” ) )
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.

          1 + Len ( [Subject] ) – Len ( Substitute ( [Subject], “,”, “” ) )
      DummyTbl[Dummy] <= [SubCnt]
SUBSTITUTE ( Student[Subject], “,”, “|” ),

splitting delimitted subjects to individual subjects

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

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

end result

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

Posted by SQLJason, 8 comments
A Sample SSRS Dashboard and some Tips & Tricks

A Sample SSRS Dashboard and some Tips & Tricks

The other day, I was browsing for some sample SSRS dashboards and one of the top images that came up made me cringe. You might wonder what could be so bad in a dashboard as to make me cringe. Well, apart from the factor that the dashboard was openly flouting all the best practices for visualizations and that it would have misled countless people in virtue of being one of the top five images for the search term – ‘SSRS Dashboard’, it was made by ME. To be precise, this dashboard was made by me around 3.5 years back, a time when I believed more in ‘eye-candy’ rather than effective visualizations. That was when I felt that I owed a proper SSRS dashboard to the community.

SSRS Sample dashboard and some tips and tricks

First of all, let me start by saying that this dashboard was inspired (a polite way of saying – a complete rip-off Smile) from this Dundas Dashboard example. Personally, I felt that this was a very good dashboard example and I decided to reproduce it in SSRS. The result is given below:-

SSRS Sample dashboard

Let me tell you some of the things that I like in this dashboard:-

1) The colours are carefully chosen. Note how the dashboard is not a kaleidoscopic combination of different colours like it’s predecessor. Light tones of grey are used to depict the axis as well as the gridlines, while the charts are more darker which gives them more attention.Also, in spite of having 4X times more information than the previous dashboard, the new one doesn’t look crowded (thanks to the right choice of colours)

2) The dashboard focuses on the three main KPIs but also has an executive summary on the right. I also like the way each line represents a story for that KPI.

3) I particularly like the KPI boxes on the left. Each box has a current value (which is highlighted by the size), difference from YTD (which is the small box on the top right), a sparkline on the bottom as well as conditional formatting to show whether the KPI is above or below the target. I think it gives a sexy eye-candy sort of feel to the dashboard without bending the visualization best practices. Also, I prefer the faded red and green colours to the normal traffic light red and green colours that we see in usual dashboards.

4) The labels are all formatted appropriately (for eg, 3M instead of 3,000,000). This ensures that we don’t waste a lot of space for labels. With that said, let me go ahead and show you some of the tips and tricks I employed for making this dashboard:-

I) Alternating Background for the sparkline


You can see the above sparkline (which is for the four quarters) has an alternating background for the four quarters. By default, you can have alternating colours in SSRS chart background if you set the background color of the chart area to a color (say light grey) and the InterlacedColor property of the X axis to another color (say dark grey). Now the problem is that the line charts will start at the meeting point of the two colours (like shown below) instead of at the centre.

Normal alternating background for sparklines

One of the ways you can get this done is by adding 4 striplines to the X-axis. For the first and third stripline, ensure that the BackgroundColor is the same (lets say light grey) and the second and fourth should have the same BackgroundColor (which is dark grey). All the striplines should have a Stripwidth of 1. The IntervalOffset property should be 0.5, 1.5, 2.5, 3.5 respectively for the four striplines.

Stripline property

Click on the Y axis and press the delete button to remove it. For the X axis, click on it and set the LineStyle property to None, Major TickMarks–>LineStyle to None and HideLabels to True. This will give us the required result below. For use in the dashboard, remove the chart border also.

required alternate background for sparklines

II) Pretty KPI boxes

KPI boxes

The pretty KPI boxes are just a combination of textboxes and the sparkline we made above. This is how it actually looks in design mode

KPI boxes in design mode

Right now, I have hardcoded most of the stuff. But you can change it as required. For eg, to get the conditional formatting, you can write a simple expression in the Color property of the textbox. The only other thing is to have different font sizes / font styles for the $ and numeric value. To demonstrate that, I pull in a textbox and type $ in it. After that, I drag and drop a field from my dataset (SAC) to the textbox.

ssrs textbox

Now I can individually select $ and give it a different font style / font size and also do the same for SAC (whose expression I modified to =Sum(Fields!SAC.Value, “G1_B1”) from =Fields!SAC.Value)

ssrs textbox - selected text properties

III) Formatting the labels

To format your labels in thousandss, you can use the format – 0,’K’

Label format - thousands

Similarly, if you want to format in millions, you can use – 0,,’M’. Note that you are just increasing a comma.

Label format - millions

IV) Correlated column charts

correlated column charts in SSRS

For this, add a normal column chart with 3 measures.

column chart

Now change the PointWidth property of the second measure to 0.6 and third measure to 0.3.

PointWidth property

Now right click on the Chart Area, and click on Properties. Check the ‘Enable 3D option and set the rotation, inclination and wall thickness to 0.

3D option

After that, set the BackgroundColor property of the chart area to No Color. Now you should have the required correlated column chart.

final correlated chart

Well, there are a few more tips in the dashboard that I made (even though I have hardcoded a LOT of stuff, since this was just a demo) and if you want, you can download the rdl file of this report from here. Hopefully, I have redeemed myself by creating a better dashboard than the original one! Smile

Posted by SQLJason, 44 comments