How to get Previous day report if i run report today?

[bvk.cognoise]

Hi,

i have a requirement could you please help in this.
i have a report in that report i have DATE column,my requirement is i want to show records only for  Previous day Transactions if i run Today.how can i create Hardcoded filter like this.
for eg:i f i run my report on 10 aug,it should show 9 aug records.it is (relational modelling package)

please give me reply ASAP.your help highly appreciated.

thanks
bvk

[Lynn]

Take a look at the add days function.

Code Select Expand

[YourDateColumn] = _add_days(current_date,-1)

[bvk.cognoise]

Hi Lynn,

thank you for your quick reply  i tried this expression

[MY DateColumn] = _add_days(current_date,-1)

i am getting zero records.in Date column that DATE  foramt is like this "YYYY MM DD"

if i use  this function( _add_days(current_date,-1) is it results to get zero records.because i dont know how this function filters that DATE column means in which format (eg:DD MM YYYY or MM DD YYYY)


please help in this it is very urgent requirement. :(

thanks
bvk

[Lynn]

If your data is really a date, then it is stored as a date. Format is just for display.

Is it a datetime datatype perhaps? You need to compare apples to apples. If the date in the database is 2010 08 05 10:23:00 then comparing for 2010 08 05 isn't going to work.

Figure out how to query your column outside of Cognos (like in Toad or SQL Developer or something) and then you'll know how to write your filter appropriately.

[KLeonard1]

Make sure you cast both data items as a DATE just in case they are not...

cast([MY DateColumn] as date) = _add_days(cast(getdate() as date), -1)


I use this all the time.

[bvk.cognoise]

Hi Lynn and  KLeonard1 ,

your correct my DATE column stored like this  Oct 8, 2007 12:00:00 AM .so now how can i ceate filter for this to view previous day  records if i run the report today.

thanks
bvk

[bvk.cognoise]

HI KLeonard1and Lynn,

thank you very much for your advice now its working fine.thanks alot.

thanks
bvk

[barrysaab]

Bvk,could you please elaborate what actually you did in detail.Thanks

[Brandimore]

Hi All

What if you want to return the previous 2 days. I have a calculation that returns only weekday information. It seems to work.

But I want to return the previous to days. If I put -2 instead of -1 then it returns the previous 2nd day.  What I'm looking for, if today is December 21st.

- Return Dec 20th AND Dec 19th

How can this be done?

My current code:

if (_day_of_week(current_date,7) <=2)
then (_add_days(current_date,-(_day_of_week(current_date,7)+1)))
else (_add_days(current_date,-1))

[barrysaab]

Lynn,Could you please tell me how to filter the data if it is way you suggested  as Ex:Oct 8, 2007 12:00:00 AM  ,definitely _add_days(current_date,-1) wouldn't work,then what should be my filter like.Please help.Thanks

[HalfBloodPrince]

cast([DateColumn],date) = _add_days(cast(getdate() ,date), -1)

[barrysaab]

Thanks,HalfBloodPrince.I will give it a try.Merry Christmas to all members.