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:
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:
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:
With the second Value Prmpt set as per the following:
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:
Within the first Date Prompt (Date From) set as per the following:
Within the second Date Prompt (Date To) set as per the following:
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.
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.
