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.

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!

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

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.

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. Before we go forward, let us look at some functionalities of an excel slicer using the below image as a reference. 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 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 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 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. 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. 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 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!

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.

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!