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.DATAX (only available in Jedox Excel Add-In)
*Note: In Jedox Web, these functions are not available in the Paste Data Function dialog. 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. Select the cube you want to address and choose a data function.
Click on Paste to insert the function into the selected spreadsheet cell.
By default the first element of each dimension is selected:
In this example, the selected cube has four dimensions: “All Years”, “Year”, “All Customers”, and “Status”. Thus, the PALO.DATAC function has six arguments:
- Connection = “localhost/Biker”
- Cube name = “Status”
- First element of the dimension “Years” = “All Years”
- First element of the dimension “Months” = “Year”
- First element of the dimension “Customer” = “All Customers”
- First element of the dimension “Status” = “Status”
Note: The cube “Status” has only string cells. Because of that you don’t get back a value in the spreadsheet cell.
In Excel Add-in, the formula uses PALO.EL syntax:
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: