Cognos Analytics

 View Only

  • 1.  current_date and filter/parameter check

    Posted Mon May 06, 2019 09:50 AM

    Hi,

    I want to have a filter that does the following:
    compare a edit box prompt (called "To Date") to the current_date and
    compare a edit box prompt (called "From Date") to the current_date

    and if they match, make the To Date be the current date - 15 years.

    I believe making the To Date would be "To Date" = add_years (To_Date, -15) but for whatever reason, I can't seem to get the format of the To Date and current_date correct to compare them.

    Any ideas? When I look at the To Date param it says May 6, 2019 and the current_date says May 6, 2019.



    ------------------------------
    Vic Nicholls
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: current_date and filter/parameter check

    Posted Mon May 06, 2019 10:23 AM
    Hi Vic,
    Which version are you using and which product ?
    If you're using Report Studio, could you attach that part of the specification?
    What are the values that are entered in the edit fields boxes?
    Have you tried using a date-prompt ?
    //Henk

    ------------------------------
    HENK CAZEMIER
    ------------------------------

    Original Message


  • 3.  RE: current_date and filter/parameter check

    Posted Mon May 06, 2019 12:26 PM
    Version 11.0.12.
    Part of spec? XML? and how would I do part of the spec?
    On the prompt page, I have 2 date prompts called To Date and From Date. Both are UI edit box, calendar type Gregorian.  When clicking on the box, a calendar comes up and I choose the calendar. From outputing the parameters on the page, May 6, 2019 is what shows for both items.
    I've tried the current_date which is a constant to compare to.
    Basically I want to say if the parameters From Date and To Date = today's date, make the from date add_years - 15.

    Thanks.

    ------------------------------
    Vic Nicholls
    ------------------------------

    Original Message


  • 4.  RE: current_date and filter/parameter check

    Posted Mon May 06, 2019 01:10 PM
      |   view attached
    Hi Vic,
    Attached is a report spec, you may need to connect it to a different package in order to get it to run. However you can also review/copy/paste the expressions. If you get an error opening it in Report Studio, you may want to edit the version numbers in the first line to match version numbers that you see in specs. in your environment; I'm pretty sure I did not use anything recently introduced.

    I used a UI - edit box- Gregorian calendar named the parameter from_date
    The start_date column has the expression:

    case when ?from_date? = current_date
    then _add_years(?from_date? , -15 )
    else ?from_date?
    end

    Some of the other columns show how to do it with macros values (evaluated at query prep time).
    I hope this helps.
    //Henk

    ------------------------------
    HENK CAZEMIER
    ------------------------------

    Attachment(s)

    xml
    compare_from_date.xml   10 KB 1 version
    Original Message


  • 5.  RE: current_date and filter/parameter check

    Posted Tue May 07, 2019 05:50 AM
    Hi,

    I tried that in a filter and kept getting errors. I then tried to put it in a data item and still get the following message:
    (code)
    case when ( ?Trans Date From Param? = current_date )
    then ( _add_years( ?Trans Date From Param? , -15 ) )
    else ( ?Trans Date From Param? )
    end
    (error)
    QE-DEF-0374 Prompt 'Trans Date From Param' is used multiple times with conflicting data types.

    Thanks.

    ------------------------------
    Vic Nicholls
    ------------------------------

    Original Message


  • 6.  RE: current_date and filter/parameter check

    Posted Tue May 07, 2019 10:01 AM
    Hi Vic,
    I tried a similar expression in Compatible Query Mode, which is what you're running based on the message, it works.
    Are you using the prompt ?Trans Date From Param? in any other expression in the same report?
    If you rename this prompt to ?t1? in the 3 instances in this expression, do you still get the same error?
    If you'd like, you can send me the report spec and I can review it to see if I can find the issue.
    //Henk

    ------------------------------
    HENK CAZEMIER
    ------------------------------

    Original Message


  • 7.  RE: current_date and filter/parameter check

    Posted Tue May 07, 2019 10:35 AM
    Hi,

    I will attempt to send it to you. Thanks!

    Vic

    ------------------------------
    Vic Nicholls
    ------------------------------

    Original Message