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) to the row titles or column titles area.
- Move the Products dimension to the row titles area and the Regions dimension to the column titles area and click Paste. The following view is displayed:
- Adjust your view. The following options are available:
Doubleclick B7 once to expand the Product hierarchy
Change the year to May in D3
Change All Versions to Actual in F3
Change Units to Turnover in G3
- Select multiple elements in the page headers area (cells D3:G3). For example, in the screenshot below, we selected in cell E3 the years 2014, 2015, and 2016:
- 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 Maximum function to our selection and received the following Jedox view:
The spreadsheet now displays the maximum turnover in May for the years 2014, 2015, and 2016. 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 in 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.