BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: Upcoming Events: BOBJ conference in Paris: Mar 28, PGHBOUG: Apr 4.

Incorrect Date-Based Calculations in BOXI 3.1 SP5


 
Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
Xensor
Senior Member
Senior Member



Joined: 16 Jan 2013

Posts: 57


flag
PostPosted: Thu Jan 17, 2013 9:03 am 
Post subject: Incorrect Date-Based Calculations in BOXI 3.1 SP5

Hi,

I have an unusual problem in our universe that will take a little explaining, however before that i have a quick question: Has service pack 5 in any way effected how Business Objects universes deal with dates and date-based calculations in Oracle databases?

Anyway onto my long question. As stated above we are using BOXI 3.1 SP5 (no fixpacks) attached to our Oracle backend. We recently upgraded from SP3 direct to SP5 to fix issue we were having with being unable to edit complex reports in Infoview. Whilst it fixed that issue it now appears that a number of date based calculations, which are designed to work out the number of working days between 2 dates, no longer function correctly but return erratic results, sometimes being correct, sometimes not. These calcs worked perfectly before the upgrade and have not been changed in any way since the upgrade. I'll go into some more detail below...

We have a derived table which provides the 2 dates joined to our primary calendar table, which list every date in the calendar year plus bank holidays, working days etc. The join expression is:

Code:

Aliasedcalendar.calendar_day Between
Derivedtable.startdate And Derivedtable.enddate And
Aliasedcalendar.working_day='Y'


Of the back of that we have an object which calculates the days between the 2 dates:

Code:

count(trunc(Aliasedcalendar.calendar_day))-1


I cannot for the life of me work out why it's now not working. I tried the following piece of code i found on the net as a substitute to see if it's an issue with our coding or possibly something more fundamental:

Code:
CAST(sum(CAST(Date1_DTM AS DATE)- cast(Date2_DTM AS DATE)) AS INTEGER)


Unfortunately this also returned erratic results which suggests a more fundamental issue but there's nothing in the patch notes and no other BO users, as far as i've seen on the net have had this kind of issue..

No doubt one question that will be asked is why we didn't test the patch before rolling out. The simple answer is we don't have a test environment, or BO implementation is quite small and rather... patchwork in nature (basically it runs on a virtual machine which is dual-managed by our IT provider and our BO and Oracle universe supplier, who don't particularly like to talk to each other). Anyway i digress...

If anyone has any ideas about next steps (i haven't gone to SAP yet but i will if needed) i'd be most appreciative! Apologies for the long length and i hope it's in the right forum!

Regards,

Chizo Ejindu
Back to top
Marek Chladny
Forum Advocate
Forum Advocate



Joined: 27 Nov 2003

Posts: 19252
Location: Bratislava


flag
PostPosted: Thu Jan 17, 2013 9:37 am 
Post subject: Re: Incorrect Date-Based Calculations in BOXI 3.1 SP5

Welcome to Bicon_mrgreen.gifB!

Can you please mention what the data in the database looks like, what an SQL generated by WebI gives and what is the expected (and correct( result? It's not clear from your post where the problem lies.

Can you also take an SQL statement(s) that WebI generates and run it in a query tool (Toad, SQL Navigator, etc) to see whether the results are OK or not?

_________________
BO: BI 4.0, 4.1 | XI 3, 3.1 | XI r2 | 6.x | 5.x
DB: Oracle, MS SQL Server, DB2, Teradata, Netezza
HW: Win, Linux and AIX servers
www.perpendulum.com

Latest Blog Posts
• 2013-07-16 Number of reports in a WebI document
• 2013-04-01 Optional prompts in a universe
• 2012-06-08 Calendar table script for Oracle
Back to top
Xensor
Senior Member
Senior Member



Joined: 16 Jan 2013

Posts: 57


flag
PostPosted: Thu Jan 17, 2013 10:56 am 
Post subject: Re: Incorrect Date-Based Calculations in BOXI 3.1 SP5

Hi Marek,

Thanks for the quick reply, i'll provide the updates you asked for tomorrow as I need to fight some fires for the rest of the day!

Regards,

Chizo Ejindu
Back to top
Xensor
Senior Member
Senior Member



Joined: 16 Jan 2013

Posts: 57


flag
PostPosted: Fri Jan 18, 2013 6:47 am 
Post subject: Re: Incorrect Date-Based Calculations in BOXI 3.1 SP5

Hi Marek,

It's amazing how you can spend days banging you head against a seemingly impossible issue to find the solution is simple! The issue was not being caused by the calculation failing in some unusual way, it was caused by another object in the report which had a very minor change in the universe but the knock on effect was a calculation context issue with this particular calculation but no other calculations in the reports. The fact that this object was updated around the same time we rolled out SP5 obfuscated the issue - obviously going from SP3 to SP5 is a significant update whereas a small change to one object shouldn't be a major issue right? icon_smile.gif

Anyway thanks for your pointers as that got me on the right track to actually find the problem.

Regards,

Chizo Ejindu
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0311 seconds using 18 queries. (SQL 0.0031 Parse 0.0009 Other 0.0271)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo