system
May 13, 2013, 3:37pm
#1
Looking for a formula to make this filter :
Want only the previous day data to pull in the report when this filter is applied.
Date format for the object which I’m using for this custom filter is dd/mm/yyyy
Thanks,
Nisha Singh (BOB member since 2013-03-08)
system
May 13, 2013, 3:51pm
#2
Welcome to B:bob:B !
Do you want to create it as a pre-defined condition in a universe? Then what database do you use?
Marek Chladny (BOB member since 2003-11-27)
system
May 13, 2013, 3:58pm
#3
Thanks, happy joining this forum finally. Always found it very usefull.
Yes, want to get it created in the universe so that other reports could also use it. Ours is SQL Server 2008
Need formula so that I can provide it to our resource who creates objects in the universe. He is struggling with the formula.
Was trying this : ((@Select (Installment Facts\Record Written DT Display))= (GETDATE() -1))
Did not work
Nisha Singh (BOB member since 2013-03-08)
system
May 13, 2013, 4:54pm
#4
[Moderator Note: So moving from WebIntelligence XI to Semantic Layer / Universe Designer]
Then this sticky topic can be helpful:
As per this topic on Oracle relative dates, here’s the same set of dates in SQL Server.
Please note that some may not work on SQL Server 2000, but all definitely work in 2005/8.
Yesterday
cast(convert(char(10),dateadd(d,-1,getdate()),23) as datetime)
Today
cast(convert(char(10),getdate(),23) as datetime)
The following also works for today:
dateadd(dd, datediff(dd,0,getdate()), 0)
Start of Current Month
cast(convert(char(7),getdate(),23)+'-01' as datetime)
End of Current Month
dateadd(d,-1…
Why was it not working? Did you get wrong results? Did get an error message?
Marek Chladny (BOB member since 2003-11-27)
system
May 13, 2013, 8:55pm
#5
I experimented with {fn CURRDATE()} as in the formula: DATEADD(dd,-1,convert (SMALLDATETIME, {fn CURDATE()})). The issue here is that the formula works in Webi, but does not parse in Designer, making it difficult to debug.
Nisha Singh (BOB member since 2013-03-08)
MarkP
May 14, 2013, 12:48pm
#6
I experimented with {fn CURRDATE()} as in the formula: DATEADD(dd,-1,convert (SMALLDATETIME, {fn CURDATE()})). The issue here is that the formula works in Webi, but does not parse in Designer, making it difficult to debug.
It won’t parse in designer.
getdate() doesn’t use a table and as such won’t parse but it will work when you use an object from a table with it in a query
Create an object for Yesterday as:
cast(convert(char(10),dateadd(d,-1,getdate()),23) as datetime)
Or create a pre-defined condition as:
@Select(Installment Facts\Record Written DT Display)=cast(convert(char(10),dateadd(d,-1,getdate()),23) as datetime)
The condition should parse because I would expect the other object to be table-based.