Jedox Data Functions Overview
Jedox software includes standard spreadsheet functions, Subset filter functions, and array functions. Here you find the functions classified by their intended use, followed by a list of possible parameters.
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():
- 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.
- 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.
- 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().
1=SELF (All Products) | 4=LEAFS (Base cells of Europe: 19 countries) |
2=CHILDREN (Children from Year: Qtr.1,Qtr.2,Qtr.3,Qtr.4) | Other coordinates have the default value 1 (=SELF) |
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:
0=No splashing | 1= Standard splashing (splash-Parameter #) |
2= Set value to base cells (splash-Parameter !) | 3= Add value to base cells (splash-Parameter !!) |
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.Subset()
The main 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:
- Restricting by removing elements.
- Structuring by changing the order and possibly the frequency of the elements, or by directly adding elements.
- Indirectly by changing the behavior of other filters.
The function PALO.Subset() has the following arguments:
Argument | Form | Description |
Server/Database | Text | localhost/Demo or A1 (the cell which contains the entry). |
Dimension | Text | Products or A2 (the cell that contains the name of the dimension). |
Indent | Number | The allowed values are 1 (equivalent to empty), 2, 3, and 4. The argument alters the numbering of the hierarchy level, which in turn affects the Subset, where the hierarchy level is specified in the filter. Indent 1: The elements of the highest hierarchy level are assigned the number 1, the second highest level the number 2, and so on. Indent 2: The elements in the lowest hierarchy level (base elements) are assigned the number 0. The number is incremented by 1 for every step up in the hierarchy. Indent 3: The elements of the highest hierarchy level are assigned the number 0, the second highest level the number 1, and so on. Indent 4: This number dynamically adjusts the indent of elements in the Subset based on the overall Subset results. See the description of Indent 4 for details. |
Alias | Text/Array | Contains at most two aliases: {"Alias1","Alias2"} or "Alias". Alternatively, it is possible to use a reference to the cell, e.g. {A1} or {A1:A2} . When using "Alias", the sorting filters do not use the original names of the elements for their operations but rather the aliases defined in the attribute cube. If empty, the aliases are disabled. If you want to paste a Subset with a defined alias parameter, you must select at least two columns as a range in order to see the alias. If you select three, the indent will be displayed in the first column. |
Filter | Filter name() | Allowed are PALO.HFILTER(), PALO.TFILTER(), PALO.AFILTER(), PALO.DFILTER(), PALO.PICKLIST(), and PALO.SORT(). |
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.SUBCUBE()
This function is required to specify the area of a Subset data filter. For more information, see PALO.DFILTER().
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 invoked from the active sheet. This function is available only in Excel Add-in.
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().
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. It defines hierarchically a Subset with criteria relative to 1. ELEMENT or 2. REVOLVE_ELEMENT. Case 1 and Case 2 should not be combined.
Argument | Form | Description |
Element | Element name | Activates the selection of elements that are above or below "Element" within the dimension hierarchy. |
Above/Below |
0, FALSE or Empty |
This argument can only be used in combination with "Element". If argument is 1 or TRUE, the elements that are above the "Element" within the hierarchy are selected. If argument is 0, FALSE or Empty, the elements below are selected. Above / below means that the elements must be direct or indirect children or parents of "Element". If the argument is 2, the siblings of "Element" are selected. In parallel hierarchies, when "Element" is not specified with path, siblings for all parents are returned. |
Exclusive | TRUE/FALSE | This argument can only be used in combination with "Element". TRUE = Do not show the element itself, i.e., the element is indicated in "Element". Empty or FALSE = Show the element. |
Hide | Number | This argument can only be used in combination with "Element". It hides either the base elements (HIDE = 1) or the consolidations (HIDE = 2). Empty = nothing will be hidden. |
Revolve element | Element name | This argument causes elements to be removed from the Subset if they are not on the same level as "Revolve element". Therefore, this argument should not be used in combination with "Element". Empty = None. "Revolve element" additionally requires the specification of "Revolve count". Otherwise, an error message will be returned. |
Revolve count | Number | Determines how long the revolving list should be. The length of the list is dictated by the number in "Revolve count". Empty = None. |
Revolve add | Number | Display elements at a lower level (=1) or at a higher level (=2). Here, the level fields of the elements assigned by the Jedox Server are used as criteria. "Revolve add" additionally requires the specification of "Revolve count". Otherwise, an error message will be returned. Empty = Nothing . |
Start level | Number | Elements will be removed if they do not meet the level entered in "Level start" at a minimum and the level in "End level" at a maximum. |
End level | Number | 2nd argument belonging to "Level start". |
Use relative level | FALSE or Empty TRUE |
Default value is FALSE. If TRUE, the "start" and "end" levels are not absolute to hierarchy, but relative to the "bound" element (first argument), that have level 0. If no bound element is selected, only top elements are returned. |
Text filter PALO.TFILTER()
This restricting filter removes elements from a selection that do not match defined Elementname or Alias patterns. If an alias for the element exists, the third function argument specifies whether the alias will be used for the search.
Argument | Form | Description |
Filt. definition | Text | Array which contains the definition of the element patterns. |
Extended |
Integer, |
Defines the filter behavior. Possible values are: 0, FALSE or Empty: Filter uses wildcard patterns for search, case sensitive. 1 or TRUE: Filter uses PERL regular expressions, case sensitive. 2: Filter uses PERL regular expressions, case insensitive. 3: Filter uses wildcard patterns for search, case insensitive. |
Not Alias | TRUE/FALSE | If FALSE (default), TFILTER will search on Alias if one is defined. Otherwise TFILTER will search on Elementname. If TRUE, TFILTER will search on Elementname even if an Alias is defined. |
A wildcard pattern is a sequence of characters that defines a search pattern and only *
and ?
can be used as wildcards.
Picklist filter 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 preselection, i.e., only these elements will be passed on to further filters of the Subset.
Argument | Form | Description |
Definition | Name of elements | Which elements belong to the pick list. The definition is essential to the pick list. |
Type | Number | 0 / Empty =Insert before the list, 1 =Insert after the list, 2 = insert into the list, 3 = As preselection for the Subset. |
Attribute filter PALO.AFILTER()
This filter removes elements that do not match defined attribute patterns.
Argument | Form | Description |
Filt. definition | Text Array | Array containing the definition of the attribute patterns. Supported operators for numeric criteria are: <, >, =, <=, >=, <> |
Type | Integer, TRUE, FALSE |
Defines the filter behavior. Possible values are:
|
Translate array | Array | An array that contains TRUE or FALSE values. |
The attribute pattern passed as first argument is an array that contains information about the filter expressions to be applied for each attribute. It must 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 within the array. The filters are then combined with the logical (inclusive) operator OR, i.e., elements that match either the expression from the first line or the second line 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:
^(Jan|Feb|Nov)$
|
Matches the attributes "Jan", "Feb" or "Nov". |
^J
|
Matches all the attributes starting with a "J". |
[sd]
|
Matches all the attributes with "s" or "d" in the attribute name. |
n$
|
Matches all the attributes ending in "n" |
^X.*
|
Matches all the attributes starting with an X and followed by an arbitrary number of characters. |
(XP)+
|
Matches all the attributes in which "XP" occurs at least once. |
Note: when passing values from a spreadsheet area to the PALO.AFILTER Subset in Excel, make sure that an empty string is explicitly set for empty values and attributes of type "string" in the source area, instead of just having an empty cell, e.g. by setting the expression =""
.
Data filter 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. You can perform 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 be filtered based on their values, whereby operations such as <100
will be applied.
The filtering is similar for string values, e.g. you can filter by applying >w
, whereby lexicographical rules apply. However, the slice may contain only one cell. The reason is that you cannot summarize strings.
You can use multiple DFilters in one Subset. They all must filter for the same dimension but can run on different sub-cubes. 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 result set. This behavior can be changed by setting the eighth parameter of the DFilter function (see below).
Argument | Form | Description |
PALO.SUBCUBE() | Function | PALO.SUBCUBE 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 which is already determined by the Subset remains empty. |
Operations | See examples: |
Checks whether the values determined for the elements satisfy the criteria. |
Top | Number | This is another possible argument. In case the integer value TOP is passed, only a few TOP elements with the highest values are displayed. If the argument is left empty, the elements will not be restricted. |
Upper percentage | Number | If a numeric value between 1 and 99 (inclusive) is passed, then only the largest elements are selected (whose total value reaches the exact limit or whose total value is closest to it) . The selection starts at the largest element, then adds the second largest, and so on. The selection ends when the total sum is closest to the limit. |
Lower percentage | Number |
If a numeric value between 1 and 99 (inclusive) is entered here, then only the smallest elements (whose total value reaches the exact limit or whose total value is closest to it) are selected. The selection starts at the smallest element, then adds the second smallest, and so on. The selection ends when the total sum is closest to the limit. |
Cell Operator | Number | If a value is entered, the respective operator will be applied to each slice. For example, you can calculate the sum, minimum, or maximum of the cells in the slice. 6 = STRING expects strings to be contained in the cells. For slice use 1. Operators: 0 / Empty = SUM, 1 = ALL, 2 = AVERAGE, 3 = MAXIMUM, 4 = ANY, 5 = MINIMUM, 6 = TEXT Elements are returned – not the values. |
No Rules | TRUE/FALSE | FALSE / Empty = enterprise rules are computed for DFilters. TRUE = enterprise rules are not computed for DFilters. |
Use AND | TRUE/FALSE |
Combining two DFilters, the second DFilter must have a TRUE, FALSE, or an empty variable.
Example:
|
Sorting filter PALO.SORT()
This is a structuring filter. For example, it can 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 their definition, as they are in the unordered list view in Modeler.
Argument | Form | Description |
Whole | Number | 0 / Empty = Order of definition. 1 = Builds a hierarchic order and shows the children of elements that have been removed from the Subset. It is a prerequisite that the children are contained in the Subset. 2 = Will not show the children but will cut the View at those positions. |
Criteria | Number | Sort by 0 / Empty = Definition, 1 = Value, 2 = Elementname, 3 = Alias, 4 = Consolidation (Order of the elements in the hierarchical view in Modeler). This argument should not be used in combination with "Attribute". |
Attribute | Text | Does not sort by Aliases if defined, but by the terms defined by the attribute. Note that this argument should not be used in combination with "Criteria"! |
Type limitation | Number | 0 / Empty = No impact 1 = Sorts base elements only 2 = Sorts consolidations only To do a sort, it is necessary to indicate the sort criteria. Therefore, this argument works only in connection with the arguments "Whole" and "Criteria". |
Level element | Text | If not Empty, sorting takes place only at the level of this element. To sort, it is necessary to indicate the sort criteria. Therefore, this argument works only in connection with the arguments "Whole" and "Criteria". |
Reverse | Number |
0 / Empty = Without effect. 1 or 3 as argument only works in conjunction with "Whole <> 0 / Empty". |
Show duplicates | Number | 0 / Empty = Don’t display duplicates 1 = Display duplicates |
Number of elem. | Number | Number of returned elements. |
Start with | Number | Element position with which the display of elements begins (element 1 has element position 0, element 2 has 1, and so on.). |
Element path | Number |
0: Internal Subset result will not contain element paths (default behavior) 1: Subset result will contain full element path using element ID's 2: Subset result will contain full element path using element names Especially in Subsets using dynamic indent, transferring the paths in the internal Subset result can improve the generated Subset result structure. As the usage of paths will generate a slightly larger amount of transferred data, thus impacting the performance, values 1 or 2 should only be used when required. |
Sequence of execution:
The restrictive filters are applied at first. 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 that have both restricting and structuring characteristics, only the restricting part is executed. The order of execution is irrelevant, since only elements are removed from the selection.
Next, the structuring filters are 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.
PALO.EEXISTS(Server/Database,Dimensionname,Elementname)
Returns TRUE if element exists, else FALSE, e.g.:
=PALO.EEXISTS("localhost/Demo","Regions","Spain")
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.ETYPE(Server/Database,Dimension,Element,Error suppr.)
Argument | Form |
Server/Database | Text |
Dimension | Text |
Element | Text |
Error suppr. | Empty or FALSE: Error messages will not be suppressed. TRUE: Error messages will be suppressed. |
Returns the type of the described element (numeric, string, or consolidated), e.g.:
=PALO.ETYPE("localhost/Demo","Regions","Italy")
returns "numeric".
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.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.
Notes:
- 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.
- In Excel Add-in, the PALO.EADD function does not execute by default. To enable it, insert 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.EDELETE(Server/Database,Dimension,Element,Error suppr.)
Deletes the described element, e.g.:
=PALO.EDELETE("localhost/Demo","Products","Subnote XK")
The PALO.EDELETE function can be used in Excel Add-in only in connection with an import because there the deletion is controlled by the import.
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")
The function PALO.ERENAME can only be used in connection with an import because the renaming is controlled via the import.
PALO.EUPDATE(Server/Database,Dimension,Element,Type,C-Elements,Append)
Updates an element after the specified data. To demonstrate this function here a meaningless 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.ECHILD(Server/Database,Dimension,Element,Index,Error suppr.)
Returns the name of the specified child element, e.g. element maintenance functions:=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.ELEVEL(Server/Database,Dimension,Element,Error suppr.)
Returns the number of hierarchy levels that follow the element within the dimension, e.g.:=PALO.ELEVEL("localhost/Demo","Regions","Germany")
returns "0". =PALO.ELEVEL("localhost/Demo","Regions","West")
returns "1".
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 nth 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 already been created and "Italy" is consolidated in it.
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.ESIBLING(Server/Database,Dimension,Element,Index,Error suppr.)
Argument | Form |
Server/Database | Text |
Dimension | Text |
Element | Text |
Index | The element itself has the index 0, the sibling in the hierarchy view found directly below has the index 1, and so on. The sibling found directly above has the index -1 and so on. |
Error suppr. | Empty or FALSE: Error messages will not be suppressed. TRUE: Error messages will be suppressed. |
Returns a sibling element of the specified element. The sibling to be returned must be specified with the index number.
Examples:
=PALO.ESIBLING("localhost/Demo","Regions","Portugal",1)
returns "Spain".
=PALO.ESIBLING("localhost/Demo","Regions","Portugal",-1)
returns "Italy".
This function can be also used to verify if 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.EALIAS(Server/Database,Dimension,Attribute,Alias,Index)
Lists all elements with the matching alias within an area you define in advance.
In the "Demo" database, the "Color" attribute has been created for "Products", and nine products have been given the "Black" attribute 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.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.EFIRST(Server/Database,Dimension,Error suppr.)
Returns the first element in the described dimension, e.g.:=PALO.EFIRST("localhost/Demo","Regions")
returns "Germany".
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.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".
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.ESELECT(Server/Database,Dimname,Element,Sub-function,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:
Argument | Form | Description |
Server/Database | Text | Reference to server and database. |
Dimname | Text | Reference to dimension |
Element | Text | Reference to path for selected element. Parts of path are separated by backslash. |
Sub-function | Number | Behavior in Excel and in Jedox Web Spreadsheets (Designer mode): 0 / Empty: Double-click opens dialog “Select Element”, 1: Double-click opens cell entry for editing. Behavior in Jedox Web Spreadsheets (User mode, cell locked): Behavior in Jedox Web Spreadsheets (User mode, cell unlocked): The argument values 2 to 7 are used to control the usage of element-based formatting. The argument then defines whether the element should be treated as a POV element, a row header, or a column header; furthermore, the argument allows the control of the element selection on double-click on the cell. 2: POV element, enable Element selection on click 3: POV element, disable Element selection on click 4: Row header element, enable Element selection on click 5: Row header element, disable Element selection on click 6: Column header element, enable Element selection on click 7: Column header element, disable Element selection on click Depending on the chosen sub-function, the element-based formatting can then be enabled in the Cell Properties dialog. For example, when using the sub-function argument "2", the element-based formatting would be applied when enabling "Styles" for "Header" in the Cell Properties dialog. |
Subsetname | Text | Reference to Subset. If empty or missing then all elements are used. |
Subsettype | TRUE/FALSE | The type of the stored Subset: TRUE is global, FALSE is private. |
Alias | Text | Reference to attribute name. If empty or missing then element name will be used. If the Alias has a localized value in the current user's locale, that value will be returned. |
Aliasformat | Number | 0 : alias 1 : element name 2 : element name + " - " + alias 3 : alias + " - " + element name 4 : element name + " (" + alias + ")" 5 : alias + " (" + element name + ")" 6 : element name + " " + alias 7 : alias + " " + element name |
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.ETOPLEVEL(Server/Database,Dimension)
Returns the number of hierarchy levels in a dimension, e.g.:=PALO.ETOPLEVEL("localhost/Demo","Regions")
returns "2".
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.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])
Argument | Form |
Server/Database | Text |
Type | Empty: Lists all cubes or optional a number for the type of cubes:
|
Show permission | Optional: TRUE, FALSE(default) |
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])
Argument | Form |
Server/Database | Text |
Type |
Empty: Lists all dimensions
|
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., ....])
Argument | Form |
Server/Database | Text |
Show normal d. | Optional: TRUE (default), FALSE |
Show system d. | Optional: TRUE, FALSE (default) |
Show attribute d. | Optional: TRUE, FALSE (default) |
Show userinfo d. | Optional: TRUE, FALSE (default) |
Show permiss. d. | Optional: TRUE, FALSE (default) |
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])
Argument | Form |
Server/Database | Text |
Dimension | Text |
Type | Empty: Lists all cubes or optionally a number for the type of cubes: 0 = normal cubes, 1 = system cubes, 2 = attribute cubes, 3 = userinfo cubes |
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)
Argument | Form |
Server/Database | Text |
Dimension | Text |
Unused | Argument is not yet used. |
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)2)
Argument | Form |
Server/Database | Text |
Dimension | Text |
Element | Text |
Error suppr. | Empty or FALSE: Error messages will not be suppressed. TRUE: Error messages will be suppressed. |
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.)
Argument | Form |
Server/Database | Text |
Dimension | Text |
Element | Text |
Error suppr. | Empty or FALSE: Error messages will not be suppressed. TRUE: Error messages will be suppressed. |
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)
Argument | Form |
Server | Text |
List system | TRUE: system databases will be listed FALSE: system databases will not be listed |
List userinfo | TRUE: userinfo databases will be listed FALSE: userinfo databases will not be listed |
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:
Column Number | Content |
1 | The name of the database. |
2 |
The type of the database:
|
3 |
The status of the database:
|
4 | The number of dimensions. |
5 | The number of cubes. |
6 | The permission on the database (D, W, R, N) |
7 | The time needed for loading. |
8 | The memory usage. |
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")
Argument | Form |
connectionname
|
String |
dns or ip |
String |
port
|
Number |
username
|
String |
password
|
String |
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")
Parameters
Name | Possible entry |
Server/Database | A text entry (Name) such as "localhost/Demo" or a coordinate such as A1 |
Cube | A text entry (Name) such as "Sales" or a coordinate such as A2 |
Dimension | A text entry (Name) such as "Europe" or a coordinate such as A4 |
Element/Consolidated element | A text entry (Name) such as "Variance" or a coordinate such as B8 |
Coordinate | Can be an element |
Type | "n" for numeric, "s" for String/Text and "c" for consolidated |
Weighting factor | A number |
Delete | TRUE or FALSE (TRUE if existing entries should be deleted) |
Position of base element | A number |
Position | A number |
Number of consolidated elements | A number |
Old name, new name | Two coordinates such as A1,B1. The names to be changed are in the coordinates. |
Position of the sibling element | A number |
Value | The coordinate containing the value to be written. Instead of a coordinate such as A1, you can also use a formula such as A1*B1. This is described in the push rules. |
Splash | TRUE or FALSE (TRUE = write in consolidated element) |
Error | A coordinate, e.g. B10. This refers to the cell in which the corresponding PALO.SETDATA function is executed. |
Storage location (path) | A path such as C:/Documents and Setting/Own Files/Error log.txt. In this case the faulty data records of an import are saved in the "Error log.txt" file. |
Empty String | In the current version, various functions have been extended to accept the argument "Empty String". If you enter "TRUE" as actual argument here, a possible error message will not be displayed. Instead, the cell will be left empty! If you enter "TRUE" as actual argument here, a possible error message will not be displayed. Instead, the cell will be left empty! |
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 empty in a formula, two quotation marks ("") are normally set to generate an empty string.
Updated November 5, 2024