IBM Business Analytics Proven Practices: Setting Dynamic Date Prompt Values within IBM Cognos 10.2 Report Studio

Product(s): IBM Cognos 10; Area of Interest: Reporting

This document describes a technique to set dynamic default values for date prompts using a combination of prompt macros and JavaScript.

Share:

Vinit Kharche, Software Developer, IBM

Vinit KharacheVinit Kharche is a Software Developer at IBM India Software Lab (EMM group) with over 3 years of experience. He has been responsible for development of Reports using Cognos for EMM product suite.



14 November 2012

Introduction

Purpose of Document

This document describes a technique to set dynamic default values for date prompts using a combination of prompt macros and JavaScript.

Applicability

The technique outlined in this document was validated using IBM Cognos BI 10.2 and the GO Sales(query) sample package.

Exclusions and Exceptions

The technique outlined in this document requires the use of undocumented and unsupported capabilities in IBM Cognos BI. There may be a risk associated with this technique in that support for these capabilities may change or be dropped entirely in some future release.

This technique was created as a proof of concept and does not cover all usage scenarios. This technique should be thoroughly tested before being used on a live IBM Cognos BI system.


Implementing the Technique Step by Step

The following section will step a user through:

  1. The creation of a simple list report.
  2. The application of the filters to incorporate a date prompt macro with default dynamic date values.
  3. Implementing the JavaScript to pre-select the dynamic date values within the date picker prompt objects.

Creating the Simple List Report

  1. Open IBM Cognos Report Studio against the GO Sales(query) sample package.
  2. From the Toolbox pane, select and drag the List object onto the right hand report canvas.
  3. From the Source pane, select the Sales(query)\Products\Product line query item and drag it into the List object.
  4. Also from the Source pane, select Sales(query)\Sales\Revenue and drag it into the List object beside the Product line object. When completed, the report should consist of a List object with a Product line column and a Revenue column.

Adding the Dynamic Prompt Macro Filters

For this example the requirement is to have the report default to the last 90 days based on the current date the report is executed.

  1. Create two detail filters on the Query which is used by the List with the following definitions.
    Filter Name: FromDate
    Filter Expression:[Sales (query)].[Time].[Date]>=(#prompt('FromDate','date', '(_add_days (current_date,-90))')#)

    Filter Name: ToDate
    Filter Expression: [Sales (query)].[Time].[Date] <=(#prompt('ToDate','date', 'current_date')#)
  2. Ensure the Usage property for the detail filters is changed from Required to Optional. When completed the Query Explorer screen should have Product line and Revenue within the Data Items pane and two filters in the Detail Filters pane. This is illustrated in Figure 1.
    Figure 1 - IBM Cognos Report Studio Query Explorer displaying the Data Items and Detail Filters
    Figure 1 - IBM Cognos Report Studio Query Explorer displaying the Data Items and Detail Filters
  3. From the Toolbox pane, drag a Date Prompt object onto the report canvas above the List object.
  4. When prompted, select the Use existing parameter radio button.
  5. From the Use existing parameter drop down box, select the FromDate prompt name and click the Finish button.
  6. Within the right hand report canvas pane, click on the date prompt previously inserted. Within the Date Prompt properties pane, set the Name property to PDateFrom.
  7. Also within the Date Prompt properties pane, switch the Select UI property from Calendar to Edit box.
    Figure 2 illustrates the Date Prompt properties pane for the FromDate parameter with the Name property set to PDateFrom and the Select UI property set to Calendar.
    Figure 2 - IBM Cognos Report Studio Date Prompt properties pane for the FromDate parameter
    Figure 2 - IBM Cognos Report Studio Date Prompt properties pane for the FromDate parameter
  8. Repeat steps 3 to 7 within this section for the ToDate parameter, setting the Name property name of PDateTo.
  9. From the Toolbox pane, drag a Prompt Button onto the report canvas next to the two date prompt objects above the List object.
  10. Click on the Prompt Button and in the Properties pane, change the type from Next to Finish.

Adding the JavaScript to Set the Dynamic Prompt Values

  1. From the left hand Toolbox pane, locate the HTML object.
  2. Drag an HTML object to the right of the FromDate Prompt object.
  3. Double-click the HTML item and add the following code:
    <script type="text/javascript">
    var fdf = getFormWarpRequest();
    var listDateFrom = fdf.txtDatePDateFrom
    var ftp = getFormWarpRequest();
    function last90Days()
    {
    var ndays = 90;
    var dtToday = new Date();
    var dtlast90Days = new Date (dtToday - (86400000*(ndays)));
    var strlast90Days = [dtlast90Days.getUTCFullYear(), dtlast90Days.getMonth()+1,
      dtlast90Days.getDate()].join("-");
    return strlast90Days;
    }
    pickerControlPDateFrom.setValue(last90Days() );
    </script>
  4. Click on the OK button to save the code.
  5. Drag another HTML object to the right of the ToDate Prompt object.
  6. Double-click the HTML item and add the following code:
    <script type="text/javascript">
    var fdt = getFormWarpRequest();
    var listDateTo = fdt.txtDatePDateTo
    var ftp1 = getFormWarpRequest();
    function last0Days()
    {
    var dtToday = new Date();
    var strlast0Days = [dtToday.getUTCFullYear(), dtToday.getMonth()+1,
      dtToday.getDate()].join("-");
    return strlast0Days;
    }
    pickerControlPDateTo.setValue(last0Days() );
    </script>
  7. Click on the OK button to save the code.

Testing the Report

To test the report and its associated JavaScript, simply run the report through IBM Cognos Viewer. When the report renders, the report data should reflect the data between the current date and the past 90 days. The date prompt objects should also have the equivalent values pre-set for the user.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Big data and analytics on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Big data and analytics
ArticleID=845182
ArticleTitle=IBM Business Analytics Proven Practices: Setting Dynamic Date Prompt Values within IBM Cognos 10.2 Report Studio
publish-date=11142012