Cognos: Setting dynamic default date prompts

In IBM Cognos Report Studio you can set default values in your prompts, however these are not dynamic. So when you have reports that you want to mostly run for the previous month period, but then from time to time you may want to run it for other periods, you would have to do this in two separate reports or not have default dates set at all.

Here is a way for you to have both.


Default Date Query

Create a new report in Report Studio and create a Query called ‘p_DefaultDates’. The ‘p_’ stands for prompt. Note: For my example I am using a SQL Server database so the functions below may need to be adjusted according to your data source.

Within this Query create three Data Items called:

  • Current Date
  • First of Month
  • Last of Month

Within these Data Items type in the following:

Current Date current_date
First of Month _first_of_month ( [Last of Month] )
Last of Month _last_of_month ( _add_months ([Current Date], -1 ) )

When you run this in View Tabular Data you will get the following:

Cognos: Setting dynamic default date prompts | QMetrix

 

Hidden Prompt Page

Then create a Prompt Page called ‘Prompt Page Hidden’. Within the prompt page drag in a table 1 column, 2 rows, two HTML Items and two Value Prompts and arrange them as follows:

Within the first HTML Item type in the following:

Within the second HTML Item type in (or copy) the following:

 

function setPrompts()
{
f = getFormWarpRequest();
f.getElementsByTagName(“select”)[0][2].selected = true;
f.getElementsByTagName(“select”)[1][2].selected = true;
setTimeout(“promptButtonNext();”, 300);
}

setPrompts();

Within the first Value Prompt set as per the following:

Cognos: Setting dynamic default date prompts | QMetrix

 

With the second Value Prmpt set as per the following:

Cognos: Setting dynamic default date prompts | QMetrix


Visible Prompt Page

Now create a prompt page that you will see. On this page add a table consisting of 2 columns and 2 rows, 2 text items and 2 date prompts and arrange them as follows:

Cognos: Setting dynamic default date prompts | QMetrix


Within the first Date Prompt (Date From) set as per the following:

Cognos: Setting dynamic default date prompts | QMetrix


Within the second Date Prompt (Date To) set as per the following:

Cognos: Setting dynamic default date prompts | QMetrix


Run the Report

Now when you run your report, you will see the Visible Prompt Page with the start and end date prompts being the first and last days of the previous month.

Cognos: Setting dynamic default date prompts | QMetrix


You then just pass these values into the main query within your report as you normally would.


Adjusting for other Periods

Adjusting for other periods is very easy; just by change what is in the p_Default_Dates Query.

Related Articles

Financial planning solutions: A buyer’s guide

[eBook] When purchasing a planning solution, you want to find the right technology for your organisation’s specific needs, and the right partner to support your needs and goals. Here are 5 steps to getting that right.

Stay in touch

Get the latest insights from QMetrix straight in you inbox.

Scroll to Top

This website uses cookies to ensure you get the best experience on our website. More Information

Skip to content