Archive for July, 2011

SSRS does not allow parameters to dynamically hide and unhide (unless asp scripting/xml/url changing/other scripting outside BIDS is implemented).

A workaround around could be to keep all the parameters visible and instead of trying to hide the ones not needed just make a single value available.

Let me elaborate:

1) @Par [text type] > the parameter you want the the dynamic effect applied to it

2) “usp_dp” > if the parameter is a multi valued then lets say this is the name of the SP it generates the values from. This SP also needs to have a parameter of its own that will take an integer value input. Lets call it @ID.

3) @Switch [text type] > if this is equal “A” then you want to use @Par else not.

You will need:
1) Another paramter (either existing or a new one) that accepts integer. I usually use an existing parameter that accepts a certain ID number. For this example lets say we call it @ID (this parameters is of same tyep and name as in usp_dp).

2) On BIDS 2008 on the “Report Parameter Properties” goto the “Advanced” tab and select “Always refresh” or do the same for any other versions. – This step is optional but recommended.

We will first consider that its a multi-valued parameter:
1) Goto the “Dataset Properties” of “usp_dp” and then to “Parameters” and on for @ID Parameter Name put the below “Parameter Value:


What the above does is:
Depending on the value of @ID it will generate the list available for @Par to display. So if you don’t want to display any values you have to say that @ID = 0.

Finally what you need to do is write your “usp_dp” script in such a way that when ever @ID =0 the script should return “N/A” else it will return the normal values for the parameters.

I am sure if you have worked out the multivalued parameter then working out how to apply a similar effect to a textbox parameter (where you need to type in your value) will not be a problem. My suggestion is to use the “Default Values” option in the “Report Parameter Properties”.

Read Full Post »