Jedox / PALO OLAP Functions

Jedox software includes standard worksheet functions, subset filter functions (labeled with ¹⁾), and array functions (labeled with ²⁾). After the description of all PALO functions, you can find a list of possible parameters and general information on subset filter functions and array functions.

PALO functions

PALO.AFILTER() ¹⁾

This filter removes elements that do not match defined attribute patterns.

The attribute pattern passed as first argument is an array that contains information about the filter expressions to be applied for each attribute. It has to consist of at least two rows: the first row specifying at least one existing attribute, and the second row consisting of a filtering expression for that attribute. Each column in the array represents one attribute, plus filtering expressions. Expressions for various attributes in one row are combined with the logical operator AND.

You can include more than one row with filtering expressions in the array. The filters are then combined using the logical (inclusive) operator OR, meaning that elements matching either the expression from the first, or (also) the second row will be included.

For example, the array below (two rows, two columns) defines a filter on a dimension with attributes "Name" and (numeric) "Price", and includes all elements in the result where the Name includes the string "Off-Road Bike" and the Price is greater than 500:

{"Name","Price";"Off-Road Bike",">500"}

Note: For formulas, you must use straight quotation marks.

A logical operator AND for two filter expressions on the same attribute can be achieved by setting multiple columns for the attribute in the array. The array on the right returns all elements where the "Price" attribute is greater than 500 AND smaller than 1000:

{"Price","Price";">500","<1000"}

A wildcard pattern is a sequence of characters that defines a search pattern; only “*” and “?” can be used as wildcards.

If PERL regular expressions are used, signs such as “[” or “$” have an impact.

Examples of PERL regular expressions:

Note: When passing values from a spreadsheet range to the PALO.AFILTER subset in Excel, ensure that for empty values and attributes of type "string", an empty string is explicitly set in the source range, instead of having only an empty cell, for example by setting the expression ="".

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.CONNECTIONUSER(Server)

Available in Jedox 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.COORD()

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

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.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.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 coordinates. If the argument of the function is an empty string instead of a coordinate, the result is an empty string.

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

Returns the average value of the cells in the specified cell range whose 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))

Helper functions: 

• PALO.AGR.CHILDREN()
• PALO.AGR.LEAVES()
• PALO.AGR.SELF()

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

Returns the number of cells of the specified range whose 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))

Helper functions: 

• PALO.AGR.CHILDREN()
• PALO.AGR.LEAVES()
• PALO.AGR.SELF()

See also PALO.EXPANDTYPE.

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

Returns the maximum of the cells of the specified range of cells whose 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.

For more information about PALO.EXPANDTYPE, see PALO.DATA.AVG.

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

Returns the minimum of the cells of the specified range of cells whose 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 0, then the output is 0.

For more information about PALO.EXPANDTYPE, see PALO.DATA.AVG.

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))

For more information about PALO.EXPANDTYPE, see PALO.DATA.AVG.

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 example returns the same value as
=PALO.DATA("localhost/Demo","Sales","All Products","Europe","Year","2015","Actual","Units")

PALO.DATAA(Server/Database,Cube, CoordinateArray)

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, CoordinateArray)

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 Jedox Excel Add-in.

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

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.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 Jedox Excel Add-in and Jedox Web.

This function was developed for use with Jedox 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 has to be present at all times, 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 problem would be 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 can not be used inside of cyclic calculations. Also, 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 ore 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 also not 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 amount 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 Jedox 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. Use of PALO.DATAV is only possible in related cell areas. A maximum of two arrays can be used in PALO.DATAV.

PALO.DATAX(Server/Database,Cube, coordinate1, coordinate2,… coordinate16)

This function is only available for Jedox Excel Add-in.

