# SSRS

## Scatter Line Charts in SSRS

Sometime during the last month, I saw an image of an Archimedean Spiral and was wondering whether I could recreate the same in SSRS. The Archimedean Spiral is actually a scatter line chart (a combination of scatter chart and line chart). I have over 6+ years of experience creating visualizations using SSRS, but the closest I had come to such a visualization was the Scatter Chart.

A scatter line chart is used either to show the relationship among the items in several distinct series of data or to plot a line using x and y coordinates. The x and y coordinates of the data marker are determined by two data values. A scatter line chart draws attention to uneven intervals in data. This type of chart is often used to plot scientific data, and can highlight the deviation of collected data from predicted results. A scatter line chart can also be used to organize data chronologically (even if the data is not in chronological order). For eg, consider the Scatter Chart below

A scatter chart doesn’t tell the full story as we don’t know the chronology in which the data was collected.

A scatter line chart can give that extra bit of information as you can see from the image above. To create a scatter line chart, you need to have at least two measures (for the X & Y coordinates) and a category group. Follow the steps below to create an Archimedean spiral using the scatter chart:-

1) Create a dataset with the two measures (X & Y coordinates) and a category. Since I am trying to replicate the Archimedean spiral, I used the formula given below to come up with the coordinates and category.

You can see that my category (the Pos field) is just a running number used for determining the order.

2)  Drag and drop a Smooth Line with Markers chart into the layout. Use Y as the value and Pos as the Category group.

3) Once you are done with that, click on the values and press F4 to bring up the chart series properties. Under Data, expand DataPoint property and then expand Values. Now, type the following formula in the X property as shown in the image below

=sum(Fields!X.Value)

Note that X is the name of the field in our dataset and should be replaced by whatever is the field name that should be present in the X axis.

4) Preview it, and you should be able to see our Archimedean Spiral.

One thing to note here is that the lines are connected based on the sorting order of the Category group field. So in case you are using a name field like month (January, February, etc), ensure that the sorting order is based on the month number (else you might not get the right results). Feel free to download the report file from here. Though scatter line charts might not be as useful as sparklines, they have their place in data visualization and it is always good to know that you can make one using SSRS.

## Risk Matrix Chart in SSRS

Of recent, I have been getting mails from my readers whether I have forsaken Reporting Services for Analysis Services in my blog. I want to reassure all of you that this is not the case and that I will be equally focussing on both the technologies. Just that there is a lot of development happening in the AS side and it is very important for all of us to keep on expanding our skillsets and building our expertise. That said, I am writing this post on how to create a Risk Matrix chart in SSRS for all of you guys as a new year gift from my side.

A risk matrix chart, as the name suggests, is used for performing risk analysis. Typically, we combine the Likelihood and Impact ratings of an event to arrive upon a risk score, which aids in deciding on what action to take in view of the overall risk. For the purpose of this post, I have just got 3 levels for the Impact and Likelihood (and this can be increased/decreased as per your requirement). For both Likelihood and Impact, a rating of 1 means Low, 2 means Medium and 3 means High. Now I have categorized the risk score into Low, Medium, High & Critical and would like to plot my events as a scatter chart against this. For that, follow the steps below:-

1) Create an image with the required risk scores in excel / PowerPoint / Paint. I came up with the following image.

This will server as the background image of our scatter chart. Add this image to the report.

2) Create a report with the required data sources and dataset. In this case, I have just made a sample dataset

SELECT     ‘Project A’ AS Project, 0.5 AS Likelihood, 0.8 AS Impact
UNION ALL
SELECT     ‘Project B’ AS Project, 0.8 AS Likelihood, 1.3 AS Impact
UNION ALL
SELECT     ‘Project C’ AS Project, 0.9 AS Likelihood, 2.5 AS Impact
UNION ALL
SELECT     ‘Project D’ AS Project, 1.4 AS Likelihood, 0.9 AS Impact
UNION ALL
SELECT     ‘Project E’ AS Project, 1.5 AS Likelihood, 1.5 AS Impact
UNION ALL
SELECT     ‘Project F’ AS Project, 1.2 AS Likelihood, 2.9 AS Impact
UNION ALL
SELECT     ‘Project G’ AS Project, 2.1 AS Likelihood, 0.2 AS Impact
UNION ALL
SELECT     ‘Project H’ AS Project, 2.4 AS Likelihood, 1.4 AS Impact
UNION ALL
SELECT     ‘Project I’ AS Project, 2.7 AS Likelihood, 2.9 AS Impact
UNION ALL
SELECT     ‘Project J’ AS Project, 1.6 AS Likelihood, 1.2 AS Impact
UNION ALL
SELECT     ‘Project K’ AS Project, 2.2 AS Likelihood, 2.2 AS Impact
UNION ALL
SELECT     ‘Project L’ AS Project, 1.1 AS Likelihood, 0.7 AS Impact

3) Go to the toolbox and drag and drop a chart item into the report body. Select the chart type as Scatter chart and click on OK.

4) Drag and drop Likelihood from the dataset fields list into the Values and then select Impact as the X Value in the chart. Also drop Project field into the Category Group of the chart.

5) Change the Axis titles to Likelihood and Impact for the Y and X axis respectively. Then go to both of the axis properties, and set the minimum as 0, maximum as 3 and Interval as 1.

6) Now click on the chart area and add the RiskMatrix image as the background image.

You might also want to hide the major gridlines for the horizontal and vertical axis.

7) Now with a bit of cosmetic changes to your marker colours, we arrive upon the end result. It is a good practice to enable the tooltips on the points, so that we can just hover our mouse to find out the project names.

The beauty of this approach is that it can be extended to a lot of other scenarios. For eg, I remember using this approach to answer a scatter chart with four quadrants question in the MSDN forum.

Ok, this is all from me for this year. As for my new year wish, I would like to quote Ann Landers-

“Let this coming year be better than all the others. Vow to do some of the things you’ve always wanted to do but couldn’t find the time. Call up a forgotten friend. Drop an old grudge, and replace it with some pleasant memories. Vow not to make a promise you don’t think you can keep. Walk tall, and smile more. You’ll look ten years younger. Don’t be afraid to say, ‘I love you’. Say it again. They are the sweetest words in the world.”

Wishing all of you a very prosperous new year!

## SSRS Charts with Data Tables (Excel Style)

Ok, this post was supposed to come out a lot earlier but I was just too lazy. Complacency is a struggle we all have to fight and I would be lying if I say that I am immune to all these. This post is a direct result of a few forum posts where people said that charts with data tables are not possible in SSRS (unless you add a tablix beneath the chart and spend umpteen hours trying to align both of them!). Working with SSRS is like making love to a woman – you need to know the woman and what makes her click well for a fabulous time. Well, you can still manage without knowing her well enough, just that it won’t be that awesome!

For people who are familiar with excel charts, adding a data table to a chart is a one-click affair. It is nice to see the data table just beneath the chart and it also serves as a legend. A simple example is shown below:-

In SSRS, having a tablix beneath the chart serves as an ok workaround as long as the number of fields in the category is fixed. When the number of fields change change (lets say, when the value of a report parameter changes), the alignment goes for a toss. Here, I will be demonstrating a much more elegant way to add the data tables than trying to align the tablix and chart. 1) Create a simple dataset from the AdventureWorks cube, displaying the Month, Category and Internet Sales Amount. Add a parameter for Calendar year with a default value of CY 2007.

2) Drag and drop a matrix to the designer pane and then add the Category field on the row, Month on the column and Internet_Sales_Amount on the data as shown below

3) Add two rows outside group above the category row as shown below.

4) Now you can delete the top row (but not the associated group) containing the Category and the Month Header as shown below

5) Once that is done, drag and drop the month field to the textbox above the data value. Here, I just want to display the first three letters of the month (instead of having the month followed by year), so I am using the expression below directly:-

=left(Fields!Month.Value,3)

You might also want to do some quick formatting like hiding the textbox borders for the top row.

6) Once this is done, the only part remaining is to make a chart, format it and then place it on the top right textbox. For that, drag and drop a stacked column chart outside of the matrix, and then drag Internet_Sales_amount on the Values, Month on the Category Groups and Category on the Series Groups as shown below.

