Jedox Data Functions Overview

Each PALO.DATA() function is a connection to one unique cube cell. All PALO.DATA functions contain the same arguments in the same order:

PALO.DATA*([connection],[cube name],[element of the first dimension],[element of the second dimension],[element of the third dimension] ...)

• The asterisk (*) indicates that this order applies to several functions.
• The first argument of the function defines the connection and the database.
• The second argument defines the cube.
• The remaining arguments are the cell coordinates.

This connection between the server (where the values in the cubes are stored) and the front end (e.g. Excel, Jedox Spreadsheet) enables Jedox to read and edit the values. In the context of database Views, all functions are processed in the same way on the server. However, in custom-designed reports and spreadsheets each function has a unique behavior.

PALO.DATA(Server/Database,Cube,Coordinate1,Coordinate2,… Coordinate16)

Reads the value of the described data cell from the cube, e.g.:
=PALO.DATA("localhost/Demo","Sales","Desktop L","Germany","Oct",2015,"Actual","Units")

You can input a maximum of 253 characters. If the argument of the function is an empty string instead of a coordinate, the result is an empty string.

Each spreadsheet cell and in conjunction each OLAP database cell is calculated separately. This process is slower, but the calculated value can always be passed to other OLAP functions.

PALO.DATAC(Server/Database,Cube,Coordinate1,Coordinate2,… Coordinate16)

Reads the value of the described data cell from the cube, e.g.:
=PALO.DATAC("localhost/Demo","Sales","Desktop L","Germany","Oct",2015,"Actual","Units")

Contrary to PALO.DATA, all PALO.DATAC functions of a cube in one file are updated with a single call of the server, and this is consequently faster than with PALO.DATA functions.

If instead of a coordinate an empty string is used as a function argument (or if an empty cell is referenced), then the result will be an empty string. This is true for both Excel Add-in and Jedox Web.

This function was developed for the use with Excel Add-in. In Jedox Web, the function will correctly execute writeback but does not use the "collect" mechanism used by Excel.

Important constraints for using PALO.DATAC():

1. In Excel, the function PALO.DATAC internally triggers two recalculation cycles of the workbook. During the first cycle, the result of the PALO.DATAC formula is not yet available. This implies that it is problematic to have formulas depend on PALO.DATAC() where a result must be always present, such as a second PALO.DATAC formula indirectly depending on a first PALO.DATAC formula. Therefore PALO.DATAC may only be used to display values from the cube or writeback values into the cube. PALO.DATAC() or its value may not be used in formulas, as this leads to incorrect results.
2. If you use Excel's copy / paste functionality, and you have PALO.DATAC() on the sheet, and automatic recalculation of the workbook is turned on, then the clipboard will be empty after pasting its content for the first time. The workaround for this issue is to disable automatic recalculation.
3. In Jedox Web, you can generally use DATAC formulas as input for other functions. The only constraint is that DATAC cannot be used inside of cyclic calculations, i.e., =PALO.DATAC(PALO.DATAC(...),...). When using DATAC inside of an IF() formula, a scenario where the formula calculation encounters two DATAC formulas should be avoided. For example, there can't be one DATAC formula the "condition" argument, and then another in the "then" or "else" argument; however, using DATAC only in both "then" and "else" arguments is allowed, as only one of them would be executed during the calculation.

PALO.DATAT(Server/Database,Cube,Coordinate1,Coordinate2,… Coordinate16)

In Excel, PALO.DATA, PALO.DATAC and PALO.DATAV can display 32.767 characters per cell.

If you need to display more than 32.767 characters, you can use PALO.DATAT in an array of two or more cells. In this case, the continuation of the text is displayed in the next cell of the array. Each cell of the array can display up to 32.767 characters.

PALO.DATAT() returns the value of the descriptive text cell in an array you define in advance, e.g.:
=PALO.DATAT("localhost/Demo","Market","Comment","Year","Units","All Products","2015","Total")

In Jedox Web, PALO.DATAT is not supported as an array function. It is neither needed, because in Jedox Web the functions PALO.DATA, PALO.DATAC or PALO.DATAV don’t have a value limit of 32.767 characters per cell. Only the storage space or the performance of a report will limit here the number of characters.

If instead of a coordinate an empty string is used as a function argument (or if an empty cell is referenced), then the result will not be an error message but an empty string. This is true for both Excel Add-in and Jedox Web.

PALO.DATAV(Server/Database,Cube,Coordinate1,Coordinate2,… Coordinate16)

Returns the value of a data cell from a cube when you use an Excel array formula. This function is similar to PALO.DATA, but it improves performance because a single array formula is generated for the complete area. The use of PALO.DATAV is only possible in related cell areas.

The PALO.DATAV formula can return a result from an array in rows, columns, or a matrix (rows and columns). If two arrays are used, the results can only be returned in a matrix. Thus, you cannot retrieve results in a row-row, or column-column. Note that a maximum of two arrays are to be used in PALO.DATAV.

PALO.DATAX(Server/Database,Cube,Coordinate1,Coordinate2,… Coordinate16)

This function is only available for Excel Add-in.

This function returns the value of the described element from the cube if the corresponding cell is on the currently active spreadsheet.

Note: this function corresponds to the PALO.DATAC function, but the calculation is limited to the current spreadsheet. This speeds up the calculation since it is no longer necessary to calculate the entire workbook. However, if you switch to another spreadsheet and later return to the spreadsheet containing the PALO.DATAX formulas, you may have to manually trigger a recalculation of the spreadsheet. This step is always necessary if the spreadsheet call returns #VALUE! instead of a value.

PALO.EL() and PALO.ELALL()

Both functions are helper functions related to flexible cube layout. They are used in other PALO functions like PALO.DATA*() to specify not only an element name or an array of elements, but to specify a dimension name as well. Functions that use PALO.EL() for coordinates will continue to work even if the layout of a cube (e.g. order of dimensions) changes.

PALO.EL() expects two parameters as input. The first is the name of the dimension, the second is either an element name or (for use in PALO.DATAV) an array of element names. Note that if you want to use the PALO.EL() formula, it must be used for all coordinate arguments. You cannot mix the old simple element coordinate syntax with the use of PALO.EL().

When using the PALO.EL() formulas the order in which the coordinates are specified does not matter. The dimension name is used to look up the specified elements, regardless of the dimension layout (order) in the cube. Each dimension that defines a default read/write element can be completely omitted.

PALO.ELALL() is specific case used in PALO.CUBE_CLEAR(). It only takes a dimension name as argument. It's meaning is "all elements in dimension X".

Example:

=PALO.DATA("localhost/Demo","Sales",PALO.EL("Regions","Europe"),PALO.EL("Products","All Products"),PALO.EL("Years","All Years"),PALO.EL("Months","Year"),PALO.EL("Versions","All Versions"),PALO.EL("Measures","Units"))

PALO.DATAA(Server/Database,Cube,Coordinate Array)

The array version of PALO.DATA. Returns the value of the cube cell specified through the coordinates given as an array, e.g.:

=PALO.DATAA("localhost/Demo","Sales",{"DesktopL","Germany","Oct",2015,"Actual","Units"})

PALO.DATAAC(Server/Database,Cube,Coordinate Array)

The array version of PALO.DATAC. Returns the value of the cube cell specified through the coordinates given as an array, e.g.:

=PALO.DATAAC("localhost/Demo","Sales",{"DesktopL","Germany","Oct",2015,"Actual","Units"})

PALO.DATAAT(Server/Database,Cube,Coordinate Array)

The array version of PALO.DATAT. Returns the value of the string element specified through the coordinates given as an array.

PALO.DATAATC(Server/Database,Cube,Coordinate Array)

This function is only available for Excel Add-in.

Behaves like PALO.DATAAT, except that all PALO.DATAATC functions of a cube in a file are updated with a single call to the server.

PALO.DATA.AVG(Server/Database,Cube,Coordinates,Expandtypes)

Returns the average value of the cells in the specified cell range for which the value is not zero, e.g.:
=PALO.DATA.AVG("localhost/Demo","Sales",PALO.COORD("All Products","Europe","Year","2015","Actual","Units"),PALO.EXPANDTYPE(1,4,2))

