Uncategorized

Download link for my 24 HoP Session

Download link for my 24 HoP Session

February 12, 2014

It’s been almost 3 months since I blogged and this has been the longest time I have been away from this space since I started blogging. There are a couple of reasons behind it (which included the suspension of my blog by Google for allegedly hosting malicious code!!!) but more on this later. For now I just wanted to post the download link for the demos used in my 24 Hours of PASS session – DataViz You Thought You Could NOT Do with SSRS. image The report solution files are available for download here. The rdl files for the 8 reports that I showed as well as the shared data sources are present in the zip file. You can run the reports by pointing the shared data sources to your local database for most reports. However, the following reports will not run as additional information or data is required – Squarified Tree Map.rdl and MCFC.rdl. However, you can learn more about these two charts by visiting my earlier blogs on Heat Maps for SSRS using Map Control and Linking and Brushing Visualization with SSRS. This was a sneak peak of my session that I am presenting at the PASS BA Conference. For people who are wondering what this is all about – On February 5, BA and BI community experts presented a series of 1-hour webcasts delivering best practices and expert tips for getting the most from your data. This 24 Hours of PASS event provided a sneak peek at what you can expect at the PASS Business Analytics Conference May 7-9 in San Jose, CA. Sign up today and you can use my discount code BASF2A to save $150 off the registration price. The 24 Hours of PASS session recordings (which includes my session also) are now available for streaming. Access the recordings for free now.

Posted by SQLJason, 4 comments

QR Codes in Power View

November 19, 2013

Today, I was reading a good article by Florian Mader – A GPS Photo Gallery in Power BI. That inspired me to write something on Power View and since I had not originally planned to write a blog today, I decided to write something short.   QR Codes in Power View

QR Codes are not available by default in Power View and it is normal to hear many people say that it is not possible in Power View. The reason behind such a conclusion is because Power View is not at all flexible and you don’t even have simple options to change your chart colors, let alone program a new chart type (which is not a bad thing, as the focus is on ease of use and simplicity. More options would make the tool more complex). However, in this case, we can utilize the power of the internet and Image URL feature of Power Pivot to generate QR Codes. For this post, I am using the model below with some sample data.

Model + Sample Data

The fact table consists of Customer name, Brand Name, Date and Sales. The requirement is to display the QR code for the selected Brand. For that, follow the steps below:-

1) To create the QR code, it is necessary to create a calculated field in the data model. Since the QR code should be there for each brand, the calculated field should also be in the Brand table. The formula for the calculated field QRCode is given below

=”http://qrcode.kaywa.com/img.php?s=8&d=” & [Brand] & “” & [URL]

QR Code Formula

My regular readers might remember this URL from my post – Generating QR Codes in SSRS. There are other QR code generating sites also, I am just using this as an example.

2) Once the URL is generated, make sure that the Data Category property in the Advanced tab is set to Image URL.

Data Category property

3) Now we can use the QRCode field in Power View to display the QR Codes. You might get a warning to enable external content, which is normal.

QR Code in Power View

4) We can also use it along with other visualisations like shown below.

QR Code along with other visualizations in Power View

The output of the QR Code from my smartphone is given below:-

Output

Limitations Now that said, there are a few limitations. It is absolutely necessary to have a calculated field to make the QR Codes. This means that the values will be pre-computed and we can not add a measure or some value that changes during the run time of the report to the URL of the report. Also, we can not make much changes to the size of the QR Codes as we don’t have a lot of control on that. This technique can also be used to get other chart types into Power View. However, the same limitations will apply. That is all for now, time for me to get back to baby-duty Smile.

Posted by SQLJason, 0 comments
Category Shading for Regions in Power Map

Category Shading for Regions in Power Map

November 12, 2013