This function, which is available only in Jedox Excel Add-in, returns the value of the described element from the cube if the corresponding cell is on the currently active worksheet.
Note: This function corresponds to the PALO.DATAC function. However, the computation is limited to the currently worksheet. This speeds up the computation, because it is no longer necessary to compute the whole workbook. If, however, you switch to a different worksheet and later come back to the worksheet containing the PALO.DATAX formulas, you may have to manually trigger a re-computation of the worksheet. This step will always be necessary if calling the worksheet returns "#VALUE!" instead of a value.

PALO.DFILTER()¹⁾

Calculates either a numeric value or a string value for each of the elements of the subset. In the case of numeric values, a slice can be defined for each of the elements of the subset. The slice is always the same except for the element itself. Now you can do operations using the cell values within the slices, such as Total Sum, Maximum, or Minimum based on all cells. This results in a value for each of these elements.

The elements can now be filtered based on their values, whereby operations such as "<100" will be applied.
The filtering is similar for string values, e.g. you could filter by applying ">w", whereby lexicographical rules apply. However, the slice may contain only one cell. The reason is obvious: you cannot summarize strings.

You can use multiple DFilters in one subset. They all have to filter for the same dimension, but can run on different subcubes. By default, the result sets of all DFilters are combined using OR. All elements that are part of either DFilter result will be in the final result set. This behavior can be changed by setting the eighth parameter of the DFilter function (see below).

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 database “Demo” which 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.EADD(Server/Database, Dimension, Type, Element, Parent Element, Weight, Clear, Error suppr.)

Adds the described dimension element or structures to a database, e.g.:
=PALO.EADD("localhost/Demo","Years","n","2015","",1,FALSE) – consolidated element = BLANK here =PALO.EADD("localhost/Demo","Months","c","Qtr.1","Year",1,FALSE)

Possible values for Clear are 0 (or FALSE), 1 (or TRUE) and 2

• 0: Removes no elements of the dimension before importing
• 1: Removes all elements of the existing dimension before importing
• 2: Removes all existing elements of the C-dimension before importing. Basic elements are not deleted.

Note 1: make sure that all PALO.EADD functions referring to the same dimension use identical parameters (true or false). Otherwise, all PALO.EADD functions will execute that parameter for the complete dimension that was found first. Reason: the sequence in which Excel formulas are processed cannot be determined. During an import the Delete command will only be executed once with the first PALO.EADD () and then it will be ignored.

Note 2: in Jedox Excel Add-in, the PALO.EADD function does not execute by default. To enable it, put the formula PALO.ENABLE_LOOP(TRUE) inside a VBA macro, and execute it before calculating the sheet. Once the VBA macro is executed, the EADD function executes, and continues to run until the argument of the formula is either changed to FALSE, or Excel is restarted.

The VBA macro codes you can use are:

Private Sub EnableLoop()
    Dim Result As Variant
    Result = Application.Run("PALO.ENABLE_LOOP", True)
End Sub

Private Sub DisableLoop()
    Dim Result As Variant
    Result = Application.Run("PALO.ENABLE_LOOP", False)
End Sub

It is also possible to use PALO.ENABLE_LOOP(TRUE) in a spreadsheet cell (without using a VBA macro), but then the correct execution order cannot be guaranteed because of the constraints in Excel.

PALO.EALIAS(Server/Database, Dimension, Attribute, Alias, Index)²⁾

Lists all elements with the matching alias, in an area you define in advance.

In the database "Demo", the attribute "Color" was created for "Products", and nine products have received the attribute "black" in the following examples:

=PALO.EALIAS("localhost/Demo","Products","Color","black")

displays these nine products.

=PALO.EALIAS("localhost/Demo","Products","Color","black", 3)

displays the 3rd of the nine products

=PALO.EALIAS("localhost/Demo","Products","Color","black", 10) displays again the nine products, because the index 10 does not exist.

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.ECHILD(Server/Database, Dimension, Element, Index, Error suppr.)

Returns the name of the specified child element, e.g.:

=PALO.ECHILD("localhost/Demo","Regions","South",3) returns "Spain".

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

