SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need help in Building a parameterized Report.


Need help in Building a parameterized Report.

Author
Message
Mac1986
Mac1986
Mr or Mrs. 500
Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)

Group: General Forum Members
Points: 559 Visits: 791
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.
robert.gerald.taylor
robert.gerald.taylor
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1793 Visits: 1486
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
Mac1986
Mac1986
Mr or Mrs. 500
Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)

Group: General Forum Members
Points: 559 Visits: 791
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'
robert.gerald.taylor
robert.gerald.taylor
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1793 Visits: 1486
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
davoscollective
davoscollective
SSC Eights!
SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)

Group: General Forum Members
Points: 921 Visits: 1004
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search