Want to hire me? For availability and scheduling please email info@arcepm.com

22 March 2016

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.

6 comments:

Philip Hulsebosch said...

Hello Cameron, how did you find out this list of functions? Did you test these one by one?

I agree that these are very easy to implement, especially compared with MDX, but the control of correct calculating in all possible situations is a bit gone. It is getting harder to say what is in the cube is correct, since the user can not see/should not care where the calculation is taking place.

Regards, Philip.

Jeff said...

Can't wait to see this in person.

Semi-related: Are attribute dimensions supported in this latest release of PBCS, and can you get to them from Smart View?

Peter Nitschke said...

Hey,

Phil - interestingly these functions can be identified in the calc manager HSP Functions that are identified with Excel (though I may have only told Cameron after this post, so it's very possible he went through the excel functions one at a time....*grin*)

Jeff - attribute dimensions are still not supported in Planning connections in PCBS. Might be soon - haven't had confirmation one way or another.

Cheers
Pete

Todd Binenstock said...

Thanks for all the awesome info. Am I the first to ask if misspelling Complete (eat) is part of the joke?

Cameron Lackpour said...

Todd,

Good to hear from you again.

The Compleat vs. Complete conceit is from the old book on VW repairss. I blame a father with a 1966 Squareback as inspiration:
http://www.amazon.com/Keep-Volkswagen-Alive-Step-Step/dp/1566913101

Geeks never fall from the tree although the 1960s were a much more freewheeling automotive landscape. Who would today buy a manual, aircooled, two door sedan with an engine that has a reputation of spontaneous combustion. He says it was the best car he ever had....

Cameron

Peter Nitschke said...

And here was me thinking the next career path for Cameron involved more....trout.
https://en.m.wikipedia.org/wiki/Izaak_Walton
Pete