Jedox Views help you to quickly show and analyze data from Jedox cubes in a spreadsheet in Excel or Jedox Web.
With a spreadsheet open, click New View on the Jedox Ribbon in Excel or Query > New view in a Jedox Spreadsheet to open the Paste view dialog window (shown below).
Below you see the Jedox Web dialog. A description of the native Excel Add-in dialog you can find here.
Select the database and the cube you want to use for the view.
Select the dimensions and elements that you want to see and how they should be arranged. You can move the dimensions by dragging and dropping them from the source area to the page headers area (POV), to the row titles area or to the column titles area.
To receive the next screenshot please do following adjustments:
- Select the database Demo and the cube Sales.
- Drag and drop the Products dimension to the area Rows.
- Drag and drop the Regions dimension to the area Columns.
- Drag and drop Months, Years, Versions, and Measures to POV.
- In POV click the button on Months and select Jan, the button on Years and select 2018, the button on Versions and select Actual. Units is automatically selected for Measures.
Now click on the Paste button, and then double-click on B11 and C9 in the created view:
Description of the Jedox view above:
| Cell address | Name | Description | Option / Functionality |
| B2 | Title | Title is the name of the stored view. If the view is not stored, title is the name of the used cube. | Option to hide whole row in tab "Options" of dialog "Edit view": Hide title. If Allow pivot is checked in Paste View Options, then double-clicking Title opens Paste View dialog. |
| B3 | Subtitle | A concatenation of the element names which are selected in header dimensions (C6:F6), separated by a bullet point. | Option to hide whole row in tab "Options" of dialog "Edit view": Hide subtitle. |
| B5 | Database | Combination of connection and database name. | Can be hidden with headers option. |
| B6 | Cube | Name of the used cube. | Can be hidden with headers option. |
| C5:F5 | Header dimensions | The dimensions of the used cube which were left in POV. | Option to hide the whole rows 5 and 6 in tab "Options" of dialog "Edit view": Hide headers. |
| C6:F6 | Selected header dimension elements | The selected dimension elements of the dimensions which were left in POV. | Option to hide the whole rows 5 and 6 in tab "Options" of dialog "Edit view": Hide headers. |
| Column B starting with B11 |
Row titles | Row titles of the displayed rows. | Double-click on consolidated elements (displayed with bold characters) will expand / collapse child elements. |
| Row 9 starting with C9 |
Columns titles | Column titles of the displayed columns. | Double-click on consolidated elemenets (displayed with bold characters) will expand / collapse child elements. With right-click on the head cell of a column you receive sort commands. |
It is possible to select multiple elements in the page headers area (cells C6:F6). For example, in the screenshot below, we selected in cell D6 the years 2016, 2017, and 2018:
After selecting multiple elements, you can select among the aggregation functions Sum, Minimum, Maximum, or Average. Note that these functions operate after any cell calculations that are required for the view.
| Sum | The default aggregation type; it calculates the sum of the selected elements in the current view. |
| Maximum | Shows only the highest value from selected elements in the current view. |
| Minimum | Shows only the smallest value from the selected elements in the current view. |
| Average | Calculates the average value of the selected elements in the current view. |
If multiple elements have been selected, Drill Through and Drill History are not possible.
To continue our example from above, we applied the Average function to our selection and received the following Jedox view:
The view now displays the average of units in January for the years 2016, 2017, and 2018. Such calculations are also known as aggregation functions and ad hoc client-side rules.
Important: due to a constraint in Excel, copying or moving a sheet containing a View to another workbook can lead to corrupted data (custom sheet properties are lost, which leads to missing XML). As a workaround, we recommend to manually create a new workbook by going to File > New and use that as a target for the sheet you want to move/copy.
Notes:
- Jedox Views use both the name of the dimension and the internal dimension ID when defining a view. Thus, if the cube layout is changed, or the dimension is renamed, Views continue to work.
- When opening a spreadsheet file which contains a view created in an old Jedox layout or theme, the view will be automatically converted to the new layout in Excel Add-in. In Jedox Web, however, the conversion is not automatic. Instead, it is necessary to open the "Edit View" dialog once and paste the view again from there.
- To learn more about building custom reports with changed formats and differently positioned contents, please read Detaching Jedox Views.
Updated November 7, 2022