Grey out a paramter based on the value of another parameter?

Question

Post reply

Grey out a paramter based on the value of another parameter?

jbalbo

SSCertifiable

Points: 7583
More actions
March 19, 2015 at 1:02 pm

#305946

Hi
I have a parameter called "WithWithoutAddress" there are two value 1 (with), 0 (without).
I have another parameter called AddressType
What I want to do is if "WithWithoutAddress" = 0 then grey out AddressType otherwise choose address type
Thanks
Joe

Viewing 5 posts - 1 through 4 (of 4 total)

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

I don't believe that you can disable/enable a parameter (I could be wrong but I am pretty sure that you can't).

One thing you can do is this:

You have a parameter @WithWithoutAddress, which exists before your @AddressType Parameter in the parameter list.

@WithWithoutAddress has the values 0 & 1

The dataset for AddressType would contain this query (since I don't have your address type query)

WITH AddressTypes AS (SELECT value FROM (VALUES ('value 1'),('value 2'),('value 3')) t(value))

SELECT TOP (@WithWithoutAddress * (SELECT COUNT(*) FROM AddressTypes)) value

FROM AddressTypes

UNION ALL

SELECT NULL

WHERE @WithWithoutAddress = 0

If @WithWithoutAddress = 1 then the query will return:

value

-------

value 1

value 2

value 3

If @WithWithoutAddress = 0 then the query will return:

value

-------

NULL

Next you set @AddressType to get it's data from the AddressType dataset and set the default value for @AddressType to (Null). Set @AddressType to allow null values.

If the user sets @WithWithoutAddress to 0 then their only option will be (Null).

It's not sexy but it works.

Its because of the limited parameter functionality of SSRS that many people use .NET or some 3rd party tool like MVC.

Edit: Typo

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

jbalbo SSCertifiable Points: 7583 More actions · Answer 2

Hi Alan

Thanks for getting back so quick.

I get most if it, just a bit confused on the dataset part.

Here is the dataset I am using, if you could incorporate it in what you did I "may" understand it a bit more.

I really want to understand it because I see myself doing this more than once or twice

Thanks

Joe

SELECT Name, Active, ID

FROM PersonAddressTypes

WHERE (Active = 1)

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

jbalbo (3/19/2015)
Hi Alan
Thanks for getting back so quick.
I get most if it, just a bit confused on the dataset part.
Here is the dataset I am using, if you could incorporate it in what you did I "may" understand it a bit more.
I really want to understand it because I see myself doing this more than once or twice
Thanks
Joe
SELECT Name, Active, ID
FROM PersonAddressTypes
WHERE (Active = 1)

Sure... Take a look at this:

DECLARE @WithWithoutAddress bit = 0;

WITH PersonAddressTypes AS

(

SELECT Name, Active, Id

FROM (VALUES ('value 1',1,51),('value 2',1,55),('value 3',0,59)) t(Name, Active, Id)

)

SELECT Name, Active, ID

FROM PersonAddressTypes

WHERE (Active = 1)

AND @WithWithoutAddress = 1

UNION ALL

SELECT NULL, NULL, NULL

WHERE @WithWithoutAddress = 0;

Note that, in your dataset you would omit this:

WITH PersonAddressTypes AS

(

SELECT Name, Active, Id

FROM (VALUES ('value 1',1,51),('value 2',1,55),('value 3',0,59)) t(Name, Active, Id)

)

This is just code for emulating your PersonAddressTypes table.

If @WithWithoutAddress is 1 it will return the same values as it does now. If @WithWithoutAddress =0 then it will return NULLs like so:

Name Active ID

------- ----------- -----------

NULL NULL NULL

Does that help?

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

jbalbo SSCertifiable Points: 7583 More actions · Answer 4

Hi Alan

Thanks so much, it work great and I even understand it 🙂

I did run into the problem that AddressType will not take a null because it is a multi value parameter.

But like you said its not pretty but the parameter is "blank if you choose without, so it is doing its job

Thanks Again

Joe