7) Right click on the vertical axis, select the Vertical Axis Properties and then modify the expression for Maximum from Auto to the expression written below

=1.2 * max(Fields!Internet_Sales_Amount.Value, “DST_Main”)

8) Delete the axis titles, chart titles and the horizontal axis from the chart

9) Hide the labels for the vertical axis.

10) We need to place this chart within the tablix and make it repeat for each of the month. But before that, we need to make sure that there are no white spaces within the chart area, and hence change the following properties for the Chart Area.

Also set the BorderStyle property for the Chart as None and disable the MajorTickMarks for the Vertical Axis. This will also help in making the charts look continuous.

11) Now as a last step, we need to ensure that the vertical axis is only displayed for the first chart, Hence, set the LineStyle property for the vertical axis as shown below =iif(Fields!Month.Value=First(Fields!Month.Value, “DST_Main”),”Solid”,”None”)

12) Drag and drop the chart into the tablix cell

13) To add a color as a column next to the Category, we can use the Custom Code technique for consistent colors described here. Then ensure that the BackgroundColor property for the column as well as the series color for the chart is the expression below

=Code.GetColor(Fields!Category.Value)

14) Now preview the report and you will get the intended result. Change the parameter value from 2007 to 2008, and you will still see that the alignment is proper (even though the number of bars have changed)

You can download the completed rdl file from here. Now you know which post to point to incase someone asks on how to create charts with data tables in SSRS

Update : 10 September /2015

For those looking to replicate this for the line charts, I have written a new post on #SSRS Line Chart with Data Tables (Excel Style) – http://www.sqljason.com/2015/09/ssrs-line-chart-with-data-tables-excel.html

## Linking and Brushing Visualization with SSRS

In spite of being in the US for more than 8 months now, I still haven’t lost touch with the English Premier League (football). For those who don’t know me, I am a big fan of Manchester United and hence by default, loathe Manchester City. They are like our biggest rivals and won the Premier League last season, and that too in the last minute of the last game! I never thought that the day would come when I would even mention them in my blog, let alone praise them. But then last week, they started a new crowdsourced analytics project called MCFC Analytics and I can’t stop thinking about what a fantastic idea it is. Needless to say, I was among the first set of people to request the data from them and have been playing around with it this week. I was also reading about the brushing and linking visualization techniques, and hence thought of using this data for illustrating this concept within SSRS.

Before I go further, let me explain what brushing and linking is. “Brushing lets the user selects data points that get highlighted in one or more views of the same data. When several views are involved, the fact that all of them highlight the same data points is commonly referred to as linking (and the views are called coordinated multiple views)” – Robert Kosara. For demonstrating this concept in SSRS, I am using 5 bar charts – Goals by Venue, Goals by Goals From, Goals by Substitutions, Goals by Team and  Goals by Opposition. Follow the steps below to reproduce the report:-

1) Download the data from http://www.mcfc.co.uk/mcfcanalytics and then import it to a table in your SQL Server database called Players.

2) Create a new report called MCFC, and point the datasource to your database containing the Players table.

3) Create 2 hidden report parameters – RP_Field and RP_FValue with no available values and All as the default value. The RP_Field will be used for determining the series field of the chart (can be Venue, GoalType, Starts, Team or Opposition) and the RP_FValue will actually pass the field value which is being clicked.

4) Make a new dataset called DST_Main and then use the query below as your dataset query

SELECT [Player Surname],
[Player Forename],
[Team],
[Opposition],
[Venue],
[Starts],
GoalType,
[Gls],
— Field used to dynamically calculate the series field
CASE
WHEN @RP_Field = ‘All’ THEN ‘0’
WHEN @RP_Field = ‘Venue’ AND @RP_FValue = [Venue] THEN ‘1’
WHEN @RP_Field = ‘Venue’ AND @RP_FValue <> [Venue] THEN ‘0’
WHEN @RP_Field = ‘Starts’ AND @RP_FValue = [Starts] THEN ‘1’
WHEN @RP_Field = ‘Starts’ AND @RP_FValue <> [Starts] THEN ‘0’
WHEN @RP_Field = ‘GoalType’ AND @RP_FValue = [GoalType] THEN ‘1’
WHEN @RP_Field = ‘GoalType’ AND @RP_FValue <> [GoalType] THEN ‘0’
WHEN @RP_Field = ‘Team’ AND @RP_FValue = [Team] THEN ‘1’
WHEN @RP_Field = ‘Team’ AND @RP_FValue <> [Team] THEN ‘0’
WHEN @RP_Field = ‘Opposition’ AND @RP_FValue = [Opposition] THEN ‘1’ ELSE ‘0’
END AS Srs,
— Field used to dynamically sort the charts
CASE
WHEN @RP_Field = ‘All’ THEN [Gls]
WHEN @RP_Field = ‘Venue’ AND @RP_FValue = [Venue] THEN [Gls]
WHEN @RP_Field = ‘Venue’ AND @RP_FValue <> [Venue] THEN ‘0’
WHEN @RP_Field = ‘Starts’ AND @RP_FValue = [Starts] THEN [Gls]
WHEN @RP_Field = ‘Starts’ AND @RP_FValue <> [Starts] THEN ‘0’
WHEN @RP_Field = ‘GoalType’ AND @RP_FValue = [GoalType] THEN [Gls]
WHEN @RP_Field = ‘GoalType’ AND @RP_FValue <> [GoalType] THEN ‘0’
WHEN @RP_Field = ‘Team’ AND @RP_FValue = [Team] THEN [Gls]
WHEN @RP_Field = ‘Team’ AND @RP_FValue <> [Team] THEN ‘0’
WHEN @RP_Field = ‘Opposition’ AND @RP_FValue = [Opposition] THEN [Gls] ELSE ‘0’
END AS SrsGls
FROM   [MCFC].[dbo].[Players]
UNPIVOT ([Gls] FOR GoalType IN ([Goals from penalties], [Goals Open Play], [Goals from Set Play], [Goals from Corners], [Goals from Direct Free Kick], [Goals from Throws])) AS [Gls]

5) Make 5 stacked bar charts with [sum(Gls)] as Values and [Srs] as the Series Group. The category for the five charts are Venue, GoalType, Starts, Team and Opposition respectively.

6) Change the Color expression of all the five charts as below =iif(Fields!Srs.Value=”0″,”Silver”,”00AFEF”) This is done so that we can see the brushing effect in the bars.

7) Add a textbox on the top of the report with RESET as the value. Set the action properties of the textbox as shown below

8) Set the action properties of the Venue chart as shown below

Similarly, set the action properties of the other 4 charts.

9) Set the sorting property for the category group as

=iif(Parameters!RP_Field.Value=”All” or Parameters!RP_Field.Value=”Starts”,sum(Fields!Gls.Value),Sum(Fields!SrsGls.Value))

You can also add the category group (for eg, the Venue chart will have Venue field) as a secondary sort option, as shown below.

This sorting will make it easier for us to understand the data.

10) Now preview the report and you should see it as shown below

11) Click on any of the bars, and you shall see the report getting the brushing effect. For eg, you can see it as shown below when Manchester City is clicked in the Team chart.

Through this chart, we can easily see that Man Utd had more home wins than away, scored more goals through open play and scored most against Arsenal. If you want to see which team scored most through Corners, click on the corners bar in Goals From chart.

Very easily, you can see that Manchester city scored the most and Blackburn Rovers conceded the most goals through corners. You can already see the power of linking the charts instead of keeping it as individual unlinked ones.

12) Click on the Reset button to bring the report to the original state in step 10. This post has been written keeping an experienced SSRS developer in mind. If you felt that the explanations are not enough, feel free to download the report file and check yourselves.

As always, I am interested in hearing your views and opinions about this. Feel free to comment on the MCFC Analytics project as well as this brushing concept.

Note: I am part of the core team that is organizing a SQL Saturday in Charlotte, NC on October 27. If you are around the North Carolina region, go and register yourselves for the event here. If you are far from us and can’t come for the event, spread some love on twitter and other social media sites using the hash tag #sqlsat174.

## Simulating Slicers in SSRS Reports

