PeopleTools 8.51 nVision – Excel forumulas throw UnrecoverableExceptionFilter

How about an upgrade story?  We are upgrading PeopleTools from 8.48 to 8.51 – as well as upgrading from Windows 2003 32 bit server to Windows 2008 R2 64 bit server – and for a trifecta from Office 2003 to Office 2007.

Okay – so we have nVision reports that worked fine in PTools 8.48 and Excel 2003.  One of my tasks was to upgrade the nVision reports to Office 2007 and then test them in the new PTools environment.  Found this problem – which has been reported to Oracle.

Report would error out with the following as part of the log file entry:

PSPAL::Abort: Unrecoverable exception received
PSPAL::Abort: Location: E:\pt85110b-retail\peopletools\src\pspal\exception_winunhandled.cpp:594: PSPAL::UnhandledExceptionFilter::UnrecoverableExceptionFilter

Couldn’t find anything on the Oracle site, went back and forth several times with Oracle on the service call.  This nVision had several pages and a macro associated with it – the macro was run from a call by NvsInstanceHook.  I took out the call to the macro, and started removing pages – finally got it to run by removing two specific pages in the layout.

I’m not going to go thru all the steps I took to find the bug – and it is a bug – what I found was the 8.51 version of nVision does not properly handle IF formulas in cells.

There was a nested IF statement having 5 truth checks.  There was also both an AND and an OR condition in the statement.  If I whittled the formula down to three checks maximum then the layout would run to success.

Some examples are in order here.  This formula is an edited version of the original – which failed:

=IF(AND(O5<>”Condition One”, O5<>”Condition Two”, O5<>”Condition Three”), IF(OR(O5=”Condition Four”, O5=”Condition Five”), , “”), <Add a bunch of cells>)

So – if I got rid of the OR condition – in essence only going with the AND operator:

=IF(AND(O5<>”Condition One”, O5<>”Condition Two”, O5<>”Condition Three”), “”, <Add a bunch of cells>)

The layout would work fine.  Same if I only used the OR conditional portion of the formula:

=IF(OR(O5=”Condition Four”, O5=”Condition Five”), <Add a bunch of cells>, “”)

Clearly nVison can handle having formulas in cells, plus it can handle the AND and OR conditional operators – it’s the number of truth table checks in the formula nVision is having an issue with.  Which is a shame as per Microsoft you can have up to 64 nested inline IF statements in a formula.

What I finally did was break the formula down – do the AND truth table test in one cell, the OR test in another.  Those cells would be hidden in the report – what was shown was an IF statement where if the value of the AND cell was blank use the value from the OR test.

%d bloggers like this: