Tuesday, January 10, 2012

Use the Same Report for Scheduling and Manual Run

Business Case:
A scheduled report with a date filter should be customized to be used with date prompts.

Environment: IBM Cognos 8.4.1, Report Studio

Example:
I had built a scheduled report for my users that had a date filter for the past 7 days.  This report was scheduled to run every Monday and email the results to the users. The users wanted to be able to run this report for custom dates as well.

Solution:
This is a pretty standard request that I had come across many times.  When the report has a date filter using 'current_date' as the time of reference, then the report can be scheduled without a date prompt. For a report that should filter for the past 7 days, the filter will look like this:
[Order Date] between _add_days(current_date,-7) and _add_days(current_date,-1)
When the same report has to take date as input, users generally tend to take the easiest approach of making a copy of the report and change the filter to use parameters that get values from date prompts. The new filter will look like this:
[Order Date] between cast(?start_date?,date) and cast(?start_date?,date)

There is nothing wrong with this approach, except that, now you have two reports that have the same business logic and layout, but have to be maintained separately. If there's a change in the logic or layout, it has to be done in two places. This becomes cumbersome when the report is complex and has many queries.

As a best practice, I always like to merge the two requirements into a single report by adding a value prompt to the report. Let's see how this is done. This solution can be adopted for any scenario that requires a separate filter for manual vs. scheduled run.

Implementation:
  • Create a value prompt with two static choices: "Last Week" and "Custom". Let's call this parameter date_range.
  • Create two date prompts, one for Start Date and one for End Date. Let's call these parameters start_date and end_date.
  • Use the following expression in your query filter:
(?date_range? = 'Last Week' and
[Order Date] between _add_days(current_date,-7) and _add_days(current_date,-1))

OR

(?date_range? = 'Custom' and
[Order Date] between cast(?start_date?,date) and cast(?start_date?,date))
  • The expression above will make sure that the corresponding date filter is applied depending on the selection of "Date Range".
  • For example, when the users select "Last Week" from the value prompt, the filter will ignore what is selected in the date prompts.
  • Now, if you want to schedule the report to run on every Monday for the past week, schedule the report and set the "Date Range" prompt value in the schedule to "Last Week"
  • You can give any date values in the date prompts, since they will be ignored anyway.
Conclusion:
You can use this technique to many other cases as well. For example you can create custom groups of filter values and define your own group names. By hard coding the group members in the filter, the users will be able to select a group and get the report that has the individual members.

4 comments:

  1. Good job sriram. The above solution help me to achieve what exactly I want it.
    Winson.

    ReplyDelete
  2. This is a great document hwen you have the prompts in the report. By any chance, have you figured out a way to do it when you have Prompt Macros in the model?

    We have forced prompts in our model, do to data capacity. Lots of data, and the users must be forced to filter if they do ad-hoc querying, anytime. This has obviously created limitations when trying to automate. Have you tried this at all?

    ReplyDelete
    Replies
    1. Senator, I have not tried this. If you can give me an example, I can try it.

      Sriram.

      Delete
  3. Hi Sriram,
    I am creating a report to select alarms from db2 for a given datetime.

    I tried creating a filter based on example above.


    [new].[7706Alarms].[FIRSTOCCURRENCE] between cast(?start_date?,date) and cast(?start_date?,date))

    However it gives me error on validate.
    Error - QEDEF-0459 CCLExcpetion



    Pls suggest.

    ReplyDelete