[Geography].[Country].CurrentMember .MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS
[Geography].[Country].CurrentMember.UniqueName
MEMBER [Measures].[ParameterLevel] AS
[Geography].[Country].CurrentMember .Level .Ordinal
SELECT
{ [Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel] } ON COLUMNS,
[Geography].[Country].Children ON ROWS
FROM [Adventure Works] The purpose of the parameters will be explained later when we are implementing the features. 4) Now create a dataset called DataSet1 with the query below WITH MEMBER [measures].[rsa] AS
IIf (
instr ( @GeographyCountry, [Geography].[Country].CurrentMember.UniqueName ) > 0,
1,
NULL
)
MEMBER [Measures].[ParameterValue] AS
[Geography].[Country].CurrentMember.UniqueName
SELECT NON EMPTY
{ [Measures].[Reseller Sales Amount], [measures].[rsa], [Measures].[ParameterValue] } ON COLUMNS,
NON EMPTY
{
( [Geography].[Country].[Country].AllMembers * [Date].[Calendar].[Calendar Year].AllMembers )
} ON ROWS
FROM [Adventure Works] This query will be used to generate a chart in our report. You might have noticed that the query is not filtered by the GeographyCountry parameter. The reason for it is that we would like to have a “brushing” effect on our chart where the unselected countries are displayed in a grey colour and the selected countries are highlighted (unlike the traditional reports where just the selected values are displayed). The calculated measure “rsa” will be used to determine if the countries are selected or not. Now your report data pane should look like shown below 5) Now create a simple chart with Reseller Sales Amount as the Value, Calendar Year as Category Group and Country as Series Group. Click on the Reseller Sales Amount value in the chart data and press F4 to open up the properties. Then enter the following expression for the Color property =iif(sum(Fields!rsa.Value)>0,”Automatic”,”WhiteSmoke”) This will give the brushing effect to the chart that we talked about before in step 4. 6) Now let us make a matrix and add the Country field from DataSet1 to the rows. Click on the textbox where the Country field was added and enter the expression below in the Value property under BackgroundImage =iif(sum(Fields!rsa.Value)=0,”BSU”,”BSS”) Also make sure to set the Source property as Embedded, MIMEType as image/png and BackgroundRepeat property as Clip 7) Now right click on the same textbox and select the Textbox Properties. Go to the action tab and select the Go to Report option. Specify the report name as Slicers (which is the same report) and pass the [ParameterValue] field to the GeographyCountry parameter as shown below This will give the feature (a) of the excel slicer that we discussed above, where we can click on a particular field and see the report filtered for that selected value. 8) Since we cant implement the CTRL key feature to add and remove values from the selected list, we are going to add one column on the right and left of the Country textbox. Then we can implement actions such that we can add values when we click on the left column (so I will keep a + symbol in that textbox) and remove values when we click on the right column (so I will keep a symbol). 9) Right click the textbox having + symbol, select the textbox properties and then go to the action tab. Then select the Go to Report option. Specify the report name as Slicers (which is the same report) and pass the expression below to the GeographyCountry parameter as shown below =split(join(Parameters!GeographyCountry.Value,”,”)+”,”+Fields!ParameterValue.Value,”,”) You can see that the expression first uses a join function to make the parameter object as a comma separated string, then concatenates the current selected value (note that the unique name of the country is being passed which is the ParameterValue field) and finally uses the split function to convert the comma separated string to the parameter object. The image is shown below for reference So clicking on the + symbol will give the feature (b) of the excel slicer that we discussed above, where we can click on a particular field and see it being added to the list of selected values. 10) Right click the textbox having – symbol, select the textbox properties and then go to the action tab. Then select the Go to Report option. Specify the report name as Slicers (which is the same report) and pass the expression below to the GeographyCountry parameter as shown below =split(
iif(Parameters!GeographyCountry.Value(0)=Fields!ParameterValue.Value,
replace(join(Parameters!GeographyCountry.Value,”,”),Fields!ParameterValue.Value+”,”,””),
replace(join(Parameters!GeographyCountry.Value,”,”),”,”+Fields!ParameterValue.Value,””)),
“,”) Removing from the list is a bit more complex as we have to check first whether the value which needs to be unselected is the first member of the parameter. If yes, then we will have to replace the value and a comma with an empty string, else it has to be a comma followed by a value. The image is shown below for reference So clicking on the – symbol will give the feature (c) of the excel slicer that we discussed above, where we can click on a particular field and see it being removed from the list of selected values. 11) To get the final reset feature, we can add an image to the top right corner of the tablix, with the expression below =iif(join(Parameters!GeographyCountry.Value,”,”)=join(Parameters!AllGC.Value,”,”),”All”,”Reset”) Make sure that the Source property is Embedded and MIMEType is image/png. So clicking this image will give us the feature (d) of the excel slicer. Now we need to set the action for the image. For that, select the Go to Report option and set the expression below for the report =iif(join(Parameters!GeographyCountry.Value,”,”)=join(Parameters!AllGC.Value,”,”),Nothing,”Slicers”) This will ensure that the action is enabled only if all the values are not selected and this is where we use the second parameter AllGC (for comparing whether the Geography Country is equal to AllGC). Also add the value [@AllGC] to be passed to the GeographyCountry parameter. 12) Now preview the report and you can see the chart getting changed as per the slicers. 13) Now, I would like to add a new feature also to the slicer which is the All But The Selected functionality. So when you click on the slicer button value for the first time, only that selected value is displayed. Currently, if you click on the same button again, the report refreshed but there is no change in the report as the same value is being selected. With this new feature that we implement, clicking a selected value will display all the value except the selected one, like shown below For this functionality, copy & paste the expression below instead of [ParameterValue] field in step 7. This expression will check if the value which is clicked is the only value in the selected list. If yes, then it selects all the values except that and if no, then it just displays the report for that selected value. =iif(join(Parameters!GeographyCountry.Value,”,”)=Fields!ParameterValue.Value,
split(iif(Parameters!AllGC.Value(0)=Fields!ParameterValue.Value,replace(join(Parameters!AllGC.Value,”,”),Fields!ParameterValue.Value+”,”,””),replace(join(Parameters!AllGC.Value,”,”),”,”+Fields!ParameterValue.Value,””)),”,”)
,Fields!ParameterValue.Value) 14) Preview the report and now you can also see this new feature added in your report. Now there is an obvious disadvantage with slicers in SSRS that the report needs to be refreshed and so you will see the “Loading” screen when the slicers are changed. But this is just a small issue and with fast reports, this might be under a second. I know this is a long post and that there are a lot of report expressions in this post, but if read carefully, you can get to know a lot of important concepts which can be applied to other scenarios. If anyone needs the report file, feel free to click and download it from the link. As for me, I am already thinking on another type of visualization involving these slicers, hope to put it out as a blog soon!

## SSRS Chart Issues in SharePoint Integrated Mode

Have you ever faced a problem that you had almost given hopes on, and then pushed yourself to that one last hopeless try which succeeded? Well, it happens a lot with me, I just seem to be very lucky in such scenarios. I have always felt that men try to over-romanticize the idea of discovering something by accident (remember the Nobel laureate Albert Szent-Györgyi saying – “A discovery is said to be an accident meeting a prepared mind”?). I don’t know how prepared I was but it sure was an accident that made this discovery. I had been dabbling with some SSRS reports in the SharePoint Integrated mode and was facing a lot of issues with the inline charts that I was using. This post will talk about the issues that I encountered as well as the solution which I discovered accidentally. To give an idea of the environment which had the issues, I was using SQL Server 2012 Reporting Services in integrated mode with SharePoint 2010. To recreate the issues, follow the steps below:- 1) Create a simple report with some charts embedded within a table or a matrix. I have created a data bar, bar chart, bullet chart as well as a line chart as an example. 2) Implement some actions as well as tooltips on the charts. As an example, I have implemented an action on the first databar to go to http://www.bing.com and tooltip expressions on all of the 4 charts. You can also implement actions and tooltips on some of the regular tablix columns without the charts. Preview it and make sure the tooltips as well as the chart actions are working. 3) Now deploy this report in Sharepoint and preview the report. Even though it is not that evident from the image, there were quite some issues that I faced with this report and I am listing them below:-
a) The actions that were defined on the frist bar chart was not working for some bars.
b) The tooltips defined for the second bar chart was not working.
c) The tooltips defined for the bullet chart was not working.
d) The tooltips defined for the line chart was not working. In addition to that, the lines appeared to be jaggy and seemed to have some pixels cut off from them. Click and expand the image above to see jagged effect on the line chart.
Also, the report seemed to take more time in displaying the charts in SharePoint than when I had previewed it in the SSDT environment. It is to be noted that the actions as well as the tooltips in the tablix columns without the charts worked fine. 4) Now to solve the issue, all you have to do is to enclose each chart in a rectangle before using it in the cell of the tablix. 5) Once you have done the above step for every chart, save and deploy the report to SharePoint. You can preview the report and see that now the tooltips as well as the actions are working for the charts. Also, the line charts seemed to be smooth and devoid of the jagged line effects seen earlier. Performance also seems to be much better than before. Moral of the story – If you are using inline charts in SSRS SharePoint Integrated mode and facing similar issues, then try to use the charts within a rectangle. This might save you some headaches later.

