Need help in Building a parameterized Report.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services » Need help in Building a parameterized Report....

Need help in Building a parameterized Report.

Rate Topic

Display Mode

Topic Options

Author

Message

Mac1986

Posted Saturday, December 22, 2012 10:58 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 4:07 AM
Points: 180, Visits: 516

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

robert.gerald.taylor

Posted Monday, December 24, 2012 8:29 AM

Right there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 6:44 AM
Points: 759, Visits: 802

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

Mac1986

Posted Thursday, December 27, 2012 1:52 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 4:07 AM
Points: 180, Visits: 516

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

robert.gerald.taylor

Posted Thursday, December 27, 2012 6:33 AM

Right there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 6:44 AM
Points: 759, Visits: 802

Mac1986 (12/27/2012)

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

davoscollective

Posted Thursday, December 27, 2012 5:40 PM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 6:31 PM
Points: 413, Visits: 703

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 »

Permissions