Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

ssrs 2008 dependent parmeter values sometimes Expand / Collapse
Posted Friday, February 7, 2014 3:41 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 9:33 AM
Points: 379, Visits: 451
In an SSRS 2008 R2 report, there are 5 parameters that the user currently is required to enter. I am addiong a new option to a parameter called 'State' where there is suppose to be a new option to select 'NOT in USA'. When the user selects this new option, there is a parameter area called 'City' where the user is not required to select a value.

Thus in this situation, for the parameter called 'City', what should I change? Should I allow 'null' to be a default option?

The following is the part of the main query that deals with the parameters:
AND En.startyear = @startYear
AND En.endyear = @endYear
AND IsNull(En.State,'') in (@State)
AND IsNull(EN.City,'') in (@City)
AND in (@prod_code)

How would I change the main query to not use the value in 'City' in the main query where the option seleected for 'state' is 'NOT in USA'?
Post #1539423
Posted Wednesday, February 19, 2014 8:44 AM



Group: General Forum Members
Last Login: 2 days ago @ 5:51 AM
Points: 491, Visits: 1,466
I would personally NULL city when not in USA. See examples below

DECLARE @State NVARCHAR(20) = 'Not In USA'

WhatState NVARCHAR(20) ,

( WhatState, City )
VALUES ( N'Some USA State', N'Some USA City' ),
( 'Not In USA', 'Some UK City' )

-- When State is Not In USA
FROM @TestData
WHERE WhatState = @State
AND City = ISNULL(@City, city)

-- When State is in USA
SET @State = 'Some USA State'
SET @City = 'Some USA City'
FROM @TestData
WHERE WhatState = @State
AND City = ISNULL(@City, city)

Post #1543073
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse