We need to retrieve the current date (always the previous business day)
and place it as a condition on a report. I have a table of valid
business dates, Date_Info, to be used as a lookup table, but it is not
joined to any other tables.
I defined a universe object as Select: @Prompt(‘Enter
Date’,‘D’,‘Date_Info\Current Date’) and edited the lov to select only
type ‘Current’. The lov works in Designer. But on a report, the lov
shows every date record. The ‘Export with Universe’ and deleting the
associated file name on my PC before running didn’t work.
Have also tried the @script function with no luck. It always returns me
to the Free-Hand SQL dialog or to the Query Panel without running the
report. The BOA docs conflict on whether this works anywhere other than
Free-Hand SQL.
Can anyone offer any tips on what I’m doing wrong, or post an example of
proper use of these functions?
If my understanding is correct, you only require one date. If this is the
case, try writing a view in the database to calculate the ‘current’ date,
and place this in the universe as a table. You can then be sure that you
will only get the date you need.
Darren Turland Senior A/P
Argos Distributors Ltd. UK
We need to retrieve the current date (always the previous business day)
and place it as a condition on a report. I have a table of valid
business dates, Date_Info, to be used as a lookup table, but it is not
joined to any other tables.
Since your Date_Info table is not joined with other tables, it can’t be used
in object definition. So, for accessing the columns from this table you can
create
a Database function, which will read this table and return current date. In
your object
defintion, you will just refer to this function. (Object will not parse,
because you
havn’t specified a table, but it will work)
Usage of @script:
In designer define an object ‘Current_Date’ with foll SQL: @Script(‘Todays_Date’, ‘D’, ‘Dates.Spt’)
Now when you pull this object in your query and Run it, BO will execute the
script ‘Dates.spt’
and retrieve the value for ‘Todays_Date’ BO variable.
To understand assume, Dates.spt will have foll lines: