Cognos Analytics

Cognos Analytics

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

 View Only

  • 1.  Date Format in Table List Header Using Report Expression

    Posted Fri April 14, 2023 04:27 AM

    Hi All,


    We are using IBM Cognos Analytics with Watson (11.2.0).


    We have this date prompt which is used to filter the data. At the same time we have this requirement to get the date selected, minus 1 months from it and add some strings on the table header (basically get the months before the date selected and add strings in between). Can refer to attachment below for reference.

    I know it's a bit long but here is how I do to get this value from the report expression:

    'As at ' + date2string (timestamp2date(_add_months(date2timestamp(string2date(ParamValue('date_filter'))),-1))) + ' (Official)'


    Instead of having the whole date, is there a way to get only Months and Year ( Mar 2023) ? 


    Your help is deeply appreciated. 


    Regards,
    Aiman



    ------------------------------
    Mohamad Aiman Arif Mohamat Saat
    ------------------------------


  • 2.  RE: Date Format in Table List Header Using Report Expression

    Posted Fri April 14, 2023 07:01 AM

    Depending on what your needs are, I'd be tempted to break that up into separate chunks and just concatenate them. So something along the lines of

    'As at' + cast(month(_add_months(ParamValue('data_Filter'),-1)) as char(3)) + cast(year(_add_months(ParamValue('date_filter),-1)) as char(4))

    I haven't tested that, so do some checking and make sure I haven't got any typoes in there :)



    ------------------------------
    Chris Turner
    ------------------------------

    Original Message


  • 3.  RE: Date Format in Table List Header Using Report Expression

    Posted Fri April 14, 2023 11:26 AM
    Edited by Mark Fry Fri April 14, 2023 11:29 AM

    Hi,

    I'm assuming this is a list report? I would be tempted to just unlock the structure of the report and add the "As at" and "(Official)" text as text items into the list column header. For the date, add a query calculation that does _add_months([your date item], -1), and drag this into the column header between the text items. You can then select the query calculation in the column heading and specify the data format to be just the month and year.



    Cheers!

    MF.



    ------------------------------
    Mark Fry
    Technical Consultant
    ------------------------------

    Original Message


  • 4.  RE: Date Format in Table List Header Using Report Expression

    Posted Tue April 18, 2023 03:17 AM

    Hi Mark, 

    Thanks for your suggestion! I never knew we can do this on the list report header.



    ------------------------------
    Mohamad Aiman Arif Mohamat Saat
    ------------------------------

    Original Message