Pages

Thursday, July 5, 2012

Simulating Slicers in SSRS Reports

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!

18 comments:

  1. Jason Bhai,
    It's not just SSRS, from here on "Jason SSRS".Please keep on blogging with new SSRS tips and tricks.
    God bless you.
    Thank you.

    ReplyDelete
  2. OMG!! This is truly ausome. People who have underestmated SSRS, just come out and see here

    ReplyDelete
  3. Really gud!. thanks for the post

    ReplyDelete
  4. I am very interested in trying this, but can someone tell me how to "create a new Analysis Services datasource connection to the AdventureWorks cube." I would really appreciate it. Thanks!

    ReplyDelete
  5. You can download the Analysis Services sample project (AdventureWorks Cube) from here - http://sqlserversamples.codeplex.com/

    Once that is done, deploy and process the cube.
    http://www.c-sharpcorner.com/UploadFile/anavijai/installing-adventureworks-analysis-services-database/

    Now you can create a data source connection to this cube
    http://msdn.microsoft.com/en-us/library/dd207039.aspx

    ReplyDelete
  6. Do you have any tips for how I might use multiple slicers in the same report? I've tried but when I click one slicer, it unfilters the other one. I want to be able to filter the report by multiple slicers at the same time.

    ReplyDelete
    Replies
    1. If you want multiple values to be clicked within the same slicer, I have already detailed it above (using the + symbol)
      If you want multiple slicers to be present, it is relatively simple. Make sure that for every action, you need to pass the extra paramter for the other slicer.

      Delete
    2. What I'm looking for is the latter (multiple slicers present). For every action, I am passing in the extra parameter for the other slicer. Doing this makes both slicers work, however, they don't work together. When I click a value in one slicer it unfilters the other slicer.

      My two slicers are "Month" and "Sales Rep." If I click "July" in the "Month" slicer, the report is filtered for just July's sales. But suppose I want to see July's sales for just "John Brown." When I click "John Brown" in the "Sales Rep" slicer, the "Month" slicer becomes unfiltered. In other words, the report now shows sales for just "John Brown," but for ALL months, rather than just July.

      Delete
    3. For this, you should pass both the Month and Sales Rep parameters to the report. Make sure that for Month parameter, you pass the parameter value (like =Parameters!PName.Value) and for sales rep parameter, you will have to pass the Field value (like =Fields!Fname.Value). If it doesnt work, please mail your rdl to jason143@gmail.com and I will take a look

      Delete
    4. That worked perfectly! Thank you very much! You are an SSRS god.

      Delete
    5. Hi Hankob,

      Can you please mail me ur rdl. I am trying this but its not working.Even I have the same requirement as you have done with Multiple slicers.

      It would be great if you send the rdl or a an example of same kind.
      My mail id Vishenne@gmail.com

      Delete
    6. @Vishenne M - No problem. I just sent it to you.

      Delete
  7. This comment has been removed by the author.

    ReplyDelete
  8. Hi Hankob,

    Please send to this mail id also prasad7350@gmail.com

    ReplyDelete
  9. Dear Jason,
    do you have sample solution based on normal tables (non cube). I dont know mdx and a dont know how create slicers on data based on flat tables.

    ReplyDelete