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
Author
Message
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: Thursday, September 25, 2014 11:17 AM
Points: 377, Visits: 446
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 cust.prod 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


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 9:26 AM
Points: 463, Visits: 1,418
I would personally NULL city when not in USA. See examples below

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

DECLARE @TestData TABLE
(
WhatState NVARCHAR(20) ,
City NVARCHAR(20)
)

INSERT INTO @TestData
( WhatState, City )
VALUES ( N'Some USA State', N'Some USA City' ),
( 'Not In USA', 'Some UK City' )

-- When State is Not In USA
SELECT *
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'
SELECT *
FROM @TestData
WHERE WhatState = @State
AND City = ISNULL(@City, city)

Post #1543073
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse