Would it be possible to run a script on a Businessquery excel file to refresh daily on its own, and, at the same time, an object in Designer point to a single column in that BusinessQuery file to obtain its list of values?
If this is possible, then if the spreadsheet were to pull a date value from the database and format it say for example ‘mm/dd/yyyy’, would that format be carried over into the list of values?
Basically I am trying to find a workaround for the time showing up in a list of values for dates. When i use to Oracle function to_char() they aren’t sorted the right way because we want the dates to be sorted in descending order chronologically.
I will eternally worship anyone who can answer this one. :))
Mohammad Syed
Lord Abbett & Co
________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
I am not an Oracle person, but I would think that you should be able to enclose your to_char function in a to_date function ex. to_date(to_char(field,‘mm/dd/yyyy’)) if the to_date function does not support date/time to date conversion by itself. Let me know if this works.
Mohammed,
Instead of using the Oracle to_char function to remove the time, use the trunc() function instead. This keeps the object as a date but removes the time from it.
Two part question:
Would it be possible to run a script on a Businessquery excel file to refresh daily on its own, and, at the same time, an object in Designer point
to a single column in that BusinessQuery file to obtain its list of values?
If this is possible, then if the spreadsheet were to pull a date value
from the database and format it say for example ‘mm/dd/yyyy’, would that format be carried over into the list of values?
Basically I am trying to find a workaround for the time showing up in a list
of values for dates. When i use to Oracle function to_char() they aren’t sorted the right way because we want the dates to be sorted in descending order chronologically.
mohammad,
to get rid of the time part simpy use the TRUNC(date_field) function in Oracle,
ie. define your universe object with the Trunc function. You can then use it in a subquery to pull exactly those values you are interested instead of using an BQY query.
hope this helps
Walter
mohammad syed wrote:
Two part question:
Would it be possible to run a script on a Businessquery excel file to refresh daily on its own, and, at the same time, an object in Designer point to a single column in that BusinessQuery file to obtain its list of values?
If this is possible, then if the spreadsheet were to pull a date value from the database and format it say for example ‘mm/dd/yyyy’, would that format be carried over into the list of values?
Basically I am trying to find a workaround for the time showing up in a list of values for dates. When i use to Oracle function to_char() they aren’t sorted the right way because we want the dates to be sorted in descending order chronologically.
I will eternally worship anyone who can answer this one. :))
–
DI Walter Muellner
Leiter Bereich Consulting
Delphi Software GmbH, Vivenotgasse 48, A-1120 Vienna / Austria Tel: +43-1-8151456-12, Fax: +43-1-8151456-450 e-mail: w.muellner@delphi.at, WEB: http://www.delphi.at
Yes but since the date objects in businessobjects translate an oracle date into the date format in the windows regional settings, the time still shows up. Trunc() automatically puts the time for each date to 12:00AM ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
I am not an Oracle person, but I would think that you should be able to enclose your to_char function in a to_date function ex. to_date(to_char(field,‘mm/dd/yyyy’)) if the to_date function does not support date/time to date conversion by itself. Let me know if this works.
Joe Lauer
__________________________________________________ Do You Yahoo!?
Yahoo! Photos – now, 100 FREE prints! http://photos.yahoo.com
Mohammed,
Instead of using the Oracle to_char function to remove the time, use the trunc() function instead. This keeps the object as a date but removes the time from it.
Two part question:
Would it be possible to run a script on a Businessquery excel file to refresh daily on its own, and, at the same time, an object in Designer point
to a single column in that BusinessQuery file to obtain its list of values?
If this is possible, then if the spreadsheet were to pull a date value
from the database and format it say for example ‘mm/dd/yyyy’, would that format be carried over into the list of values?
Basically I am trying to find a workaround for the time showing up in a list
of values for dates. When i use to Oracle function to_char() they aren’t sorted the right way because we want the dates to be sorted in descending order chronologically.
__________________________________________________ Do You Yahoo!?
Yahoo! Photos – now, 100 FREE prints! http://photos.yahoo.com
Just a note In ORACLE, whenever you wrap a to_date() around a to_char(), you are simply reversing the effect of the to_char formatting. The problem is not a database issue, it is an OS issue. whatever you set as the date and time format in the WINDOWS REGIONAL SETTINGS will be the format of the list of values. Whats really sucks is that you MUST specify a time format in the WINDOWS REGIONAL SETTINGS. I don’t know if there is a parameter out there to change where the date format of the list of values is read from rather than OS. I am currently checking.
________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com