It’s been a really busy month for me professionally (with the PASS Summit 2013,  SQL Saturday Charlotte BI Edition, etc) as well as personally (with a steady stream of family flying in to visit the new born baby as well as looking after a new born baby – how I miss my 8 hrs. of sleep!). I remember people saying to me that things are going to change drastically once you have a baby. Well, what can I say other than it’s 100% true Smile. Understandably, I don’t get the same amount of time to spend on blogging and with all the stuff that has been happening behind the scenes, I decided to procrastinate till everything gets a little bit more calmer. That is when I was reading through an article and fell upon this gem of a line from Zip Zaglar – “If you wait until all the lights are “green” before you leave home, you’ll never get started on your trip to the top”. So I decided to shake off my laziness and get started on blogging about a cool feature in Power Map that has not been explored or blogged much – Category Shading for Regions.

Category shading within Regions in Power Map

The objective of this post is to explore the category shading feature in Power Map as well as try to see what kind of questions can be answered (after all, a feature that just adds flashiness and no business value is not desirable). For this post, I will be using the stats for my blog for visitors from the USA by browsers.

Sample data from my blog stats- 2013 data

With this data, I created a simple Power Map visualization which shows the number of visitors by state. (Check out this link if you are new to Power Map or need more info on how to create this visualization).

Region shading in Power Map

With the above visualization, I can answer questions like – Which state in the US has the most number of readers? I can easily see that California and Texas are where I have got the most number of readers. Now that I have a general understanding of the visitors, I am going to ask a few more specific questions based on the browsers that they are using:-

I) What is the dominant browser for my readers in each of the states?

To answer this question, bring the Browser field to the category and now you should notice an icon which appears on the top-right of the category textbox (denoted by 2 in the image below). Click on the icon and select the No shading option as shown below.

Category shading in Power Map - Show full value

Now, I can see the results for myself. Interestingly, the dominant browser in almost all the states is Internet Explorer. There are a couple of states (6 to be precise) where Chrome is the dominant browser. This brings me to my next question.

II) How dominant are the leading browsers within each state with respect to their competing browsers in that state?

Click on the category shading icon and select the “Shade based on category value within location” option. Now you should see the below visualization.

Category shading in Power Map - Shade within location

From this, you can easily see the leading browsers based on the color as well as the shading shows the share for the leading browser. The darker the shade for the color, the more dominant the browser is within the region. For eg, you can see that Internet Explorer is pretty dominant in Alaska as well as North Dakota, while Chrome is pretty dominant in Wyoming. In California, you can see that IE is leading but not by much.

tooltips

However, when I saw the actual numbers in the tooltip, I found it to be really less. This brought me to my third question.

III) Which states have more visitors and what is the dominant browser there?

Click on the category shading icon and select the “Shade based on category value compared to all other values” option. Now you should see the below visualization

Category shading in Power Map - Shade compared to all other values

You can see that it is a refined version of the map which was obtained initially by not putting the category field. The darker the color, the more number of visitors I have. So from the above image, we can easily see that the top 4 states by readership counts are California (Internet Explorer), Texas (Internet Explorer), Washington (Internet Explorer) and North Carolina (Chrome). And now looking at the previous image (for Question II) in combination with this image, we can see that the difference is not much and that the competition between the browsers are pretty tight across the states. Also, for the three states where the difference is really big (Alaska, North Dakota and Wyoming), we can see that the counts are pretty small and hence not that significant.

IV) How do the states within the same dominant browser measure up against each other?

For this, click on the category shading icon and select the “Shade based on category value compared to other values in the category” option. Now you should see the below visualization.

Category shading in Power Map - Shade compared to other values in category

From this image, I can see that for Internet Explorer, the top 3 states are California, Texas and Washington & for Chrome, the top 3 states are North Carolina, Iowa and Utah. It is important to understand that there might be other states which has more value for that browser. For eg, California might have more readers using Chrome than North Carolina, but it is not taken into account since Chrome is not the dominant browser there. Time to finish this lengthy post and this will be all from me today. But before I sign off, I wanted to let you know that you can animate this kind of visualization across time by dragging the date field to the time-play axis. This can answer the same 4 questions we asked above across time, which is pretty powerful. Also, we can look at the existing set of data with Power View and answer more interesting questions – a sneak peek of a simple dashboard is given below. Have fun with Power BI till we speak again! Smile

