You can call a variety of VBA and Excel functions
from DMX, giving you unprecedented flexibility in the types of queries you can
call against your data mining models.
For example, you can change the formatting of a prediction
output like this:
SELECT lCase(Predict(MyModel.[Home
Ownership]) FROM MyModel PREDICTION JOIN ….
Would return the predicted
homeownership in lowercase.
If a function exists in both Excel and VBA you need to
prefix the function name with the name of the function. For example
SELECT Excel!Log(Predict(Sales)),
VBA!Log(Predict(Sales)) From MyModel ….
Returns the base 10 log (Excel) and
the natural log (VBA) of the predicted sales.
If a Excel or VBA function also exists in MDX or DMX or
contains a $ character, you need to escape the function name with square
brackets ([]). For example
SELECT [Format](Predict(Sales), ‘$d.dd’)
FROM MyModel ….
Returns the sales amount using the
currency symbol and two decimal places – e.g. $20.56.
The supported functions from VBA are:
|
aBS
|
lOG
|
|
array
|
lTrim
|
|
aSC
|
lTrim$
|
|
aSCB
|
mID
|
|
aSCW
|
mID$
|
|
aTN
|
mIDB
|
|
cBOOL
|
mIDB$
|
|
cBYTE
|
mINUTE
|
|
cCUR
|
mIRR
|
|
cDATE
|
mONTH
|
|
cDBL
|
nOW
|
|
choose
|
nPER
|
|
cHR
|
nPV
|
|
cHR$
|
oct
|
|
cHRB
|
oct$
|
|
cHRB$
|
partition
|
|
cHRW
|
pMT
|
|
cHRW$
|
pPMT
|
|
cINT
|
pV
|
|
cLNG
|
qBColor
|
|
cOS
|
rATE
|
|
cSNG
|
rGB
|
|
cSTR
|
rIGHT
|
|
cVAR
|
rIGHT$
|
|
cvDate
|
rIGHTB
|
|
cVErr
|
rIGHTB$
|
|
date
|
rnd
|
|
dATE$
|
round
|
|
dATEADD
|
rTrim
|
|
dateDiff
|
rTrim$
|
|
datePart
|
sECOND
|
|
dATESERIAL
|
sgn
|
|
dATEVALUE
|
sIN
|
|
day
|
sLN
|
|
dDB
|
space
|
|
error
|
space$
|
|
error$
|
sqr
|
|
eXP
|
str
|
|
fix
|
str$
|
|
format
|
strComp
|
|
format$
|
strConv
|
|
fv
|
string
|
|
hex
|
string$
|
|
hex$
|
switch
|
|
hOUR
|
sYD
|
|
iIF
|
tAN
|
|
iMEStatus
|
tIME
|
|
inStr
|
tIME$
|
|
iNT
|
timer
|
|
iPMT
|
timeSerial
|
|
iRR
|
tIMEVALUE
|
|
isDate
|
tRIM
|
|
isEmpty
|
tRIM$
|
|
iSERROR
|
typeName
|
|
isNull
|
uCase
|
|
isNumeric
|
uCase$
|
|
isObject
|
val
|
|
lCase
|
varType
|
|
lCase$
|
wEEKDAY
|
|
lEFT
|
yEAR
|
|
lEFT$
|
|
|
lEFTB
|
|
|
lEFTB$
|
|
|
lEN
|
|
|
lENB
|
|
Supported Excel Functions
|
Acos
|
Acosh
|
And
|
*Application
|
|
Asc
|
Asin
|
Asinh
|
Atan2
|
|
Atanh
|
AveDev
|
Average
|
BetaDist
|
|
BetaInv
|
BinomDist
|
Ceiling
|
ChiDist
|
|
ChiInv
|
ChiTest
|
Choose
|
Clean
|
|
Combin
|
Confidence
|
Correl
|
Cosh
|
|
Count
|
CountA
|
*CountBlank
|
*CountIf
|
|
Covar
|
*Creator
|
CritBinom
|
*DAverage
|
|
Days360
|
Db
|
Dbcs
|
*DCount
|
|
*DCountA
|
Ddb
|
Degrees
|
DevSq
|
|
*DGet
|
*DMax
|
*DMin
|
Dollar
|
|
*DProduct
|
*DStDev
|
*DStDevP
|
*DSum
|
|
*DVar
|
*DVarP
|
Even
|
ExponDist
|
|
Fact
|
FDist
|
Find
|
FindB
|
|
FInv
|
Fisher
|
FisherInv
|
Fixed
|
|
Floor
|
Forecast
|
*Frequency
|
FTest
|
|
Fv
|
GammaDist
|
GammaInv
|
GammaLn
|
|
GeoMean
|
*Growth
|
HarMean
|
*HLookup
|
|
HypGeomDist
|
*Index
|
Intercept
|
Ipmt
|
|
Irr
|
IsErr
|
IsError
|
IsLogical
|
|
IsNA
|
IsNonText
|
IsNumber
|
Ispmt
|
|
IsText
|
Kurt
|
Large
|
*LinEst
|
|
Ln
|
Log
|
Log10
|
*LogEst
|
|
LogInv
|
LogNormDist
|
*Lookup
|
Match
|
|
Max
|
*MDeterm
|
Median
|
Min
|
|
*MInverse
|
MIrr
|
*MMult
|
Mode
|
|
NegBinomDist
|
NormDist
|
NormInv
|
NormSDist
|
|
NormSInv
|
NPer
|
Npv
|
Odd
|
|
Or
|
*Parent
|
Pearson
|
Percentile
|
|
PercentRank
|
Permut
|
Pi
|
Pmt
|
|
Poisson
|
Power
|
Ppmt
|
Prob
|
|
Product
|
Proper
|
Pv
|
Quartile
|
|
Radians
|
*Rank
|
Rate
|
Replace
|
|
ReplaceB
|
Rept
|
Roman
|
Round
|
|
RoundDown
|
RoundUp
|
RSq
|
Search
|
|
SearchB
|
Sinh
|
Skew
|
Sln
|
|
Slope
|
Small
|
Standardize
|
StDev
|
|
StDevP
|
StEyx
|
Substitute
|
*Subtotal
|
|
Sum
|
*SumIf
|
SumProduct
|
SumSq
|
|
SumX2MY2
|
SumX2PY2
|
SumXMY2
|
Syd
|
|
Tanh
|
TDist
|
Text
|
TInv
|
|
Transpose
|
*Trend
|
Trim
|
TrimMean
|
|
TTest
|
USDollar
|
Var
|
VarP
|
|
Vdb
|
*VLookup
|
Weekday
|
Weibull
|
|
ZTest
|
|
|
|