DB Agnostic Dates

[Former Member]

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.

[Former Member]

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

[Former Member]

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

[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.

[Former Member]

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