Returns the number of children in the described consolidated element, e.g.:

=PALO.ECHILDCOUNT("localhost/Demo","Regions","South") returns "3".

PALO.ECOUNT(Server/Database, Dimension, Error suppr.)

Returns the total number of elements in the described dimension, e.g.:
=PALO.ECOUNT("localhost/Demo","Years") returns "9".

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

Deletes the described element, e.g.:

=PALO.EDELETE("localhost/Demo","Products","Subnote XK")

Note: with Excel Add-in the function PALO.EDELETE can only be used in connection with an import, because there the deletion is controlled via the import.

PALO.EEXISTS(Server/Database, Dimensionname, Elementname)

Returns TRUE, if element exists, else FALSE. E.g.:

=PALO.EEXISTS("localhost/Demo","Regions","Spain")

PALO.EFIRST(Server/Database, Dimension, Error suppr.)

Returns the first element in the described dimension, e.g.:

=PALO.EFIRST("localhost/Demo","Regions") returns "Germany".

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

Returns the hierarchy level of an element within the dimension, e.g.:

=PALO.EINDENT("localhost/Demo","Regions","Italy") returns "3".

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

Returns the position of the specified element in the dimension, e.g.:

=PALO.EINDEX("localhost/Demo","Regions","France") returns "2".

PALO.EISCHILD(Server/Database, Dimension, Parent Element, Element...)

Checks whether a consolidated element contains the described element, e.g.:

=PALO.EISCHILD("localhost/Demo","Regions","West","Germany")

returns TRUE.

PALO.EL()
PALO.ELALL()

Both functions are helper functions, related to flexible cube layout. They are used within other PALO functions, such as PALO.DATA*(), to specify not just an element name or array of elements, but also to specify a dimension name with it. 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 has to be used for all coordinate arguments. You can not mix the old, simple element coordinate syntax with usage of PALO.EL().

When using the PALO.EL() formulas, the order in which coordinates are specified then 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.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 an specified element, also the specified element itself, in a predefined area, e.g.:

=PALO.ELEMENT_LIST_PARENTS("localhost/Demo","Months","Qtr.1")

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

Returns the number of hierarchy levels that follow after the element within the dimension, e.g.:

=PALO.ELEVEL("localhost/Demo","Regions","Germany") returns "0". =PALO.ELEVEL("localhost/Demo","Regions","West") returns "1".

PALO.ENAME(Server/Database, Dimension, Index)

Returns the name of the element of a specific position (first position is 1), e.g.:

=PALO.ENAME("localhost/Demo","Regions",2) returns "France".

Note: to display or select aliases in a PALO.ENAME formula on protected Excel sheets, it is necessary to allow changing cell formats. Displaying or selecting element names without alias usage works even if cell format cannot be changed by users.

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

Returns the name of the subsequent element within the dimension, e.g.:

=PALO.ENEXT("localhost/Demo","Regions","Italy") returns "Portugal".

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

Returns the name of the n^th parent of the specified element, e.g.:

=PALO.EPARENT("localhost/Demo","Regions","Italy",1)

returns "South".

=PALO.EPARENT("localhost/Demo","Regions","Italy",2)

returns "New Group", if “New Group” has been created before and “Italy” is consolidated in it.

Note: if an element occurs only in one consolidation, only the parameter "number" = "1" is possible. If an element occurs in several consolidations, the "Number" determines which consolidated element is returned.

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

Returns the number of consolidated elements in which the described element is contained, e.g.:

=PALO.EPARENTCOUNT("localhost/Demo","Regions","Italy")

returns "1".

=PALO.EPARENTCOUNT("localhost/Demo","Regions","Italy",2)

returns "2", if "New Group" has been created before and "Italy" is consolidated in it.

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

Returns the name of the preceding base element, e.g.:

=PALO.EPREV("localhost/Demo","Regions","Italy") returns "Austria".

PALO.ERENAME(Server/Database, Dimension, Old Name, New Name, Error suppr.)

Renames the described element, e.g.:

=PALO.ERENAME("localhost/Demo","Products","Notebook TT","Notebook TS")

Note: the function PALO.ERENAME can only be used in connection with an import, because the renaming is controlled via the import.

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)

Note: this function is available only in Jedox Excel Add-in.

PALO.ESELECT(Server/Database, Dimname, Element, Subfunction, Subsetname, Subsettype, Alias, Aliasformat)

Returns the name or the alias of an element from a dimension or subset. This function allows specifying a stored subset, which is used to filter element lists shown in the Select Element dialog. Additionally, it allows specifying an Alias and an Alias format. Long element lists on specific dimension levels are displayed in paged mode. The search in both Paste Elements dialog and Select Element dialog will search across the full dimension.

In Jedox Web, the PALO.ESELECT function will implicitly set internal properties on the cell to handle user clicks on the cell and show the Element Selection dialog. To unset these internal properties, when you want to change the cell content from the PALO.ESELECT function to some other formula or static content, you must delete the entire cell content once, by either hitting the DEL key while the cell is selected, or by selecting Clear > Clear contents from the Design menu. Styles do not have to be removed.

The function's arguments are explained below:

Example:

=PALO.ESELECT("Localhost/Biker","Months","Qtr.1\Mar",0,"Calendar",TRUE,"deutsch",2)

Result: Mar - Mrz

The ESELECT function's primary use is to offer an element picker which is shown when a report user double-clicks a cell that holds an ESELECT function. It is not intended for programmatic usage of attribute values, for example within other spreadsheet functions like IF() or CONCATENATE(). In these cases, a PALO.DATA or DATAC function pointing to the attribute cube should be used.

Note: when using references for parts of PALO.ESELECT() formula, the element picker will overwrite references with static values when a user changes the element selection in the picker dialog.

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

Returns a sibling element of the specified element. The sibling, which should be returned, must be specified with the index number. E.g.:

=PALO.ESIBLING("localhost/Demo","Regions","Portugal",1)

returns "Spain".

=PALO.ESIBLING("localhost/Demo","Regions","Portugal",-1)

returns "Italy".

This function can also be used to verify whether an element exists in a dimension by entering the name of the element and the position "0". If the element exists, the name of the element is returned; if not, "# Name?" is returned, e.g.:

=PALO.ESIBLING("localhost/Demo","Regions","Italy",0)

returns "Italy".

=PALO.ESIBLING("localhost/Demo","Regions","Greece", 0)

returns "#Name?".

PALO.ETOPLEVEL(Server/Database,Dimension)

Returns the number of hierarchy levels in a dimension, e.g.:
=PALO.ETOPLEVEL("localhost/Demo","Regions") returns "2".

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

Returns the type of the described element (numeric, string, or consolidated), e.g.:

=PALO.ETYPE("localhost/Demo","Regions","Italy")

returns "numeric".

PALO.EUPDATE(Server/Database, Dimension, Element, Type, C-Elements, Append)

Updates an element after the specified data. To demonstrate this function here a nonsense example:
=PALO.EUPDATE=("localhost/demo","Months","Jan","C",{"Feb",1,"Jun",2})

After this import command "Jan" is a C element, consolidated with 1xFeb and 2xJun. A correction of the last example would be the following import:
=PALO.EUPDATE("localhost/Demo","Months","Jan","N",{0,0})

PALO.EWEIGHT(Server/Database, Dimension, Parent, Name of child, Error suppr.)
Returns the weighting factor of the described element at consolidation, e.g.:
=PALO.EWEIGHT("localhost/Demo","Regions","West","Germany") returns "1".

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 0, then the output is 0.

PALO.GETELEMENT

Returns the name of the element. The argument must be an Excel cell containing a PALO.ENAME or a PALO.SELECT formula, e.g.:

PALO.GETELEMENT(A1) will display "Year" if in A1 the following formula is inserted in the cell A1: =PALO.ESELECT("localhost/Biker";"Months";"Year";0;;;"deutsch";0).

PALO.HFILTER() ¹⁾

Defines hierarchically a subset with criteria relative to 1. ELEMENT or 2. REVOLVE_ELEMENT. Case 1 and Case 2 should not be combined.

PALO.LOGIN_TEST(Server, Username, Password)

Verifies the username and password combination.

PALO.PICKLIST() ¹⁾

With PALO.PICKLIST(), you can either add elements to the subset that cannot be removed, or you can determine a set of elements as pre-selection, i.e., only these elements will be passed on to further filters of the subset.

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.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)-

Lists the following databases after a default installation: System (system), Demo (normal), Biker (normal), Config (userinfo), fgrp1(userinfo), fgrp2(userinfo), rgrp1(userinfo), rgrp2(userinfo), pkgs (userinfo).

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.SCC

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

PALO.SERVER_INFO(Server, Show counters)²⁾

Available in Jedox 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, 5 - GPU 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.SERVER_SUBSET(Server/Database, Dimension, Global, Subsetname, Variable, Variable,…) ²⁾

Returns a subset of dimension elements.
Variables passed in the function itself are accepted. The values offer the variables can be passed via the function PALO.SERVER_SUBSET directly, as pairs of arguments for each variable.

The following example retrieves elements for a stored subset named "base elements" on dimension "Months", and passes the value "German" for the variable "alias":

=PALO.SERVER_SUBSET("localhost/Demo","Months",TRUE,"base elements","alias","German")

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.SETDATA_BULK(Server/Database,Cube,Cells,Values,Splashmode,Wait)

Available in Jedox 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.

Splashmodes:

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.SETDATAA(Value, Splash, Server/Database, Cube, Coordinate Array, Locking Area, SVS_Trigger, Wait)

This function is only available for Jedox 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: 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 "Destop 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 Jedox Excel Add-in.

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

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 as PALO.SETDATA(). If the condition is FALSE, nothing happens.

PALO.SORT() ¹⁾

This is a structuring filter. It can, for example, sort by the order of definition, by data values, by element names, by alias names or by the order of consolidations. In addition, it can produce a hierarchical order, which takes parallel hierarchies in the subset into account.

Without the sorting filter, the elements are displayed in the order of definition, as in the unordered list view in Modeler.

PALO.SUBCUBE() ¹⁾

This function is an essential selection criterion when using DFilter. The arguments are the name of the cube and the dimensions excluding the dimension that is covered by the subset.
The elements are entered in the sequence in which the dimensions are defined in the cube. The place in the dimension that is already determined by the subset remains empty.

PALO.SUBSET()¹⁾

The principal structure of this function is:

=PALO.SUBSET(Server/Database,Dimension,Indent,Alias,Filter1,Filter2,Filter3,Filter4,Filter5,Filter6).

The function PALO.SUBSET() is the basic filter required by all the other filters to display data. Filters can have an impact on a subset in three different ways:

1. Restricting, by removing elements
2. Structuring, by changing the order and possibly the frequency of the elements, or by directly adding elements
3. Indirectly, by changing the behavior of other filters

The function PALO.SUBSET() has the following arguments:

PALO.SUBSETSIZE(Server/Database,Dimension,Indent,Alias,Filter1,Filter2,...)

Returns the number of dimension elements of the subset.

The arguments are the same as of the function PALO.SUBSET().

PALO.SUBSETX(Server/Database,Dimension,Indent,Alias,Filter1,Filter2,...)

This function is equal to PALO.SUBSET() with one difference: it must be called from active sheet. This function is only available in Jedox Excel Add-in.

PALO.TFILTER()¹⁾

Removes elements from a selection when they do not match defined Elementname or Alias patterns.

A wildcard pattern is a sequence of characters that defines a search pattern and only “*” and “?” can be used as wildcards.

PALO.USERGROUPS(Server) ²⁾

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

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 view from a full XML View definition specified in the function. This function is only available in Jedox Web.

Parameters

Notes:
You might have to replace the comma (,) with a semicolon (;) and the apostrophe (‘) with quotation marks ("). This depends on country settings.
If an argument remains blank in a formula, two quotation marks ("") are normally set to generate a blank string.

Subset filter functions

The filter functions PALO.HFILTER(), PALO.TFILTER(), PALO.PICKLIST(), PALO.AFILTER(), PALO.DFILTER(), and PALO.SORT() are sub-functions of PALO.SUBSET (). They do not work independently, but need PALO.SUBSET() as the basic function. In addition, the function PALO.SUBCUBE() is required when using PALO.DFILTER().

Behavior of filter functions:

The listed filter functions have a restricting and/or structuring effect on the elements of the dimension that is selected in PALO.SUBSET().

Hierarchy filter PALO.HFILTER()

This filter is both restricting as well as structuring. It is restricting in that it defines a subset within the hierarchy of a dimension (e.g. all elements below a specific element). It is structuring in that it revolves the structure of the subset, meaning that the selected elements will be repeated until a defined number of elements has been reached. 

Text filter PALO.TFILTER()

This restricting filter removes elements from a selection that do not match defined element name or alias patterns. If an alias for the element exists, the third function argument specifies whether the alias will be used for the search.

Picklist filter PALO.PICKLIST()

With the picklist filter you can do one of two things:

1. add elements directly, so that they cannot be removed from the subset by any means.
2. pre-select elements, whereby the remaining filters of the subset will only be applied to the selected elements. In this case Picklist filter is restrictive. 

Attribute filter PALO.AFILTER()

This restrictive filter eliminates elements from a selection if they don’t match defined attribute patterns. 

Data filter PALO.DFILTER()

The data filter is both restrictive and structuring. For each element, a data value will be calculated. If the data value meets specific criteria, the element will be retained; otherwise it will be dismissed. The element will then be sorted by the calculated data values using the sorting filter. 

Sorting filter PALO.SORT()

This structuring filter determines the sequence of the elements in the subset (e.g. sorting by data value, alphabetically, or by alias). If the sorting filter is not used, the elements will be displayed in the order of creation.

Sequence of execution:

First, the restricting filters will be applied. If the “Preselecting” argument is chosen in the picklist filter, only the selected elements will be passed on to the further filtering of the subset. Otherwise all elements of the chosen dimension will be passed on. for filters with both restricting and structuring characteristics, only the restricting part will be executed. The order of execution is irrelevant, because elements are merely removed from the selection.

Next, the structuring filters will be applied. There is an exactly defined sequence that results inevitably from the filters:

• At first, there is a list of elements that have not been eliminated by the previous filters. They appear in the sequence in which they are defined in the Modeler.
• If the argument “Insert” has been chosen in the function PALO.PICKLIST(), its elements will be inserted in the order of creation.
• If PALO.SORT() has been specified with arguments, then its instructions will be executed at this point.
• If the argument “Insert before the list” or “Insert after the list” has been chosen in the function PALO.PICKLIST(), this instruction will be executed next.
• Finally, the revolving arguments of the hierarchical filter will be executed.

Array functions

The output of an array function normally needs more than one cell.

To activate an array function:

1. Create the array function with the function wizard in the selected cell.
2. Copy the function to the area in which you can expect an output of the function.
3. Highlight this area.
4. Click in the formula bar at the end of the function.
5. Press Ctrl + Shift + Enter.

To deactivate an array function:

1. Highlight the whole area of the array function.
2. Click in the formula bar at the end of the function.
3. Press Ctrl + Enter.

Updated September 27, 2022