To open the Paste View dialog in the Jedox Excel Add-in, click on the View button in the Jedox Ribbon.
To open the Paste View dialog in Jedox Web, click on the Query menu item and select Paste View.
This dialog allows you to create Jedox data views on a worksheet by simply selecting the dimensions and elements that you want to see and how they should be arranged:
Dimensions can be moved to the row titles area (7) or the column titles area (5).
1 – Select Server/Database
In the top left corner, you must select the server and database for your connection. This setting determines which cubes are available in the cube selection area (2).
2 – Select Cube
Next you must select a cube. The cubes that are available to you depend on the chosen database (1) and section (3). The dimensions of the chosen cube are displayed in the area on the left side, below the Server/Database.
3 – Sections of the Database
In each database there are three kinds of cubes: data cubes, attribute cubes, and user management cubes. Each type of cube is represented by a symbol, which appears next to the Select Cube drop-down menu. The symbols and cubes are described below:
|Data cube||This module enables access to standard cubes.|
|Attribute cube||This part contains additional information for single-dimension elements, which are called attributes. Examples of attributes include product name in different languages, customers phone numbers, responsible person, address, etc.|
|User management cube||The various rights of the different users and groups are stored and controlled here.|
4 – Page header area
The dimensions of the chosen cube (2) are displayed in the page header area. You can select the dimensions that you want to see as row or column titles and move them to the area below or to the right. Dimensions that aren’t moved are displayed as page headers in the Jedox View.
Dimensions in the header section of a View can also be hidden. To hide a dimension, uncheck the box next to the dimension name in the Paste View dialog.
Note: If a dimension has a Default Read Element defined, that element will be used in the header of Views (unless the user explicitly selects another element). If a Default Read Element is defined on a dimension that previously didn’t have one, existing Views will not be updated automatically.
5 – Column titles area
The elements of these dimensions are displayed horizontally as column titles. You can expand and collapse underlying child elements by double-clicking on them or opening the context menu (right mouse click).
6 – Row titles area
The elements of these dimensions are displayed vertically as row titles. You can expand and collapse underlying child elements by double-clicking on them or opening the context menu (right mouse click).
6a – Swap
This icon allows you to exchange the entries in the row titles area with the entries in the column titles area. This action is also possible in a Jedox default view by double-clicking on cell B5.
7 – Select Elements
Double-clicking Select Elements… opens the Select elements for <Dimension> dialog window. Here you can select the elements that you want to see in the view as page headers (one cell for each dimension) or as row or column titles (cells are possible for each element of the displayed dimensions).
The following screenshot displays the Select elements for Customers dialog for the Orders cube in the Biker database.
You can select single elements and/or accumulated elements in any combination. To mark several elements, hold down the SHIFT and/or CTRL key. After you have finished your selection, click OK.
You can also transfer your selection into the Picklist (12). There are two ways to do this:
- Mark the elements in the left window and then add them to the Picklist by clicking the arrow to the right.
- Double-click on the elements.
If there are elements in the Picklist, these will be used when you click OK, regardless of what is selected in the left window area. If the Picklist is empty, clicking on OK will select all marked elements in the window area on the left side. If nothing is marked in the left window area, the previous selection will be used. You may also select dimension attributes to display.
In the screenshot above, the City attribute has been selected as Alias (8), using the Alias format “element name (Alias)” (9). In this case, the first element (Spain) is displayed as “Bike World (Madrid)”. The Alias format list (visible in the screenshot above) shows the various ways of displaying the Alias and element name.
If the dimension has stored subsets, you can select one of them as preselection for the offered elements on the left side of the Select Elements… dialog window.
In the screenshot above, there is no stored subset selected (11).
The checkmark (10) “Enforce stored subset definition” is described in KB article Using Subsets in Jedox Views.
Buttons above the field to display elements (left window field):
Expand one hierarchy level
Expand one hierarchy branch
Collapse one hierarchy level
Collapse one hierarchy branch
Expand all hierarchy levels
Collapse all hierarchy levels
Search field for elements
Buttons above the field picklist (right window field):
Toggle displaying elements in list mode
Sort alphabetically in ascending order
Toggle displaying elements in tree mode
Sort alphabetically in descending order
Sort in order of creation
Notes about the Select Elements box:
- The elements in the left window are displayed only in tree mode (hierarchical display).
- The button to toggle between list mode and tree mode refers to the view of the selected items in the picklist.
- When searching for elements with the search field, the search will now by default select the first found element (not all matching elements). With the next click on the search icon, the next matching item will be displayed.
If you select Select Elements… for a dimension in page area, you will receive a different dialog that doesn’t have a picklist but has the additional possibility of using a function after the selection of multiple elements. See Creation of a Jedox View for more information.
For Jedox Excel Add-in:
To display the next matching item, press F3. To search in reverse order, press Shift + F3. To display all matching items at once, add an asterisk (*) to the search string.
Here you can select Data Options and Layout Options.
Select Data Function
The following database functions can be used for the representation of data from a Jedox cube:
- PALO.DATA(): in the context of a view, PALO.DATA is the default function. A separate PALO.DATA() function, referring to a distinct cube cell, is set in each spreadsheet cell. In the context of a view, PALO.DATA() does not have performance drawbacks with regards to server-side processing. PALO.DATA() also allows to perform writeback of values to the OLAP Server on the spreadsheet cell.
- PALO.DATAX(): This function is similar to the PALO.DATA function, but the computation is limited to the currently active worksheet. Worksheets that are not currently being viewed are not included in the calculation, which speeds up the computation, because it is not necessary to compute the whole workbook.
- PALO.DATAV(): Similar to PALO.DATA , but the function works even faster on the client because an array formula is generated for the complete area. PALO.DATAV() is the fastest function, but it can only be used in connected cell areas and not if you want to perform writeback to the OLAP Server. As an array function, it controls several cube cells for display in Excel cells simultaneously. Writeback only works if the function is entered in one single cell.
If zero suppression is checked, then cells with null value will not be displayed. If all the selected cells have zero value, then one element will be still displayed. You can apply the Zero Suppression option on specific dimensions. To do this:
- Select a dimension on the Select Elements dialog.
- Click More.
- Check Zero Suppression.
This applies to the POV area. The element selectors for rows and columns don’t filter the elements directly from the select elements tree. Instead, the zero suppression is applied when you paste the view on the sheet.
Note: if zero suppression is checked, the displayed view is correct at the time of its creation, but zero suppression will not be updated automatically after changes in the data.
Parents below the children
By default, parents are displayed above their children. This default display can be changed here.
Use element name path
If checked, elements in Jedox views will be identified with their element paths. An advantage of this option is that Jedox views will still work after the deletion and recreation of elements, if the recreated element names and hierarchical paths are identical. A disadvantage of this option is that Jedox views will not work if the elements are renamed.
If unchecked, elements in Jedox views are identified with their IDs. The advantage in this case is that Jedox views will still work after the renaming of elements. The disadvantage is that Jedox views will not work after the deletion and the recreation of elements.
Select View Style
Here you will find different color schemes for the Jedox view. When you select a new style, it will be applied to all Jedox views in the active Excel workbook.
Here you can select whether gridlines are displayed in the worksheet.
This option forces a line break when the text in a column is too long.
Here you can specify how child elements should be indented (0-15 steps) in row titles.
This option changes the width of a column. The default is 14.
Note: Line break and column width interact as follows: the line break is applied only to those lines that are filled by using the column titles. For these, the stated column width is used. Columns that are filled by using the row titles are always displayed with the necessary width.
With the button (14) „Save as…“ you can store every view definition on the server as a global view (accessible to all users) or as a private view (accessible only to the current user). After changes in a stored view the button (15) „Save…“ will be active to save just made changes:
At position (13) the currently active view is displayed.
With the buttons at position (16) you can add a new view, or edit, duplicate or delete the currently active view.
If you work with Excel Add-in, you can also use the following keyboard shortcuts:
|Ctrl+N||to add new view||Ctrl+D||to duplicate view||Ctrl+S||to save current view|
|F2||to edit view||Del||to delete view||Ctrl+Shift+S||to save as for current view|