Need help in Building a parameterized Report.

  • 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.

  • 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

  • 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'

  • 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

  • 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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply