Problem in creating new dataset in Cascading param

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Analysis Server and Reporting Services (2012)

Problem in creating new dataset in Cascading param

Author

Topic

vinnydrums
Starting Member

1 Post

Posted - 2013-10-21 : 02:34:51

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-21 : 07:31:28

why do you need a separate datset for summary? why not apply grouping on dataset3 and then apply required aggregates on it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources