Jedox Spreadsheet Functions

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

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)

Most Jedox Spreadsheet functions work the same way as their corresponding Excel counterparts; see Microsoft's documentation for details. There are some exceptions, which are explained in the section below.

The table below lists all the implemented Jedox spreadsheet functions. For details on Jedox PALO.DATA functions, see Jedox Data Functions Overview.

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
IFS 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,width,height)
 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.
REGRESSION() This function does not exist in Excel, but the syntax is similar to TREND() or GROWTH() functions:

REGRESSION(reg_type, known_y's, [known_x's], [new_x's], [const])

The only difference is the first parameter, which is a numerical representation of the regression type. Supported regression types and their corresponding numbers are

1 - linear
2 - logarithmic
3 - e-Exponential
4 - ab-Exponential
5 - power
6 - inverse
7 - logistic
8 - quadratic
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 July 22, 2024