If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Displaying data between dates

Started by Sreeni P, 05 Feb 2010 11:41:43 AM

Previous topic - Next topic

Sreeni P

Hi,

i have one date prompt, when i'm selecting any perticular date from that prompt(ex:3 march 2009),it should dispaly the values between (1 jan 2009 to 3 march 2009)...here the '1 jan' is a default for any year...

EX:  if i select 5 feb 2010 , then it should display the values between 1 jan 2010  and 5 feb 2010

Sreeni P

need in urgent...does any body have an idea about this?

CognosPaul

I'm assuming this is relational, so you can use this:

[Date]>=#prompt('Date','date','','_make_timestamp(extract(year,','','),1,1)')# and
[Date]<=#prompt('Date','date')#

laks

You can use this in your detail filter

Date_date_item between to_date(to_char(?Date?,'YYYY')||'-01-01','YYYY-MM-DD') and ?Date?

Sreeni P

Quote from: PaulM on 07 Feb 2010 01:25:07 AM
I'm assuming this is relational, so you can use this:

[Date]>=#prompt('Date','date','','_make_timestamp(extract(year,','','),1,1)')# and
[Date]<=#prompt('Date','date')#

Quote from: laks on 07 Feb 2010 01:37:46 AM
You can use this in your detail filter

Date_date_item between to_date(to_char(?Date?,'YYYY')||'-01-01','YYYY-MM-DD') and ?Date?

Thx for the reply , can u elaborate in briefly that how would i get the result using this scenarios?

CognosPaul

Your requirements are to return YTD for the prompted date. The easiest, but not the only, way to accomplish would be to create a timestamp that uses the year of the prompted date and 1 for both month and day.

Lets say the user were to select 2010-02-08. In my example the prompt would then become:

[Date]>=_make_timestamp(extract(year,'2010-02-08'),1,1) and [Date]<='2010-02-08'

Which would then resolve to [Date]>='2010-01-01' and [Date]<='2010-02-08'.

In Laks example, Oracle specific, the filter would be:

[Date_date_item] between to_date(to_char('2010-02-08','YYYY')||'-01-01','YYYY-MM-DD') and '2010-02-08'

Which would then resolve to
[Date_date_item] between '2010-01-01' and '2010-02-08'

In both cases you are using the prompted value to find the first day of that year.

Sreeni P