PALO.DATA.CNT(Server/Database,Cube,Coordinates,Expandtypes)

Returns the number of cells of the specified range for which the value is not zero, e.g.:
=PALO.DATA.CNT("localhost/Demo","Sales",PALO.COORD("All Products","Europe","Year","2015","Actual","Units"),PALO.EXPANDTYPE(1,4,2))

See also PALO.EXPANDTYPE.

PALO.DATA.MAX(Server/Database,Cube,Coordinates,Expandtypes)

Returns the maximum of the cells of the specified range of cells for which the value is not zero, e.g.:
=PALO.DATA.MAX("localhost/Demo","Sales",PALO.COORD("All Products","Europe","Year","2015","Actual","Units"),PALO.EXPANDTYPE(1,4,2))

Calculation: the largest value of a cell in the specified area is returned. Zero values are ignored.
Exceptions: if all cell values are 0, then the output is 0.

PALO.DATA.MIN(Server/Database,Cube,Coordinates,Expandtypes)

Returns the minimum of the cells of the specified range of cells for which the value is not zero, e.g.:
=PALO.DATA.MIN("localhost/Demo","Sales",PALO.COORD("All Products","Europe","Year","2015","Actual","Units"),PALO.EXPANDTYPE(1,4,2))

Calculation: the smallest value of a cell in the specified area is returned, zero values are ignored.
Exception: if all cell values are zero, then the output is zero.

PALO.DATA.SUM(Server/Database,Cube,Coordinates,Expandtypes)

Returns the sum of cells in the specified range of cells, e.g.:
=PALO.DATA.SUM("localhost/Demo","Sales",PALO.COORD("All Products","Europe","Year","2015","Actual","Units"),PALO.EXPANDTYPE(1,4,2))

PALO.DATA.SUM is only used internally for average calculation. PALO.DATA.SUM returns the aggregated sum. The result isn't different from normal PALO.DATA. The above-mentioned example returns the same value as:
=PALO.DATA("localhost/Demo","Sales","All Products","Europe","Year","2015","Actual","Units")

PALO.AGR.CHILDREN()

This is a helper function for the statistical functions PALO.DATA.AVG and PALO.DATA.CNT.

PALO.AGR.LEAVES()

This is a helper function for the statistical functions PALO.DATA.AVG and PALO.DATA.CNT.

PALO.AGR.SELF()

This is a helper function for the statistical functions PALO.DATA.AVG and PALO.DATA.CNT.

PALO.COORD()

This is a helper function for certain statistical functions. For more information, see PALO.DATA.AVG.

PALO.EXPANDTYPE(Integer,Integer,Integer)

This is a helper function for some statistical functions such as PALO.DATA.AVG. It specifies the coordinates of PALO.COORD().

In the specified area, all cells that do not have a zero value will be added and divided by the number of terms of the sum. However, if all cell values are zero, then the output is zero.

PALO.SETDATA(Value,Splash,Server/Database,Cube,Coordinate1,Coordinate2,…..)

Writes the value into the cell specified through the coordinates. Set splash to TRUE if you want to write in a consolidated element, otherwise set it to FALSE. The parameter VALUE can be a number or the reference to a spreadsheet cell with a number, e.g. A1. Alternatively, it can be a formula.

Examples:

=PALO.SETDATA($A$1,FALSE,"localhost/Demo","Sales","Desktop L","Germany","Dec","2018","Budget","Units")

=PALO.SETDATA($E$1*$F$1*$G$1,FALSE,"localhost/Demo","Sales","Desktop L","Germany","Dec","2018","Budget","Units")

PALO.SETDATAA(Value,Splash,Server/Database,Cube,Coordinate Array,Locking Area,SVS_Trigger,Wait)

This function is only available for Excel Add-in.

Writes the value into the cell specified through the coordinates given as an array. Set splash to TRUE if you want to write in a consolidated element, otherwise set it to FALSE. The parameter VALUE can be a number or the reference to a spreadsheet cell with a number, e.g. A1. Alternatively, it can be a formula.

