I am learning IBM cognos and recently facing one issue after migrating from CQM to DQM. Can you please guide me where to ask my question?
Below is my filter in DQM :
([Report Date]>_last_of_month(_add_months (cast(?Para_CurrYear?+'-'+substring('000'||substring(cast((( cast(?Para_CurrPeriod?,integer)3)), varchar(3)),5,2),char_length(cast((( cast(?Para_CurrPeriod?,integer)3)), varchar(3))),2)'-''01',date),-3))) or ([Report Date] is null)
But I am getting below error:
"XQE-DAT-0005 Cannot convert the string value '2019-3-01' to data type date.".
Any help or guidance is appreciated.
Thanks
The format of a string being cast to date needs to be yyyy-mm-dd. In your case it should be 2019-03-01.
You will need to find the length of the result of the ?Para_CurrPeriod? prompt and, if the length is one, pad it with a zero.
I didn't spend too much time examining the expression but it struck me as rather complex, perhaps unnecessarily so. I didn't understand why you are using ?Para_CurrPeriod? twice. You are hard coding the first day of the month into the cast. I'm guessing that you are prompting for a year and then prompting for a month number and adding them to the hard-coded day. I was wondering if whether the expression which generates your date be simpler, perhaps in this format? Nevertheless, if your expression works for you with the added testing for the month length then don't fix wot ain't broke.
cast (
{some set of functions and stuff which returns the year}
|| '-' +
{some set of functions and stuff which returns the month number}
||
{some set of functions and stuff which returns the day number}
, date)
Or
cast (
{some set of functions and stuff which returns the year}
|| '-' +
{some set of functions and stuff which returns the month}
|| '-01'
, date)
Example
cast (
substring(cast(MONTH_KEY as varchar( 8 )),1,4 ) //this function is getting the first 4 chars of a 8 char string
|| '-' +
substring(cast(MONTH_KEY as varchar( 8 )),5,2 ) //this function is getting the 5th and 6th chars of a 8 char string
|| '-01' //for my purposes I needed the first day of the month to be hard coded but there's no reason why this could not be a function too.
, date)