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!