How to put a filter on a multivalue parameter to not display all the value in the dropdown

Question

Hi i have a report where i have two parameters one is Customer number and other is DIVISION_NO; both the parameters are multi-value and getting their value from two different data-set. the parameter Division number depends on customer number(to each customer_no is associated one or multiple division_no) so once customer_no parameter is populated then i can populate division number. Now For one particular Customer_no i want to display one particular division number only, so all the other division_no associated to that particular Customer_no i don't want to shown them in the drop down list.

Can anyone help me in this please?

Answer 1

You need create and pass in the Customer_No as a variable, eg @CustomerNo, into dataset for the Division_No parameter

Ensure the Division Parameter is listed below the Customer_no parameter in the list

Make the Division_No dataset something similar to:

IF @CustomerNo = <restricted_Cust>
BEGIN
    select Division_no
    from   table
    where  CustomerNo = @CustomerNo
    AND    Division_no IN (restricted list)
END
ELSE
BEGIN
    select Division_no
    from   table
    where  CustomerNo = @CustomerNo
END