Power View dashboard - Browser Wars

Posted by SQLJason, 3 comments
PASS Summit & SQL Saturday Charlotte (BI Edition) 2013

PASS Summit & SQL Saturday Charlotte (BI Edition) 2013

October 11, 2013

In less than a week, I will be joining the biggest gathering of my #SqlFamily in my own place of residence – Charlotte, NC. The PASS Summit is easily the best conference for SQL Server professionals and is worth every single cent that you paid (or are thinking of paying). If you haven’t registered, make sure that you register for it now by following the link below- REGISTER NOW for PASS SUMMIT 2013 This year, we have also extended the #SqlFamily experience by conducting a SQL Saturday the day after PASS Summit in Charlotte. There is a balanced spread of BI, DBA as well as non-technical sessions and the schedule can be found here. So if you are here for the Summit, don’t forget to register for a FREE day of training and an extended experience with our #SqlFamily. The link to register is given below REGISTER NOW for SQL Saturday Charlotte (BI Edition) SQL Saturday Charlotte BI Edition 2013 And if you are at either one of the events, don’t forget to say a hi to me at any of the below places:- – Pretty much floating all around the place during the Summit (Oct 16 – 18). – Oct 16 – Room 202 A-B 10:15 – 11:30 AM : Visualizing John Snow’s Cholera Map Using Microsoft BI  – Oct 17 – Room 203 A – 1:30 – 2″:45 PM : Geospatial Analytics Using Microsoft BI – Oct 19 – Find me in the White Organizer shirt during SQL Saturday Charlotte BI edition Hope to see a lot of you pretty soon! Smile

Posted by SQLJason, 0 comments
How to make Map Reports in SSRS

How to make Map Reports in SSRS

September 29, 2013

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 http://tinyurl.com/SQL2012Eval

2. AdventureWorksDW2012 Database. You can download it from http://tinyurl.com/Adw2012SqlDB

3. AdventureWorks Multidimensional Models for SQL Server 2012. You can download it from http://tinyurl.com/Adw2012CubeDB

4. Some sample shapefiles for the United Kingdom, which can be downloaded from http://tinyurl.com/ShapefileUK

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

UNION ALL

SELECT ‘N Ireland’ AS Country, 300 AS Sales

UNION ALL

SELECT ‘Scotland’ AS Country, 350 AS Sales

UNION ALL

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.

clip_image002

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.

clip_image004

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 http://tinyurl.com/Jason-UploadSHP. 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 http://tinyurl.com/Jason-AddMapToGallery

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 http://diva-gis.org. 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

SELECT NAME_1, NAME_2, geom

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

SELECT NAME_1, NAME_2, LEN(NAME_2) AS Sales

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.

clip_image006

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.

SELECT DISTINCT NAME_1

FROM GBR_adm2

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

SELECT NAME_1, NAME_2, geom

FROM GBR_adm2

WHERE (NAME_1 = @RP_Country)

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

SELECT NAME_1, NAME_2, LEN(NAME_2) AS Sales

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.

clip_image008

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

clip_image010

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.

clip_image012

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

FROM GBR_adm1

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

clip_image014

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

FROM GBR_adm1

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.

clip_image016

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 http://tinyurl.com/Shape2Sql.

clip_image017

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 http://tinyurl.com/FindLatnLong 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

) ON [PRIMARY]

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 http://tinyurl.com/ImageToMaps 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

clip_image019

The query is available in http://tinyurl.com/SpatialLogo 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!

September 11, 2013

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.

themes

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.

image

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

Conclusion

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

September 9, 2013
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

August 14, 2013

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

August 13, 2013

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

July 25, 2013

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