Jedox Spreadsheet Functions

This article lists the functions you can use in Jedox Spreadsheets. Note that PALO functions are described in the article Jedox / PALO OLAP Functions.

The functions are grouped as follows:

1. Jedox Spreadsheet Functions (without PALO functions)

2. Special Spreadsheet Functions

3. ODBC Functions

4. Functions that do not exist in Excel or that differ from those in Excel

5. Supported data types in the SQL server

Note: Jedox Spreadsheets do not support R1C1 reference style. Only the A1 style is supported.

1. Jedox Spreadsheet Functions (without PALO functions)

Almost all Jedox Spreadsheet functions are programmed in the same way as their corresponding Excel counterparts. There are some exceptions.

The table below lists all the implemented Jedox spreadsheet functions (without PALO functions):

ABS ACOS ACOSH ACOT
ACOTH ADDRESS AND ARABIC
AREAS ASARRAY ASIN ASINH
ATAN ATAN2 ATANH AVEDEV
AVERAGE AVERAGEA AVERAGEIFS BASE
BESSELI BESSELJ BESSELK BESSELY
BETA.DIST BETA.INV BETADIST BINOM.DIST
BINOM.INV CEILING CELL CHAR
CHISQ.DIST.RT CHISQ.INV.RT CHISQ.TEST CHOOSE
CLEAN CODE COLUMN COLUMNS
COMBIN COMPLEX CONCATENATE CONCATENATE.RANGE
CONFIDENCE.NORM CONFIG.GET CORREL COS
COSH COT COTH COUNT
COUNTA COUNTBLANK COUNTIF COUNTIFS
COVARIANCE.P CSC CSCH CURRENTUSER
DATE DATEVALUE DAVERAGE DAY
DB DCOUNT DCOUNTA DDB
DECIMAL DEGREES DELTA DEVSQ
DGET DMAX DMIN DPRODUCT
DSTDEV DSTDEVP DSUM DVAR
DVARP EDATE ENCODEURL EOMONTH
ERF ERF.PRECISE ERFC ERFC.PRECISE
ERROR.MSG ERROR.TYPE EVEN EXACT
EXP EXPON.DIST F.DIST.RT F.INV.RT
F.TEST FACT FACTDOUBLE FALSE
FILTERXML FIND FISHER FISHERINV
FIXED FLOOR FORECAST FORMULATEXT
FREQUENCY FV GAMMA.DIST GAMMA.INV
GAMMALN GCD GEOMEAN GESTEP
GROWTH HARMEAN HIDECOLUMN HIDEROW
HLOOKUP HOUR HYPERLINK HYPGEOM.DIST
HYPGEOMDIST IF IFERROR IFNA
IMABS IMAGINARY IMARCCOS IMARCCOSH
IMARCCOT IMARCCOTH IMARCCSC IMARCCSCH
IMARCSEC IMARCSECH IMARCSIN IMARCSINH
IMARCTAN IMARCTANH IMARGUMENT IMCONJUGATE
IMCOS IMCOSH IMCOT IMCOTH
IMCSC IMCSCH IMDIV IMEXP
IMINV IMLN IMLOG10 IMLOG2
IMNEG IMNORM IMPOLAR IMPOWER
IMPRODUCT IMREAL IMSEC IMSECH
IMSIN IMSINH IMSQRT IMSUB
IMSUM IMTAN IMTANH INDEX
INDEX INDIRECT INFO INT
INTERCEPT IRR ISBLANK ISERR
ISERROR ISEVEN ISFORMULA ISLOGICAL
ISNA ISNONTEXT ISNUMBER ISODD
ISPMT ISREF ISTEXT KURT
LARGE LCM LEFT LEN
LINEST LN LOCALIZE LOG
LOG10 LOGEST LOGNORM.DIST LOGNORM.INV
LOGNORMDIST LOOKUP LOOKUP LOWER
MATCH MAX MAXA MAXIFS
MDETERM MEDIAN MID MIN
MINA MINIFS MINUTE MINVERSE
MIRR MMULT MOD MODE.SNGL
MONTH MROUND MULTINOMIAL MUNIT
N NA NEGBINOM.DIST NEGBINOMDIST
NETWORKDAYS NOEXPAND NORM.DIST NORM.INV
NORM.S.DIST NORM.S.INV NORMDIST NOT
NOW NPER NPV ODBCCOUNT
ODBCDATA ODBCERROR ODBCEXEC ODBCINIT
ODD OFFSET OR PEARSON
PERCENTILE.INC PERCENTRANK.INC PERMUT PI
PMT POISSON.DIST POWER PROB
PRODUCT PRODUCTIFS PROPER PV
QUARTILE.INC QUOTIENT RADIANS RAND
RANDBETWEEN RANK.EQ RATE REGRESSION
REPLACE REPT RIGHT ROMAN
ROUND ROUNDDOWN ROUNDUP ROW
ROWS RSQ SEARCH SEC
SECH SECOND SERIESSUM SHEETNAME
SHOWPICT SIGN SIN SINH
SKEW SLN SLOPE SMALL
SQRT SQRTPI STANDARDIZE STDEV.P
STDEV.S STDEVA STDEVIFS STDEVPA
STDEVPIFS STEYX SUBSTITUTE SUBTOTAL
SUM SUMIF SUMIFS SUMPRODUCT
SUMSQ SUMX2MY2 SUMX2PY2 SUMXMY2
SYD T T.DIST.2T T.DIST.RT
T.INV.2T T.TEST TAN TANH
TDIST TEXT TIME TIMEVALUE
TODAY TRANSPOSE TREND TRIM
TRIMMEAN TRUE TRUNC TYPE
UNFORMATTED UPPER USERGROUPS USERLOCALE
VALUE VAR.P VAR.S VARA
VARIFS VARPA VARPIFS VLOOKUP
WEBSERVICE WEEKDAY WEEKNUM WEIBULL.DIST
XIRR XNPV XOR YEAR
Z.TEST    

2. Special Spreadsheet Functions

In addition to the Jedox/PALO functions in category Jedox (implemented in Jedox Web and in Jedox Excel Add-in), Jedox spreadsheets (only in Jedox Web) have specially programmed functions for applications and for database access:

Lookup & Reference Functions
ASARRAY() Takes a series of (unspecified number of) arguments and returns their values as an array. Can be used to make functions like Microchart or SUMIF work when they reference cells in DynaRanges.
HIDECOLUMN(cellreference) Hides column if cellreference is TRUE. Cellreference is TRUE if ABS(cellreference)>0.
Use a cellreference or function that dynamically returns TRUE / FALSE.
Hardcoded arguments like HIDECOLUMN(TRUE) or HIDECOLUMN(FALSE) don’t work dynamically.

Due to the cell design in Jedox Web, when using the HIDECOLUMN function, and a cell in the row to the left has defined a right border as cell format, this right border will not be shown once the column is hidden. Additionally, if a cell in the hidden column itself defines a right border, this border will still be visible when the column is hidden.
HIDEROW(cellreference) Hides row if cellreference is TRUE. Cellreference is TRUE if ABS(cellreference)>0.
Use a cellreference or function that dynamically returns TRUE / FALSE.
Hardcoded arguments like HIDEROW(TRUE) or HIDEROW(FALSE) don’t work dynamically.

Due to the cell design in Jedox Web, when using the HIDEROW function, and a cell in the row above has defined a bottom border as cell format, this bottom border will not be shown once the row is hidden. Additionally, if a cell in the hidden row itself defines a bottom border, this border will still be visible when the row is hidden.

NOEXPAND(reference) Prevents cloning of cell reference passed as argument when a DynaRange is activated, and returns the value of the passed reference.
SHOWPICT(url,name,title)
 Displays an image (PNG or SVG) within a cell. The image is fetched from a URL and shown in the cell in which the formula is used. You can also prefix the image path with @ to reference an image in the Designer; for example, =SHOWPICT("@//Default/Public Files/icons/settings.png","name","title","200","80%").

