Post reply

Limit no of values selected for Multi-valued parameters

  • January 13, 2011 at 9:54 pm

    #230390

    Im working on SSRS 2008.

    I have a report with one parameter having Multiple Values. I dont want the user to select more than 5 values. How can i restrict this?

    Currently I have made a textbox giving error msg "You can select only 5 parameters" when the report is run. If user selects more than 5 values then this error msg(in a textbox) is visible else the Tablix with report gets visible. But the issue is,suppose I select 7 parameters then the query does get executed for these 7 parameters and then the error msg is shown. Can we validate the parameters in some other way so that the report executes only when the parameters are 5 or less?

  • January 16, 2011 at 11:10 pm

    #1274091

    Hi Sweta,

    I tried building your scenario and found something which could be a possible solution for you.

    Report Scenario -

    Well, our aim is to restrict users from selecting values of a parameter (ofcourse in multiple choice case) more than 5.

    Means, a user can select 1 to 5 parameter values at a time and a report should display for respective selected values but as soon as the user selects 6 values (1 to 6 or more than this) for a parameter, the report should not execute at all and display a warning message to the user saying - "Please select only 5 parameter values at a time" or any other custom message of yours.

    So, to acheive this report requirement, will follow certain steps:

    1: Create a report.

    2. Suppose, report dataset query is as below-

    SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey,

    RevisionNumber, OrderQuantity, UnitPrice

    FROM abc

    3. Now, we should use 2 seperate parameters -

    1st - a ProductKey parameter (suppose my main query dataset executes for this filter condition i.e. for a particular ProductKey).

    2nd - a ProductKeyCount parameter as I need to calculate the no. of product keys selected in my 1st parameter (so as to restrict the users for selecting only 5 ProductKeys at a time.)

    Make this 2nd parameter as a Hidden parameter and

    Set the default value of this to [@ProductKey.Count] where @ProductKey resembles the 1st parameter already created.

    4. Now, we need to bind these above parameters to the main dataset query as-

    SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey,

    RevisionNumber, OrderQuantity, UnitPrice

    FROM abc

    WHERE (ProductKey IN (@ProductKey)) AND (@ProductKeyCount < 6)

    5. Now, to display a warning message, create a text box above the actual report table and set the visibility property for these two as -

    for textbox - Go to properties - Visibility - Hidden - (write an expression as =IIf(Parameters!ProductKeyCount .Value > 5,false,true) )

    for report table - Go to properties - Visibility - Hidden - (write an expression as =IIf(Parameters!ProductKeyCount .Value > 5, true,false) )

    6. Run the report and test it for your requirement. It should work only for 5 selected param values and for values more than 5, a warning message should get displayed.

    I hope, it helps. 🙂

    Meanwhile, if you find any other alternate solution to this, let me know as well. 🙂

    Regards,

    Niraj

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply