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
Take a look at the add days function.
[YourDateColumn] = _add_days(current_date,-1)
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
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.
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.
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
HI KLeonard1and Lynn,
thank you very much for your advice now its working fine.thanks alot.
thanks
bvk
Bvk,could you please elaborate what actually you did in detail.Thanks
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))
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
cast([DateColumn],date) = _add_days(cast(getdate() ,date), -1)
Thanks,HalfBloodPrince.I will give it a try.Merry Christmas to all members.