Create Chart with Rolling Totals Data

Business Case:
User wants to create a chart report that displays rolling 12 months revenue by for current year (In this example 2010). Although 2009 revenues should be used in calculating the rolling totals, the chart should display only 2010 months.

Implementation:

• Create a base query with data for Prior Year (2009) and and Current Year (2010). My data sample looks like this:

• Set the aggregate property of Revenue in the Base Query to "Total".
• Add a data item called [Dummy] with expression 'Dummy'.
• Create a new query, call it CY and drag the base query as a short cut
• Add the data items from the base query to CY query.
• Add a filter in 'CY' query to filter only for current year's data. In this example, I added:

extract(year, [Order Date]) = 2010

• Create a new query called summary and add a join element.
• Add Base query and CY to the join element. The queries should look like this:

• Join the queries based on the data item [Dummy]. We are basically creating a cartesian product, but since cognos needs a join, we are using a dummy column.

•  Now that we have created a cartesian product, we should limit the data to rolling 12 months. So add this filter to the Summary query:

[Base Data].[Order Date] between _add_months([CY].[Order Date],-11) and [CY].[Order Date]

• Add the [Date] from 'CY' query and revenue from "Base Data" Query.
• Create the chart based of the "Summary" query.  The report output will look like this:

•  If you compare the tabular data and the base data above, you will see that each month's revenue includes that month and the previous 11 months.

Hide "Select All / Deselect All" Link in Value Prompts

Keywords: Value Prompt - Show or Hide - Select All / Deselect All - Javascript - Report Studio - Cognos 8.3 - Cognos 8.4

Business Case:
Business users want  the "Select All / Deselect All" link displayed under mulit-select Value Prompts to be hidden.  This is usually requested when the value prompt has hundreds or thousands of values and they shouldn't all be selected at once.

Note: To limit the number of options a user can select at a time, refer to the script in this article: Validate Value Prompt using Javascript

Implementation:
The links displayed in the prompt page are HTML elements. So, we can use Javascript to hide them. There are two different scripts that can be used to achieve this.

Note: The scripts in this article are available in IBM Knowledge Base (Link to IBM Knowledge base Article.)

Script 1: Use IBM's built in Object Names and Methods to hide the link

• The script works in Cognos 8.3 and 8.4

<script type="text/javascript">
var a = document.getElementsByTagName("A");
for( var i = a.length-1; i >= 0; i-- )
{
    var link = a[i];

    if( typeof(link.id) == "string" && link.id.match(/PRMT_(SV|LIST)_LINK_(DE)?SELECT_/) )
    {
        link.parentNode.removeChild(link);
    }
}
</script>

• To use this script, place an HTML Item after the value prompt and place the script inside this item.
• The advantage of this script is that, it's a simple one step approach.

Script 2 - Alternate more generic approach (not dependent on internal prompt names)

• Add an HTML item to the left and right of the value prompt in the Report Studio
• For the first HTML item (left) add the following script:

<span id = "A1">

• For the second HTML item (right) add the following script:

</span>

• Add an HTML item at the bottom of the prompt page next to "Run" button and add the following script for the third HTML item:

<script>
var theSpan = document.getElementById("A1");
var a = theSpan.getElementsByTagName("A");

for( var i = a.length-1; i >= 0; i-- )
{
    var link = a[i];
    link.parentNode.removeChild(link);
}
</script>

Show or Hide Date Prompts based on Radio Button Selection

Keywords: Date Prompt - Show or Hide - Radio Button - Javascript - Report Studio - Cognos 8

Business Case:
Many a times, you may want to give users the option to run the report for predefined date ranges (prior month, prior year etc) or enter custom dates. When the user selects a predefined option, the date prompt should be disabled. When the user selects "Custom" the date prompt should be enabled. This needs to be done to avoid confusion in the prompt page.

Example:
A daily scheduled report runs for the prior day. Users want to use the same report for custom dates as well. If they want to run the report for a historical data, they should be able to select a date or date range.


Solution:
We will be using Javascript to manipulate the Radio Button and date prompts. We will attach an HTML "On Change" event to the radio buttons, which will trigger a function. Depending on the selection, the function will disable or hide the date prompt.

Implementation:

• To start with, create a radio button with necessary static values. E.g. "Yesterday" and "Custom"
• Set the "Required" property to "No". This is very important. If this is not done, the "Finish" button will not be enabled.
• Create a Date Prompt and set the "Required" property to "No".
• We will need 3 HTML Items.
• Place the first HTML item immediately before the date prompt and the second HTML item immediately after the date prompt.
• These two HTML items will create a handle for the date prompt.
• Place the third HTML item at the end of the page. This will contain the main script. 
• I created a table and placed the prompts inside them. When you have done this, the prompt page layout will look like this:

•  Place the following script in HTML Item 1:

<span id="date_prompt">

•  Place the following script in HTML Item 2:

</span>

•  Place the following script in HTML Item 3, the main script:

<script type="text/javascript">
var inputs = document.getElementsByTagName("input");
var list_box = new Array();

/****Identify Radio Buttons and load them into an array****/
var radio_buttons = new Array();
j=0;

for(i=0;i<inputs.length;i++)
{
    if(inputs[i].type=='radio')
    {
        radio_buttons[j] = inputs[i];
        j++;
    }  
}

/****Set the onclick event of each radio button option to trigger our custom function****/
radio_buttons[0].setAttribute("onclick",function(){ToggleDate();});
radio_buttons[1].setAttribute("onclick",function(){ToggleDate();});

/****Create a handle for date prompt****/
var prompt = document.getElementById("date_prompt").childNodes[0];

function ToggleDate()
{
    if(radio_buttons[0].checked)
    {
        /**** First options (in our case "Custom") is selected.****
         **** Date Prompt will be Shown or Enabled.            ****/

        //document.getElementById("date_prompt").style.display = '';
        prompt.disabled = '';
    }
    else
    {
        /**** Second options (in our case "Yesterday") is selected.****
         **** Date Prompt will be Hidden or Disabled.              ****/

        //document.getElementById("date_prompt").style.display = 'none';
        prompt.disabled = 'true';
    }
}
</script>

• Now when you run the report depending on the radio button you select, the date prompt will be disabled on enabled.
• If you want to hide the date prompt instead of disabling it, comment out (add // in front) the following lines

prompt.disabled = '';

prompt.disabled = 'true';

• and uncomment (remove // from front) the following lines:

//document.getElementById("date_prompt").style.display = '';

//document.getElementById("date_prompt").style.display = 'none';


• Note that the code for disabling works only for date prompts, whereas the code for hiding works for any element that you put between the two HTML items. 
• The last step is to create a filter the combines both the prompt selections.
• This filter should be set to "Optional".
• In my example, the prompts are named as date_type (radio button) and report_date (date prompt). The filter expression will be:

(?date_type? = 'custom' and [Order Date] = cast(?report_date?,date))
OR
?date_type? = 'yesterday' and [Order Date] = trunc(_add_days(current_date,-1))

Note: Refer to "Use the Same Report for Scheduling and Manual Run" to see more details about how to structure the filter expressions in these cases.

• When you run the report, the prompt page will look like this:

• When the "Custom" option is selected the date prompt will still be enabled. See below:

• When the "Yesterday" option is selected the date prompt will be disabled, like this:

• Once the prompt page is done, you would want to add the date selection in the report page, so that users know what range they selected.
• I do this by creating a data item in the report query: Report Date
• The expression for Report Date would be something like this:

Case
   When ?date_type? = 'custom' Then cast(?report_date?,date))
   When ?date_type? = 'yesterday' Then trunc(_add_days(current_date,-1))
End


• You can now create a singleton item on the report page and drag this data item to the page.

In the next article, I will post the script to do the same thing using a drop down value prompt instead of Radio Buttons.