To paste a data function into your worksheet using Jedox Excel Add-in, select a cell and click on Paste Data Function in the Jedox Ribbon (see image below). If you are using Jedox Web, go through the Query menu item.
With this command you can paste one of the following functions:
- PALO.DATA
- PALO.DATAC
- PALO.DATAX (only available in Jedox Excel Add-In)
- PALO.DATAV*
- PALO.SETDATA*
*Note: these functions are only available in the Paste Data Function dialog of Jedox Excel Add-in. In Jedox Web spreadsheets, these functions are not available. They must be entered directly in the formula bar. For more information, see Jedox / PALO OLAP Functions.
The PALO functions can display a cube cell value in a spreadsheet cell or write back a spreadsheet cell value into a cube cell.
The dialog looks like this:
First, you have to select the database at the top of the dialog. All cubes within the selected database are listed below.
If you use Jedox Web dialogs, all databases and cubes are ordered alphabetically.
Select the cube you want to address and choose a data function.
Click on Paste to insert the function into the selected spreadsheet cell.
Default arguments
By default the first element of each dimension is selected:
In this example, the selected cube has ten dimensions. Thus, the PALO.DATAC function has 12 arguments:
- Connection = “localhost/Demo”
- Cube name = “Sales”
- First element of the dimension “Products” = “All Products”
- First element of the dimension “Regions” = “Europe”
- First element of the dimension “Days” = “All Days”
- First element of the dimension “Months” = “Year”
- First element of the dimension “Years” = “All Years”
- First element of the dimension “Chart Type” = “All Chart Types”
- First element of the dimension “Market” = “Share”
- First element of the dimension “Stock” = “Open”
- First element of the dimension “Versions” = “All Versions”
- First element of the dimension “Measures” = “Units”
In the spreadsheet cell you get back the correspondent value, which in this case is 0.
In Excel Add-in, the formula uses PALO.EL syntax:
Guess arguments
To avoid the default behavior of selecting the first element of each dimension, you can use the option “Guess Arguments”, which is available as a checkbox in the Paste Data Function dialog:
To use the option “Guess Arguments” you have to provide element names of the dimensions in the sheet. In this case the function takes the cell references instead of the first elements of the dimensions. The example below shows the syntax in Jedox Web:
In Excel Add-in, the syntax uses the PALO.EL formula:
In addition, the element of the dimension “Years” was not found. There is also the first element (“All Years”) used. That’s because the searching area for "Guess Arguments" only contains (beginning with A1) the spreadsheet cells above and to the left of the active cell.
Element names that are found in the same row or column are interpreted as row or column titles and the way they are fixed ($ in cell address) adjusted accordingly, e.g. $B6 and $F$2, but also C$2. The last one is wrong in this case and has to be fixed manually also as absolute address before you copy the function to other cells:
For copying, the arguments must be changed to:
For function PALO.SETDATA, the first argument is not affected by Guess Arguments. It must be set manually.
Skip filled cells
You can select "Skip filled cells" to not overwrite data still existing in some cells of an area that you have selected:
See also:
Offline Mode for Excel Workbooks
Updated September 27, 2022