- 1 OLAP Designer
- 1.1 New OLAP
- 1.2 Add tab
- 1.3 Add cumulative table
- 1.3.1 Table layout
- 1.3.2 Cumulative table properties
- 1.3.3 Fields visibility
- 1.3.4 Reordering fields
- 1.3.5 Sorting
- 1.3.6 Filtering
- 1.3.7 Conditional formatting
- 1.3.8 Custom field creation
- 1.3.9 Custom field creation (ES function)
- 1.4 Add chart
The OLAP Designer is a data summarization tool that is used in the context of data processing. They are a good solution when you need to summarize and analyze large amounts of data. OLAPs are used to summarize, sort, reorganize, group, count, etc. data retrieved from two types of data sources.
The aforementioned data sources are the two following:
- View (Scroller)
To use a view as the data source, you must have previously designed and develop the view. For detailed information about views, please refer to the ESBooks\ EBS-ReportingEN
SSAS (SQL Server Analysis Services)
To use SSAS as a data source, you must connect to the SSAS database using the ESDBConnectionEdit.exe. After running the previously mentioned file, you have to provide the following information:
- SQL Server (OLAP)
- Database (OLAP)
- Define the connection type.
If the windows user is authenticated and have access to the database you select ‘Windows Authentication (OLAP)’, else you insert the user credentials in the next two columns; User (OLAP) and Key (OLAP)
For detailed information about SSAS, please refer to the related documentation file.
To create a new OLAP, open the OLAP designer (menu: Tools & Configuration \ Additional development tasks \ Olap designer…) and go to File > New. A pop-up screen appears in order to define the following:
|Area||The area where the OLAP will be stored|
|Data source||The data source that will be used for retrieving data|
|View||The dataset of the data source|
Each OLAP report could be consisted of one or more tabs. To add a tab, select the option Add \ Tab \ New. You need to use this option only if you wish to add multiple tab pages.
Add cumulative table
To add a cumulative table, click ‘Add’ in the toolbar and select ‘Cumulative table’. In the pop up screen you have to define the title.
After naming a cumulative table a pop up dialog box is shown in order to design the layout of the cumulative table. Each table is divided into four (4) areas in which the available fields of the selected data source can be placed. These areas are:
By default, when creating a new OLAP table, all fields are placed into the ‘Available fields area’
You can place a field from one area to another by dragging it from the ‘Available fields area’ and dropping it to the target area. Using the drag and drop method, you can change the area in which a field is placed. To apply any changes to the table you have to click the ‘Update’ button.
Date time fields
By default, for each date time field the system will provide the following standard dimensions:
- 10-day - In this context, this dimension must be placed under the month dimension
Cumulative table properties
The cumulative table properties are accessed through right-click on the table and by selecting ‘Cumulative table properties’. A pop up dialog box with two sections are shown with the following options:
- Table title
- Allow field drag & drop
- Filter area
- Row area
- Column area
- Row - Open levels (number)
- Column – Open levels (number)
- Row total
- Column total
- Row grand total
- Column grand total
You can show/hide fields by using one of the two (2) following actions:
- Right-click on a field and select from the actions list Field and Hide field.
- Right-click on a field and select from the actions list Show field list. To hide a field you drag and drop it in the field list section. To show a field, select it, then drag and drop it into the section you wish to be shown.
- Drag & drop method. Drag the field header and drop it to the new position you wish to be placed.
- Through the field list. A list with the available options is shown by right-clicking on any field of the table. Select Show field list action and reorder the fields you wish.
To sort the values of a column based on the values of a row or vice versa, right-click the column/row and choose the field whose values should be sorted by the column/row summaries from the context menu:
The appearance of individual data cells could be changed based on specific conditions using the Format rules context menu. This feature helps to highlight important information, identify trends and exceptions, and compare data.
To invoke the menu, do the following:
- Right-click the data cell corresponding to the intersection of the required data, row and column fields.
- Select the required rule from the list.
Custom field creation
A pop-up screen appears in order to define the following:
|Type||The data type of the field.|
|Area||The area in which this field will be shown|
|Formatting type||The formatting type of the field|
|Expression||The expression used for calculating this field.|
To write the expression, click on the button in the expression field to open the expression editor. The expression editor provides lists of the available fields of the data source, constants, operators and functions which can be used for writing an expression.
Custom field creation (ES function)
To create a custom field using functions provided by Entersoft, right-click on any data field and then Field… and New field based on parallel period.
Declare the title of the field you wish to create and select one of the functions listed. The available functions are the following:
- Difference in relation to the first value
The difference between the first value of a hierarchy and each one of the following. A hierarchy in the column area should be defined.
- Difference (%) in relation to the first value
The percentage of difference between the first value of a hierarchy and each one of the following. A hierarchy in the column area should be defined.
- Running total
The cumulative sum. A hierarchy in the column area should be defined.
The difference between the previous and next value of a defined field in a hierarchy. A hierarchy in the column area should be defined.
- Difference (%)
The percentage of difference between the previous and next value of a defined field in a hierarchy. A hierarchy in the column area should be defined.
You can add a chart to an OLAP report based on a previously created table. To add a chart, select the Add option and then Chart based on table. A dialog box opens, in which the title of the chart must be declared and the table that this chart will use for retrieving data. By default the chart is placed in the rightmost part of the designer. To place the chart wherever you wish, click on the button and enable the layout design and then drag and drop the chart to the position you wish to place it. Afterwards, disable the layout design and close the designer pane by clicking on the button.
To select the measures for depiction in the chart, right-click on the chart and click Select measures and select the measures that will be depicted in the chart.
The chart does not create multiple Y axes for fields of different units, for instance, turnover and quantity, so it is highly recommended to use fields of the same unit.