Optional Parameter "Locking_area": locked cell_area (it is possible to define an area of child cells if target is a parent cell).

Note that the VALUE can only be set to one cell, not to an area of cells! Therefore, only one row can be used for the cell address.
The advantage of this functions compared with PALO.SETDATA() is that you can restrict splashing with the parameter Locking_area!

Example:

Description: PALO.SETDATAA() splashes 170000 in parent cell "Desktop L,Europe,Dec,2019,Budgets,Units", displayed children France and Spain are locked.

PALO.SETDATAAIF(Condition,Value,Splash,Server/Database,Cube,Coordinate Array,…)

This function is only available for Excel Add-in.

Writes the value into the cell specified through the coordinates given as an array. If condition is TRUE, same thing occurs as with PALO.SETDATAA(). If the condition is FALSE, nothing will occur.

PALO.SETDATAC(Value,Splash,Server/Database,Cube,Coordinate1,Coordinate2,…..)

Similar to PALO.DATAC, the calculation of the SETDATAC formula first "collects" all instances of the formula for a specific cube, and then executes the change of data in a single request. This leads to improved performance, especially in scenarios where connections with high network latency to remote servers are in use. The arguments to the SETDATAC function are identical to PALO.SETDATA.

This function is fully functional in Excel Add-in. In Jedox Web Spreadsheets, the function correctly executes writeback but does not use the "collect" mechanism that is used in Excel Add-in.

PALO.SETDATAIF(Condition,Value,Splash,Server/Database,Cube,Coordinate1,Coordinate2,…)

Writes the value into the specified cell of the cube. If condition is TRUE, same thing occurs as with PALO.SETDATA(). If the condition is FALSE, nothing will occur.

PALO.SETDATA_BULK(Server/Database,Cube,Cells,Values,Splashmode,Wait)

