Looks like I need some serious help. No matter what Option I try I'm getting error on the parameter ServerName. I'm not able to cascade this parameter. Below is the query that i'm using. It looks like this is very basic of SSRS but i'm never build complex reports, i'm having hard time. Please help me.
--For CPU Usage
If @ApplicationName = 'Informatica'
BEGIN
If @EnvironmentName = 'Prod'
BEGIN
SELECT Environment,NetworkName, SampleTime, Max FROM Singularity.dbo.PerfData_CPUServerView nolock
Where Environment = @EnvironmentName
and NetworkName in (@ServerName)
And SampleTime >= @StartDateTime and SampleTime <= CONVERT(date, DATEADD(dd, 1, @EndDateTime), 101)
Order by SampleTime asc
END
ELSE
BEGIN
SELECT Environment,NetworkName, SampleTime, Max FROM Singularity.dbo.PerfData_CPUServerView nolock
Where Environment = @EnvironmentName
and NetworkName in (@ServerName)
And SampleTime >= @StartDateTime and SampleTime <= CONVERT(date, DATEADD(dd, 1, @EndDateTime), 101)
Order by SampleTime asc
END
END
If @ApplicationName = 'Velocity'
BEGIN
If @EnvironmentName = 'Prod'
BEGIN
SELECT Environment,NetworkName, SnapshotTime,ProcessUsePct FROM SqlResourceUseHistoricView nolock
Where Environment = @EnvironmentName
and NetworkName in (@ServerName)
AND SnapshotTime >= @StartDateTime
AND SnapshotTime <= CONVERT(date, DATEADD(dd, 1, @EndDateTime), 101)
order by SnapshotTime asc
END
ELSE
BEGIN
SELECT Environment,NetworkName, SnapshotTime,ProcessUsePct FROM SqlResourceUseHistoricView nolock
Where Environment = @EnvironmentName
and NetworkName in (@ServerName)
AND SnapshotTime >= @StartDateTime
AND SnapshotTime <= CONVERT(date, DATEADD(dd, 1, @EndDateTime), 101)
order by SnapshotTime asc
END
END
If @ApplicationName = 'MSCloud'
BEGIN
If @EnvironmentName = 'Prod'
BEGIN
SELECT Environment,NetworkName, SnapshotTime,ProcessUsePct FROM SqlResourceUseHistoricView nolock
Where Environment = @EnvironmentName
and NetworkName in (@ServerName)
AND SnapshotTime >= @StartDateTime
AND SnapshotTime <= CONVERT(date, DATEADD(dd, 1, @EndDateTime), 101)
order by SnapshotTime asc
END
ELSE
BEGIN
SELECT Environment,NetworkName, SnapshotTime,ProcessUsePct FROM SqlResourceUseHistoricView nolock
Where Environment = @EnvironmentName
and NetworkName in (@ServerName)
AND SnapshotTime >= @StartDateTime
AND SnapshotTime <= CONVERT(date, DATEADD(dd, 1, @EndDateTime), 101)
order by SnapshotTime asc
END
END