How to create a report running current month by default or date range if specified

Check list of all posts

This document is intended to share two solution tips, which can applied for many difference cases.
Given that we need to create a report to satisfy requirements:
1) This report can be scheduled without any specifying parameter, by default, it will execute with current month.
2) This report can specify data range if needed.
Based on this requirement, we need to create a prompt page as below

If user select specify data range, then start date and end date will appear for users to spcifiy.

Create a fake value prompt to avoid refreshing report
When a user change the option from current month to specify date range, the start date and end date will be appear.  Obviously, you need to make prompt to set up as auto prompt.

 

Doing so will make report submit right away and move to real report page. To avoid this action, we need to make a faked value prompt to take  value from the selection.  We need to also make this faked value as NOT visible

Certainly, we need to make date range conditional with render variable

 

Make filter optional to allow user and Cognos schedule to run report against current month

Make default moth is used when date range is not specified. Cognos can only support  case 1 (IF
THEN) and  case 2 (CASE WHEN), doesn’t support the 3rd case.  Make sure this filter is OPTIONAL.

 

Case 1: filter works, as Cognos send SQL after applying the filter, in this case, ( [Sales (query)].[Time].[Date] BETWEEN _first_of_month(_add_months (current_date, -1 ))

if ( ?p_selected? = 'CURRENT'  ) THEN     
 ( [Sales (query)].[Time].[Date] BETWEEN _first_of_month(_add_months (current_date, -1 )) AND _last_of_month (_add_months (current_date, -1 )))
ELSE ( [Sales (query)].[Time].[Date]   BETWEEN ?Report Start Date? AND ?Report End Date? )

Case 2: Filter works , same reason as above

CASE WHEN ( ?p_selected? = 'CURRENT'  ) THEN     
 ( [Sales (query)].[Time].[Date] BETWEEN _first_of_month(_add_months (current_date, -1 )) AND _last_of_month (_add_months (current_date, -1 )))
ELSE ( [Sales (query)].[Time].[Date]   BETWEEN ?Report Start Date? AND ?Report End Date? )
END

Case3 filter doesn’t work, as Cognos doesn’t know how to handle another two filters. In this case, the filter WON’T apply at all. all data will be retrieved.

(
 ( ?p_selected? = 'CURRENT'  ) 
 AND
  ( [Sales (query)].[Time].[Date] BETWEEN _first_of_month(_add_months (current_date, -1 )) AND _last_of_month (_add_months (current_date, -1 )))
)
OR
(
 ( ?p_selected? <> 'CURRENT'  ) 
 AND
 ( [Sales (query)].[Time].[Date]   BETWEEN ?Report Start Date? AND ?Report End Date? )
)