Available in Excel Add-in and Jedox Web (in Jedox Web this function isn't displayed in the function wizard). Sets values of several cube cells The values are written into the cells specified through the parameter cells. Cells and values can be arrays. The result of PALO.SETDATA_BULK is TRUE.

Writeback operations done via PALO.SETDATA_BULK are part of the audit trail, if audit is enabled for a cube. It will appear in the Audit screen like any manual writeback, with additional information to identify the bulk.

Splash modes:

Example with arrays:
=PALO.SETDATA_BULK("localhost/Demo","Sales",{"Desktop L","Germany","Dec","2018","Budget","Units";"Desktop Pro","Germany","Dec","2018","Budget","Units"},{10000;20000},2)

Example with ranges:
The parameter cells and values of this example are in the ranges C4:H5 (without quotes) and J4:J5.
=PALO.SETDATA_BULK("localhost/Demo","Sales",$C$4:$H$5,J4:J5,2)

PALO.CUBE_EXISTS(Server/Database,Cubename)

Returns TRUE, if cube exists, else FALSE, e.g.:
=PALO.CUBE_EXISTS("localhost/Demo","Sales")

PALO.CUBE_LIST_DIMENSIONS(Server/Database,Cube,[Type])

Returns a list with all dimensions of the specified cube in an area you define in advance, e.g.:
=PALO.CUBE_LIST_DIMENSIONS("localhost/Demo","Sales")

The 3rd argument is optional and specifies whether the returned results should include Virtual Dimensions. If no argument is given, then only regular dimensions will be returned. If set to 1, then Virtual Dimensions (if there are any) will be included in the result.

PALO.DATABASE_EXISTS(Server/Database)

Returns TRUE, if database exists, else FALSE, e.g.:
=PALO.DATABASE_EXISTS("localhost/demo")

PALO.DATABASE_LIST_CUBES(Server/Database,[Type,Show permission])

Lists the cubes of a database in an area you define in advance, e.g.:
=PALO.DATABASE_LIST_CUBES("localhost/Demo",1) - Lists system cubes of the database "Demo".

PALO.DATABASE_LIST_DIMENSIONS(Server/Database,[Type])

Lists the dimensions of a database in an area you define in advance, e.g.:
=PALO.DATABASE_LIST_DIMENSIONS("localhost/demo",0) - Lists the normal dimensions of the database "Demo".

PALO.DATABASE_LIST_DIMENSIONS_EXT(Server/Database,[Show normal d., ....])

Lists the dimensions of a database in an area you define in advance, e.g.:
=PALO.DATABASE_LIST_DIMENSIONS_EXT("localhost/demo",FALSE,TRUE) - Lists the system dimensions of the database "Demo".

PALO. DIMENSION_EXISTS(Server/Database,Dimensionname)

Returns TRUE, if dimension exists, else FALSE, e.g.:
=PALO.DIMENSION_EXISTS("localhost/Demo","Regions")

PALO.DIMENSION_LIST_CUBES(Server/Database,Dimension,[Type])

Lists in which cubes a dimension is contained, in an area you define in advance, e.g.:
=PALO.DIMENSION_LIST_CUBES("localhost/Demo","Months",0) - Lists all normal cubes of the "Demo" database that contain the specified dimension.

PALO.DIMENSION_LIST_ELEMENTS(Server/Database,Dimension,Unused)

Lists which elements are contained in a dimension, in an area you define in advance, e.g.:
=PALO.DIMENSION_LIST_ELEMENTS("localhost/Demo","Months")

PALO.ELEMENT_LIST_ANCESTORS(Server/Database,Dimension,Element)²⁾

Lists all ancestors of the given element in a predefined area, e.g.:
=PALO.ELEMENT_LIST_ANCESTORS("localhost/Demo","Months","Jan")

PALO.ELEMENT_LIST_CHILDREN(Server/Database,Dimension,Element)

Lists all child elements of a consolidated element in a predefined area, e.g.:
=PALO.ELEMENT_LIST_CHILDREN("localhost/Demo","Regions","West")

PALO.ELEMENT_LIST_CONSOLIDATION_ELEMENTS(Server/Database,Dimension,Element,Start,Limit)

An extension of PALO.ELEMENT_LIST_CHILDREN, used for very large amounts of data. The following arguments are optional:

• start: can define the first index of the children to return or the name of the element to start the list.
• limit: the maximum count of elements that should be returned.

PALO.ELEMENT_LIST_DESCENDANTS(Server/Database,Dimension,Element,Error suppr.)

Lists all children and grandchildren of an element in a predefined area, e.g.:
=PALO.ELEMENT_LIST_DESCENDANTS("localhost/Demo","Products","Monitors")

PALO.ELEMENT_LIST_PARENTS(Server/Database,Dimension,Element)

Lists all parents of an element in a predefined area, e.g.:
=PALO.ELEMENT_LIST_PARENTS("localhost/Demo","Months","Jan")

PALO.ELEMENT_LIST_SIBLINGS (Server/Database,Dimension,Element)

Lists all siblings of a specified element, including the specified element itself, in a predefined range, e.g.:
=PALO.ELEMENT_LIST_PARENTS("localhost/Demo","Months","Qtr.1")

PALO.ROOT_LIST_DATABASES(Server,List System,List Userinfo)

Lists the databases that exist on the server, in an area you define in advance, e.g.:
=PALO.ROOT_LIST_DATABASES("localhost",TRUE,TRUE)-

PALO.ROOT_LIST_DATABASES_EXT(Server,List System,List Userinfo,ShowPermission)

An array function, extension of PALO.ROOT_LIST_DATABASES. It displays information about the permission if the parameter ShowPermission is set to TRUE.

This function can return up to 8 columns of data:

PALO.VIEW.TABLE(Server/Database,Cube,Stored View,Global st. View)

This function returns the result of a Stored View as a regular table in a structure that is usable for a Jedox Web Chart. This function is only available in Jedox Web.
Example:
=PALO.VIEW.TABLE("localhost/Demo","Sales","Actual_2018_2019",TRUE)

This form of the function is used if you select a Stored View for a chart:

A second possibility of this function is to create a chart of a Jedox View that is already in a Jedox Spreadsheet. In this case the function must be used with different parameters as follows:

PALO.VIEW.TABLE(Server,Database,view_handle)

Example:
=PALO.VIEW.TABLE("localhost","Demo",B6)

In B6 is the entry of a standard Jedox View, called view_handle: =PALO.CUBE($B$5,"Sales").

The PALO.VIEW.TABLE function in H6 is useful to create a chart of an existing View using only H6 as sheet range:

PALO.VIEW.TABLEXML(Server/Database,Cube,XML)

Calculates the View from a full XML view definition specified in the function. This function is only available in Jedox Web.

PALO.CUBE_CLEAR(Server/Database,Cube,Coordinate1,Coordinate2,...)

Clears the value of a whole cube or a specified cube area. This function can only be used in a VBA script, e.g.:

Sub Test4()

                        Dim serverdb As String
                        Dim cube As String
                        Dim ignore As Variant

                        serverdb = "localhost/Demo"
                        cube = "sales"

                        ' clear whole cube
                        ignore = Application.Run("PALO.CUBE_CLEAR", serverdb, cube)

                        Dim subcube(1 To 6) As Variant
                        Dim Years(1 To 2) As Variant

                        Years(1) = "2004"
                        Years(2) = "2005"

                        subcube(4) = Years

                        ' clear partial cube
                        ignore = Application.Run("PALO.CUBE_CLEAR", serverdb, cube, subcube)

                        MsgBox ignore

                        End Sub
                    

PALO.ECC

Stops the cache collection. This function expects no arguments. It can be used only in VBA scripts and not in Excel cells.

PALO.PING

This function updates the internal client cache. It must be called after finishing creating or deleting elements and can only be used in a VBA script, e.g.:

' Update internal client cache
                    ignore = Application.Run("PALO.Ping", connectionname)

The expected argument is connectionname, provided in PALO.REGISTER. If successful, it returns TRUE.

PALO.REGISTER

This function opens the OLAP connection to the Jedox Server. It can only be used in a VBA script, and not in an Excel cell, e.g.:

' Define connectionname
                    connectionname = Application.Run("PALO.REGISTER", "myserver", "localhost", 7777, "admin", "admin")

Note: for Windows SSO, use "" for the username and password.

If successful, it returns the connection name.

PALO.REMOVE_CONNECTION

Closes the OLAP connection to the Jedox Server. It can only be used in a VBA script, and not in an Excel cell, e.g.:

' Close connection
                    ignore = Application.Run("PALO.REMOVE_CONNECTION", connectionname)

The expected argument is connectionname, provided in PALO.REGISTER. If successful, it returns TRUE.

PALO.SCC

Starts the cache collection. This function expects no arguments. It can only be used in VBA scripts and not in Excel cells.

PALO.CONNECTIONUSER(Server)

Available in Excel Add-in and Jedox Web (in Jedox Web, this function isn’t displayed in the function wizard). This function displays the user of the connection "Server", e.g.:
=PALO.CONNECTIONUSER("localhost")

PALO.ERROR_LOG(Error,Path,Value,Cube,Coordinate1,Coordinate2,…,Coordinate15)

Writes the data records of an import file that does not adhere to import specifications into an error file, e.g.:
=PALO.ERROR_LOG(F3,"c:/Error.txt",A1,B1,C1,D1,E1,F1)

This function is available only in Excel Add-in.

PALO.LOGIN_TEST(Server,Username,Password)

Verifies the username and password combination.

PALO.SERVER_INFO(Server,Show counters)

Available in Excel Add-in and Jedox Web. This function displays:

• major version of the server
• minor version of the server
• number used to distinguish type of server (1 - ST 32bit, 2 - MT 32bit, 3 - ST 64bit, 4 - MT 64bit)
• build number of the server
• encryption (0 for none, 1 for optional, 2 for required)
• corresponding HTTPS port or 0 if HTTPS is not supported
• data token (changed by every write operation), e.g.:
  =PALO.SERVER_INFO("localhost")

PALO.USERGROUPS(Server)

This function lists all user groups of the user of the connection "Server", e.g.:
=PALO.USERGROUPS("localhost")