Date Format in WEBI Report Condition

building reports webintelligence
1

In our universe we have date objects, the data stored in this object at DB level is in ‘MM/dd/ HH:mm:ss’ format.

We are facing a issue with date object in webi, when used as a condition/filter with prompt and after selecting date from calendar. BO is applying date formatting.

Please find details in Scenario 1 and Scenario 2

Universe : Teradata

Object Details
Object Type : Date
Object Qualification : Dimension

Scenario 1

When we create report with date object in filter / condition “Constant” (without prompt) and update date from calendar the syntax is as below

BO Syntax

DATE_Object BETWEEN ‘2011/07/05 00:00:00’ AND ‘2011/07/06 00:00:00’

Teradata Syntax

DATE_Object BETWEEN ‘2011/07/05 00:00:00’ AND ‘2011/07/06 00:00:00’

Scenario 2

When we create report with date object in filter / condition and use prompt the syntax is as below

BO Syntax

DATE_Object BETWEEN {d ‘2011-07-05’} AND {d ‘2011-07-06’}

Teradata Syntax

DATE_Object BETWEEN (‘2011-07-05’(DATE,FORMAT ‘YYYY-MM-DD’)) AND (‘2011-07-06’(DATE,FORMAT ‘YYYY-MM-DD’))

In report condition when prompt is used, at BO SQL level date formatting is enforced and same is carried forward to DB level.

This is affecting query performance and is causing the Database to use very large amount of memory to execute this report.

Report execution time in Scenario 1 is 50 seconds approx and Scenario 2 is 4 minutes approx

I want the date format should be (‘2011-07-05’) AND (‘2011-07-06’) or (‘2011/07/05 00:00:00’ AND ‘2011/07/06 00:00:00’)

So can anyone suggest me how to avoid this date formatting

shashi_07 (BOB member since 2009-02-11)

2

The performance problem is not due to the format but due to the date value passed in SQL.

You need to check your ODBC connection language.

2007/05/07 can be understood as 05 august 2007 (YYYY/DD/MM) or 07 may 2007 (YYYY/MM/DD).

If you copy paste the sql code in your database sodtware it can be wrong if connected in different language than BO is.

If BO is asking for a prompt you should use the calendar and not type your own date as BO know how to translate But you, which format should you use ?

To verify my theory : add a simple table with all dates (or min and max), is it what you are expecting ?

mathieuBO (BOB member since 2011-01-06)

3

I am selecting date from calendar and not manually inputting date.

The issue arises only when I user prompt and select date from calendar.

The date is in condition is in ‘YYYY-MM-DD’

I just want BO should not apply date formatting.

shashi_07 (BOB member since 2009-02-11)