August 25, 2009 at 9:53 am
Hi Experts
I need to Hide or disable parameters based on the value of prior parameter.
I have a report, in which I have a parent parameter 'p' which has 3 possible values ('x',' y','z'). so based on these values i need to make either of the child parameters (c1,c2,c3) visible.
When a user selects a value from parameter 'p', only one parameter either 'c1', 'c2' or 'c3' has to be displayed.
When a user selects 'x', I need to make 'c1' parameter visible, if 'y' got selected, then 'c2' parameter and if 'z' is chosen, 'c3' parameter has to visible.
I am working with SQL Server 2005 Reporting Services and SQL Server 2005 Database Engine is my source.
Note: I am not talking about Cascading Parameter. I dont want to load the values of the Child parameter based on Parent Parameter.
Thanks in advance
Sunny
August 26, 2009 at 7:32 am
To my knowledge there is not a way to show/hide parameters based on another parameter in SSRS.
If this is a requirement I would recommend creating an independent web page that has the parameters and then a submit button that calls the report with the selected parameters.
If you have to use the report manager interface I would recommend using cascading parameters, but default the unnecessary parameters to 'N/A' and only load actual values in the necessary parameter. You could also always have the parameter listed second be the one that gets actual values.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 17, 2009 at 4:49 am
Jack,
That is what we have been doing to effectively disable the report params based on other params.
But, can you suggest how to do so if the parameter is of the date type.
The "NA" value cannot be assigned to it.
It can be NULLed, but since it is a parameter available to the user, the user may always unNULL it and give some value.
Is there a way we can prevent user from entering any value (in the date param), based on the value selected for some other param?
September 17, 2009 at 5:39 am
Yusuf,
There really isn't much you can do about it with the Date type. You can make it a string type, but then you lose the calendar control. Your only other option is to ignore the value of the date parameter in your dataset based on the value in your other parameter(s).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 18, 2009 at 7:35 am
Yusuf Ali Bhiwandiwala (9/17/2009)
Jack,That is what we have been doing to effectively disable the report params based on other params.
But, can you suggest how to do so if the parameter is of the date type.
The "NA" value cannot be assigned to it.
It can be NULLed, but since it is a parameter available to the user, the user may always unNULL it and give some value.
Is there a way we can prevent user from entering any value (in the date param), based on the value selected for some other param?
What you may want to try is to create a parameter called, "UseDate". Give it 2 values, "Yes,No".
The enable or disable the Date parameter based on the value of the "useDate". It's greyed out so the end user understands that it won't be used unless the "UseDate" parameter is set to "Yes".
You could also make the "useDate" parameter hidden and base the "Yes" or "No" on the value selected in your parameter "p".
You will have to change you data set accordingly to factor in the "useDate" parameter value also.
September 18, 2009 at 11:35 am
tmacs33,
The question is : How do we grey out a report parameter (date type or not)?
If greying out can be achieved, the SSRS world would be a happier place 🙂
September 18, 2009 at 12:59 pm
Yusuf Ali Bhiwandiwala (9/18/2009)
tmacs33,The question is : How do we grey out a report parameter (date type or not)?
If greying out can be achieved, the SSRS world would be a happier place 🙂
You can grey out a parameter by basing it on a previous parameter. Create a parameter useDate and give it available values of Yes and No.
create a datase_p like the below.
select test1 =
case WHEN @useDate = 'Yes'
THEN
cast(convert(nvarchar(10),getdate(),101) as smalldatetime)
ELSE Null
END
In your parameter p make the default value =test1
Now when you select "yes" the date option isn't greyed out. When you chose "no" the date is greyed out.
September 20, 2009 at 11:27 pm
You can grey out a parameter by basing it on a previous parameter. Create a parameter useDate and give it available values of Yes and No.
create a datase_p like the below.
select test1 =
case WHEN @useDate = 'Yes'
THEN
cast(convert(nvarchar(10),getdate(),101) as smalldatetime)
ELSE Null
END
In your parameter p make the default value =test1
Now when you select "yes" the date option isn't greyed out. When you chose "no" the date is greyed out.
I'm sorry friend. This will not work. The user will still be able to give some value to the date parameter (p) , even if useDate is selected as No.
September 21, 2009 at 7:20 am
Yusuf Ali Bhiwandiwala (9/20/2009)
I'm sorry friend. This will not work. The user will still be able to give some value to the date parameter (p) , even if useDate is selected as No.
Not really, in your dataset just ignore what they put in (p) when usedate = no.
May 4, 2016 at 9:02 am
Yes, I tried this is not working. It is not grey, it is showing blank space, there user can give value.
May 4, 2016 at 9:18 am
Yusuf Bhiwandiwala (9/18/2009)
tmacs33,The question is : How do we grey out a report parameter (date type or not)?
If greying out can be achieved, the SSRS world would be a happier place 🙂
You can't, it's not an option in SSRS. You can't show/hide, grey out or manipulate the look of a parameter in any way in SSRS. You can't add tool tips, you can't change the font, you can't change the order they appear. Once the report is loaded, that's it.
You can, as has been mentioned, set up the parameters to cascade meaning that the parameters will display values based on previously selected values. If a parameter depends on another parameter it will be greyed out until the parameter that it requires has been chosen.
This is a weakness of SSRS. The only way around it is to use something like .NET MVC, a custom ASP.net page that handles the parameters and passes them to a report before it's rendered. Another option is SharePoint.
-- Itzik Ben-Gan 2001
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply