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

Basic CASE Statement in Cascading parameterized report Expand / Collapse
Author
Message
Posted Tuesday, April 2, 2013 9:01 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 4:30 AM
Points: 219, Visits: 689
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...
Post #1438153
Posted Tuesday, April 2, 2013 9:43 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 4:30 AM
Points: 219, Visits: 689
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

Post #1438156
Posted Tuesday, April 2, 2013 11:07 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 4:30 AM
Points: 219, Visits: 689
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.
Post #1438167
Posted Thursday, April 4, 2013 7:27 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, August 21, 2014 7:26 AM
Points: 582, Visits: 2,090
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?
Post #1438796
Posted Thursday, April 4, 2013 7:28 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, August 21, 2014 7:26 AM
Points: 582, Visits: 2,090
Never mind I just saw your other post...

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

Post #1438797
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse