none
TSQL in cascading parameters SSRS report

    Question

  • I have managed to create a Cascading parameters report with 4 input parameters as below.
    I'm unable to create a Summary dataset with stats. PLease help me.

    Parameter 1: @Division
    Parameter 2: @Datasetname
    Parameter 3: @StartTime
    Parameter 4: @EndTime

    Dataset 1:
    Select * from Monitoring..VelocityDatasetCategory

    Dataset 2:
    Select Distinct DatasetName,DatasetID,divisionID  from Monitoring..VelocityDataset nolock order by DatasetName
    

    Dataset 3:
    SELECT     A.DatasetID, A.DatasetName, A.BatchNumber, A.BatchStartTime, A.BatchEndTime, A.Runtime, A.DeltaCount, A.DeltaSizeInMB,A.StatusID
    FROM         Monitoring..VelocityDatasetSLASummary AS A INNER JOIN
    Monitoring..VelocityDataset AS B ON A.DatasetName = B.DatasetALias
    WHERE     (A.BatchStartTime >= @BatchStartTime) AND (A.BatchEndTime <= CONVERT(date, DATEADD(dd, 1, @BatchEndTime), 101))
    ORDER BY A.BatchNumber DESC

    Dataset 4: This is where I'm having issue.
    IF OBJECT_ID('TEMPDB..#SummaryTable')IS NOT NULL 
    DROP TABLE #SummaryTable
    CREATE TABLE #SummaryTable (DatasetName sysname, DatasetID INT,BatchStartTime datetime,BatchEndTime datetime,StatusID INT,IsScheduled INT)
    Insert INTO #SummaryTable
    Select A.DatasetName,A.DatasetID,A.BatchStartTime,A.BatchEndTime,A.StatusID,B.IsScheduled FROM Monitoring..VelocityDatasetSLASummary A (NOLOCK)
    INNER JOIN Monitoring..VelocityDataset B ON A.DatasetName = B.DatasetALias 
    WHERE (A.BatchStartTime >= @BatchStartTime) AND (A.BatchEndTime <= CONVERT(date, DATEADD(dd, 1, @BatchEndTime), 101))
    --and B.DatasetID = 21
    
    IF (Select top 1 IsScheduled FROM #SummaryTable) = 0
    	BEGIN
    		IF OBJECT_ID('TEMPDB..#Scheduled')IS NOT NULL
    		DROP TABLE #Scheduled
    		CREATE TABLE #Scheduled ([Status] Varchar(20),[BatchCount] Float,DatasetID INT)
    		Insert INTO #Scheduled
    		SELECT 'Total Count' as [Status], COUNT(1), (Select top 1 DatasetID from #SummaryTable) from #SummaryTable
    		UNION ALL
    		Select 'Success Count' as [Status],COUNT(1),(Select top 1 DatasetID from #SummaryTable) from #SummaryTable 
    		where statusID = 1
    		Union All
    		Select 'Failure Count' as [Status],COUNT(1),(Select top 1 DatasetID from #SummaryTable) from #SummaryTable 
    		where statusID = 2
    		Union All
    		Select 'Late Start' as [Status], COUNT(1),(Select top 1 DatasetID from #SummaryTable) from #SummaryTable 
    		where statusID = 4
    		Union ALL
    		Select 'OUT OF CYCLE (N/A)' as [Status],0,(Select top 1 DatasetID from #SummaryTable)
    		Union ALL
    		SELECT 'Total Expected' as [Status], COUNT(1),(Select top 1 DatasetID from #SummaryTable) from #SummaryTable 
    		Union all
    		Select 'NO SHOW (N/A)' as [Status],0,(Select top 1 DatasetID from #SummaryTable) 
    		
    		Select * from #Scheduled
    		Union All
    		Select 'Success Percentage' as [Status],
    		100 - (Cast((((Select [BatchCount] from #Scheduled where Status = 'Failure Count') + (Select [BatchCount] from #Scheduled where Status = 'Late Start'))/(Select [BatchCount] from #Scheduled where Status = 'Total Count')) as decimal(10,2))* 100 ),(Select top 1 DatasetID from #SummaryTable) 
    		Union all
    		Select 'Overall Success Percentage' as [Status],
    		100 - (Cast((((Select [BatchCount] from #Scheduled where Status = 'Failure Count') + (Select [BatchCount] from #Scheduled where Status = 'Late Start'))/(Select [BatchCount] from #Scheduled where Status = 'Total Expected')) as decimal(10,2))* 100 ),(Select top 1 DatasetID from #SummaryTable) 
    	END
    ELSE
    	BEGIN
    		PRINT N'Once above Non- schdeduled Query is sucessful, I shall start building for Scheduled datasets.'
    	END

    End result of Dataset 4 should look like this
    Status BatchCount DatasetID
    Total Count 34 21
    Success Count 21 21
    Failure Count 12 21
    Late Start 1 21
    OUT OF CYCLE (N/A) 0 21
    Total Expected 34 21
    NO SHOW (N/A) 0 21
    Success Percentage 62 21
    Overall Success Percentage 62 21
     
    Friday, October 18, 2013 1:10 AM

Answers

All replies

  • Hi Vinnydrums,

    Since this issue is related to Reporting Services, I will move this thread to Reporting Services forum. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

    Thank you for your understanding and support.

    Best Regards,
    Allen Li


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Monday, October 21, 2013 2:27 AM
  • I have managed to create a Cascading parameters report with 4 input parameters as below.
    I'm unable to create a Summary dataset with stats. PLease help me.

    Parameter 1: @Division
    Parameter 2: @Datasetname
    Parameter 3: @StartTime
    Parameter 4: @EndTime

    Dataset 1:
    Select * from Monitoring..VelocityDatasetCategory

    Dataset 2:
    Select Distinct DatasetName,DatasetID,divisionID  from Monitoring..VelocityDataset nolock order by DatasetName
    

    Dataset 3:
    SELECT     A.DatasetID, A.DatasetName, A.BatchNumber, A.BatchStartTime, A.BatchEndTime, A.Runtime, A.DeltaCount, A.DeltaSizeInMB,A.StatusID
    FROM         Monitoring..VelocityDatasetSLASummary AS A INNER JOIN
    Monitoring..VelocityDataset AS B ON A.DatasetName = B.DatasetALias
    WHERE     (A.BatchStartTime >= @BatchStartTime) AND (A.BatchEndTime <= CONVERT(date, DATEADD(dd, 1, @BatchEndTime), 101))
    ORDER BY A.BatchNumber DESC

    Dataset 4: This is where I'm having issue.
    IF OBJECT_ID('TEMPDB..#SummaryTable')IS NOT NULL 
    DROP TABLE #SummaryTable
    CREATE TABLE #SummaryTable (DatasetName sysname, DatasetID INT,BatchStartTime datetime,BatchEndTime datetime,StatusID INT,IsScheduled INT)
    Insert INTO #SummaryTable
    Select A.DatasetName,A.DatasetID,A.BatchStartTime,A.BatchEndTime,A.StatusID,B.IsScheduled FROM Monitoring..VelocityDatasetSLASummary A (NOLOCK)
    INNER JOIN Monitoring..VelocityDataset B ON A.DatasetName = B.DatasetALias 
    WHERE (A.BatchStartTime >= @BatchStartTime) AND (A.BatchEndTime <= CONVERT(date, DATEADD(dd, 1, @BatchEndTime), 101))
    --and B.DatasetID = 21
    
    IF (Select top 1 IsScheduled FROM #SummaryTable) = 0
    	BEGIN
    		IF OBJECT_ID('TEMPDB..#Scheduled')IS NOT NULL
    		DROP TABLE #Scheduled
    		CREATE TABLE #Scheduled ([Status] Varchar(20),[BatchCount] Float,DatasetID INT)
    		Insert INTO #Scheduled
    		SELECT 'Total Count' as [Status], COUNT(1), (Select top 1 DatasetID from #SummaryTable) from #SummaryTable
    		UNION ALL
    		Select 'Success Count' as [Status],COUNT(1),(Select top 1 DatasetID from #SummaryTable) from #SummaryTable 
    		where statusID = 1
    		Union All
    		Select 'Failure Count' as [Status],COUNT(1),(Select top 1 DatasetID from #SummaryTable) from #SummaryTable 
    		where statusID = 2
    		Union All
    		Select 'Late Start' as [Status], COUNT(1),(Select top 1 DatasetID from #SummaryTable) from #SummaryTable 
    		where statusID = 4
    		Union ALL
    		Select 'OUT OF CYCLE (N/A)' as [Status],0,(Select top 1 DatasetID from #SummaryTable)
    		Union ALL
    		SELECT 'Total Expected' as [Status], COUNT(1),(Select top 1 DatasetID from #SummaryTable) from #SummaryTable 
    		Union all
    		Select 'NO SHOW (N/A)' as [Status],0,(Select top 1 DatasetID from #SummaryTable) 
    		
    		Select * from #Scheduled
    		Union All
    		Select 'Success Percentage' as [Status],
    		100 - (Cast((((Select [BatchCount] from #Scheduled where Status = 'Failure Count') + (Select [BatchCount] from #Scheduled where Status = 'Late Start'))/(Select [BatchCount] from #Scheduled where Status = 'Total Count')) as decimal(10,2))* 100 ),(Select top 1 DatasetID from #SummaryTable) 
    		Union all
    		Select 'Overall Success Percentage' as [Status],
    		100 - (Cast((((Select [BatchCount] from #Scheduled where Status = 'Failure Count') + (Select [BatchCount] from #Scheduled where Status = 'Late Start'))/(Select [BatchCount] from #Scheduled where Status = 'Total Expected')) as decimal(10,2))* 100 ),(Select top 1 DatasetID from #SummaryTable) 
    	END
    ELSE
    	BEGIN
    		PRINT N'Once above Non- schdeduled Query is sucessful, I shall start building for Scheduled datasets.'
    	END

    End result of Dataset 4 should look like this
    Status BatchCount DatasetID
    Total Count 34 21
    Success Count 21 21
    Failure Count 12 21
    Late Start 1 21
    OUT OF CYCLE (N/A) 0 21
    Total Expected 34 21
    NO SHOW (N/A) 0 21
    Success Percentage 62 21
    Overall Success Percentage 62 21
     
    Monday, October 21, 2013 7:49 AM
  • Hi Vinnydrums,

    Thank you for your post.

    In order to solve the problem more efficiently, I need to clarify some information.
    1. What result you have got with the Dataset 4 query code?
    2. Could you please post the detail information for your table definition codes and sample data?

    Regards,
    Alisa Tang


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Monday, October 21, 2013 9:28 AM
    Moderator
  • Thanks  Alisa. Below are the details.

    Dataset 1:

    Select * from Monitoring..VelocityDatasetCategory

    Table Definition:

    CREATE TABLE [dbo].[VelocityDatasetCategory](
    [DivisionID] [int] NULL,
    [Division] [varchar](40) NULL

    Dataset 2:

    Select Distinct DatasetName,DatasetID,divisionID from Monitoring..VelocityDataset nolock order by DatasetName

    CREATE TABLE [dbo].[VelocityDataset](
    [Datasetname] [sysname] NULL,
    [DatasetAlias] [sysname] NULL,
    [DatasetID] [int] NULL,
    [ApplicationAlias] [sysname] NULL,
    [IsScheduled] [int] NULL,
    [TableCount] [int] NULL,
    [TargetSeverName] [varchar](40) NULL,
    [SourceSeverName] [varchar](100) NULL,
    [LinkedSeverName] [sysname] NULL,
    [TargetDatabaseName] [sysname] NULL,
    [EDWSourceSystemID] [int] NULL,
    [DivisionID] [int] NULL,
    [Division] [varchar](40) NULL,
    [Isactive] [int] NULL,
    [ParameterHistory] [int] NULL,
    [SQLJOBID] [uniqueidentifier] NULL

    Dataset 3:

    SELECT A.DatasetID, A.DatasetName, A.BatchNumber, A.BatchStartTime, A.BatchEndTime, A.Runtime, A.DeltaCount, A.DeltaSizeInMB,A.StatusID FROM Monitoring..VelocityDatasetSLASummary AS A INNER JOIN Monitoring..VelocityDataset AS B ON A.DatasetName = B.DatasetALias WHERE (A.BatchStartTime >= @BatchStartTime) AND (A.BatchEndTime <= CONVERT(date, DATEADD(dd, 1, @BatchEndTime), 101)) ORDER BY A.BatchNumber DESC

    CREATE TABLE [dbo].[VelocityDatasetSLASummary](
    [DatasetID] [int] NULL,
    [BatchNumber] [bigint] NULL,
    [DatasetName] [sysname] NULL,
    [BatchStartTime] [datetime] NULL,
    [BatchEndTime] [datetime] NULL,
    [Runtime] [varchar](50) NULL,
    [Status] [sysname] NULL,
    [DeltaCount] [varchar](20) NULL,
    [DeltaSize] [bigint] NULL,
    [DeltaSizeInMB] [bigint] NULL,
    [StatusID] [int] NULL,
    [Queue] [bit] NULL,
    [LastUpdatedBy] [sysname] NULL,
    [LastUpdatedDate] [datetime] NULL

    For Dataset 3, I have kept a filter on Column DatasetID to parameter.DatasetName

    I'm trying to get the below desired result set for Dataset 4:

    Desired Result set for Dataset 4:

    Status BatchCount DatasetID
    Total Count 34 21
    Success Count 21 21
    Failure Count 12 21
    Late Start 1 21
    OUT OF CYCLE (N/A) 0 21
    Total Expected 34 21
    NO SHOW (N/A) 0 21
    Success Percentage 62 21
    Overall Success Percentage 62 21
     

    I have written TSQL (from earlier post of dataset 4)to simulate the same but not able to get desired results. I'm unable to proceed further.Please let me know if you need any further details.

    Monday, October 21, 2013 8:42 PM
  • Hi Vinnydrums,

    Thank you for your reply.

    You can try to use the below code to get the Status values:

    with cte as
    (
    select 
    case when [Status]='Success' then sum([BatchNumber]) end as Success,
    case when [Status]='Failure' then sum([BatchNumber]) end Failure,
    case when [Status]='Late Start' then sum([BatchNumber]) end [Late Start],
    case when [Status]='OUT OF CYCLE (N/A)' then sum([BatchNumber]) end [OUT OF CYCLE (N/A)],
    case when [Status]='NO SHOW (N/A)' then sum([BatchNumber]) end [NO SHOW (N/A)]
    from [dbo].[VelocityDatasetSLASummary]
    group by [Status]
    )
    select Success,Failure,[Late Start],[OUT OF CYCLE (N/A)],[NO SHOW (N/A)],Success/(Success+Failure) as [Success Percentage],
    Success/(Success+Failure) as [Overall Success Percentage]
    from cte

    In order to solve the issue, I need clarify more information. Is it possible that you can provide the sample data of these tables? Thank you for your understanding.

    Hope this helps.

    Regards,
    Alisa Tang


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Wednesday, October 23, 2013 8:36 AM
    Moderator