The analysis grid is the design item that provides the main analysis functions in BEx Analyzer. It displays the results of a query in a table in which you can navigate and execute OLAP functions.
For more information about inserting the analysis grid and accessing its properties, which are described below, see Inserting Design Items.
You use the analysis grid as the central design item in your worksheets. The analysis grid displays query results in data cells, and characteristics and structures in either rows or columns of a table. When you design a workbook, you can use the analysis grid together with the navigation pane and other design items to create a query application with full access to all OLAP functions.
In analysis mode, you can navigate in the analysis grid in the following ways:
● Using the context menu
● Using drag and drop
● Using icons (see below)
● By double-clicking the primary mouse button (see below)
Using the properties of the analysis grid, you can configure various settings that affect the way the grid behaves and is displayed.
The functions and properties of the analysis grid are described in the following sections:
● Cell Types of the Analysis Grid
● Context Menu Functions by Cell Type
● Analysis Grid Icons
● Double-Clicking the Primary Mouse Button
● Analysis Grid Properties
When referring to the analysis grid, we use the following terminology for the different types of cells:
Different navigation functions are available depending on which type of cell you select.
The context menu functions available in analysis mode for the analysis grid are outlined in the table below. A function is available in the context menu for a given cell type if the corresponding cell in the table below is shaded and marked with an X. See the notes below the table for the cells that contain asterisks (*).
Function |
Characteristic |
Characteristic Value |
Structure Member (KYF) |
Data Value |
Back One Navigation Step |
x |
x |
x |
x |
Back to Start |
x |
x |
x |
x |
Keep Filter Value |
|
x |
x |
|
Select Filter Value |
x |
x |
x |
|
Filter and Drill Down By à List of Dimensions |
|
x |
x |
|
Remove Filter |
* |
* |
* |
|
Exchange Dimension1 with à List of dimensions (+ structures) |
x |
x |
|
|
Drill Down à List of Dimensions |
x |
x |
x |
|
Remove Drilldown |
** |
** |
** |
|
Swap Axes |
x |
x |
x |
|
Sort Dimension à Sort Ascending / Descending |
x |
x |
|
|
Properties |
x |
x |
|
x |
Query Properties |
x |
x |
x |
x |
Transfer Values |
*** |
*** |
*** |
*** |
Reset Values |
*** |
*** |
*** |
*** |
Save Values |
*** |
*** |
*** |
*** |
Goto |
*** |
*** |
*** |
*** |
Notes:
* Displayed if the filter has already been set.
** Displayed if the drilldown has already been set.
*** Displayed if a cell in the query is activated as input-ready and data was entered or if there is data in the buffer for planning.
**** Displayed if Goto has been customized in the query.
For more information about the functions in this table, see Context Menu Functions. Many of these functions are also available when you use drag and drop.
The following icons can be displayed in the analysis grid:
● Sort Ascending/Descending – displayed if the sort function is available
● Expand/Collapse Hierarchy – displayed when hierarchies are available and activated (see Characteristic Properties)
● Document – displayed if available and enabled (see Display Options in Query Properties)
● Drag&Relate – displayed if available and enabled (see Display Options in Query Properties)
You can switch off the display of all icons by deselecting the Apply Formatting property (see below).
In addition, scrolling icons are displayed inside the scroll bars if you have enabled them in the analysis grid (see clipping properties below). However, the icons are not affected by the Apply Formatting setting.
You can execute the following navigation functions in the analysis grid by double-clicking the primary mouse button:
● If you do not want to display drilled-down dimension values in a report, you can double-click a dimension.
● You can double-click a structure member (key figure) to filter the results according to this structure member.
You configure properties for the analysis grid on the following tab pages in the Properties of Analysis Grid dialog box. (For more information about inserting the analysis grid and accessing its properties, see Inserting Design Items.)
● General – you can configure data provider, cell range, and behavior options
● Clipping –you configure whether to clip or scroll the grid display
● Associated Charts – allows you to associate Microsoft Excel charts with the analysis grid
Analysis Grid Properties
Property |
Description |
Name of Analysis Grid |
Displays the name of the grid, which is generated automatically and is unique. This name is used in the BEx Analyzer → Design Toolbar menu to refer to a particular instance of the analysis grid. |
General Tab Page |
|
Data Provider
|
You assign an existing data provider to the analysis grid, create and assign a new one, or change or delete a data provider. The initial view of a data provider corresponds to the query view. |
Area
|
You manipulate coordinates for a cell or cells in this field to move or resize the button. |
Use Formulas
|
You select this checkbox to replace each cell in the analysis grid with a Microsoft Excel formula. This property of the Analysis Grid design item is set by the context menu function Convert to Formula. The property is activated in supported queries only. |
Reference Structure |
Some texts occur several times in the structure of a column or row. If they are converted into formulas, this may produce invalid formulas (#NU) in the table. If you set this indicator, the formula can also be uniquely specified for these cells. |
Adjust Print Area
|
Adjusts the Microsoft Excel print area automatically to the size of the analysis grid, and also repeats the header rows and header on every page. |
Enable Cell Protection
|
Locks (protects) all cells that are not defined in the query as input ready, and unlocks all input-ready cells. To lock the cells that are not input-ready against input, you must also protect the workbook with a password in the workbook settings. This prevents you from changing data in any cells other than input-ready cells. This is mainly used with planning functions and prevents you from entering data in inappropriate places. See Functions for Manual Planning.
Compare this cell-by-cell protection with Microsoft Excel sheet protection, enabled by default in design mode, with workbook protection, which you can enable in Microsoft Excel or using BEx Analyzer (password-protect workbook). More information: Workbook Settings |
Apply Formatting
|
Deselect this checkbox to switch off the formatting display, including icons and background color. Displaying formatting during navigation can be time consuming. Therefore, you may want to switch it off for performance reasons. Example: You are navigating to find a result that you want to export to a text-based file. Scroll bars and scrolling icons are not affected by this setting. |
Allow Navigation
|
Deselect this checkbox to deactivate the context menu and drag and drop functions in analysis mode. A similar option is available for the navigation pane. This prevents you from analyzing and navigating in the query. |
AutoFit
|
When you select this option, the cell width in the analysis grid expands as required to fit the contents. If this option is selected, after you navigate and the analysis grid is rendered, the columns are resized horizontally to the minimum size needed to render the whole content of the column. |
Display Sort Icons |
The icons for sorting characteristics and key figures in ascending and descending order are only displayed in a report when this option is selected. |
Display Hierarchy Icons |
The icons for expanding or collapsing a hierarchy are only displayed in a report when this option is selected. |
Suppress New Lines |
In BI applications using data providers that are assigned an input-ready query, the system allows you to manually enter data in new input-ready rows. Select this checkbox to suppress the option for creating new input-ready rows. |
Do Not Delete Number Formats When Converting to Formula |
Number formats are deleted by default when you convert data in cells for the result set into formulas. Select this checkbox to retain the unit when you work in formula mode. |
Clipping Tab Page |
|
You use the clipping options to specifically define the size of the analysis grid using clipping or scroll bars. Clipping settings work together with cell coordinate settings in the Range field. You can individually configure horizontal or vertical clipping using the following options: Horizontal ● Clip – the analysis grid can only extend as far to the right as the rightmost column defined in the Range field. The horizontal display beyond this point is clipped, or not displayed. ● Full Size – the analysis grid is displayed starting from the left-most column defined in the Range field, but expands to the right to be as wide as required to display the results. ● Scroll – the analysis grid is displayed within the right and left coordinates configured in the Range field, and a scroll bar allows you to scroll to the right or to the left within the results. You can scroll using the following icons in the scroll bar: ○ Scroll Full Left ○ Scroll Left ○ Scroll Full Right ○ Scroll Right Vertical ● Clip – the analysis grid can only extend as far down as the lower-most row defined in the Range field. The vertical display beyond that is clipped, or not displayed. ● Full Size – the analysis grid is displayed starting from the top-most row defined in the Range field, but expands down as many rows as required to display the results. ● Scroll – the analysis grid is displayed within the upper and lower coordinates configured in the Range field, and a scroll bar allows you to scroll up or down within the results. You can scroll using the following icons in the scroll bar: ○ Scroll to Bottom ○ Scroll Down ○ Scroll Up ○ Scroll to Top More information: Moving and Resizing Design Items. |
|
Associated Charts Tab Page |
|
You use this function to associate Microsoft Excel with the analysis grid, so that the charts always represent the results of the query. The name of any chart you have inserted on any sheet in the workbook appears in the Associated Charts list. Select the checkbox next to the chart or charts you want to associate with this analysis grid. When you associate a chart with a grid, the chart is automatically updated with the cell ranges and values in the grid, even if the grid resizes and the values within it change when you navigate.
● When you use Microsoft Excel to create charts for your analysis grid, you can leave the data range (cell coordinates) blank. Associated charts are automatically updated with the appropriate data range in the current navigational state. ● Worksheets are automatically protected in design mode. Temporarily unprotect the sheet to insert the chart (Tools → Protection → Unprotect Sheet in the Microsoft Excel menu), or switch to analysis mode first. |
More Information:
● Creating Planning Applications in BEx Analyzer