Jedox Spreadsheet Functions

image_pdfimage_print

In Jedox Spreadsheets are implemented a lot of functions. We have them grouped in this article under the following topics:

  1. List of all Jedox Spreadsheet Functions (without PALO Functions)
  2. Special Spreadsheet Functions
  3. ODBC Functions
  4. Export behavior of functions that do not exist in Excel or that differ from those in Excel

All PALO functions you will find in a own article:  Jedox / Palo OLAP Functions (All functions of category Jedox)

Note: Jedox Spreadsheet does not support  R1C1 reference style (cell addresses in row / column reference style)!

1. List of all Jedox Spreadsheet Functions (without PALO Functions)

Almost all Jedox Spreadsheet functions are programmed identically as the corresponding Excel counterparts. There are only 3 exceptions:

  • The Jedox function CELL() has a few limitations compared to the function in Excel with the same name. You can read more about the CELL function in Jedox here.
  • The Jedox function HYPERLINK() has more functionality than the function in Excel with the same name. You can read more about the HYPERLINK function in Jedox here.
  • The Jedox function INFO() returns other information than the function in Excel with the same name. You can read more about the INFO function in Jedox here.

Here is a list of all implemented Jedox spreadsheet functions (without PALO functions):

1

ABS

ACOS

ACOSH

ACOT

2

ACOTH

ADDRESS

AND

ARABIC

3

AREAS

ASARRAY

ASIN

ASINH

4

ATAN

ATAN2

ATANH

AVEDEV

5

AVERAGE

AVERAGEA

AVERAGEIFS

BASE

6

BESSELI

BESSELJ

BESSELK

BESSELY

7

BETA.DIST

BETA.INV

BETADIST

BINOM.DIST

8

BINOM.INV

CEILING

CELL1)  (differences)

CHAR

9

CHISQ.DIST.RT

CHISQ.INV.RT

CHISQ.TEST

CHOOSE

10

CLEAN

CODE

COLUMN

COLUMNS

11

COMBIN

COMPLEX

CONCATENATE

CONCATENATE.RANGE

12

CONFIDENCE.NORM

CONFIG.GET

CORREL

COS

13

COSH

COT

COTH

COUNT

14

COUNTA

COUNTBLANK

COUNTIF

COUNTIFS

15

COVARIANCE.P

CSC

CSCH

CURRENTUSER

16

DATE

DATEVALUE

DAVERAGE

DAY

17

DB

DCOUNT

DCOUNTA

DDB

18

DECIMAL

DEGREES

DELTA

DEVSQ

19

DGET

DMAX

DMIN

DPRODUCT

20

DSTDEV

DSTDEVP

DSUM

DVAR

21

DVARP

EDATE

ENCODEURL

EOMONTH

22

ERF

ERF.PRECISE

ERFC

ERFC.PRECISE

23

ERROR.MSG

ERROR.TYPE

EVEN

EXACT

24

EXP

EXPON.DIST

F.DIST.RT

F.INV.RT

25

F.TEST

FACT

FACTDOUBLE

FALSE

26

FILTERXML

FIND

FISHER

FISHERINV

27

FIXED

FLOOR

FORECAST

FORMULATEXT

28

FREQUENCY

FV

GAMMA.DIST

GAMMA.INV

29

GAMMALN

GCD

GEOMEAN

GESTEP

30

GROWTH

HARMEAN

HIDECOLUMN

HIDEROW

31

HLOOKUP

HOUR

HYPERLINK (more functionality!)

HYPGEOM.DIST

32

HYPGEOMDIST

IF

IFERROR

IFNA

33

IMABS

IMAGINARY

IMARCCOS

IMARCCOSH

34

IMARCCOT

IMARCCOTH

IMARCCSC

IMARCCSCH

35

IMARCSEC

IMARCSECH

IMARCSIN

IMARCSINH

36

IMARCTAN

IMARCTANH

IMARGUMENT

IMCONJUGATE

37

IMCOS

IMCOSH

IMCOT

IMCOTH

38

IMCSC

IMCSCH

IMDIV

IMEXP

39

IMINV

IMLN

IMLOG10

IMLOG2

40

IMNEG

IMNORM

IMPOLAR

IMPOWER

41

IMPRODUCT

IMREAL

IMSEC

IMSECH

42

IMSIN

IMSINH

IMSQRT

IMSUB

43

IMSUM

IMTAN

IMTANH

INDEX

44

INDEX

INDIRECT

INFO (different parameters!)

INT

45

INTERCEPT

IRR

ISBLANK

ISERR

46

ISERROR

ISEVEN

ISFORMULA

ISLOGICAL

47

ISNA

ISNONTEXT

ISNUMBER

ISODD

48

ISPMT

ISREF

ISTEXT

KURT

49

LARGE

LCM

LEFT

LEN

50

LINEST

LN

LOCALIZE

LOG

51

LOG10

LOGEST

LOGNORM.DIST

LOGNORM.INV

52

LOGNORMDIST

LOOKUP

LOOKUP

LOWER

53

MATCH

MAX

MAXA

MAXIFS

54

