Cognos Analytics

Expand all | Collapse all

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

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

    Posted 30 days ago
    Edited by Jim Denton 30 days ago
    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
    ------------------------------


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

    Posted 30 days ago

    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
    ------------------------------



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

    Posted 30 days ago
    Edited by Jim Denton 30 days ago
    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
    ------------------------------



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

    Posted 30 days ago

    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
    ------------------------------



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

    Posted 29 days ago
    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
    ------------------------------



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

    Posted 30 days ago

    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
    ------------------------------