Basic CASE Statement in Cascading parameterized report

Question

Post reply

Basic CASE Statement in Cascading parameterized report

Mac1986

SSCertifiable

Points: 6216
More actions
April 2, 2013 at 9:01 pm

#291765

Hi All,
Writing a small CASE statement that in SQL Server. Below is what I'm try to accomplish.
If the user selects 'MAC' as the input parameter in the report, SSRS should use below query.
BEGIN
SELECT NetworkName, SampleTime, [Max] FROM vinny..PerfData_CPUServerView (nolock)
Where NetworkName in (@ServerName)
And SampleTime >= @StartDateTime and SampleTime <= CONVERT(date, DATEADD(dd, 1, @EndDateTime), 101)
Order by SampleTime asc
END
Else it should this query.
BEGIN
select NetowrkName.SnapshotTime, ProcessUsePct from vinny..SqlResourceUseHistoricView (NOLOCK)
Where B.NetworkName in (@ServerName)
And Snapshottime >= @StartDateTime and SnapshotTime <= CONVERT(date, DATEADD(dd, 1, @EndDateTime), 101)
Order by SnapshotTime asc
END
I tried IF ELSE statement but SSRS is not giving results for cascading parameterized reports.
Please help...

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

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

Mac1986 SSCertifiable Points: 6216 More actions · Answer 1

Below is what I came up with. Please help me get the right one. I'm getting NULL when i run it.

DECLARE @ApplicationName Varchar(50) = 'Informatica'

DECLARE @EnvironmentName Varchar(50) = 'UAT'

DECLARE @ServerName Varchar(50) = 'TK5BIINFMAPPE02'

DECLARE @StartDateTime DateTime = '2013-03-12 00:00:00.000'

DECLARE @EndDateTime Datetime = '2013-03-29 00:00:00.000'

DECLARE @Statement varchar(max)

Select @Statement = ('select NetworkName,SnapshotTime, ProcessUsePct from ')

SELECT @Statement = @Statement +

CASE

when @ApplicationName = 'Informatica' Then ' vinny..PerfData_CPUServerView (NOLOCK) Where B.NetworkName in (@ServerName) And Snapshottime >= @StartDateTime and SnapshotTime <= CONVERT(date, DATEADD(dd, 1, @EndDateTime), 101) Order by SnapshotTime asc'

when @ApplicationName = 'Velocity' Then ' vinny..SqlResourceUseHistoricView (NOLOCK) Where B.NetworkName in (@ServerName) And Snapshottime >= @StartDateTime and SnapshotTime <= CONVERT(date, DATEADD(dd, 1, @EndDateTime), 101) Order by SnapshotTime asc'

when @ApplicationName = 'MSCloud' Then ' vinny..SqlResourceUseHistoricView (NOLOCK) Where B.NetworkName in (@ServerName) And Snapshottime >= @StartDateTime and SnapshotTime <= CONVERT(date, DATEADD(dd, 1, @EndDateTime), 101) Order by SnapshotTime asc'

when @ApplicationName = 'OA' Then ' vinny..SqlResourceUseHistoricView (NOLOCK) Where B.NetworkName in (@ServerName) And Snapshottime >= @StartDateTime and SnapshotTime <= CONVERT(date, DATEADD(dd, 1, @EndDateTime), 101) Order by SnapshotTime asc'

end

Exec @Statement

Select @Statement

Mac1986 SSCertifiable Points: 6216 More actions · Answer 2

Basically what I'm trying to achieve is, Based on selected parameter of the report, Query used should change.

I have only 2 Queries.

Query1 should be used when @ApplciaitonName 'Informatica' is selected

and Queriy2 should be used for rest of all @ApplicationNames

Please do help.

Steven.Howes SSCertifiable Points: 6230 More actions · Answer 3

so

IF @ApplicationName = 'MAC'

BEGIN

SELECT NetworkName, SampleTime, [Max] FROM vinny..PerfData_CPUServerView (nolock)

Where NetworkName in (@ServerName)

And SampleTime >= @StartDateTime and SampleTime <= CONVERT(date, DATEADD(dd, 1, @EndDateTime), 101)

Order by SampleTime asc

END

ELSE

BEGIN

select NetowrkName.SnapshotTime, ProcessUsePct from vinny..SqlResourceUseHistoricView (NOLOCK)

Where B.NetworkName in (@ServerName)

And Snapshottime >= @StartDateTime and SnapshotTime <= CONVERT(date, DATEADD(dd, 1, @EndDateTime), 101)

Order by SnapshotTime asc

END

Didn't work for you?

Steven.Howes SSCertifiable Points: 6230 More actions · Answer 4

Never mind I just saw your other post...

http://www.sqlservercentral.com/Forums/Topic1438173-150-1.aspx