OLAP Grid User's guide
Posted by - NA - on 22 August 2008 12:38 PM

TOLAPGrid Elements
    Operating the OLAP-slice
        Toolbox Menu
        Navigation
        Selecting and copying data
        Drilling, sorting and moving the hierarchy members
    Operating context menus
        Hierarchy levels' menu
        Hierarchy members' menu
        The menu for groups of hierarchy members
        Measures' menu
    Measure values' menu
    Filtering data
    Editing data
    Setting the columns' width

TOLAPGrid Elements

The operation area of the component contains the current OLAP-slice and its control panels (pivot panels). You can change the layout of these panels, as you like.

The panels in the component are:

Name Displays Notes
Grid The current OLAP-slice Cannot be moved at will
Tree The Cube’s structure Allows any docking
Column The column axis elements Allows any docking
Row The rows axis elements Allows any docking
Pages The filtered elements Allows any docking
Values Measures Allows any docking

There are several default layouts. You can load them from the menu, called on the caption of any pivot panel.

Selecting a command of default panels' layout

You can easily manage the layout with the mouse – by simple drag-n-dropping you can:

  • attached to any side of the component;
  • placed inside any panel of the component;
  • put as bookmarks on a panel;
  • set as auto-hidden panels;
  • used as a separate floating window.
Different variants of panels' layouts

Pivot panels' layout

Let’s have a close look at the pivot panels and their functions. Cube structure panel displays all the available measures and hierarchies as a tree. The measures are grouped in the set, displayed in the branch. All the rest of the tree nodes are the dimensions that contain hierarchies. To select a measure for display you need to drag-n-drop it to the Measures panel or the data area. To select a hierarchy for display – drag-n-drop it to the hierarchy area or the Pages panel.

Pages panel (Filters' area). Keep here the hierarchies with hidden members, whose detailed elaboration is not important for the current OLAP-analysis. You can either move hierarchies from this area to the hierarchies’ area, or remove them from the Grid by placing them into the Cube area.

Measures panel is situated in the top right of the control area. The panels of the measures chosen for the Cube are displayed here. To remove a measure from the Grid, place the appropriate panel to the Cube area.

Hierarchies panel. This is where you place the hierarchies. Inside it, you can arrange the hierarchies the way you like, changing their order by drag-n-dropping. To remove a hierarchy from the Grid drag-n-drop it to the Cube structure or choose the appropriate item in the context menu.

Measure Position panel is situated in one of the hierarchies’ areas (either in the row or the column panels). It can be put either before or after all the hierarchies in the panel. The place of the panel indicates where the names of the chosen measures will be put – before or after all the hierarchies in the panel. If there’s just one measure, its name may be omitted in the Grid for the sake of saving space. This is regulated by the TOLAPGrid.Layout.HideMeasureIfPossible property.

See also
- the TDockPanel overview.

Operating the OLAP-slice

The display area of the current OLAP-slice data:

Areas and control elements of the OLAP-slice area

The OLAP-slice data are displayed in a table, whose appearance may be amended to some extent.

For operating the current OLAP-slice there are the following functions available:

  • Navigation.
  • Selecting and copying data.
  • Drilling, sorting and moving hierarchy members.
  • Operating context menus.
  • Filtering data.
  • Editing data.
  • Setting the column width.

Toolbox Menu

The toolbox menu contains the most frequently used Grid commands:

  • Open the Grid state
  • Save the Grid state
  • Export OLAP-slice data. You can set the export parameters and save the data to one of the available formats
  • Print
  • Preview
  • Copy data
  • Undo
  • Redo
  • Toggle axis
  • Managing panels’ layout

Navigation

You can easily navigate the data using the keyboard:

Button Action
Home Move to the first cell of the row
End Move to the last cell of the row
PageUp Move one page up
PageDown Move one page down
Ctrl+Home Move to the top left cell
Ctlr+End Move to the bottom right cell

Using the mouse, you can scroll the table up and down

Mouse Action Component Action
Scrolling the mouse wheel Vertical scrolling of the table
Scrolling the mouse wheel with the Shift button held down Horizontal scrolling of the table

In case, when scrolling causes the system slowdown, set the Grid. OLAPGridSettings.ScrollAndRefresh parameter to False, the detailed display then will be presented only after the scrolling is complete.

Selecting and copying data

The data area in the component can be selected like everything else in Windows:

  • select all the cells pressing Ctrl+A;
  • capture the area with the mouse;
  • change the boundaries of the selected area with direction buttons, holding down the Shift button.

You can copy the data from the selected area to the clipboard by pressing Ctrl+C. If there’s no selection, pressing these buttons will copy the whole OLAP-slice to the clipboard.

Drilling, sorting and moving the hierarchy members

To perform the drilling of the hierarchy members press buttons on the Grid cells. If there’s only one drilling button in a cell, then, instead of pressing it, you can double-click the cell itself.

By default, the drilling buttons are shown only in the cells under mouse. To see all the buttons, press the Ctrl button (set in the Grid.OLAPGridSettings.DrillingHotKeys property).

The last cells in the column area of the hierarchy members allow showing the sorting direction. A pointer that indicates the descending order of data illustrates it. You can manage the sorting modes by single clicking on the cells of the specified area: they are changed cyclically [descending sorting] -> [ascending sorting] -> [no sorting].

The view of the Grid with a sorted column

You can relocate hierarchy members by simple drag-n-dropping. The relocated cell then will change its color to grey, and the destination place will be highlighted with a blue line.

Hierarchy members' relocation mode

If a cell has any additional information to display (for instance, the hierarchy member in this cell has additional attributes or a description), then it will be shown in the tool tip, when the mouse is held over the cell for some time (500 msec by default).

Operating context menus

The context menu, called by right clicking a table cell, partly duplicates the OLAP-slice control functions.

The list of items in this menu, just like in any other described below, can be amended through the TOLAPGrid.OnGetPopupMenu event handler.

Hierarchy levels' menu

The view of context menu of the hierarchy levels' area

There is a list of commands available in the context menu of the hierarchy levels’ area (see the Figure):

Command Function
Show totals first Show the aggregated cells before all the rest
Show totals last Show the aggregated cells after all the rest
Don’t show totals Do not show the aggregated cells
Default sorting Range hierarchy members by default
Sort ascending Range the hierarchy members by values from the bottom to the top
Sort descending Range the hierarchy members by values from the top to the bottom
Aggregate all hierarchy members Aggregate all the hierarchy members (including hidden)
Aggregate visible members only Aggregate the visible hierarchy members only
Drill all down\ to the next hierarhy Drill all down to the next hierarchy
Drill all down\ to the next level Drill all down to the next level
Drill all down\ to the same-level children Drill all down to the same-level children
Drill all up Collapse all the elements of this level
Clear filter Remove any applied filters
Filter on captions Set the filter for measures
Show empty cells Show the cells with no aggregated values
Create new group … Create a new group. It will appear at the first level of the hierarchy
Copy selection Copy the selected area to clipboard
Conditional formatting Show the Conditional formatting menu

Hierarchy members' menu

The view of the context menu of the hierarchy members' area

The functions of the hierarchy members' area menu:

Command Function
Drill down to the next hierarchy Open the node up to the next hierarchy in the current area, ignoring all the lower levels of the current hierarchy
Hide this member Hide the member
Hide all members except this Hide all the members of the hierarchy, except the selected one
Hide all members above this Hide all the members of the hierarchy above the selected one
Hide all members below this Hide all the members of the hierarchy below the selected one
Show only the top Show the top elements of the level. Their number is specified in the sub-menu. If you specify a percent value there, then the picking of members will be such that their total value does not exceed the one, specified in the sub-menu.
You can choose a threshold value from those offered in the appropriate menu item or set your own one, selecting Other from the menu.
Show only the bottom Show the bottom elements of the level. Their number is specified in the sub-menu. If you specify a percent value there, then the picking of members will be such that their total value does not exceed the one, specified in the sub-menu.
You can choose a threshold value from those offered in the appropriate menu item or set your own one, selecting Other from the menu.
Filter on captions Set the filter for measures
Create new group.. Create a new group on the current level
Copy selection Copy the selected area to clipboard
Conditional formatting Show the Conditional formatting menu

If you create a group, the context menu will change. The commands responsible for operating with groups will appear:

The view of the context menu after a new group has been added

The new elements in the menu for operating with groups:

Command Function
Move member to group Place the selected member into the group
Move all members except this to group Place all the members, except the selected one, into the group
Move all members below this to group Place all the members, below the selected one, into the group

The menu for groups of hierarchy members

The context menu that appears above the group cell:

The view of the context menu above the group cell

Command Function
Drill down to the next hierarchy Open the node up to the next hierarchy in the current area, ignoring all the lower levels of the current hierarchy
Hide this member Hide the selected member
Hide all members except this Hide all the hierarchy members except the selected one
Hide all members above this Hide all the hierarchy members above the selected one
Hide all members below this Hide all the hierarchy members below the selected one
Show only the top Show the top elements of the level. Their number is specified in the sub-menu. If you specify a percent value there, then the picking of members will be such that their total value does not exceed the one, specified in the sub-menu.
You can choose a threshold value from those offered in the appropriate menu item or set your own one, selecting Other from the menu.
Show only the bottom Show the bottom elements of the level. Their number is specified in the sub-menu. If you specify a percent value there, then the picking of members will be such that their total value does not exceed the one, specified in the sub-menu.
You can choose a threshold value from those offered in the appropriate menu item or set your own one, selecting Other from the menu.
Filter on captions Set the filter for measures
Create new group Create a new group
Delete this group Delete the selected group
Clear this group Clear the selected group
Rename this group Rename the selected group
Copy selection Copy the selected area to clipboard
Conditional formatting Show conditional formatting menu

Measures' menu

The view of the context menu of the measures area

Command Function
Hide this measures Hide the selected measure
Show as Specify the display mode of the current measure in the Grid:
default
percent aggregated value in the row
percent parent element of the row
percent parent element of the column
percent total aggregated value
Copy selection Copy the selected area to clipboard
Conditional formatting Show conditional formatting menu

Measure values' menu

The view of the measure values' menu

Command Function
Sort ascending Range the cells in the column by values from the bottom to the top
Sort descending Range the cells in the column by values from the top to the bottom
No sorting Remove any sorting
Show as Specify the display mode of the current measure in the Grid:
default
percent aggregated value in the row
percent parent element of the row
percent parent element of the column
percent total aggregated value

Filtering data

The filtered elements (hierarchies or measures) are automatically placed on the filters' panel.

To start filtering, move the element to the filters' panel. In case the element has not been filtered, the appropriate editor will launch (the Hierarchy Editor for hierarchies and the Measure Filter Editor for measures). In the filters' panel, you can launch an editor for each element independently.

The buttons for launching the editor () are placed on the panels in the Hierarchy levels’ area. If the button looks like this (), it means the hierarchy had hidden members inside. Pressing the button starts the Hierarchy editor.

Editing data

You can edit the contents of the cell in the data area.

To allow editing, set the Grid.OLAPGridSettings.AllowEditing property to True. Choose the editor mode from the Grid.OLAPGridSettings.EditorMode property.

Command Function
TEditMode.Usual The editing mode is launched by double-clicking on the cell
TEditMode.InputEdit The editing mode is launched by setting focus on the cell and pressing Enter

To exit the editing mode, press Esc or click anywhere beyond the edited area.

See also:
- TOLAPGrid.OnGetEditText
- TOLAPGrid.OnSetEditText
- TOLAPGrid.OnChangeEditText

Setting the columns' width

You can change the width of columns in the data display area by dragging the right boundary with the mouse. If possible, these changes will be saved during other operations with the table (like drilling, sorting, etc.) To return to the original width, double click the right boundary of the column where it is visible. In case there were such changes in the table that saving the assigned width of the columns was impossible (for example, if as a result of the Collapse drilling, the column with the assigned width has disappeared), it will be set automatically.

You can abandon automatic setting of the cell height by setting the appropriate parameter of TOLAPGridSettings. AutoSizeCellsMode., if necessary. All the possible field values are listed in the following table:

Field value Function
TAutoSizeCellsMode.asNone Changing column's width will not cause changing the height of cells
TAutoSizeCellsMode.asAllCells The height value will be calculated for all cells
TAutoSizeCellsMode.asAllCellsExceptColumnHeader The row hight value will be automatically calculated for all cells, except those in the Hierarchy level captions area and the Hierarchy members' cells, situated above the Measure values' area

Unfortunately, if you operate a big table, calculating the columns’ width takes up too much time. This is why if there are more than 10, 000 cells in your table, their width will be set to default, but it can be corrected later.

See also
- Setting the Width of Columns while Exporting.

(647 vote(s))
This article was helpful
This article was not helpful

Help Desk Software by Kayako Resolve