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

QMetrix is ISO/IEC 27001 certified: Information Security Management System

QMetrix is pleased to announce we are now ISO/IEC 27001 certified, demonstrating our commitment to information and data security. Highlighted by various data breaches in recent times, cyber threats and criminal activities are on the rise. It is paramount for organisations to uphold high security standards and manage cyber-risks even before they occur in order

QMetrix is named 2024 APJ Workday Adaptive Planning Partner of the Year

QMetrix is named Winner of the 2024 Adaptive Planning Partner of the Year Award – APJ at the annual Workday Sales Kickoff. QMetrix announced it was named a winner of the 2024 Adaptive Planning Partner  of the Year Award – APJ by Workday, Inc. (NASDAQ: WDAY), a leading provider of solutions to help organisations manage

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