Pages

Monday, March 26, 2012

Finding Nearest Stores using SSRS Map Reports

April is turning out to be a busy month for me. Apart from taking the beta SQL Server 2012 certification exams and some reviews, I am also speaking at the Charlotte SSUG and at SQL Saturday #118 (Madison) and #130 (Jacksonville). I have also submitted at a couple of other SQL Saturdays, so if you do see me speaking at a SQL Saturday close to you, don’t forget to give a shout! As my topic for the first two sessions are on Spatial Reporting in SSRS, I was preparing for it and that is when I thought of posting one of my demos as a blog.

Finding nearest stores in SSRS

This post will teach you how to implement a report to find the nearest stores using the spatial features in SQL Server and SSRS. As some of you might know, I am living in Charlotte and Harris Teeter is one of the grocery stores that I frequent. For this demo, I would be finding some of the nearest Harris Teeter stores from a list of my hangouts. Follow the instructions below to replicate it:-

1) Create the table structure for storing the locations of Harris Teeter.

CREATE TABLE [dbo].[Harris Teeter](
    [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]

Also, create the table structure for storing a list of the hangouts.

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) Load some sample data for Harris Teeter stores in Charlotte.

INSERT INTO [dbo].[Harris Teeter] ( [Location], [PostCode], [Latitude], [Longitude] )
VALUES ('Quail Corners - 204','NC 28210-5803',35.116603,-80.858247),
('Old Towne Mall - 294','NC 28226-7159',35.117117,-80.824515),
('Morrocroft Village - 160','NC 28211-3570' ,35.152698,-80.825796),
('Park Selwyn Terrace - 35', 'NC 28209',35.1616695,-80.8492303),
('Colony Place - 4','NC 28226',35.106549,-80.806327),
('Park Road - 218','NC 28209-2229',35.1767066,-80.8510191),
('Cotswold Mall - 208','NC 28211-2802',35.177524,-80.801119),
('Arboretum - 30','NC 28226',35.096321,-80.78463),
('Myers Park - 12','NC 28207',35.1901493,-80.8231644),
('Kenilworth Commons - 61','NC 28203',35.2026843,-80.8455712),
('Ballantyne Commons - 11','NC 28277',35.05313,-80.848995),
('Stonecrest Shopping Center - 66','NC 28277',35.059911,-80.816675),
('Providence Commons - 45','NC 28277',35.066452,-80.7717459),
('Sardis Crossing - 171','NC 28270',35.138476,-80.740138),
('Central Avenue Location - 201','NC 28205-5108',35.219757,-80.809982),
('The Shops at Blakeney - 27','NC 28277',35.036336,-80.806711),
('Uptown Charlotte - 205','NC 28202-1603',35.2330664,-80.846148),
('Rea Village Shopping Center - 40','NC 28277',35.052441,-80.770867),
('The Shoppes at Ardrey Kell - 317','NC 28277',35.02431,-80.847881),
('Plantation Market - 147','NC 28105-6725',35.082745,-80.732972),
('Steele Croft - 88','NC 28278',35.103305,-80.990847),
('Matthews Township - 157','NC 28105',35.125179,-80.710001),
('Mintworth Commons - 174','NC 28227',35.172913,-80.709081),
('Weddington Corners Shopping Center - 343','NC 28104',35.023379,-80.760665)

For getting the data, I used the Harris Teeter website to get the address of the stores in Charlotte and then used this site to geocode the address to latitude and longitude. Using the same method, I also populated the MyHangouts table with the data below

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) Once that is done, we will have to convert the Latitude and Longitude to spatial data of type geography. Execute the code below for the same:-

UPDATE [Harris Teeter]
SET [GeoL] = geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + CAST([Latitude] AS VARCHAR(20)) + ')', 4326)

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

4) Create a new report and a datasource pointing to the database where the tables are stored. Create a dataset query named DST_Hangouts for selecting the data from the [MyHangouts] table.

5) Create a new report parameter which will source data from DST_Hangouts. The value will be the spatial field - GeoL while the label will be the Location.

RP_MyLoc parameter properties

6) Create another report parameter – RP_Cnt which will be of integer data type. This will be used for specifying the number of closest stores you want to see.

RP_Cnt parameter properties

7) Make another dataset query – DST_HT which will filter the list of hangouts based on the parameter selected. This dataset query will be used in the map to show the selected hangout.

8) Now, make the pivotal dataset query of this post – DST_TopHT which will be used in the map to calculate the closest stores from the selected hangout

SELECT        TOP (@RP_Cnt) Location, GeoL.STDistance(@RP_MyLoc) AS distance, GeoL, Latitude, Longitude
FROM            [Harris Teeter]
ORDER BY distance

9) Optionally, we can also include a spatial query – DST_Circle which will draw a circle of 1 km diameter around the selected hangout so that we get an idea of the scale.

SELECT        ID, Location, Latitude, Longitude, PostCode, GeoL.STBuffer(1000) AS GeoLocation
FROM            MyHangouts AS H

I have filtered this dataset by the selected hangout in the Filters tab of the daataset properties.

Filtering the dataset query

10) Now drag and drop a map from the toolbox and then add two point layers based on the datasets - DST_Top HT and DST_HT. Also add a Bing maps layer and a polygon layer for the dataset DST_Circle.

Map report design

You can see that I have used the marker type as PushPin for the DST_HT point layer and circles for the DST_TopHT point layer to differentiate both of them. It would be good to add a table also which will show the top stores and the distance.

11) Now preview the report and you should be able to see the top N stores nearest to your selected hangout.

SSRS Nearest store report

You can change the selections or the top count value and see that your report changes accordingly. You will also notice that the Bing Maps layer is data aware and centres / zooms dynamically based on the data.

SSRS Nearest store report - selection change

You can do much more like visualizing the colour of your points based on the distance or any other measure in your warehouse, which will be helpful in making a decision. For eg, there might be a store which is not the closest but is running a sale as shown in the image below.

Sale in stores

I have visualized the colors of the stores based on the distance, and in addition to that, stores running a sale are shown with a thick black border. So from this, I can see that the nearest store is at Morrocroft Village, but if I drive an extra 2 miles, I can shop at the Park Selwyn Terrace where I can save some money potentially. The choice of colors or the visualization in itself might not be appropriate, but hopefully this helps to explain the available features. As this turned out to be a pretty long post, I haven’t included all the steps as I normally do. If you do feel lost, feel free to mail me and I can send a copy of the report rdl to you. Time to give my aching fingers some rest now Smile

Sunday, March 18, 2012

Overlapping Charts in SSRS using Range Charts

How fast time flies! Another new version of SQL Server has been launched and it seems like it was only a few days ago that SQL Server 2008 was released. Those days, I was mainly focussing on SSRS, and I can’t express in words how happy I was when I saw all the new features in SSRS 2008 and 2008 R2. Today, I was reading through a great post by one of my favourite bloggers, Hilmar Buchta on SSRS Bar Chart Tips and Tricks and this prompted me to add a chart tip of my own here – Overlapping charts in SSRS.

Overlapping charts in SSRS

A quick look into this requirement and many of the BI professionals would say that it is not possible to implement this chart in SSRS. Quite often we underestimate the power of SSRS (I myself didn’t know how to implement some of the charts that Hilmar mentioned in his post) and hence I feel it is important to share and increase awareness through blog posts and other mediums. Follow the instructions below to replicate this chart:-

1) Make a new report and use the query below to make a new dataset.

SELECT        '2009' AS Year, 'Q1' AS Quarter, 45 AS Sales
UNION ALL
SELECT        '2009' AS Year, 'Q2' AS Quarter, 30 AS Sales
UNION ALL
SELECT        '2009' AS Year, 'Q3' AS Quarter, 25 AS Sales
UNION ALL
SELECT        '2009' AS Year, 'Q4' AS Quarter, 50 AS Sales
UNION ALL
SELECT        '2010' AS Year, 'Q1' AS Quarter, 50 AS Sales
UNION ALL
SELECT        '2010' AS Year, 'Q2' AS Quarter, 30 AS Sales
UNION ALL
SELECT        '2010' AS Year, 'Q3' AS Quarter, 35 AS Sales
UNION ALL
SELECT        '2010' AS Year, 'Q4' AS Quarter, 60 AS Sales
UNION ALL
SELECT        '2011' AS Year, 'Q1' AS Quarter, 55 AS Sales
UNION ALL
SELECT        '2011' AS Year, 'Q2' AS Quarter, 50 AS Sales
UNION ALL
SELECT        '2011' AS Year, 'Q3' AS Quarter, 65 AS Sales
UNION ALL
SELECT        '2011' AS Year, 'Q4' AS Quarter, 75 AS Sales

