TSQL in cascading parameters SSRS report

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
 
October 18th, 2013 4:10am

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

Free Windows Admin Tool Kit Click here and download it now
October 20th, 2013 10:29pm

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

October 21st, 2013 5:30am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics