Search This Blog

Sunday, 30 September 2012

Efficient way of using ALL as a parameter value in multivalued parameter

Most of the time, we came into a situation where we need to pass ALL values of a multivalued parameter to our stored procedure/T-Sql and generally we pass it as a comma separated value to our query. But passing a long set of values to our SP/T-Sql under IN(..,..,..) clause decreases the performance of the query because in this case query engine needs to look into all set of available values in IN(..,..,..) clause against every record. Let’s see step by step that how we can overcome from this issue.
Step 1: Add a Select statement like below in your dataset query that you are using for the parameter binding.
Select -1 as Employee_ID,'ALL' as Employee_Name /*Change the field names as per your requirement*/
Union All
<Your T-Sql Statement>
Here we are passing -1 as a value for ALL. You can choose any other value as per your requirement but do not use NULL. Because if there will be NULL in the multivalued parameter list, you will experience an error in 2008 or below versions whereas R2 will exclude NULL records from the parameter list. This SSRS behavior makes sense because if we concatenate any value(s) with NULL, resultant will be NULL.

In support of this step, you can refer “Employee_Parameter” dataset in attached sample.
Step 2: Pass the selected multivalued parameter in your details dataset’s parameter like following expression =IIF(Parameters!Employee_ID.Value(0)=-1,Nothing,Join(Parameters!Employee_ID.Value,","))
In support of this step, you can refer the expression of the @Employee_ID parameter in “Details_Dataset” dataset in attached sample.
Step 3: Now you need to modify the <where> - clause of your resultant query in such a way that will maximize the response time of your output query like following where condition.
Select <Columns List>
From
<Tables with required joins>
Where
( (@Employee_ID IS NULL) OR (Employee_ID in (<Multivalued parameter values in comma seperated form>)) )

In support of this step, you can refer the where clause of the Details_Dataset” dataset in attached sample


You can download the sample report from following link.
You need to only modify the datasource of the sample report.

Remarks
If you are using this concept in your report, you need to convey your end user that they will see all the possible records on the report if they select other available values with ALL.

13 comments:

  1. Hi Aftab,
    Thanks for sharing a wonderful article,
    I have a small question,
    I am creating my dataset using a single column called SchoolName, How can I modify the step 1 for my dataset?
    The multi valued parameter is used against only a single column called schoolname..
    Please let me know how should i change my query for the steps 1,2 and 3..?

    Thanks

    ReplyDelete
  2. Step1: Use your parameter's dataset sql query as follow

    Select '-1' as School_ID,'ALL' as School_Name
    Union All
    Select ''''+School_Name+'''' as School_ID,'ALL' as School_Name from Schools

    Step2: make changes as following
    =IIF(Parameters!School_ID.Value(0)="-1",Nothing,Join(Parameters!School_ID.Value,","))


    Step3:
    Select
    From

    Where
    ( (@School_Name IS NULL) OR (School_Name in ()) )


    let me know if you need any more clerifiction on any of the above steps

    ReplyDelete
  3. Thank you so much Aftab,
    Will check it and get back to you for any doubts..!!

    ReplyDelete
  4. I am designing a report in which I have a Parameter color_mode with values Two_Color,Full_color and Balck_White.now I want to add ALL value which when selected will display the whole data of color mode.

    In my dataset color_mode is a field with two_color,full_color and black_white as three distinct values.

    Please help me in adding ALL as a value to the parameter & give the desired output.

    Leeza Goyal

    ReplyDelete
    Replies
    1. your issue has been resolved under this link

      http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/af329fb2-1424-421d-a5d9-5e2eff4d8c50

      Delete
  5. I think it is cleaner to tie this to the default (Select All) option in the multi drop down selection list. But thanks for the starting point.

    Posted by JNeo on 4/16/2010 at 11:14 AM
    There is a workaroud to send a NULL value to the StoredProcedure if the multi-value is selected all

    - Add a multi-value parameter 'iCode' that lists the values to choose
    - Add a DataSet_Total with Total = count(*) for the values you are adding to multi-value parameter 'iCode'
    - Add a hidden and internal parameter Total with default value 'from query' chose the DataSet_Total and value field Total
    - Alter the DataSet that uses a Stored Procedure with a parameter TEXT to =iIF(Parameters!iCode.Count = Parameters!Total.Value, Nothing, ","&Join(Parameters!iCode.Value,",")&",")

    ReplyDelete
  6. Hi Aftab,

    Many thanks for you suggestion. I'm tryimg to implement this, but i still do have a question. How to implement step 2? How do i pass the iif? I've never done so.
    I hope you can help me on this.

    Best regards
    Joep Sengers

    ReplyDelete
  7. Just stumbled across this article. Tried it out and works like a charm.

    Many Thanks!
    Steve

    ReplyDelete
  8. So, now you have a drop down with your 'All' in it and also the default (Select All) that SSRS adds. Sounds like it looks really stupid.

    ReplyDelete
  9. And where is number 2 supposed to be implemented at? That makes no sense. None.

    ReplyDelete
    Replies
    1. number 2 should be implemented under Dataset's parameter

      Delete
  10. hi, SSRS gives "select all" option for multivalued parameters, what is the different.

    ReplyDelete
    Replies
    1. It’s a Workaround that cover following scenarios:
      1.If you have a requirement to provide “ALL” as an option and your parameter is not multivalve parameter
      2.Multivalve parameter cannot allow NULL option. So your report will not include those records that is expected in case of “Select ALL”
      3.It will increase the performance of report query because search engine have not to look a long list of selected values. But this scenario will differ requirement to requirement

      Delete