i am using cognos10.1 and using below expression in my report query filter in report studio.
here MonthKEY has value from "199301" to "205012"
and i want to display between "201101" and last month of this year for .e.g. "201301" as our present month is "201302"
MonthKEY >= 201101 and MonthKEY <= cast((cast (extract (year, _add_months (current_date, -1)), varchar(4))||cast (extract (month, _add_months
(current_date, -1)), varchar(4))), integer)
validation this expression show no error but i an not getting the correct result
i did some work around & found there is some problem in half part of the expression
cast (extract (month, _add_months (current_date, -1)), varchar(4))
in the above part expression it should find "01" but no result if i try to replace above half part expression with "01" it shows the correct result as expected
any help will be appreciated
This topic has been locked.
4 replies Latest Post - 2013-02-14T11:51:07Z by SystemAdmin
Pinned topic Error in the expression to find date in range
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2013-02-14T11:51:07Z at 2013-02-14T11:51:07Z by SystemAdmin
DanielWagemann 2700058MAJ411 PostsACCEPTED ANSWER
Re: Error in the expression to find date in range2013-02-13T14:34:33Z in response to SystemAdminYour month is only two digits but you are reserving four digits worth of space. Maybe you are getting two free blanks.... so your value is really '01 ' or ' 01'.
try chaning the varchar(4) to varchar(2) for the month piece.
Re: Error in the expression to find date in range2013-02-14T04:58:04Z in response to DanielWagemannHi Daniel,
I tried using varchar(2) as well but no help.
Just to add one point i tried to run the query as "view tabular data" also but no help no value is getting displayed.
Any help is appreciable
DanielWagemann 2700058MAJ411 Posts
Re: Error in the expression to find date in range2013-02-14T11:51:07Z in response to DanielWagemannHi Daniel,
My issue get resolved i replaced the existing expression by
MonthKEY >= 201101 and MonthKEY <= extract(YEAR, _add_months (current_date, -1))*100+ extract (MONTH, _add_months (current_date, -1))
and it gives the correct result as expected.:-)
Thanks for your help