Business Objects Problem - Weird Date Issue

building semantic layer
1

Problem: Report will not pull back data if your query spans a year change (like dec 31, 2008 - jan 2, 2009 doesn’t work, but jan1, 2009 - jan 2, 2009 AND dec 30, 2008 - dec 31, 2008 DO work).

Details:
I have a report that pulls back laboratory data based on approval data (between two dates). It has been working fine for over a month, but this morning I made a minor change (added one field). I ran the report and accidentally picked a future date for the end date (jan 1, 2015). I canceled the query and picked today…no data. As I have troubleshot this, I have found that it only craps out if my query crosses a year change (like 2008-2009). I thought it might have been pulling too much data, but even searching the last day of 2008 through the first day in 2009 doesn’t work (and yes, there are records).

Things I’ve tried:
[list]-Multiple computers
-clearing browser/java cache
-recreating the query/report has the same error
-some date fields have the error, some don’t, but from a data perspective they look the same.
-recreating the universe object from one of the other fields that work
-pulling the query out of the report to run in SQL developer - it works fine[/list]

I don’t know what else to try. This is really frustrating, because it works as long as my start and end date are in the same year…one day out side breaks it. And, it obviously doesn’t really even try to run the query…it INSTANTLY bounces back with no data message.

Anyone have any ideas?[/list]

rickai (BOB member since 2009-11-09)

2

Just a couple of questions:

How are your date fields stored?

What is the precise condition you are applying to limit the dates?

is the year being taken account of by the condition (you could try something like dates between 01 June 2008 and 31 Jun 2009)

clg :uk: (BOB member since 2009-09-23)

3

Thanks for the input…it helped me solve the problem. It got me looking at how those dates are formatted. Oddly enough, the database and object were both DATES, but there was some SQL creating a derived table in BO that the object was based on. That SQL used some UNION statements to combine several SQL statements. Anyway…it was changing some of the date formats. I used some custom SQL in the Webi interface to add a to_date function to the prompt…works fine now. Thanks!

rickai (BOB member since 2009-11-09)

4

Welcome to B:bob:B!

Used Custom SQL? Then also have its disadvantages in your consideration.

Jansi :india: (BOB member since 2008-05-12)