Paste View Dialog in Jedox Web

This article describes the Paste View dialog in Jedox Web, which allows you to create Jedox Views from a selected database. If you are using Excel Add-in, the native Excel dialog is described in Paste View Dialog in Excel Add-in

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.

For Excel Add-in, this Jedox Web Paste View Dialog is also available. You can enable it in Excel Add-in by checking Use Jedox Web dialogs in Excel Add-in Options.To open the Paste View dialog in the Jedox Excel Add-in, click on the New View button in the Jedox Ribbon.

The Paste View dialog has two tabs: Definition and Options:

Definition Tab

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 the source area and three areas for the arrangement of the dimension elements in the spreadsheet.

After a cube is selected, all of its dimensions and lists are displayed in the source area (6). You can drag and drop them in the following areas:

  • POV (Point of View): area of header dimensions (7)
  • Rows: row titles area (8)
  • Columns: column titles area (9)

The areas of the dialog are described below.

Select view (1)

In this area, you can select an existing view:

Tool buttons for the current view (2)

These buttons to handle the current view:

Save view, Save as, Delete view, Edit view name and Set default OLAP view.

Select server/database (3)

Here you can select the server and database for your connection from the dropdown list. This setting determines which cubes are available in the cube selection area (4).

Select Cube (4)

Here you select a cube for the view. The available cubes depend on the chosen database (3) and the database section (5). The dimensions and the lists of the chosen cube are displayed in the source area (6).

Sections of the Database (5)

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 dropdown menu. The symbols and cubes are described below:

Symbol Cubes Description
Data cubes This module enables access to standard cubes.
Attribute cubes 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 cubes The various rights of the different users and groups are stored and controlled here.

Source area (6) and page header area / POV (7)

The dimensions of the chosen cube (4) are displayed in the source area (6). You can select the dimensions / lists that you want to see as page, row, or column titles and move them to the corresponding areas.

Dimensions that stay in the source area remain hidden and their selected elements behave like defined page header elements.

Note: if a dimension has a Default Read Element defined, that element will be used in the header of Views as default element. This applies to dimensions that are placed in the header area. If a Default Read Element is defined on a dimension that did not have one before, existing views will not be updated automatically.

Row titles area (8)

The elements of these dimensions are displayed vertically as row titles. You can expand and collapse underlying child elements by double-clicking on them.

Column titles area (9)

The elements of these dimensions are displayed horizontally as column titles. You can expand and collapse underlying child elements by double-clicking on them.

Columns can be sorted by right-clicking on the column heading.

Nested sorting respects the hierarchies of the dimension on the rows in the view while sorting the data values within those hierarchies. Child elements are still shown directly below their parent (or directly above if “parents below children” option is used).

Full sorting flattens the hierarchy, sorting purely based on data values, irrespective of the hierarchies and nesting of dimensions on rows.

SELECT ELEMENTS dialog button (10)

Clicking this Button opens the dialog window SELECT ELEMENTS. 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 dialog SELECT ELEMENTS for Customers for the Orders cube in the Biker database.

The displayed elements in area 10a depend on several settings:

In 10d, you can directly add or edit Subsets. Also, if the dimension has stored Subsets, you can select one of them in field 10d as preselection for the offered elements in area 10a.

For the element selection you can use a variable by checking and defining it in section 10b. This variable section is only displayed in the Designer module. In the Reports module, the section 10b is hidden for all users. Note that the variable selection is in Excel Add-in available but not functional. Variables can only be created in Jedox Web and are not accessible in Excel Add-in.

If the dimension has stored attributes, these can be used as Alias (10b).

The Alias format list (10c) 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. Furthermore, the City attribute has been selected as Alias (10b), using the Alias format "Alias - element name" (10c). You can sort the Alias names in an Ascending, Descending, or By creation order (10g).

Note that in Jedox Web, a View will not display an Alias in the spreadsheet if the given element in the dimension does not have any value for the chosen Alias. However, in the same situation, the element name would be shown as an Alias in a detached View, in the dialog itself, in a PALO.ESELECT function in a custom report, and in the Jedox Excel Add-in.

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 (10e). There are two ways to do this:

  • Mark the elements in 10a 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 10a. If the picklist is empty, clicking on OK will select all marked elements in area 10a. If nothing is marked in the whole dialog window, the previous selection will be used for the View.

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
Clear picklist

Additional buttons

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 dialog:

  • 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. Note that when the list view mode is enabled, defining filters for this dimension via the context menu of the pasted view is not supported.
  • 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 Creating a Jedox View for more information.

Swap button (11)

This icon allows you to switch the entries in the row titles area with the entries in the column titles area.

Show now button (12)

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.

Suppress zeros button (13)

This button turns on zero suppression for the current Jedox view. For more information, see Zero Suppression.

 

Updated May 17, 2024