## Going beyond Geospatial Analysis with SSRS Map Reports

When I came to the US six months ago, I had set myself a goal of doing more presentations than I did last year in the UK (which was two – one at the London BI User group and another at SQLBits 9). Well, I am glad to say that I achieved this goal within the last two months where I was able to present at four SQL Saturdays (#118, #130, #129 & #121), one code camp (Carolina Code Camp) and at my local UG in Charlotte. I have already got two other SQL Saturdays confirmed – #122 (Louisville, KY) on July 21 and #158 (New York City) on August 4 (so if you are around, please don’t forget to come over and say a hi). One of my favorite topics to present is on Map Reports in SSRS, and a statement that I just can’t stress enough is that the map reports feature can be used much beyond geospatial analysis. You don’t necessarily have to restrict yourself with geographical boundaries, and this post will give an example on the same. (By the way, if you are reading this post before June 26 and are interested in learning more about map reports, you might want to attend my online session – ‘Fast Track to Spatial Reporting using SSRS 2012’ through PASS BI Virtual Chapter) The inspiration for this post is a demo from the Tableau product gallery where tooth decay is being visualized using images for the tooth, and I thought of replicating the same with SSRS. Follow the steps below to make a simple report:- 1) Get an image for the tooth set and then by using an online image mapping site like Image-Maps, you can trace the coordinates. Once you get the coordinates, you can convert it into polygons using the SQL function – geometry::STPolyFromText. You can download the SQL query for the tooth set that I created from the image below There are many other ways in which you can obtain the same results, though I usually use Image-Maps because it is free. 2) Create a table called dbo.Teeth and store the results of the above query into this table. 3) Create a table called dbo.[Teeth Data] and store the results of the csv sheet below into this table. 4) Now that you have both the spatial data as well as the analytical data, create a new report and make 2 datasets – DST_Spatial for the spatial dataset (which will have the columns from table dbo.Teeth) and DST_Analytical for the analytical dataset (which will have the columns from the table dbo.[Teeth Data]) 5) Drag and drop the map report item from the toolbox. From the wizard, select the SQL Server spatial query as the data source and choose DST_Spatial as the Spatial dataset. Select Color Analytical map as the map visualization and choose DST_Analytical as the analytical dataset. The match fields should be Name and Tooth as shown below:- 6) Select Decay_Scale measure as the field to visualize and click on finish. Now we should be able to see the map report item in design mode. 7) Click on preview and you should be able to see the result. Here, I have just made a very simple report and this can be extended with all the common SSRS features like drill-down, report parameters, color formatting, tooltips, labels, etc. Compare this sort of a spatial report against a tabular report. Here, a dentist might be able to make more sense of the effect in tooth decay on neighboring teeth and hence can be very useful. Hopefully, this short post will make you think of the map report item in a new light now.

## 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. 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),
(‘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. 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. 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. 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. 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. 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. 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. 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

Posted by SQLJason, 1 comment

## 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.

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.

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

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

=Sum(Fields!Sales.Value, “Year”)

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

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.

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.

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

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

## 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.

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:HeatMapHeatMap.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

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.

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.

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.

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

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

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.

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.