Query that needs to be used in Cascading parameters report

• Mac1986

  SSCertifiable

  Points: 6216

  More actions

  April 2, 2013 at 11:37 pm

  #275938

  Hi All,

  I need a small help in SSRS report.

  My report has 5 parameters.

  @ApplicationName - 4 Names comes in the dropdown menu

  @EnvironmentName

  @ServerName

  @StartDateTime

  @EndDateTime

  Everything works fine. Only help needed here is. Out of 4 names that pop up in the drop down menu, Only the @AppicationName 'Informatica' needs to be used with Query1. Rest all 3 @ApplicationName need to use Query2

  Help help me how to build the query in this condition. I tried IF Condition which is not working.

  Query1:

  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

  Query2:

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

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

  Order by SnapshotTime asc

• Mac1986

  SSCertifiable

  Points: 6216

  More actions

  April 3, 2013 at 2:49 am

  #1603058

  I figured it out. Thanks a ton.

  IF OBJECT_ID('TEMPDB..#CPUUsage')IS NOT NULL

  DROP TABLE #CPUUsage

  CREATE TABLE #CPUUsage(NetworkName varchar(20),startTime Datetime,Value int)

  Insert INTO #CPUUsage

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

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

  UNION

  Select NetworkName,SnapshotTime, ProcessUsePct from Singularity.dbo.SqlResourceUseHistoricView (NOLOCK)

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

  order by 2 asc

  Select * from #CPUUsage where Networkname = @ServerName