BusinessObjects Board

Date and Time Prompt for a Multisource Universe

I have a field in my Oracle database that has a Date with timestamp. The field is defined as a Date in Oracle and here are a few of the actual values:
08-OCT-18 11:00:10
08-OCT-18 11:30:10
08-OCT-18 12:00:11
08-OCT-18 12:30:10
08-OCT-18 13:00:11
08-OCT-18 13:30:10

This object is defined as a DateTime at the universe level.

I have a report based on a single source universe that uses this object in a prompt. I am able to enter >=08-OCT-18 11:30:00 and <=08-OCT-18 13:30:10 and I get the expected records returned. Yay!

However, I also have this object in a Multisource Universe. I have another report that uses this field as a prompt, but when I enter the same date/time range, I get 0 records returned. If I enter >= 08-OCT-18 11:00:00 and <=09-OCT-18 11:00:00 I get back all the records from 08-OCT-18, regardless of timestamp.

Based on this test, it seems the timestamp part of the date does not work as a prompt in a Multisource Universe. Has anyone encountered this problem and been able to find a work around?


office_ninja123 (BOB member since 2013-01-04)

Have a look here: DB-specific Relative Dates SQL Server, Oracle, Netezza, DB2

You probably need to use the generic formats for dates and times for multisource …

Debbie


Debbie :uk: (BOB member since 2005-03-01)

Thanks Debbie,

I am able to take my date object and format it to a Date, a Time, or a Timestamp using the CONVERT function in the Universe. When I have it as a Timestamp, the prompt in the report correctly lets me choose a date and a time. However, when the report runs, it doesn’t seem to be taking the time portion of the date in consideration even though it lets me select a time in the prompt. It seems like I have the object defined correctly at the Universe level, but it’s hard to tell.

So far the only “fix” I’ve found is changing the Universe from multisource to Single which is no small task. :hb:


office_ninja123 (BOB member since 2013-01-04)

Multisource does distinguish between Date and DateTime, but I don’t understand why it would allow you to enter the time portion but then not use it in the filter. Did you look at the generated Oracle query in Data Federation Administration Tool? That might give a hint about what it’s doing.


joepeters :us: (BOB member since 2002-08-29)

Thanks Joe,

I think that tool would be useful for Multisource Uni’s. I’ve never used it, so yesterday I asked my team lead if we have that tool and/or if he knows how to use it/install it/etc… Then we just stared stupidly at each other and decided we didn’t know enough about it :rotf:

I think I will do more research on the Data Federation Admin Tool tomorrow and see if it can shed any light. If not for this project, but the next MS Universe that comes my way. For now I’ve started re-making the Universe to a Single Source instead of Multi


office_ninja123 (BOB member since 2013-01-04)