SSRS - Build a "dropdown" box on the report

As we know, SQL Server Reporting Service only provides very limited interactions to end users. Once the report is rendered, the report itself will become a static item. Interactions mostly need to be done from  external applications. Well, there are quite a lot external applications can be used to get interactive result, such as PerformancePoint server in Sharepoint. But except for them, there are some ways to make the RS report looks better. So today I would like to show a "dropdown" box in reporting service.

Firstly let's see what will it look like:

When click the "+" next to the textbox, it will become

Click one of the options in the expanded dropdown box, the selection will appear in the Options textbox

In this case, this "dropdown" box allows multi-selection

To build this type of report, we do not need 3rd party components, customisation of the reporting service, or creation of the CRIs. What we need is just a bit imagination:

The first thing we need to do is to have a report parameter. The purpose of this parameter is to hold the value we selected from the "dropdown" box. In my report, I call it "SelectedValues"

To demonstrat the value we selected, I created a textbox on top of the "dropdown" box, to show the paramter value. Having it or without it will not affect the final result, I created it simply because a long string will be "cut" in the report parameter field, then you cannot see what happened in the design.

As you can see, the expression for this field is quite simple

 =Parameters!SelectedValues.Value  

Now we need have the options for our "dropdown" box. To make it simple, I have the query below

 select 'Option A' as Col  
 UNION ALL  
 select 'Option B' as Col  
 UNION ALL  
 select 'Option C' as Col  

Create a dataset by using this query, then we are ready to format the report:

• Put a tablix component onto the report 
• Change the tablix component to 2 x 2 size
• The row group has the col value from the dataset
• The title row has the expression to show the parameter "SelectedValues" value
• Set the tablix border to none
• Format background color for the first cell of the row group to grey

The final layout of the report looks like below

Now we need to have some special settings. (To make things simple, I will use column number + row number to reference the cell, for example, C1R2 is the grey cell in picture above)

Right click the row group from the bottom panel, go to Visibility tab, and check the radio button "Hide", so by default this row group is hidden from the layout.

Tick the checkbox "Display can be toggled by..." and from the dropdown box, select textbox C2R1. In below screenshot, the item I used to toggle the visibility of the row group, is textbox14

Select the cell C1R1,  set its border style to Solid, border color to light grey, and set attribute "CanGrow" to false (we don't want our "dropdown" box expand by its contents)


Now we have a textbox looks like a drondown box. In preview mode, we can see somthing like below. Time to design the action.


Click cell C1R2 (the cell has the value "[Col]"), and from the pop up context menu, select "Text Box Properties".

Go to Action tab, select radio button "Go to report". In the dropdown box "Specify a report", select the report we are working on.

Click "Add" button under this dropdown box to add a parameter. You should see the parameter "SelectedValues" under the parameter Name dropdown list.

Next to the parameter name, we need to have the parameter value passed into the report. Because I want to make a multi-selection "dropdown" box, what I did is click the small "fx" button, in the pop up expression window, input the expression:

 =IIF(ISNOTHING(Parameters!SelectedValues.Value), Fields!Col.Value, Parameters!SelectedValues.Value + "," + Fields!Col.Value)  

So what we did here, is we created a drill through action on the cell C1R2, when click this cell, it will go to itself with the current report parameter value, and the cell value.

That's it. Now you have a "dropdown" box on your report. The parameter values will be passed into the the report itself continually, like screenshot shows below:

Certainly there are many other ways to improve this "dropdown" box. For example, in this "dropdown" box we can select "Option" multi times, make the result ugly. To fix it, we can fix it by using "replace" function in the parameter expression.

There are lot more to improve, certainly. But what I want to show you, is by using some native functions in the reporting service, we can still build some interaction behavious. Just like when I chat with my friends, the technology could have the boundary, but our imagination is endless :D