Friday, June 12, 2015

SSRS Multi Value Parameter And Stored Procedure

I'm always struggling when it comes to dealing with SSRS multi value parameter. There're basically two ways of doing this:
1) Use SRRS Filter
2) Use good old SQL

I definitely prefer the first approach because it's so much simpler. All we need is a Dataset which returns result set for all possible values of the multi-value parameter and then we filter it based on the selected items. Let's say we have a Dataset called Companies with field Name. Steps to filter:

  1. Create a multi value parameter, let's call it @Company and suppose it contains the following items: AAA, BBB, CCC, which are the distinct names in field Name of the Dataset Companies.
  2. Click on Dataset Properties and select Filters in the left-hand pane
  3. In the Expression drop-down list, select Name as the field to filter
  4. In the Operator drop-down list box, select the In operator
  5. In the Value box, type in [@Company]
And you're done!

Now the second approach will involve creating a Stored Procedure that takes in one string argument, which contains the values of the selected multi-value parameter all joined together with a delimiter that we will need to parse in our Stored Procedure to be able to use it in the SQL IN clause. Using the same example as before, this string argument is something like: "AAA,BBB,CCC". In the SSRS report, on the Parameters of the query definition, we will need to set the parameter value to something like:

    =Join(Parameters!Company.Value,",")
This method is definitely more complicated. We will need to write a SQL string split function in our Stored Procedure taking into account the possibility that the delimiter is not always going to be a comma and whether we need to handle entry with white space in between.