The Compleat Idiot's Guide to PBCS, No. 8 -- Supported Smart Forms Excel Functions

What’s in, what’s out, and what’s not official (that would be all of it)

I’ve done this before, and I’m doing this again:  this blog is (like many if not all of my posts) information that is not in any way supported by Oracle.  Do not go to Oracle whining about, “Cameron said this worked, but it doesn’t, so I hate you Oracle, blah, blah, blah, blah, blah,” as I am telling you that what you read below is unsupported, unofficial, incomplete, tied to the PBCS release of today, 22 March 2016, unknown to anyone at @oracle.com, etc. In other words, enjoy and maybe use the below and don’t have a conniption if it all goes sideways on you.

You Have Been Warned.

The warning is over, here’s the cool stuff

Could whining actually be an effective approach?  Maybe.

In my last PBCS post on Smart Forms in Smart View, I whined the following:

“Where exactly does this ability to convert Excel formulae into a Smart Form begin and end?  There a lots and lots and lots of formula functions in Excel – that’s one of its many strengths.  Unfortunately, not all formula functions are supported and if there’s a list anywhere of what works and what doesn’t I was unable to find it.”

And then went on to whine:

“Bugger.  So that’s at least one formula that isn’t supported via Smart Form.  It sure would be nice to have that list of supported/unsupported.”

Yes, I am a whiner, to the detriment and annoyance of all who know me.  And yes, whining seems to be a theme in this post.  Perhaps I have done it recently and feel guilty about it?  The Psychology of Cameron is a frightening thing.

Now to the cool stuff

How about that currently supported/but use at your own risk list of functions?  Ask and ye shall receive.

Very, very, very nice and an awful lot of them to boot.

Function	Category
ACCRINT	Financial
ACCRINTM	Financial
AMORDEGRC	Financial
AMORLINC	Financial
COUPDAYBS	Financial
COUPDAYS	Financial
COUPDAYSNC	Financial
COUPNCD	Financial
COUPNUM	Financial
COUPPCD	Financial
CUMIPMT	Financial
CUMPRINC	Financial
DB	Financial
DDB	Financial
DISC	Financial
DOLLARDE	Financial
DOLLARFR	Financial
DURATION	Financial
EFFECT	Financial
FV	Financial
FVSCHEDULE	Financial
INTRATE	Financial
IPMT	Financial
IRR	Financial
ISPMT	Financial
MDURATION	Financial
MIRR	Financial
NOMINAL	Financial
NPER	Financial
NPV	Financial
PMT	Financial
PPMT	Financial
PRICE	Financial
PRICEDISC	Financial
PRICEMAT	Financial
PV	Financial
RATE	Financial
RECEIVED	Financial
SLN	Financial
SYD	Financial
TBILLEQ	Financial
TBILLPRICE	Financial
TBILLYIELD	Financial
XIRR	Financial
XNPV	Financial
YIELD	Financial
YIELDDISC	Financial
YIELDMAT	Financial
ISERR	Information
ISERROR	Information
AND	Logical
IF	Logical
NOT	Logical
OR	Logical
ABS	Math and trigonometry
MOD	Math and trigonometry
PI	Math and trigonometry
PRODUCT	Math and trigonometry
ROUND	Math and trigonometry
SUM	Math and trigonometry
TRUNC	Math and trigonometry
AVERAGE	Statistical
AVERAGEA	Statistical
COUNT	Statistical
COUNTA	Statistical
MAX	Statistical
MIN	Statistical
DATE	Date and time
DAY	Date and time
DAYS360	Date and time
EDATE	Date and time
EOMONTH	Date and time
HOUR	Date and time
MINUTE	Date and time
MONTH	Date and time
NETWORKDAYS	Date and time
NOW	Date and time
SECOND	Date and time
TIME	Date and time
TODAY	Date and time
WEEKDAY	Date and time
WEEKNUM	Date and time
WORKDAY	Date and time
YEAR	Date and time
YEARFRAC	Date and time
FALSE	Logical
TRUE	Logical
ACOS	Math and trigonometry
ACOSH	Math and trigonometry
ASIN	Math and trigonometry
ASINH	Math and trigonometry
ATAN	Math and trigonometry
ATAN2	Math and trigonometry
ATANH	Math and trigonometry
CEILING	Math and trigonometry
COMBIN	Math and trigonometry
COS	Math and trigonometry
COSH	Math and trigonometry
DEGREES	Math and trigonometry
EVEN	Math and trigonometry
EXP	Math and trigonometry
FACT	Math and trigonometry
FACTDOUBLE	Math and trigonometry
FLOOR	Math and trigonometry
GCD	Math and trigonometry
INT	Math and trigonometry
LCM	Math and trigonometry
LN	Math and trigonometry
LOG	Math and trigonometry
LOG10	Math and trigonometry
MROUND	Math and trigonometry
MULTINOMIAL	Math and trigonometry
ODD	Math and trigonometry
POWER	Math and trigonometry
QUOTIENT	Math and trigonometry
RADIANS	Math and trigonometry
RAND	Math and trigonometry
RANDBETWEEN	Math and trigonometry
ROUNDDOWN	Math and trigonometry
ROUNDUP	Math and trigonometry
SIGN	Math and trigonometry
SIN	Math and trigonometry
SINH	Math and trigonometry
SQRT	Math and trigonometry
SQRTPI	Math and trigonometry
SUMSQ	Math and trigonometry
TAN	Math and trigonometry
TANH	Math and trigonometry

Awesome is the only word that describes this function list.  Note that it’s 128 functions long.  Think of the Excel formulae you could write, think of the rich functionality in Excel, think of how many of these functions are not supported in BSO and then think about how many are not supported in MDX.  

Conclusion and a whine

And that Gentle Reader, is why Smart Forms are 100 (128?) times better than traditional form formulae.  Think about what you could write as one offs in a form.  No more creating a member in the Account dimension that only gets used once and is deadwood otherwise.   Instead, create the base members you need to perform the calculations and use the quite considerable power of Excel to do the heavy lifting.  Remember that Smart Forms are easily made into ad hoc forms thus keeping the calculated member in play.  Also remember that these functions are available in the Simplified Interface.  Happy times indeed.

One last whine:  when oh when oh when will we see this in on-premises?  I love, love, love the idea of Oracle writing this stuff as it is top drawer functionality but thus far it is for PBCS only.  Please Oracle, for the balance of customers who are not on The Cloud, bring this to on-premises so all of your customers can benefit from this functionality.  

Be seeing you.