Creating a Jedox View

image_pdfimage_print

This article offers an example of how to create a Jedox view, also called an OLAP view. Views allow to quickly show and analyze data from Jedox cubes.

Open a new spreadsheet in Excel or in Jedox Web.

Click New View on the Jedox Ribbon in Excel or Query > New view in a Jedox Spreadsheet to open the Paste view dialog window:

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 page headers area (current location – POV) to the row titles or column titles area.

To receive the next screenshot please do following adjustments:
Move the Products dimension to the row titles area and the Regions dimension to the column titles area. In POV double-click Months and select Jan, double-click Years and select 2018, double-click Versions and select Actual. In Measures is Units allways selected. Now click the button “Paste” and double-click 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 selectected  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. 

 

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 functions Sum, Minimum, Maximum, or Average:

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 Averagefunction 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 calculated members 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.

image_pdfimage_print