2) Make a column chart and use Sales in the Data Values, Year in the Category and Quarter in the series.

Column Chart

3) Right click on the Year category group and check the category group name. Rename it to a user friendly name like Year as shown below

Change category group name 

4) Create a new calculated measure by adding a new data value and enter the code below

=Sum(Fields!Sales.Value, "Year")

Measure expression

5) Change the chart type of the new measure to Range Column. Ensure that the chart type of the Sales measure is still column. Also bring the Sales measure to the bottom as shown in the image below

Column and range chart

6) Change the color of the Range Column as Aqua (or any color you like) from Automatic, so that all bars of the range column have the same color.

Change color of Range chart

7) You can enable the data labels for the Sales measure and disable the legend for the new calculated measure. Now if you preview the report, you will get the required result.

Overlapping bar chart

You can use the same technique (with a little modification in the dataset query) to implement charts like shown below

Overlapping stacked charta

Hopefully, this post will help in getting your creative juices flow when you are faced with a tough SSRS chart requirement!

Saturday, March 10, 2012

Heat Maps for SSRS using Map Control

I have always been inspired by a particular quote from Arthur C Clarke - “The only way of finding the limits of the possible is by going beyond them into the impossible”. The more you think of it, the more you make sense out of it. If you decide in your mind that something is impossible, you will never even try to make it happen. Consider the example of the 10-second barrier in athletics. Rather than a physical barrier, it has always been more of a psychological barrier. How else can you explain the significantly high numbers of athletes that have broken the barrier in the last 2-3 years when compared to the 20 years between 1968 and 1988? Being mindful of that, I have always tried to be open when discussing requirements and always keep a secret list with me of the requirements that I have not been able to fulfil. One of my earliest list items was that of a squarified Heat Map (or Treemap as they are generally called) in SSRS. Even though there is no out of the box way to do it in SSRS, it was said to be possible by using the map controls and has been shown by Teo Lachev a long time ago. I had even progressed as far as making a T-SQL procedure which calculates the coordinates of the heat maps but it just wasn’t that elegant enough to be used in a production system. However, a recent blog by Richard Mintz on the Squarified Heat Maps has finally made me strike off this requirement from my list as completed.

HeatMaps in SSRS

To implement heat maps in your reports, follow the steps below:-

1) Download the HeatMap.dll file from here and save it in a location in your hard-drive, say C:\HeatMap (You should see the Download option on the File Menu). This dll file is the compiled version of the code given in Richard’s blog.

2) Open SQL Server Management Studio and then execute the following code in the database that you want to create the assembly.

CREATE ASSEMBLY HeatMap from 'c:\HeatMap\HeatMap.dll' WITH PERMISSION_SET = SAFE

3) After that, execute the following code to register the stored procedure

CREATE PROCEDURE dbo.CreateHeatMap (@Width real, @Height real, @SqlStrng nvarchar(4000))
AS
EXTERNAL NAME HeatMap.StoredProcedures.TreeMapGeography

In case you get an error saying that CLR is not enabled run the code below to enable CLR before creating the procedure again

EXEC sp_configure 'show advanced options' , '1';
go
reconfigure;
go
EXEC sp_configure 'clr enabled' , '1'
go
reconfigure;
-- Turn advanced options back off
EXEC sp_configure 'show advanced options' , '0';
go

4) Now create a new report and a new dataset. For this report, I am going to be using a SQL query from the AdventureWorks database which will give me the Subcategory Name and Order Quantity. Order quantity will be the measure which will determine the size of the rectangles within the heat map. Enter the code below for the dataset

exec dbo.CreateHeatMap 20, 25, 'select  sum(FIS.OrderQuantity), EnglishProductSubcategoryName
from dbo.DimProductSubCategory SC
inner join DimProduct P
on SC.ProductSubcategoryKey=P.ProductSubcategoryKey
inner join dbo.FactResellerSales FIS
on FIS.ProductKey =P.ProductKey
group by EnglishProductSubcategoryName
order by  sum(FIS.OrderQuantity)  desc'

