Hello All,
Recently while working with dates.I have encountered with a situation.I have created a table in the database which returns sysdate.That’s the only field I have in that table.I created an universe based on that table.When i checked the data (Properties–> Displayed) .I found two dates instead of one.And the second value is not appropiate a date. I have no clue why this behavior is.
I am using BOXIR3.1 SP3.
Database: Oracle 10g.
I have checked in the database and it returns one value.
I have attached a few screen shots to understand the situation better.
Hi Mark,
Thank you for your response and my apologies for the delay in response.
I have checked the Relative date post in the forum. And I have already tried those SQL in universe designer by creating objects like Yesterday, Last month etc. And also tried to pull them into the report. Since, sysdate do not refer to any table. It throws error some time (especially if the universe has buit for ad-hoc reporting).
Our actual plan is to create a function and call all these dates functionality from it. But to avoid the dependency on DB team I thought of trying something else.
But I think it will not work in that way in BO. Still, I am not able to find why BO cannot handle this? Why it is not understanding the sysdate value returing by a database table like any other table ?
"Why would you return just “Yesterday” in a query? "
Well Mark I am not.But some time unknowingly user do that.And they don’t like getting any error.I know their dislike is not genuine. .
I will ask my question to you in a different manner.
I have a table in the database(let say:unknowingly some one creates it) which returns “sysdate” only.I have been told to create 5 different variables name as:( Current_Business Day,Previous_Business Day,Rolling 12 Weeks from current date,Previous_Quarter,Previous_Month) by using [b] sysdate[\b] column in BO.
In BO we do not have direct functionality to get these date.I will be going with a standard approach like sysdate,(sysdate-1),…etc as universe object.
Do you know any other approach to get these date variable in BO/Universe?
The Calender Table must be common to all the domains. In Dataware Housing terms you can call it as “Confirmed Dimension”.
Say for example this table will have the common dates for all depts and you can use this table acroos all the Universes, wherever you need to refer the Common DATE’s.
There are many ways to do this One other way is to use a Julian date as a reference point and comparing this to today. You can then calculate the number of days, weeks, months e.t.c. I outlined this approach here:-