This article describes the Paste View dialog and the way it allows you to create Jedox Views from a selected database.
To open the Paste View dialog in the Jedox Excel Add-in, click on the New View button in the Jedox Ribbon.
To open the Paste View dialog in Jedox Web, open a Jedox spreadsheet and select the command Query > New View of the simple ribbon toolbar.
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.
There are three areas for the arrangement of the dimension elements in the spreadsheet. In every area you can activate one dimension – then “Select elements” is displayed.
- POV – Point of View – Area of Header dimensions (4)
- Columns – Column titles area (5)
If there exists a dimension description, it will be displayed by hovering the mouse over “Select elements” (5a)
- Rows – Row titles area (6)
Description in more details:
You can drag and drop dimensions to move them to the column titles area (5) or the row titles area (6).
Section 1 – Select Server/Database
In section 1, you can select the server and database for your connection from the drop-down list. This setting determines which cubes are available in the cube selection area (2).
Section 2 – Select Cube
In section 2, you must select a cube. The available cubes 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.
Section 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.|
Section 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.
You can also hide the dimensions in the header section of a view. 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 you explicitly select another element). If a Default Read Element is defined on a dimension that didn’t have one before, existing views will not be updated automatically.
Section 5 – Column Titles Area
You can drag and drop dimensions from the Page Header area to the Columns section.
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).
Step 6 – Row Titles Area
You can drag and drop dimensions from the Page Header area to the Rows section.
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).
Section 7 – Swap
This icon allows you to exchange the entries in the row titles area with the entries in the column titles area.
Section 8 – 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.
The displayed elements in area 8a depend on several settings:
If the dimension has stored subsets, you can select one of them in field 8e as preselection for the offered elements in area 8a.
If the dimension has stored attributes, these can be used as Alias (8b).
The Alias format list (8c) shows the various ways of displaying the alias and element name. Note that when using an alias format, the search functionality will only search for alias values, not for element names or the combination element name and alias.
In the screenshot above, “Customers AZ” (8e) is selected as subset. This subset sorts the customer names (hierarchy level 4) in ascending alphabetical order.
The displayed elements in 8a are in order of creation because “Enforce stored subset definition” (8d) is unchecked.
Furthermore, the City attribute has been selected as Alias (8b), using the Alias format “Alias – element name” (8c).
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 (8f). There are two ways to do this:
- Mark the elements in 8a and then add them to the picklist by clicking the arrow to the right.
- Double-click on the elements.
In the screenshot above the entries in the picklist are sorted in alphabetical ascending order. If anything is in the picklist, these displayed elements will be used when you click OK, regardless of what is selected in area 8a. If the picklist is empty, clicking on OK will select all marked elements in area 8a. If nothing is marked in the whole dialog window, the previous selection will be used for the view.
If you check “Enforce stored subset definition” (8d), stored subset definition will update the sorting in 8a. In this case, if elements are in the picklist, they will be used, but with the sorting in the subset, displayed in area 8a, and not with the sorting in the picklist (8f):
The article Using Subsets in Jedox Views brings more information about the use of subsets.
Buttons above the field to display elements (left window field):
Expand all hierarchy levels
Collapse all hierarchy levels
Expand one hierarchy branch
Collapse one hierarchy branch
Buttons above the field picklist (right window field):
Toggle displaying elements in list mode
Toggle displaying elements in tree mode
Sort in order of creation
Sort alphabetically in ascending order
Sort alphabetically in descending order
You can find the following buttons under the More section:
|Search field for elements|
|Select elements on the 1st-5th level or base elements|
|Select the entire list|
|Deselect the list|
|Invert the selection|
|Select the branch|
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.
Section 9 – Show Preview
You can examine the view before pasting or see the effect of your changes in it by clicking Show Now in the Paste View window. This works for both Jedox Web and Excel Add-in. This option displays a preview directly in the spreadsheet. You can close the preview by clicking Cancel on the Paste View window. These changes are real-time. When you click Cancel, the View will not be pasted, and contents that were modified with the dialog will not be retained.