January 13, 2011 at 9:54 pm
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
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