The first 2 parameters within the CreateMap procedure are used for setting the width and the height of the heat map and this can be customized as per your needs. The third parameter is basically the select statement which should return the measure used for determining the rectangle sizes as well as the names used for labelling the rectangles within the heat map. Ensure that the Text option has been selected for the query and it should look like shown below

Spatial Dataset

5) Now make another dataset which will have the analytical data used for visualizing the rectangles with colours. For this demo, I am using a MDX query from the AdventureWorks cube which will return the Subcategory Names and Reseller Gross Profit.

Analyticaal Dataset

6) Drag and drop a Map report item from the toolbox to the design layout and select SQL Server Spatial query as the data source. On the next screen, select the  spatial dataset which we had created in Step 4.

Choose spatial dataset

7) On the next screen, ensure that Geo is the spatial field and the Layer type is Polygon. Click next.

8) Choose Color Analytical Map  the map visualization and click next. Then choose the dataset that we created in step 5 as the analytical dataset.

Choose analytical dataset

9) Specify the match fields as Name and SubCategory as shown in the screenshot below and click on Next

Specify the match fields

10) In the next screen, choose the field to visualize as Reseller Gross Profit and click on Finish.

Fields to visualize

11) Now if the report is previewed, you can see a nice heat map which will have the dimension size based on the Order Quantity and the colours visualized based on the Gross Profit.

HeatMap Preview

If are interested in map/spatial reports, you might want to check out some of my related posts here.

Update (11/03/2012)

To make it culture insensitive, the code has been modified and a new version of the compiled code has been posted here.

Monday, March 5, 2012

Making a Tag Cloud with SSRS Rich Text

One of my first blogs here was based on the Rich Text functionality in SSRS 2008. However I found very little use of that in my projects and did not explore that area much. However a recent question in the forums made me rethink on the way I thought about the rich text functionality.
SSRS Tag Cloud
The question was whether we could create tag clouds in SSRS. A tag cloud (word cloud, or weighted list in visual design) is a visual representation for text data, typically used to depict keyword metadata (tags) on websites, or to visualize free form text. 'Tags' are usually single words, and the importance of each tag is shown with font size or colour. This format is useful for quickly perceiving the most prominent terms and for locating a term alphabetically to determine its relative prominence. I have attached an image of my blog’s tag cloud below:-
My Blog's tag cloud
Follow the steps below to reproduce the solution:-
1) Create a new report and use the query below for the dataset so that we can get the keywords as well as the count
SELECT        'SSRS' AS Keyword, 36 AS Cnt
UNION ALL
SELECT        'SSAS' AS Keyword, 26 AS Cnt
UNION ALL
SELECT        'MDX' AS Keyword, 20 AS Cnt
UNION ALL
SELECT        'Interview Questions' AS Keyword, 18 AS Cnt
UNION ALL
SELECT        'Personal' AS Keyword, 17 AS Cnt
UNION ALL
SELECT        'Activities' AS Keyword, 16 AS Cnt
UNION ALL
SELECT        'SQL' AS Keyword, 15 AS Cnt

Name the dataset as DataSet1
2) Click on Report on the top menu, and then click on Report Properties. Select the code tab and then paste the code given below
Dim public SMax as Integer = 7
Dim public SMin as Integer = 1
Dim public HtmlTag as String = ""
Dim Public FontSize as Integer = 5

Public Function GetFontSize(ByVal NMin AS Integer, ByVal NMax AS Integer,ByVal Num AS Integer) AS Integer
FontSize =  (SMin + ((Num-NMin) * (SMax-SMin)/(NMax-NMin)))
Return Num
End Function

Public Function BuildHtmlTag(ByVal Keywrd as String, ByVal Num as Integer) AS String
HtmlTag = HtmlTag & "<font size=""" & FontSize & """>" & Keywrd & " (" &  Num & ") </font>"
Return Keywrd
End Function

Public Function DisplayHtml() as String
return HtmlTag
End Function

It should look like below once that is done
Report Properties 
3)  Drag and drop a table to the layout. Enter the header of the first column as KeywordCount and in the expression for the data value, enter the code below
=Code.GetFontSize(Min(Fields!Cnt.Value, "DataSet1"), Max(Fields!Cnt.Value, "DataSet1"), Fields!Cnt.Value)
Now, enter the header of the second column as Keyword and in the expression for the data value, enter the code below
=Code.BuildHtmlTag(Fields!Keyword.Value, Fields!Cnt.Value)
Make sure to place the table in the top left corner and ensure it looks like below
Table which calls the report code
4) Change the Border Style property for both the columns to None from Solid. Then resize the columns and rows of the tablix to be as small as you can (but still in the top left corner so that it is the first report item to be evaluated when the report runs). The end result should look like shown below.
Resized tablix
Ideally, after this the tablix should be hidden when the report is previewed. You might also want to set the font colour to white and delete the headers in case the tablix is still shown. Now when you view the deployed report in Internet explorer, the tablix might push other report items down or to the left. So care should be taken to align your report items in rectangles.
5) Drag and drop a textbox wherever you want into the report and set the width of the textbox as required. Then enter the following expression
=Code.DisplayHtml()
6) Click on OK. Then select the expression and right click as shown in the image below.Placeholder properties
7) Click on the Placeholder Properties and then select the HTML option as shown in the image below.
Interpret HTML tags as style
8) With that last step, we are done and on clicking preview, we should see our neat little tag cloud.
Tag Cloud in SSRS
The tag cloud can also be sorted on the basis of keywords or keyword counts also by just sorting the results of the tablix. The code can also be modified to show different colours for each of the keywords and do further more stuff, as all you need to do is to generate the html tags. An example is given in the original forum post. Now I really deserve a mug of beer after this!
Note: I don’t really know much of VB.net coding, so optimization tips for the code part are welcome in the comments section.

Thursday, March 1, 2012

Adding Maps to SSRS Map Gallery

I love working with map reports and I don’t miss a chance to fiddle around with it. In fact, I am such a big fan that I think there would be very few sessions (in English, of course!) involving SSRS and maps that I would have missed, both online and live. Also, this is one of those topics that can make me reply on the SSRS forum even when I am at my lazy best. So the other day, someone was asking for a map of Continental Europe that could be used for making his SSRS report. As usual, I tried to go to the site that I always turn when I need a shapefile – http://diva-gis.org. But this was one of those rare occasions where I couldn’t find what I wanted.But that is when I thought of an alternative – create a map of Europe from my database (which already had a world map), embed the spatial data in a report and then send him that report so that he could add it in his Map Gallery.

Add Maps to SSRS Map Gallery

The source for the maps can either be a shapefile or spatial data. Though I am picking the spatial data as the source for this demonstration, the process of adding a map to the map gallery is going to be similar for both the type of sources. Follow the steps below on how you can add an existing map in your report to the Map Gallery:-

1) I had already got a table called dbo.World which has the spatial data at a country level for all the countries of the world. I ran a query to filter out only for Europe (minus Russia as it was taking up the entire map space, no offence meant) and got the following result.

1 Filtered query for Europe

2) Create a new report and name it Europe.rdl. Make a new data source and dataset after that. Use the same query that was used above as the dataset query.

2 Dataset query 

3) Drag and drop the Map report item from the toolbar and select the SQL Server spatial query as the data source in the map wizard. Click on next.

3 Source for spatial data 

4) Choose the existing dataset in the next screen and click on next.

4 Choose dataset

5) In the next screen, remember to check the tick box for Embed Map Data in this Report option.

5 Embed map data in report

6) After that, keep on clicking next till you reach the Finish button. Click on Finish and you should get the result as shown below.

6 Report design

7) Now save the report and then copy the report rdl from it's source location to C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\MapGallery. Note that the location would be in Program Files and not Program Files (x86) if you are developing in a 32 bit machine.

7 Add report to map gallery

8) Now you should be able to see the map of Europe in the map gallery when you create a new map in any other report on the same system.

8 View map in map gallery

Note that you will need to move the map to C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\ReportBuilder\RptBuilder_3\MapGallery if you need to use within ReportBuilder 3.

Now this rdl that has been created can be shared and that is what I intended to send to the person in the forum, but I found a project in Codeplex which already had a map for Europe - MapGallery of Reporting Services in SQL Server 2008 R2 and sent him a direct link to this. Have a look at the site and also contribute to the project if you have some shapefile which is not there already, so that others can also benefit from it.