COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: MaryRose on 19 Aug 2015 06:26:28 AM

Title: Date functions not working in Cognos 10.2.1
Post by: MaryRose on 19 Aug 2015 06:26:28 AM
Dear All,

_add_days function is not working in Cognos 10.2. The expression is validating in query item. But report is giving time out error. We have migrated the report for Cognos 7 to Cognos 10.2. The report is working fine in version 7. Is there any other function instead of _add_days.

In the report ,I need to display last 1 year data from sysdate .

Title: Re: Date functions not working in Cognos 10.2.1
Post by: MFGF on 19 Aug 2015 08:29:52 AM
Quote from: MaryRose on 19 Aug 2015 06:26:28 AM
Dear All,

_add_days function is not working in Cognos 10.2. The expression is validating in query item. But report is giving time out error. We have migrated the report for Cognos 7 to Cognos 10.2. The report is working fine in version 7. Is there any other function instead of _add_days.

In the report ,I need to display last 1 year data from sysdate .

I used _add_days() without issues when I had the 10.2.1 build of Cognos BI. Can you share the expression you are using? What are you using for the arguments? current_date?

Incidentally, Cognos 7 and Cognos 10 are *completely* different products and architectures. Migrating a report from one to the other is possibly not a good idea - the success rate can vary widely depending on a number of factors. Just because a report works fine in Cognos 7 does not mean the same report migrated to Cognos 10 will work.

MF.
Title: Re: Date functions not working in Cognos 10.2.1
Post by: BigChris on 19 Aug 2015 09:09:05 AM
I don't know if it'll make any difference, but can you let us know what platform you're running on (SQL, Oracle etc.)?

You could also use _add_years(current_date,-1) - that will save you from worrying about leap years.

If that doesn't work for you, you're then probably looking at constructing the date one year ago using extract functions...not difficult but not as elegant as using _add_years.
Title: Re: Date functions not working in Cognos 10.2.1
Post by: MaryRose on 19 Aug 2015 11:32:25 PM
Dear All,
Thanks for the replies
The expression I use  in filter is

trunc([Cec].[Cec_Tracking].[Status_Date]) between trunc(_add_days({sysdate};-365)) and trunc(_add_days({sysdate};1))
Title: Re: Date functions not working in Cognos 10.2.1
Post by: BigChris on 20 Aug 2015 01:47:26 AM
Are the semi colons correct? I thought you needed commas
Title: Re: Date functions not working in Cognos 10.2.1
Post by: MFGF on 20 Aug 2015 02:56:34 AM
Quote from: MaryRose on 19 Aug 2015 11:32:25 PM
Dear All,
Thanks for the replies
The expression I use  in filter is

trunc([Cec].[Cec_Tracking].[Status_Date]) between trunc(_add_days({sysdate};-365)) and trunc(_add_days({sysdate};1))

You appear to be mixing Cognos functions and database functions in the same expression - not a good idea.

Try replacing the expression with

[Cec].[Cec_Tracking].[Status_Date] between _add_years(current_date,-1) and _add_days(current_date,1)

MF.