SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ssrs 2008 dependent parmeter values sometimes


ssrs 2008 dependent parmeter values sometimes

Author
Message
wendy elizabeth
wendy elizabeth
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1215 Visits: 769
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'?
higgim
higgim
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1108 Visits: 2622
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)


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search