The parameter for width and height can be specified in pixels, percentage, or "auto". If the parameter is not specified, or the input cannot be resolved, "auto" width / height will be used.

  • Simple numbers, e.g. 200, are interpreted as pixels.
  • The percentage, e.g. 80%, always refers to the cell size (for merged cells, the total size of the merged cell).
  • "auto" retains the original ratio of the image and adjusts the width or height properly in relation to the other parameter.

SHOWPICT images will be included in PDF document exports, but will not be included in XLSX exports of any kind.


Information Functions
CONFIG.GET("Key")
 Returns the value of the specified key from the entries of Jedox Web Administration/Settings, e.g.
=CONFIG.GET("studio.files.default_storage_path").
CURRENTUSER()
 Returns the current user.
ERROR.MSG(cellreference) This function returns a detailed error message for OLAP functions or macro functions when pointed to a cell containing such a function.

Examples:
Entry in A1:
=PALO.DATAC("localhost/Demo","xx","All Products","Europe","Year","All Years","All Versions","Units")

Entry in A2:
=ERROR.MSG(A1)
Returned message: Libpalo ng : Cube not found: Couldn't resolve cube name "xx" in database "Demo".
SHEETNAME()
 Returns the name of the worksheet on which the formula is used.
USERGROUPS()
 Returns the groups of the current user.
USERLOCALE()
 Returns the language setting of the current user.


Text Functions
CONCATENATE.RANGE(cellrange,string_delimiter) Concatenates entries of cellrange, separated with string_delimiter (optional).
Note: This function is not supported in combination with dynaranges.
LOCALIZE("Text") Returns the translation of "Text" in the language which is used in Jedox Web if there exists the translation in localization tool.
UNFORMATTED(text)
 Returns the value that represents a particular formatted string.


Engineering Functions
IMARCCOS IMARCCOSH IMARCCOT IMARCCOTH IMARCCSC
IMARCCSCH IMARCSEC IMARCSECH IMARCSIN IMARCSINH
IMARCTAN IMARCTANH IMINV IMNEG IMNORM
IMPOLAR IMTANH      

 

3. ODBC Functions

With ODBC functions you can read databases that have an ODBC interface.

Before you can import data from an ODBC database, you must register the database source name(DSN). In addition, the computer on which Jedox Web is installed must have an installation of the "Data Connectivity Components" from Microsoft (in a search engine you can find these components under the given name to download). For Linux installations, no additional components are needed.

You can create ODBC connections (using Windows System DSNs) in Connection Manager.
As an example, Jedox Setup installs the ODBC connection "DWH_Biker".

Under Tools - ODBC query you can access a SQL query editor with preview for the ODBC function ODBCEXEC. Only those ODBC connections that are established as such in Administration/Connections can be edited.

You can use ODBC results directly as source for ComboBoxes, or display results comfortably in Dynaranges.

Function Category: Database

ODBCINIT(dsn,username,password)
Initializes a connection to a database using an existing system DSN(Database Source Name)

Note: Using empty strings for user and password will set them as default to the DSN defined user and password (where appropriate).

ODBCEXEC(odbcinit,sql_querytext)
Executes a SQL query through a previously initialized ODBC connection. Define the SQL statement using the syntax of the database accordingly. One or more ODBCEXEC functions can use the same ODBC connection in parallel.

odbcinit Cell of the ODBCINIT() function
sql_querytext SQL query

ODBCDATA(odbcexec,row_number,column_index)
The function returns the result of an executed SQL query. The first parameter is the cell address that contains the ODBCEXEC function. The result of ODBCEXEC is organized as an array of rows and columns. Use row_number “1” and column_index “1” to display the first value.

odbcexec Cell of the ODBCEXEC() function
row_number Row of the result array
column_index Column of the result array

ODBCCOUNT(odbcexec)
Displays the number of rows returned by an executed SQL query.

odbcexec Cell of the ODBCEXEC() function

ODBCERROR(odbcinit)
Displays the error code returned by an executed SQL query. In general, a positive value means success, a negative failure.

odbcinit Cell of the ODBCINIT() function

4. Functions that do not exist in Excel or that differ from those in Excel

After an export, the following Jedox Spreadsheet functions return #NAME? because they do not exist in Excel:

ASARRAY

CONCATENATE.RANGE

CONFIG.GET

CURRENTUSER

ERROR.MSG

IMARCCOS

IMARCCOSH

IMARCCOT

IMARCCOTH

IMARCCSC

IMARCCSCH

IMARCSEC

IMARCSECH

IMARCSIN

IMARCSINH

IMARCTAN

IMARCTANH

IMCSCH

IMINV

IMNEG

IMNORM

IMPOLAR

IMTANH

LOCALIZE

MAXIFS

MINIFS

NOEXPAND

ODBCCOUNT

ODBCDATA

ODBCERROR

ODBCEXEC

ODBCINIT

PRODUCTIFS

REGRESSION

SHEETNAME

SHOWPICT

STDEVIFS

STDEVPIFS

UNFORMATTED

USERGROUPS

USERLOCALE

VARIFS

VARPIFS

The functions below differ from Excel functions.

CELL() The Jedox spreadsheet function CELL(info_type,reference) has following differences to the corresponding Excel function:

  • As the reference, an XLSX file accepts a range of cells, but a WSS file accepts only one cell.

  • An XLSX file accepts the parameter info_type in both upper- and lowercase letters. A WSS file accepts this parameter only in lowercase letters.

  • The parameter info_type only recognizes English words: address, col, color, contents, filename, format, parentheses, prefix, protect, row, type and width.

  • In a XLSX file, the info_type "filename" returns both path and filename. A WSS file only returns filename.
HIDECOLUMN() and HIDEROW() The functions HIDECOLUMN and HIDEROW also do not exist in Excel, but they do not return #NAME. Instead they are converted to PALO.HIDECOLUMN and PALO.HIDEROW for internal reasons, but these functions are not supported in Excel.
HYPERLINK() The Jedox HYPERLINK() function supports more arguments than in Excel. After export to Excel, the HYPERLINK function supports a maximum of two arguments. See HYPERLINK() Function for more information.
IFERROR()
ISERROR()		 The functions IFERROR() and ISERROR() only check for the internal data type ERROR. A manually typed string in a cell (like "#VALUE!") will not be recognized as an error. Dynaranges receive errors as a normal string from Spreadsheet Server. As such, the data type for the error has changed and is therefore no longer recognized as an error by these functions.
INFO() The Jedox INFO() function has partially the same arguments as in Excel, but some arguments don't exist in Excel. If the exported argument of the INFO function doesn’t exist in Excel, you will receive #VALUE! in the corresponding cell. See INFO() Function for more information.
SUBTOTAL() SUBTOTAL function in Excel has a range of subfunctions (1xx) that allow values in hidden rows or columns to be ignored. This behavior is supported in Jedox Web for rows or columns that are dynamically hidden using the HIDEROW / HIDECOLUMN functions. However, when hiding a row or column statically (via the row / column context menu), the SUBTOTAL result will only change after some other change to the sheet content has been made.


5. Supported data types in the SQL server

The following list shows whether and how a SQL data type will be transferred to Jedox Spreadsheet:

SQL Data Type Jedox Spreadsheet
Bigint number
Binary(n) string

Bit		 boolean
char(n) string

Date		 string

Datetime		 number
Datetime2(n) string

datetimeoffset(7)		 string
decimal(n, decimals) number
float number
geography not supported; cast it to string in SQL queries
geometry not supported; cast it to string in SQL queries
hierarchyid not supported; cast it to string in SQL queries
Image string
Int number
Money number
Nchar(n) string
Ntext string
numeric(n, decimals) numeric
nvarchar(n) string
nvarchar(MAX) string, max. size of 64000
Real numeric
Smalldatetime number
Smallint numeric
Smallmoney numeric
sql_variant not supported; cast it to string in SQL queries
Text not supported; cast it to string in SQL queries
Time(n) not supported; cast it to string in SQL queries
Timestamp string
Tinyint number
Uniqueidentifier not supported; cast it to string in SQL queries
varbinary(n) string
varbinary(MAX) string, max. size of 64000
varchar(n) string
varchar(NAX) string, max. size of 64000
Xml not supported; cast it to string in SQL queries

Updated November 15, 2024