Cognos Analytics

Cognos Analytics

Connect, learn, and share with thousands of IBM Cognos Analytics users! 

 View Only
Back to discussions
Expand all | Collapse all

Dynamic Date Filters

  • 1.  Dynamic Date Filters

    Posted Thu July 02, 2020 09:59 AM
    Version: IBM Cognos Analytics 11.1 R6

    Hey all, hoping you can assist a new user with the best approach to managing date filters.

    Basically, one of the ongoing problems we're facing is how to apply dynamic date filters to the dashboard and individual charts independently.     In our current scenario, we need to default the dashboard level to year to date (with option to adjust to any date via a date filter) and have other charts default to a different date range by default (e.g. MTD). When the user adjusts the filter at the dashboard level, all charts are updated to the new date range (e.g. 2019). Relative date filters do not appear to work in the scenario.

    Looking for advice as to how a) set a date range filter that defaults to year to date (irrespective of how many years of data exists in the date set) at the dashboard level but can be changed outside of the YTD period when required using the date picker and b) how chart defaults can be overridden when a user changes the date range at the dashboard level (e.g. from a previous month default to last year if the latter is changed in the dashboard filter).

    Is this possible? Thanks.

    ------------------------------
    Dean
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: Dynamic Date Filters

    Posted Thu July 02, 2020 10:42 AM
    There can be multiple ways of implementing this, here's one of the option :-

    Your YTD and MTD Filters can be defined at Model/Data Module level as follows:-
         a) YTD Filter
              Expression - [YourDateField] between _add_days(current_date,-1*_day_of_year (current_date)+1)  and current_date
         b) MTD Filter
              Expression - [YourDateField] between _first_of_month (current_date) and current_date

    You can add a Radio button selection for users to select their desired option.
    Let's say this is pDateMode prompt on Report page with options as
    • Default Date Filters - Hide "From Date" & "To Date" prompt selections
    • Custom Date Filters - Show "From Date" & "To Date" prompt selections

    Filter condition in each of the Query subject would say: -
    (?pDateMode?='Default Date Filters' and [YTD Filter]) OR (?pDateMode?='Custom Date Filters' and [YourDateField] between ?pStartDate? and ?pEndDate? )

    Basically you can play around with your filter conditions using aforesaid options in hand.
    Hope this helps.

    ------------------------------
    Dinesh K. Dhiman
    BI Architect,
    IBM Data and AI Experts Labs | India Software Lab Services
    ------------------------------



  • 3.  RE: Dynamic Date Filters

    Posted Thu July 02, 2020 10:57 AM
    Edited by System Admin Fri January 20, 2023 04:49 PM
    Thanks Dinesh. Is there any possibility the same functionality can be added to a dashboard instead of a report?

    ------------------------------
    Dean
    ------------------------------

    Original Message


  • 4.  RE: Dynamic Date Filters

    Posted Thu July 02, 2020 11:20 AM
    Hello Dean,

    Prompt options are not available with Dashboard utility (as of now).

    However, you can still explore the options using given Filters and based conditions in Dashboards.

    ------------------------------
    Dinesh K. Dhiman
    BI Architect,
    IBM Data and AI Experts Labs | India Software Lab Services
    ------------------------------

    Original Message


  • 5.  RE: Dynamic Date Filters

    Posted Thu July 02, 2020 01:28 PM
    Thanks for your help Dinesh.

    ------------------------------
    Pegasus Administrator
    ------------------------------

    Original Message


  • 6.  RE: Dynamic Date Filters

    Posted Thu January 22, 2026 09:42 AM

    Hi Dinesh,

    I hope you are well. 

    I like using these expressions to achieve some relative date filters while also allowing the user to enter a custom range if desired. However, I've noticed that when running the report again (reprompting), we are always presented with another prompt for ?pStartDate? and ?pEndDate?. For instance, I run the report and select a relative month-to-date. Then in the results I click the run button to return to the prompt page. I select another option (either a different relative range or a different, non-date filter), click Finish on the prompt page and then I get a generic page for requiring I select values for ?pStartDate? and ?pEndDate?. 

    Have you experienced this and found a workaround? 

    This is what my filter expression looks like:

    ( ( ?pDateType? = 'CM') and ( [Creation Date] between _first_of_month ( current_date ) and _last_of_month ( current_date ) ) )
    or
    ( ( ?pDateType? = 'MTD') and ( [Creation Date] between _first_of_month ( current_date ) and current_date  ) )
    or
    ( ( ?pDateType? = 'PM') and ( [Creation Date] between _first_of_month ( _add_months ( current_date, -1 ) ) and _last_of_month ( _add_months ( current_date, -1 ) ) ) )
    or
    ( ( ?pDateType? = 'CY') and ( [Creation Date] between _add_days ( current_date, ( ( _day_of_year ( current_date ) -1 ) * -1 ) )  and cast ( extract ( year, current_date ) || '-12' || '-31', date ) ) )
    or
    ( ( ?pDateType? = 'CD') and ( [Creation Date] = current_date  ) )
    or
    ( ( ?pDateType? = 'PD') and ( [Creation Date] = _add_days (  current_date, -1 )  ) )
    or
    ( ( ?pDateType? = 'Custom') and ( [Creation Date] between ?pFromDate? and ?pToDate? ) )

    Thanks,
    Jeremy



    ------------------------------
    Jeremy Aude
    ------------------------------

    Original Message


  • 7.  RE: Dynamic Date Filters

    Posted Thu January 22, 2026 11:55 AM
    Edited by Dinesh K. Dhiman Thu January 22, 2026 11:57 AM

    Hi Jeremy,

    This can be handled using default values.

    You can also use prompt macro for passing the default values.

    Please feel free to share the report xml if problem persist, I will be happy to help.

    Thanks

    DD



    ------------------------------
    Dinesh K. Dhiman
    Technical Product Manager, Cognos Analytics Offering Management
    ------------------------------

    Original Message


  • 8.  RE: Dynamic Date Filters

    Posted Thu January 22, 2026 01:34 PM
      |   view attached

    Hi Dinesh,

    I should've mentioned I tried setting default values. Perhaps I'm doing something wrong there. Please fid report XML attached. 

    I really appreciate your help and thanks for the quick reply!



    ------------------------------
    Jeremy Aude
    ------------------------------

    Attachment(s)

    txt
    report_spec.txt   37 KB 1 version
    Original Message


  • 9.  RE: Dynamic Date Filters

    Posted Fri January 23, 2026 10:52 AM

    Hi Jeremy,

    If I understand this posting correctly -- your prompt page has two parameters named "startdate" and "enddate" and you are questioning why you get prompted for another "start" and "end" date when re-running the report.

    If that is the case, I think it is due to your filter statement using the parameters names of "fromdate" and "todate".  If think if you change this filter to "start" and "end" -- you might find that your extra prompt will disappear. 

    Hope that does the trick for you.



    ------------------------------
    John Cusack
    Analytica iQ
    ------------------------------

    Original Message


  • 10.  RE: Dynamic Date Filters

    Posted Fri January 23, 2026 02:18 PM

    Hi John,

    Thanks for the reply. I wish it were that easy (though I'd be a little embarrassed if this were the case). The prompts are assigned the same name as the filter statement pFromDate and pToDate. They are hidden in a block and only display when the user selected "Custom Range" instead of a predefined relative date from the value prompt. I think I'm not seeing this extra prompt page on the first run of the report because the default values "_add_days ( ReportDate(); -1 )" are being used. It seems the reprompt (clicking the run button within the viewer) is clearing out those default values, causing the extra page to appear. 

    I don't if what I said is easy for you to visualize (sorry!). Here are a few screenshots. 



    ------------------------------
    Jeremy Aude
    ------------------------------

    Original Message


  • 11.  RE: Dynamic Date Filters

    Posted Fri January 23, 2026 04:29 PM
    Edited by Jeremy Aude Fri January 23, 2026 08:52 PM

    I have a solution: remove the logic from the filter and move two data item expressions. 

    Data Item 1: Effective Start Date

    case
      when ?pDateType? = 'CM' then _first_of_month(current_date)
      when ?pDateType? = 'MTD' then _first_of_month(current_date)
      when ?pDateType? = 'PM' then _first_of_month(_add_months(current_date, -1))
      when ?pDateType? = 'CY' then _add_days(current_date, ((_day_of_year(current_date) - 1) * -1))
      when ?pDateType? = 'CD' then current_date
      when ?pDateType? = 'PD' then _add_days(current_date, -1)
      when ?pDateType? = 'Custom' then ?pFromDate?
    end

    Data Item 2: Effective End Date

    case
      when ?pDateType? = 'CM' then _last_of_month(current_date)
      when ?pDateType? = 'MTD' then current_date
      when ?pDateType? = 'PM' then _last_of_month(_add_months(current_date, -1))
      when ?pDateType? = 'CY' then cast(extract(year, current_date) || '-12-31', date)
      when ?pDateType? = 'CD' then current_date
      when ?pDateType? = 'PD' then _add_days(current_date, -1)
      when ?pDateType? = 'Custom' then ?pToDate?
    end

    Updated filter expression: [Creation Date] between [Effective Start Date] and [Effective End Date]

    I actually ended up putting these calculations into my data module so I can reuse across reports. 

    Thanks everyone.



  • 12.  RE: Dynamic Date Filters

    Posted Tue January 27, 2026 01:33 AM

    Glad to hear, you found an alternate solution Jeremy :)

    Happy Cognos-ing.



    ------------------------------
    Dinesh K. Dhiman
    Technical Product Manager.
    Cognos Analytics Offering Management
    ------------------------------

    Original Message


Related Content