MDETERM

MEDIAN

MID

MIN

55

MINA

MINIFS

MINUTE

MINVERSE

56

MIRR

MMULT

MOD

MODE.SNGL

57

MONTH

MROUND

MULTINOMIAL

MUNIT

58

N

NA

NEGBINOM.DIST

NEGBINOMDIST

59

NETWORKDAYS

NOEXPAND

NORM.DIST

NORM.INV

60

NORM.S.DIST

NORM.S.INV

NORMDIST

NOT

61

NOW

NPER

NPV

ODBCCOUNT

62

ODBCDATA

ODBCERROR

ODBCEXEC

ODBCINIT

63

ODD

OFFSET

OR

PEARSON

64

PERCENTILE.INC

PERCENTRANK.INC

PERMUT

PI

65

PMT

POISSON.DIST

POWER

PROB

66

PRODUCT

PRODUCTIFS

PROPER

PV

67

QUARTILE.INC

QUOTIENT

RADIANS

RAND

68

RANDBETWEEN

RANK.EQ

RATE

REGRESSION

69

REPLACE

REPT

RIGHT

ROMAN

70

ROUND

ROUNDDOWN

ROUNDUP

ROW

71

ROWS

RSQ

SEARCH

SEC

72

SECH

SECOND

SERIESSUM

SHEETNAME

73

SHOWPICT

SIGN

SIN

SINH

74

SKEW

SLN

SLOPE

SMALL

75

SQRT

SQRTPI

STANDARDIZE

STDEV.P

76

STDEV.S

STDEVA

STDEVIFS

STDEVPA

77

STDEVPIFS

STEYX

SUBSTITUTE

SUBTOTAL

78

SUM

SUMIF

SUMIFS

SUMPRODUCT

79

SUMSQ

SUMX2MY2

SUMX2PY2

SUMXMY2

80

SYD

T

T.DIST.2T

T.DIST.RT

81

T.INV.2T

T.TEST

TAN

TANH

82

TDIST

TEXT

TIME

TIMEVALUE

83

TODAY

TRANSPOSE

TREND

TRIM

84

TRIMMEAN

TRUE

TRUNC

TYPE

85

UNFORMATTED

UPPER

USERGROUPS

USERLOCALE

86

VALUE

VAR.P

VAR.S

VARA

87

VARIFS

VARPA

VARPIFS

VLOOKUP

88

WEBSERVICE

WEEKDAY

WEEKNUM

WEIBULL.DIST

89

XOR

YEAR

Z.TEST

 

1) The Jedox spreadsheet function CELL(info_type,reference) has following differences to the corresponding Excel function:
All info_types: as reference a XLSX file accepts also a range of cells, a WSS file accepts only one cell.
All info_types: in a XLSX file the parameter “info_type” can also be written in big letters. A WSS file accepts this parameter only in small letters.
The parameter „info_type“ is not yet translated: In all locations you must use the English names of this parameter: address, col, color, contents, filename, format, parentheses, prefix, protect, row, type and width.
Info_type “filename” returns in a XLSX file path+filename, in a WSS file is only filename returned.

 

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:

2.1 Function Category: Lookup & Reference

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.
Please use a cellreference or a function which returns dynamically TRUE / FALSE.
Hardcoded arguments like HIDECOLUMN(TRUE) resp. HIDECOLUMN(FALSE) don’t work dynamically!

HIDEROW(cellreference)
Hides row if cellreference is TRUE. Cellreference is TRUE, if ABS(cellreference)>0.
Please use a cellreference or a function which returns dynamically TRUE / FALSE.
Hardcoded arguments like HIDEROW(TRUE) resp. HIDEROW(FALSE) don’t work dynamically!

Note: Due to the cell design in Jedox Web, cell borders for which adjacent columns or rows are hidden with a HIDE function are not displayed on the left or on the top.

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 within a cell. The image is fetched from a URL and shown in the cell in which the formula is used.

2.2 Function Category: Information

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.

2.3 Function Category: Text

CONCATENATE.RANGE(cellrange,string_delimiter)
Concatenates entries of cellrangrange, separated with string_delimiter (optional).

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 formated string.

2.4 Function Category: Engineering

There are several functions for complex numbers:
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. Behavior of 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

 

CELL()
For this functions there are no restrictions after an export to Excel.

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 Spreadsheet function HYPERLINK supports more arguments than in Excel:
After export to Excel, the HYPERLINK function supports a maximum of three arguments.

INFO()
The function INFO 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.

 

Appendix

Supported data types in the SQL server

The following list shows whether and how a SQL data type will be transferred to 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 , users should cast it to string in sql queries
geometry -> not supported , users should cast it to string in sql queries
hierarchyid -> not supported , users should 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 , users should cast it to string in sql queries
Text -> not supported , users should cast it to string in sql queries
Time(n) -> not supported , users should cast it to string in sql queries
Timestamp -> string
Tinyint -> number
Uniqueidentifier -> not supported , users should 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 , users should cast it to string in sql queries

image_pdfimage_print
Was this post helpful?
NoYes (No Ratings Yet)
Loading...