Cognos Analytics

 View Only

  • 1.  Need help with writing a data item expression - hopefully can do a query

    Posted Fri April 09, 2021 03:46 PM
    Edited by System Fri January 20, 2023 04:19 PM
    Hi all,
        Need help with writing a data item expression - hopefully can write a query:
    Count the number of Visit dates in Month where the Assessment is value X.  Currently I have a counting function to get X based on the user entering a start and end date range for Visit, but now I need it to be based on a different Month in each row, so need something like
    Total(Visit Dates with Assessment X) WHERE Visit_Date IN Month(Date_Column)

    Jim

    ------------------------------
    Jim Denton
    ------------------------------
    #CognosAnalyticswithWatson


  • 2.  RE: Need help with writing a data item expression - hopefully can do a query

    Posted Fri April 09, 2021 03:56 PM

    Hi Jim,
    How about something like:

    total ( case when Assesment = 'x' and  ( adate between start_date and end_date  or adate in (2021-04-08, 2021-04-07) )

    then 1 end )

    // Henk



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

    Original Message


  • 3.  RE: Need help with writing a data item expression - hopefully can do a query

    Posted Fri April 09, 2021 05:02 PM
    Edited by System Fri January 20, 2023 04:18 PM
    Something like that.  Where can I find documentation on Cognos data item expressions?  I need to find out for example if there is a Month function, to get the month of a date, and IN funcition, to get at the date check Visit_Date IN Month(Date).

    Or perhaps cleaner would be Month(Visit_Date) = Month(Month_Column).

    ------------------------------
    Jim Denton
    ------------------------------

    Original Message


  • 4.  RE: Need help with writing a data item expression - hopefully can do a query

    Posted Fri April 09, 2021 05:12 PM

    Hi Jim,

    The functions and documentation are integrated with the expression editor in the various tools (Reporting, Webbased Modeling and Framework Manager).

    In report Studio you can tap on the functions tap and see all supported functions, with help and examples. (see attached screen shot).

    If you'd like to do a webex, you can send me a message at henk.cazemier@ca.ibm.com

    For your case you may want to do xdate between _first_of_month( bdate) and _last_of_month (bdate)
    //Henk



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

    Original Message


  • 5.  RE: Need help with writing a data item expression - hopefully can do a query

    Posted Sat April 10, 2021 04:40 PM
    Thanks for all of the help.  I was able to use the _month function BUT ... for some reason one of reports generates an error "The vendor specific function "_month" is not supported."  Same code copied doesn't work.  I tried without the underscore and got the same message.

    Jim

    ------------------------------
    Jim Denton
    ------------------------------

    Original Message


  • 6.  RE: Need help with writing a data item expression - hopefully can do a query

    Posted Fri April 09, 2021 07:08 PM

    Hi Jim,  documentation online covers scalar functions, aggregate functions, conditional functions etc

    i.e. 

    https://www.ibm.com/docs/en/cognos-analytics/11.0.0?topic=modeling-using-expression-editor

    https://www.ibm.com/docs/en/cognos-analytics/11.0.0?topic=SSEP7J_11.0.0/com.ibm.swg.ba.cognos.ca_mdlg.doc/c_businesstimefunction.html



    ------------------------------
    NIGEL CAMPBELL
    ------------------------------

    Original Message