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.
Variants for defining element arguments
In all PALO.DATA*() functions, as well as PALO.SETDATA*() functions, element names can be passed directly as arguments. However, an extended syntax is also possible, where instead of specifying element names, the PALO.EL() function is used, which will additionally specify dimension names. This safe-guards the PALO:DATA*() functions against later changes to the cube layout, especially changes in the order of dimensions in the cube.
For more information, see Jedox / PALO OLAP Functions.
PALO.DATA
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
When PALO.DATAC() is used, the spreadsheet engine (Jedox Excel Add-in or Jedox Web) first collects all PALO.DATAC functions that are pointing to the same cube in the current application and then retrieves the values in a single request. PALO.DATAC() offers superior performance compared to PALO.DATA() and should be used in most cases. However, its calculation mechanism implies a few constraints.
- In Jedox Excel Add-in, a PALO.DATAC() function cannot be referenced from other PALO.* functions. For example, when using a PALO.DATAC() function in cell A1, you cannot use another PALO.* function in a different cell that in turn references the cell A1.
- In Jedox Web, the usage above is possible. The only unsupported calculation would be using a PALO.DATAC() function directly within another PALO.DATAC() function, such as =PALO.DATAC(PALO.DATAC(...),...), but the usage via a cell reference is possible. Furthermore, you cannot use PALO.DATAC() in any cyclic calculations in Jedox Web.
PALO.DATAX
(only available in Jedox Excel Add-In)
Returns the value of the described element from the cube if the corresponding cell is on the currently active work sheet.This speeds up the computation by avoiding a recalculation of 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.
PALO.DATAV
Returns the value of a data cell from a cube when you use an Excel array formula. This function works even faster than PALO.DATAC, because an array formula is generated for the complete area. Use of PALO.DATAV is only possible in connected cell areas. A maximum of two arrays can be used in PALO.DATAV.
This function allows only reading access, no writing back. As an array function, it controls several cells simultaneously.
PALO.SETDATA
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.
Updated June 5, 2023