SSRS

Scatter Line Charts in SSRS

Scatter Line Charts in SSRS

January 28, 2013

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.

Scatter Line charts in SSRS

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

Scatter chart

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

For the same scatter chart, the scatter-line charts can be different

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. , r=a+btheta

Dataset query for Archimedean spiral

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.

Smooth line chart with markers

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)

Modifying X axis properties

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.

Archimedean spiral in ssrs

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.

Posted by SQLJason, 0 comments
Risk Matrix Chart in SSRS

Risk Matrix Chart in SSRS

December 30, 2012

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.

Risk Matrix chart in SSRS

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.

background image for SSRS Chart

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

Add image to 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.

scatter chart in ssrs

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.

Add x and y values as well as category of scatter 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.

SNAGHTML108f3df0

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

Add background image

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

remove major gridlines

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.

Risk matrix chart

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.

scatter chart with 4 quadrants

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! Smile

Posted by SQLJason, 2 comments
SSRS Charts with Data Tables (Excel Style)

SSRS Charts with Data Tables (Excel Style)

October 30, 2012

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! Winking smile

1 Charts with Excel style data tables

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

2 Excel chart with data table

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.

3 Dataset query

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

4 SSRS Matrix with fields

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

5 Add two rows outside group for matrix

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

6 Delete header rows

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.

7 Hiding textbox borders and setting month expression

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.

8 Chart

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”)

9 Chart vertical axis max property

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

10 Delete Axis titles

9) Hide the labels for the vertical axis.

11 Hide Labels

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.

12 Custom Inner Plot Position

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”)

13 Vertical axis property

12) Drag and drop the chart into the tablix cell

14 Chart in tablix

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)

15 Color of column as well as series

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)

Charts with data tables

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 Smile

 

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

SSRS Line Chart with Data Tables (Excel Style)

Posted by SQLJason, 61 comments
Linking and Brushing Visualization with SSRS

Linking and Brushing Visualization with SSRS

August 26, 2012

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.

Linking and Brushing in 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.

1 Parameter properties

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.

2 Chart properties

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

3 Reset button

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

4 Chart action properties

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

5 Other chart properties

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.

6 SortProperties

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

7 Report initial view

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.

8 Report brushing effect

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.

9 Report Final

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.

Download MCFC.rdl

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.

SQLSaturday_Flyer

Posted by SQLJason, 4 comments
Simulating Slicers in SSRS Reports

Simulating Slicers in SSRS Reports

July 5, 2012

One of my readers noticed that I had used something resembling excel slicers in my previous post and asked me more on how to implement it in SSRS. For people following the blogosphere, they must already be familiar with this idea as it was introduced almost an year back by Simon Sabin (blog | twitter) in his post as well as some sessions. He used custom code to simulate the slicer features and I thought it would be a good exercise for me to replicate the features (with some slight variations) using just report expressions and actions. Also, this scenario touches on some important SSRS concepts and will serve as a good example on how to play around with multivalued report parameters. image Before we go forward, let us look at some functionalities of an excel slicer using the below image as a reference. Initial State of excel slicer a) When we click on any of the product category, only the selected product category is shown (and the excel sheet also refreshes for the selected product category) as shown below Selecting Bikes and then selecting Clothing in slicer b) When we hold the Ctrl key and click any unselected product category, it is added to the selected list instead of being the only selection (as was the case in above) and the result is shown below Hold CTRL and then selecting Clothing and then Accessories c) When we hold the Ctrl key and click any selected product category, it is removed from the selected list and the result is shown below Hold CTRL and then selecting Clothing d) When we click on filter icon on the top, it resets to the initial state with all product categories and the filter icon gets greyed out. Return to initial state when filter icon is clicked We will not be able to replicate the features in SSRS exactly as there is no way to know whether the CTRL key has been pressed or not. However, we will be implementing a workaround to have the same feature. For this post, I would be using the AdventureWorks cube for building my queries. Follow the steps below to replicate the solution:- 1) Create a report named Slicers and then create a new Analysis Services datasource connection to the AdventureWorks cube. 2) Save the four images (All, BSS, BSU, Reset) below in your local system and then add them to the report. AllBSSBSUReset 3) Then create two new multivalued report parameter called GeographyCountry & AllGC and use the same dataset named GeographyCountry created with the query below for setting the parameters’ available and default values. WITH MEMBER [Measures].[ParameterCaption] AS
[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 Report Data Pane 5) Now create a simple chart with Reseller Sales Amount as the Value, Calendar Year as Category Group and Country as Series Group. SSRS Chart 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 Background Image properties 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 Report action for Country textbox 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). image 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 Adding to a selected list 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 Removing from a selected list 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”) Conditionally displaying reset icon 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. Setting action for Reset icon 12) Now preview the report and you can see the chart getting changed as per the slicers. Report with 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 'All But the Selected' feature explanation 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. SSRS Report with the new feature 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. Download Slicers.rdl As for me, I am already thinking on another type of visualization involving these slicers, hope to put it out as a blog soon!

Posted by SQLJason, 22 comments
SSRS Chart Issues in SharePoint Integrated Mode

SSRS Chart Issues in SharePoint Integrated Mode

July 2, 2012

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. SSRS Chart Issues in SharePoint Integrated Mode 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. SSRS Report with inline charts 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. Preview the SSRS Report 3) Now deploy this report in Sharepoint and preview the report. Issues with the SSRS Chart when previewed in SharePoint 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. How to enclose chart within rectangle in SSRS 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. SSRS Report when previewed in SP 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.

Posted by SQLJason, 7 comments
Going beyond Geospatial Analysis with SSRS Map Reports

Going beyond Geospatial Analysis with SSRS Map Reports

June 22, 2012

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) image 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]) image 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:- image 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. image 7) Click on preview and you should be able to see the result. image 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.

Posted by SQLJason, 6 comments
Finding Nearest Stores using SSRS Map Reports

Finding Nearest Stores using SSRS Map Reports

March 26, 2012

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

Posted by SQLJason, 1 comment
Overlapping Charts in SSRS using Range Charts

Overlapping Charts in SSRS using Range Charts

March 19, 2012

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!

Posted by SQLJason, 19 comments
Heat Maps for SSRS using Map Control

Heat Maps for SSRS using Map Control

March 10, 2012

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

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.

Posted by SQLJason, 13 comments