February 7, 2014 at 3:41 pm
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'?
February 19, 2014 at 8:44 am
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)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply