Inserting and Changing a View
Views in Jedox Add-in for Excel 365 are static, as they are inserted into the spreadsheet without PALO functions, while preserving their familiar manipulation capabilities such as Splashing (data entry), expand / collapse, Zero Suppression, etc. They are not updated with each Excel auto-calculation, giving you more control over updating their content.
Once connected to your cloud instance, you can insert an existing View from the Stored View option or create a New View, both available within the Jedox Ribbon and task pane.
To display the stored Views, select the respective server / database and cube. When you hover over a stored View, you can pin or unpin it for quick access from the Home section.
When you choose to create a new View, the task pane on the right will ask you to first select the database and cube, and then to choose the dimensions and elements you want to display.
Click on the Apply button to insert your choice.
To save your new View, you must use the Save As option by clicking on the 
icon at first. Any further changes on an existing View can be saved by clicking on the 
icon until it turns green.
You can change the content of a View via the Edit View button.
When editing the View, there is an option to enable Live changes, eliminating the need for previews and displaying changes instantly. When the option is enabled, the Apply button will be hidden.
Within the opened task pane you can rearrange the selected dimensions via drag-and-drop, select different elements via the 
icon and, if available, apply Alias from the 
button.
Hovering over any expanded parent element will display the Select and Deselect buttons next to the element name, as shown in the image above, allowing you to select or deselect all visible child elements at once.
You can also select multiple POV elements, choosing which function to be applied (Sum, Minimum, Maximum, or Average).
After inserting the View, you can quickly change the analysis focus by keeping only certain data elements. The Keep Only button will be enabled as soon as you select one or more elements of a dimension. This feature cannot be simultaneously applied to multiple dimensions or to rows and columns. To revert the changes, you must edit the View from the task pane.
Notes on using Keep Only with nested dimensions:
- The button will only be enabled if all selected elements belong to the same dimension.
- If the selected elements belong to an inner dimension, the elements of the outer dimension will not be affected by the new selection.
- If the same element is selected multiple times within an inner dimension (i.e., the same inner element belongs to different outer elements), the button will remain disabled.
In addition, you can change the content within the View with splashing (data entry), expand / collapse underlying child elements (by clicking on them) on rows and columns, use Zero Suppression, and clear cell content.
The PALO functions can be restored at any time by detaching the View.
The View last refresh date informs you about the last update on the View's content.
If there are multiple connections, the recently used Views of the active connection will be hidden or deleted as soon as you disconnect or delete this connection. In addition, the View displayed on the spreadsheet can no longer be edited, and any attempt will trigger the "Connection not found" error message.
Custom Reports
From this section, you can create custom reports by integrating real-time data values from Jedox's In-Memory DB directly into Excel 365.
Insert Elements
The Insert Elements button allows you to insert element names or aliases. Once you have selected the database and dimension, you can either select one or more elements and insert them vertically or horizontally.
If you have inserted one element, you can update your selection by clicking on this cell, as the element is inserted with the function PALO.ESELECT.
If you have inserted more than one element, you will need to make a new selection when an update is needed, as these elements are inserted as text and not as a function.
Insert Data Function
After you have inserted the elements, you can use Insert Data Function to display their value from the database cube into a spreadsheet cell, or to write the value of a spreadsheet cell back into the database cube. This feature utilizes the PALO.DATAC function.
Guess arguments
The "Guess arguments" functionality automatically refers to cells on your sheet that contain potential arguments for the PALO.DATAC function. Double-click the cell with the value to display the referenced arguments.
Note that this function can only search within a range of 20 columns above and 20 rows to the left of the position where the PALO function was inserted.
Switching to Add-in for Excel 365
To ensure a smooth switchover between the Excel Add-in and the Add-in for Excel 365, the following aspects should be considered:
-
Uninstall the Excel Add-in: both the Excel Add-in and the Add-in for Excel 365 can be installed on your desktop but cannot be used at the same time. To automatically launch the installed Add-in 365, you must first uninstall the Excel Add-in. To access both simultaneously, use the Excel Add-in on your desktop, and the Add-in 365 on Excel for the web or Excel for macOS.
-
Use the same connection name: PALO functions include the connection name as part of the formula arguments. When opening a report created with the Excel Add-in, ensure that you use the same connection name to avoid breaking the existing PALO formulas.
-
Jedox Views & PALO functions: Views and PALO functions created with the Excel Add-in will display a #VALUE error at cell level when you open the report for the first time. To see the View content and enable its capabilities, you need to click on Rebuild View, as Excel recognizes the Excel Add-in View as detached. To retrieve the functions values, you must update the sheet/workbook.
-
@ symbol: Excel automatically inserts a @ symbol in front of the PALO functions if they have been inserted with the Excel Add-in. This does not pose an issue on Excel desktop, but when used on macOS or in Excel for the web, the formulas may break. In such cases, delete the @ symbols from the workbook and save it.
Tip: you can use Excel's "Replace" option to quickly remove all @s. -
Double-click interactions: the double clicks used in the Excel Add-in to open the Select Element screen or to expand/collapse parent elements in Views are no longer supported in the Add-in 365. These actions must now be performed with one click. Note that double-clicking will trigger the action twice and may affect performance depending on the data set that you are interacting with.
-
Subsets: due to Microsoft's Dynamic array formulas, Subsets are only displayed with the Index row. To display the rows with the Elementname and, if available, the Alias, you must copy the formula and insert it in an area where all rows and columns can be displayed.
For more information, see What's new and Features in Add-in for Excel 365
Updated April 9, 2024