Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help in Building a parameterized Report. Expand / Collapse
Author
Message
Posted Saturday, December 22, 2012 10:58 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 4:30 AM
Points: 219, Visits: 689
Hi All,
I have a ETL design which has 58 Tasks that it does every day. Each ETL is assigned with a unique WorkQueueRunGroupRunID and stored in a table.

If anyone would like to see the runtimes of individual runtimes of all 58 Task 50 days ago from now, we would select the StartDateTime of particular WorkQueueRunGroupRunID which matches to getdate-50, as below
SELECT TP.TransformationPackageName,WQ.WorkQueueRunGroupRunID,WQ.WorkQueueStartWorkDate,WQ.WorkQueueEndWorkDate, 
cast((datediff (ss,WQ.WorkQueueStartWorkDate,WQ.WorkQueueEndWorkDate)) as decimal(8,0))/60.0 [RunTime(Minutes)]
,wq.workqueuestatus
FROM CloudBIMetaData.dbo.WorkQueue WQ WITH (NOLOCK)
JOIN CloudBIMetaData.dbo.WorkQueueRunGroupTransformationPackage WQRGTP WITH (NOLOCK)
ON WQ.WorkQueueRunGroupTransformationPackageID = WQRGTP.WorkQueueRunGroupTransformationPackageID
JOIN CloudBIMetaData.dbo.WorkQueueRunGroup WQRG WITH (NOLOCK)
ON WQRG.WorkQueueRunGroupID = WQRGTP.WorkQueueRunGroupID
JOIN CloudBIMetaData.dbo.TransformationPackage TP WITH (NOLOCK)
ON WQRGTP.TransformationPackageID = TP.TransformationPackageID
--WHERE WQ.WorkQueueRunGroupRunID = '38DBAF7A-0A14-47C4-BD6E-608A94AE34DB' --Copy the WorkQueueRunGroupRunID Here
ORDER BY WQ.WorkQueueStartWorkDate DESC
GO

and then see Give appropriate WorkQueueRunGroupRunID to see runtimes of all 58 tasks. as below
SELECT TP.TransformationPackageName,WQ.WorkQueueRunGroupRunID,WQ.WorkQueueStartWorkDate,WQ.WorkQueueEndWorkDate, 
cast((datediff (ss,WQ.WorkQueueStartWorkDate,WQ.WorkQueueEndWorkDate)) as decimal(8,0))/60.0 [RunTime(Minutes)]
,wq.workqueuestatus
FROM CloudBIMetaData.dbo.WorkQueue WQ WITH (NOLOCK)
JOIN CloudBIMetaData.dbo.WorkQueueRunGroupTransformationPackage WQRGTP WITH (NOLOCK)
ON WQ.WorkQueueRunGroupTransformationPackageID = WQRGTP.WorkQueueRunGroupTransformationPackageID
JOIN CloudBIMetaData.dbo.WorkQueueRunGroup WQRG WITH (NOLOCK)
ON WQRG.WorkQueueRunGroupID = WQRGTP.WorkQueueRunGroupID
JOIN CloudBIMetaData.dbo.TransformationPackage TP WITH (NOLOCK)
ON WQRGTP.TransformationPackageID = TP.TransformationPackageID
WHERE WQ.WorkQueueRunGroupRunID = '38DBAF7A-0A14-47C4-BD6E-608A94AE34DB' --Copy the WorkQueueRunGroupRunID Here
ORDER BY WQ.WorkQueueStartWorkDate DESC
GO

Now, I want to make a SSRS report which would automate this process. Please help me what to put as the parameter in this case. and get runtimes of 58 tasks.

All I want is a filter in the report, where users can choose their date, then when they push the ViewReport option they should see all the 58 sessions of that one particular day.
Post #1399732
Posted Monday, December 24, 2012 8:29 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 2:47 PM
Points: 1,170, Visits: 1,211
Mac1986 (12/22/2012)
All I want is a filter in the report, where users can choose their date, then when they push the ViewReport option they should see all the 58 sessions of that one particular day.

Create a begin and end date parameter in your report and add to your WHERE clause like:
WHERE WorkQueueStartWorkDate BETWEEN @StartDate AND @EndDate

HTH,
Rob
Post #1399920
Posted Thursday, December 27, 2012 1:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 4:30 AM
Points: 219, Visits: 689
Thanks Rob..Is there a way to that user selects a date in the top filter and in the respective WQ.WorkQueueRunGroupRunID assigned to that date gets selected in the where clause.????

I asking this because the results are 100% accurate if we use the Unique Column 'WQ.WorkQueueRunGroupRunID'
Post #1400494
Posted Thursday, December 27, 2012 6:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 2:47 PM
Points: 1,170, Visits: 1,211
Mac1986 (12/27/2012)
Thanks Rob..Is there a way to that user selects a date in the top filter and in the respective WQ.WorkQueueRunGroupRunID assigned to that date gets selected in the where clause.????

I asking this because the results are 100% accurate if we use the Unique Column 'WQ.WorkQueueRunGroupRunID'

I'm not sure what you're asking, but the WHERE clause I showed you will give you all records with the WorkQueueStartWorkDate between the start and end dates (inclusive). Is the problem because your jobs cross midnight (12am) and span multiple days? If so, just add the time element so that you're only getting the records you're after.

HTH,
Rob
Post #1400596
Posted Thursday, December 27, 2012 5:40 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 10:57 PM
Points: 458, Visits: 857
I can see why you might not want to use the @StartDate and @EndDate suggestion because you probably only want your users to have to pick one date and have it show all the tasks on that date. A slight modification to that might be

WQ.WorkQueueStartWorkDate between @StartDate and DateAdd(d,1,@StartDate)

It's still the easiest suggestion but there are some complicating questions that need to be answered:

Does the WorkQueueStartWorkDate have a time component? (Or is it just midnight)
Is it possible for there to be more than one run per day? (How does your ETL logging handle multiple runs?)

The answer to those two questions could complicate the way you want to do it, but the following may help. If your DW is huge and you have an index on the WorkQueueRunGroupRunID then this might be a bit faster but other than that there is no benefit over the previous suggestion.

1) create a parameter @RunID

2) Create a dataset for the parameter:

Select 
WQ.WorkQueueStartWorkDate
WQ.WorkQueueRunGroupRunID
FROM CloudBIMetaData.dbo.WorkQueue WQ
Order by WQ.WorkQueueStartWorkDate Desc

Note - If you have a master table of dates and run id's then use that instead.

3) Edit the properties of the parameter and set the Available values to "get values from a query" and use the dataset created in step 2.
Use the WorkQueueRunGroupRunID as the Value Field
Use the WorkQueueStartWorkDate as the Label. If the date has a time component you can format it to show the date only.

Note - If you have multiple runs per date then you will need to add another differentiator for the label so you know which run you are selecting for that day. GUID's won't be in order of run time.

4) Edit your main report Dataset to add:
Where WQ.WorkQueueRunGroupRunID = @RunID


Note: this will need some tweaking if you potentially have more than one run per day, because you might see multiples of each of your tasks. Even if you don't think you will have more than one ETL run per day, you probably will one day when it breaks and you need to re-run it.



Post #1400761
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse