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

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

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

Hi Mark, 

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