Hide and show Parameters in SSRS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Hide and show Parameters in SSRS

Author

Topic

asqldeveloper
Starting Member

17 Posts

Posted - 2008-04-15 : 12:44:30

I am doing report development against OLAP. I have Parameter1 and then my second parameter is a boolean (True/False). I want to show Parameter 3/Paramater 4 based on selection of Param2 (So, if true, show Param3, if false, show Param 4) and please remember we are doing this in a sequence.

Is this even possible in SSRS 2005? If yes, how the hell do I achieve it.

PS: Sorry for cursing, but I am about to lose my mind. Any help is much much appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-15 : 13:03:02

Unfortunately you cant have expressions for manipulating hidden properties of parameters in report manager.

jhermiz

3564 Posts

Posted - 2008-04-15 : 22:42:37

quote:
Originally posted by visakh16

Unfortunately you cant have expressions for manipulating hidden properties of parameters in report manager.

Im not sure where visakh16 gets his answers regarding RS. This is the third time he's said something is not possible in RS but really is. For one thing you can hide the parameters in a query string.

strReportParm = "rc:Parameters=false"
strReportParm +="OtherParms"

Also you can use report manager to not prompt the user for a specific parameter, this can be changed programitically as well.

1) If you are using the reportviewer control in your application, you can set the parameter property in the property window to False. NOTE: This is only available in the reportviewer control.

2) You can set the parameters to false in the URL of the request for your report if you are going to view it as a report relying on report manager rather than the control. Add this to your URL;

rc:Parameters=false

A lot of info is found here: http://geekswithblogs.net/gobrien/archive/2004/07/30/9116.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-16 : 10:28:50

quote:
Originally posted by jhermiz

quote:
Originally posted by visakh16

Unfortunately you cant have expressions for manipulating hidden properties of parameters in report manager.

Im not sure where visakh16 gets his answers regarding RS. This is the third time he's said something is not possible in RS but really is. For one thing you can hide the parameters in a query string.

strReportParm = "rc:Parameters=false"
strReportParm +="OtherParms"

Also you can use report manager to not prompt the user for a specific parameter, this can be changed programitically as well.

1) If you are using the reportviewer control in your application, you can set the parameter property in the property window to False. NOTE: This is only available in the reportviewer control.

2) You can set the parameters to false in the URL of the request for your report if you are going to view it as a report relying on report manager rather than the control. Add this to your URL;

rc:Parameters=false

A lot of info is found here: http://geekswithblogs.net/gobrien/archive/2004/07/30/9116.aspx

jhermitz what i told was regarding setting of hidden properties in properties tab of report in BI studio. it has only a checkbox and does not allow expressions. OP was not asking about hiding of params when rendering report from url but a way to hide one parameter based on other.

asqldeveloper
Starting Member

17 Posts

Posted - 2008-04-17 : 18:11:37

Visak is right. I am not running the report in some ASP.NET or some Sharepoint application etc. The report is strictly displayed on the report manager and users browse to that report on the report manager.

I do agree with visak that I am trying to hide/show a parameter based on a parameter selection.

Is rc can be used only when you access through a URL?

Please also remember that I am doing all this against OLAP (though I know it doesnt make much difference).

buddha
Starting Member

4 Posts

Posted - 2008-12-27 : 09:48:19

I have a similar issue. Has this problem been solved. Could you please share the solution. i am also confined to SSRS 2005-Report designer and manager.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-27 : 12:03:51

can you give some background info on where you're trying to show/hide params? also are you using OLAP source?

buddha
Starting Member

4 Posts

Posted - 2008-12-27 : 14:55:28

Thanks for the quick response.
Presently my data comes from the oracle back end. I need to create the report first from the oracle and once the cube is ready(Feb ) , I have to recreate the report from the cube.
I will explain the porblem more clearly..
I have a couple of parameters that are multivalued.
I have 2 heirarchies for which I need to create cascading parameters.All parameters except the BG comes from the query.BG is a non-query parameter. I am setting the two values to chose either path.
For eg:
Year,
BG Values--PB
--SB

If PB is chosen then the cascading parameters should be
SD
SR
PB
BT
ST
If SB is chosen ,cascading parameters should be
SB
BT
ST

how can I control and show one list or other. I am using BIDS(report designer) and report manager.
Even if I can hide what value can I pass in the main query, for the parameters that are not selected.

PS:I am very new to SSRS and this is first project(my company is moving to SSRS)
Please let me know if u need more info.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 01:14:48

does SD,SR... represents parameters themselves or are they values for parameter?

buddha
Starting Member

4 Posts

Posted - 2008-12-29 : 09:01:15

SD,SR are multi value parameter names. Their values are of datatype varchar. I can make the BG(second parameter as a boolean too instead of string)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 12:16:14

quote:
Originally posted by buddha

SD,SR are multi value parameter names. Their values are of datatype varchar. I can make the BG(second parameter as a boolean too instead of string)

then isnt it enough to set dependency b/w reqd params so that they will be enabled only when dependent parameter is selected? you can do this by linking parameter values to dataset of dependent ones.

rajesh12
Starting Member

1 Post

Posted - 2009-03-16 : 15:59:21

Hi buddha,

I am having the same problem like your's.Please help me if you find any solution for this problem.

Thanks,
Rajesh.

quote:
Originally posted by buddha

Thanks for the quick response.
Presently my data comes from the oracle back end. I need to create the report first from the oracle and once the cube is ready(Feb ) , I have to recreate the report from the cube.
I will explain the porblem more clearly..
I have a couple of parameters that are multivalued.
I have 2 heirarchies for which I need to create cascading parameters.All parameters except the BG comes from the query.BG is a non-query parameter. I am setting the two values to chose either path.
For eg:
Year,
BG Values--PB
--SB

If PB is chosen then the cascading parameters should be
SD
SR
PB
BT
ST
If SB is chosen ,cascading parameters should be
SB
BT
ST

how can I control and show one list or other. I am using BIDS(report designer) and report manager.
Even if I can hide what value can I pass in the main query, for the parameters that are not selected.

PS:I am very new to SSRS and this is first project(my company is moving to SSRS)
Please let me know if u need more info.

simonlabdes
Starting Member

1 Post

Posted - 2009-04-20 : 16:28:52

I have the same request. I think the problem is simple :

2 parameters (Param_01 and Param_02)
Param_01 is saying "display Param_02 ? Yes or No?".
If Param_01 is set to "yes", then the second parameter (param_01) must be "enable" or "visible". else, it must remain hidden.

Must work in Visual Studio, in internet explorer, etc..

I guess it will be programmatically done. Where must we put the code to be executed (after hitting "view report" button)

snowville
Starting Member

1 Post

Posted - 2009-09-23 : 06:01:31

Hi,

I got allmost same problem that you asqldeveloper.
In fact, I originally got 2 multiselect cascading drop down boxes. Let's say that param1 is companies (60 rows) and param2 departments of those companies (100 per each company so all together it's 60*100 = 6000 rows)

Customers requires:
1. that she can select companies in department level and
2. default value is select all customers, so all departments are also selected.

The issue is, that 6000 rows in the drop down box slows down dramatically ssrs-reports in the client's browser, because of the long Java srcipt what has been generated.

Let's add third drop down box paramyesno with name "do you wan't select in dep. level? yes=1/no=0 "

Next we will modify the dataset of param2

select depcode id, depname name from tb_departments
where company = @param1 and 1 = @paramyesno
union all
select 'allselectted' id, 'all departments' name
where 0 = @paramyesno

Also in the stored proc you need handle that 'allselected'

Cheers!

Kind Rgds,
Snowville

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-23 : 08:37:27

snowville,

That is a very interesting way to handle that. I am glad you saw an old post and chose to respond! I will make sure I copy that code snippet to my stash!

John

"The smoke monster is just the Others doing barbecue"

lenrigby
Starting Member

1 Post

Posted - 2011-12-20 : 09:33:04

hey jhermiz

I read your post with interest and wondered if you can help me with a question (SSRS 2008)?

We have a report with one parameter and several values. It's set to run the report with a default value which is fine however is there a way of closing the parameter pane after the report has run?

thanks L

quote:
Originally posted by jhermiz

quote:
Originally posted by visakh16

Unfortunately you cant have expressions for manipulating hidden properties of parameters in report manager.

Im not sure where visakh16 gets his answers regarding RS. This is the third time he's said something is not possible in RS but really is. For one thing you can hide the parameters in a query string.

strReportParm = "rc:Parameters=false"
strReportParm +="OtherParms"

Also you can use report manager to not prompt the user for a specific parameter, this can be changed programitically as well.

1) If you are using the reportviewer control in your application, you can set the parameter property in the property window to False. NOTE: This is only available in the reportviewer control.

2) You can set the parameters to false in the URL of the request for your report if you are going to view it as a report relying on report manager rather than the control. Add this to your URL;

rc:Parameters=false

A lot of info is found here: http://geekswithblogs.net/gobrien/archive/2004/07/30/9116.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources