Skip to Content
avatar image
Former Member

DB Agnostic Dates

I'm trying to create a Universe that can use connections to (just about) any database. Specifically DB2, SQL Server and Oracle for the time being.

Everything works fine with the exception of dates to be used in report prompts. DB2 dates used in prompts fail for lack of the time elements in the calendar widget.

To get them all to midnight (or non-timestamped) date:

A DB2 timestamp field has to be defined as Date([field])

An Oracle datetime needs to be trunc([field])

SQL Server need to be DateAdd(dd,DateDiff(dd,0,[field]),0)

But, of course, I can't have all 3 of those definitions in a single object, I don't think...

Do I just have to reference the field and format it in the Universe to the longest format? Or do I need to hack some config file? Can I add functions to the .prm files for each database flavor with the same names so that applying a function is applying the logic I need for each?

Searches don't turn up this specific issue.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • avatar image
    Former Member
    Nov 16, 2010 at 09:06 AM

    Jody,

    The only thing that I can think of is to define database functions in each database that are called the same so that you actually call the function instead.

    Not sure on DB2, but calling a function in Oracle and SQL Server is the same - you'll have different syntax at the database level but the same function name at the universe layer.

    A bit long-winded but database agnostic nonetheless.

    Regards,

    Mark

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 18, 2010 at 09:07 AM

    hi Jody,

    I guess you can edit the prm files (oracle.prm,db2.prm etc ). Add the a same function name so that if you use that function in designer object it will work fine.

    Hope this helps ...

    Regards

    Kunal

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      The big downside to that approach assumes that there will be no impact of doing this on the end users remaining SAP BO estate. If you restrict changes to the database(s) and universe(s) that you are providing, it will provide a far cleaner install of your product and not interfere with the client's existing installation.

  • avatar image
    Former Member
    Nov 19, 2010 at 05:27 PM

    Thank's for the input... I think database functions are the way to go since it's not just an issue of display.

    Add comment
    10|10000 characters needed characters exceeded