I am using BO 5.1 Reporter with an Oracle db. All of the dates have been defined as mm/dd/yyyy hh:mm:ss. I have a report I where I need to total values for a process that kicks off 6 times a day, and I need to give the DAILY totals. My problem is I can’t get the hh:mm:ss off of the date so that I can provide a total for the entire date – not a total for each time the process is ran. I also seem to have limited Date functions available to me compared to the ones I see available in reading my Robert D. Schmidt Creating Documents with BO 5.1, is this a limitation due to the Oracle database?
If you want to create the date object in the universe, use the following:
TRUNC(Table.DateField)
This will truncate the date/time to 12 AM. Then all you need to do is format the object to display just the date, and it should work just as you expect.
The BO Reporter functions should be totally independent of the underlying DB of the universe.
UDO (User Defined Objects) are a different matter though.
To get the date I need, I need to do a substring to get the date value out,
I then do a to_date to get it in dataformat. However I still have the hh:mm shown, how do I get red of this in this case ?
My current code is
to_date(substr(USER_COUNT_DAILY.LOGIN_TIME,1,10))
Ive tried formating the object at the user level etc still no good
HH:MM:SS are always part of a date, they just don’t have to display. You can use: FormatDate to just display the portion that you desire. If this is regarding a List Of Values, you’ll need to search the Designer Forum for this. There are a few helpful posts out there on how this is done.
Oracle also offers Timestamp fields, but BO does not correctly handle them at this time. But… what I said still holds true… BO stores this information internally as the number of seconds from Dec.15, 1970 (at midnight)… so, that being said, it always calculates dates out using these “number of seconds” which always returns a time, whether or not it’s displayed.
If you look at the “raw data” for any date field, you should still see the timestamp associated, even if it’s always midnight.
I ran a data provider pulling three result objects of data type Date, Time, and Timestamp from a universe based on IBM DB2.
When I look in the microcube (BO Reporter, Data| View Data…) the Date field does NOT carry any time portion (hh:mm:ss).
But when I reformat my date column in BO Reporter to the format mm/dd/yyyy HH:mm:ss I do see a time portion set to midnight, although the microcube does not show any timestamp for this field.
Interesting…
It does? What’s the name of the data type? And how does it differ from the data type DATE, which in Oracle always carries a time portion (hh:mm:ss) with it anyway. Does Oracle also offer a TIME data type (just hh:mm:ss without any dates)?
Ive done all that and the report read correctly in Full Client.
However the report is built in full client and saved as HTML and made avaliable via Webi. The report is sectioned by Date (the report shows number of website hits per hour per day). When viewed in Webi the dates are listed on the left of the screen and you can select what ever date you want to look at. Its these dates that are displaying the 00:00:00 which are cannot get red of.
Maybe this will help. I ran into the same problem with a SQL database. I created an object called Date Only using this code. I am not sure how much of this exact code can be used in Oracle but I suspect it is similar. This will cut off all references to time. I didn’t just truncate the timestamp with one line of code beacuse I wanted the date to be a specific format.
**Note, this is no longer a date type object which does take some functionality away.
I want to create a condition though that means a report will only run where trde.date = sysdate -3 . The issue I have is that the trde.date fiell has the 00:00:00 as the end, when I use my sysdate obj it puts in the actual time. I’m only concerned with dates matching, how do I address this as when I play around with the formating I can still the hh:mm:ss in the dataprovider even though its not displayed as such in the report
(BOB member since 2002-08-08)
(BOB member since 2002-06-20)
(BOB member since 2002-10-04)
(BOB member since 2002-08-15)
(BOB member since 2002-10-04)
(BOB member since 2002-09-04)
(BOB member since 2002-06-20)