PivotCube User's Guide Table of contents

Section 4

4. Using PivotCube components, which provide data representation for end-users.

4.1 TPivotMap

TPivotMap is non-visual component that serves as the ‘VCL – Wrapper’ for the OLAP kernel functions and objects.  It has properties and methods to define the rows and columns of the slice, visual attributes and defining  calculated measures. This component also serves as a link to different PivotCube visual components, so that changes to the slice is reflected in the other components too (e.g. TPivotChart, TPivotGrid).

To connect the slice to a cube,  you set the Cube property of the TPivotMap component.

After a cube has been build and activated, all the slices connected to that cube will also be activated.  Aside from the above functions, TPivotMap has other properties to determine how data is displayed for the slice.

4.1.1 Dimensions determination by rows and columns in Design time

You can determine which dimensions to display in the columns and rows of a TPivotGrid by setting the Columns and Rows property respectively of the TPivotMap component, after the slice has been activated.

4.1.2 Show/hide empty cells

It happens sometimes that when building the cube, combinations of dimensions will never have any data.

For example:
Data about selling of wares X1 to customer Y are loaded into cube. But customer list includes also a customer Z, which never bought wares X1 and bought only wares X2. When the user tries to see sales volume all the wares with detailed elaboration by customers for a year, he will see the following:

January February March
X1
100
120
100
Y
100
120
100
Z
0
0
0
X2
200
250
350
Y
0
0
0
Z
200
250
350

We can see there are 2 rows, which are completely empty. With larger quantity of customers/wares the empty rows quantity will be larger too. TPivotMap provides the ability to control the display of empty rows using the HideEmptyColumns and HideEmptyRows properties.

By default TPivotMap hides empty rows and columns, but sometimes an end-user may want to display the data, if he wants to know how many customers have never bought this item.

4.1.3 Saving/restore of current configuration

TPivotMap allows you to save the state of the current slice to a file or stream (e.g. a database BLOB field), by using the following methods:

SaveToFile(FileName : string);
SaveToStream(Stream : TStream);

To restore the state of the slice, use the following methods:

TPivotMap.LoadFromFile(FileName : string);
TPivotMap.LoadFormStream (Stream : TStream);

This allows the user to save current state of his slice, and also allows the developer to prepare a set of pre-defined slices for the user.

For example:

Sales analysis
Working time use
Sales geography
Movement of goods over warehouses

The format of saved file/stream complies with the standard Windows INI file format.

The settings that are saved include:

  1. Dimensions on rows
  2. Dimensions on columns
  3. Measure order
  4. Measure views
  5. Measure visibility
  6. Filtering settings - by dimensions and measures
4.1.4 Automatic saving of current configuration

TPivotMap allows you to automatically save the configuration of the slice to a file after every change.  To use this feature, you should set the following properties:

TPivotMap.AutoSaveMap := True
TPivotMap.IniFile := < file name where the settings will be saved to>

4.1.5 Using the MapManager dialog

TPivotMap has its own tool (as an alternative to using  the toolbars) to configure its dimensions using a dialog box.



You may bring up this dialog box by using the following method:

TPivotMap.ExecuteMapManager(X, Y: integer): boolean;

where

  • X, Y - window’s coordinates, by default – ‘1’, and the window will be placed in the center of screen

The return value is True if the user choose to apply the changes.

Using this dialog to build a slice offers the following advantages:

4.1.6 Changing dimension items caption

If a dimension was created using the DescriptionField property, the developer can change the text representation of items in that dimension by calling the SetDimensionFormat procedure.

TPivotMap.SetDimensionCaptionFormat(DimensionName : string; FormatType :TDimensionCaptionFormat;FormatStr : string);

where

  • DimensionName - name(AliasField value) of dimension that items string representation will changed
  • FormatType - points, how it will be changed
  1. dcfCaption - default value, Dimension.LookupField will be used as dimension item caption
  2. dcfDescription - Dimension.DescriptionField will be used as dimension item caption
  3. dcfFormatted - result of processing formatted string will be used as dimension item caption

Example:

PivotMap1.SetDimensionCaptionFormat('Client',dcfFormatted,'%CAPTION ' + Char(13) + Char(10) +'Description: %DESCRIPTION')

where

%CAPTION - will be replaced with value of Dimension.LookupField
%DESCRIPTION - will be replaced with value of Dimension.DescriptionField

  • FormatStr - used when FormatType = dcfFormatted only

 

4.1.7 Changing way to obtain previous/next member

To handle how the previous/next member in a dimension is calculated, use the TPivotMap.TableMode property.

By default, TPivotMap.TableMode is False.  This means that only non-zero values are considered to be members of a slice.

For example:

Item1
Item2
Item3
123
0
677

In that example, previous member of Item3 is Item1.  Item2 is empty and will ignored.  Thus, calculating the difference with the previous member will produce the following results:

Item1
Item2
Item3
123
0
677 - 123 = 554

When you set TPivotMap.TableMode to True, all cells will be taken into account in calculations.  In the previous example, previous member of Item3 is Item2.  Item2 will no longer be ignored.  Thus, calculating the difference with the previous member will produce the following results:

Item1
Item2
Item3
123
-123
677

4.1.8 Changing order to calculate difference/division

Use the TPivotMap.DDirection property to change the order in which difference and division calculations are made.

When TPivotMap.DDirection is True, difference / division calculations are made as follows:

For difference: d = Item2 - Item1 
For divisions: d = Item2 / Item1

When TPivotMap.DDirection is False,  difference / division calculations are made as follows:

For difference: d = Item1 - Item2
For divisions: d = Item1 / Item2

4.2 PivotCube toolbars

4.2.1 Video rendering setting

PivotCube toolbars have the following properties for display settings:

Caption is a toolbar’s header, isn’t used in case if the toolbar has vertical alignment (alLeft or alRight)
ItemHeight   is a height of toolbar item, which represent dimension or measure. Makes sense only for vertical alignment toolbars.
ItemWidth is a width of toolbar item, which represent dimension or measure. Makes sense only for horizontal alignment toolbars.
Floating if True, then the toolbar can be dragged over into another place of the screen.
Settings

reveals the collection of properties which control the toolbar’s view.

  • Color - is the color, which will be used for drawing the toolbar.

  • Style – is the type of drawing the toolbar.

  • Direction – is the direction of gradient filling (only if Style = psWinXP).
  • Intensity – is the intensity of light/dark with the use of the gradient filling.
  • Font – is the font, with which ‘Caption’ will be printed.
Item Settings  

reveals the collection of properties which control the display of toolbar items. 

  • Color - is the color, which will be used for drawing the item. 

  • Style - the type of drawing (also determines the type of drawing for dropdown control. 

  • Direction - is the direction of gradient filling (only if Style = psWinXP). 

  • Intensity - is the intensity of light/dark with the use of gradient filling 

  • Font - is the font, with which text will be printed. 

  • FilteredColor - is the colour, with which Drop-down button will be drawn, if this item is used during filtering. 

  • InvisibleColor - For dimension related toolbars it will color which will do same as FilteredColor when dimension has invisible (hidden) items. For measure toolbar it will same as Color when measure is hidden (invisible) on grid.

DropDownAutoExpand used only for hierarchical dimensions.  When set to True, all dimension items will be expanded when the dropdown window is displayed.
DropDownColor the color of dropdown control

4.2.2 Expanding the working area

The use of toolbars does have one disadvantage - they occupy screen space, and is especially noticeable on smaller monitors (15” –17”).  To solve this problem, it is possible to use either the MapManager and MeasureManager dialogs to manipulate the onscreen items, or decrease the space occupied by the toolbars on the screen. 

To decrease the size of the toolbars, you can either call the Collapsed property of the toolbar, or to press the hide / display button on the toolbar.  The toolbar will not be removed from the screen but its’ height/width will be only 14 pixels in size.  

4.3 Dimension toolbars

PivotCube includes 3 types of toolbars which allows you to easily manipulate the dimensions of a slice.  They are implemented using the TPVDimToolBar, TPVColToolBar and TPVRowToolBar components.

To use these toolbars, they must be placed on the form, and the Map property must point to a TPivotMap instance.  

4.3.1 Using toolbars to configure the slice

The toolbars allow you to basically rearrange the dimensions and to set filters.  To rearrange the dimensions, simply drag the dimension item from one toolbar to another.  To set filters, simply click on the drop down arrow of the dimension item.

The order of the dimensions on the TPVDimToolBar toolbar is of no importance, as it is just a repository of dimensions that are in the cube but not used in the columns or rows of the slice.

The order of the dimensions on the TPVColToolBar and TPVRowToolBar toolbars determine the drilldown order of the grid's columns and rows.

 

4.3.2 Setting filters

PivotCube provides 2 ways to filter dimension items.

Custom setting

You can activate the filtering dialog by clicking on the drop down arrows on the toolbars.  The red checked items () will not be used in any aggregation.

Step-by-step setting

Using this method, you filter on individual dimension items.  To enable this method, ensure that the ViewFilter property for the toolbar is set to True, or click on the up/down arrow' button of the toolbar.

Clicking on the up and down arrows in the filter's cell will select the previous and next item in the dimension respectively.

4.4 Measure toolbar

The TPVMeasureToolbar toolbar is used to set the measure's display settings, state and for creating calculated measures.

4.4.1 Filtering

PivotCube provides a number of unique possibilities for filtering on the Measures.

4.4.1.1 By database records

Filtering by database records requires the creation of a supersaturated cube, which makes it possible to save all the initial values of the measures in the cube.  Filtering on the records makes it possible to exclude from aggregation values of the original records that do not satisfy the conditions for filtering. For example, if the cube was loaded with the following records:

'Summary' dimension
  • Client1 Ware1 100
  • Client2 Ware1 200
  • Client1 Ware1 300
  • Client3 Ware1 400
  • Client1 Ware1 500
  • Client2 Ware1 1000

Say the filter is to exclude from aggregation all records with a summary value < 200, and we want to calculate the median value.

Without using a database filter, the median value will be 350, derived as follows: (300 + 400) /  2.

Using a database filter, the median value will be 400, since the first record will be filtered out.

4.4.1.2 By cell values

Filtering on cells does not affect the aggregation of values.  It only hides the cells which do not meet the filter criteria.  If all values of a column or row are do satisfy the filter criteria, they will be hidden or shown depending on the value of the HideEmptyRows and HideEmptyColumns properties of TPivotMap.

For example:
We use the following filter:

Filter – Summa > 4500.

Before filtering

After filtering

4.4.2 Changing the sequence of measures

If cube has several measures, we may sometimes need to change the order in which they are displayed.  To do this, simply drag the measures into the required position on the toolbar.

4.4.3. Show/hide measures

PivotCube allows you to hide measures.  This can be done in three ways:

  1. Open the measure drop-down list and uncheck the measures you want to hide
  2. Open the drop-down control, and click on the button on the very measure item and uncheck property ‘Visible’.
  3. Open the MeasureManager dialog and uncheck the measures you want to hide.

Furthermore, if there are too many measures displayed, you may want to hide the un-displayed measures and their buttons on the measures toolbar.  This can be done by setting the HideButtons property of TPVMeasureToolbar to True.

4.4.3.1 Views

One measure can be represented in many different ways, each of which makes it possible for the end-user to better understand it.  The values of a  measure can be displayed not just by the value of the measure itself, but also by relating it's values to  the rest of the data.  These different ways of representing the same value are called Views.  To activate a View, click on the dropdown arrow on the Measures toolbar, and select the view(s) you want to display.

The following are the available types of Views:

4.4.3.1.1 Value

Measure’s absolute value i.e. the numerical value which is the result of aggregating the values from the data source field.

4.4.3.1.2 Percent by column or row

This displays the percentage of a measure’s absolute value in relation to the total values of the column or row.

Note: the calculation of percentages is done using the simple formula of  (Value/Total) * 100.  Hence, the result can be substantially more than 100%, if the measure is obtained as a result of a statistical function or calculating the average.

4.4.3.1.3 Ranking by column or row

Rank is the place which a given measure value would occupy if all measures on the row or the column were to be sorted. The largest value has a rank of 1.

4.4.3.1.4 Difference with previous column or row

This View displays the difference between the current measure’s absolute value and the absolute value of the previous item of the current dimension.  If the current item is the first item on the list, the value of this item's View is equal to its absolute value.

4.4.3.1.5 Summation with previous column or row

This View displays the sum of the current measure’s absolute value with the value of the previous item of the current dimension.  If the current item is the first item on the list, the value of this item's View is equal to it's absolute value.

4.4.3.1.6 Running total

This View displays a running total for each cell, either by row and/or column, for the current dimension.  For the first cell, the Running Total value is equal to the measure's value.  For the last cell in the row / column, the Running Total value will be equal to the summary row / column value.

Note: Running Total value is reasonable only for count-type measures like ctSumma or ctCount. You may get strange results for statistical measures like ctMin, ctMax, ctAverage etc.
4.4.3.2 Change of ‘Value’ type

PivotCube has the unique ability to replace a measure’s absolute value with another computed value. For example, it is possible to replace a measure's value by the value of ‘Percent by row’.  This will then allow the user to rank a measure's values based on its percentage.  

PivotCube allows you to replace the a measure's value with the following functions: 

  • RecordCount - is the count of records which were used to obtain the current aggregated value.  It has the same result with using the aggregation type ctCount.
  • Percent by column or row - percentages by columns and rows respectively.
  • Previous member by column or row - displays the value of the previous column or row 
  • Next member by column or row - displays the value of the next column or row
  • Running Total - display calculated running total along measure column

If you chose the 'Previous member' or 'Next member' functions, you can further perform a basic mathematical operation on it (+, -, *, /), with comparison to the current cell value.

4.4.3.3 Formatting

PivotCube allows you to format the display of strings for all types of measures.  For a detailed explanation of the possible formatting functions, please see the Delphi help file on the FormatFloat function.

The default format string is #0.00.  This will display any numerical value to at least 1 symbol before the decimal point and rounds all values to 2 decimal places.  For example:

Value Displayed value
0 0.00
12234.5678 12234.57
3345.6 3345.60
23  23.00
0.0026 0.00
4.4.4 Calculated measures

PivotCube has its own mathematical expression parser to support custom formulas entered by the user.  In addition to using numerical values in the formula, you can also use values from other measures (including other calculated measures).  The parser supports most of the common mathematical functions, but you can extend list of supported functions via declaring own functions. To calculate own function, you need to create own event handler

TPivotMap.OnCalcMeasureGetFunction(MeasureIndex : integer; Col,Row : integer; FunctionName : string; ParamCount : integer; Params : array of double; var Value : double).

where

MeasureIndex - real index of measure which is calulated
Col - column index
Row - row index
FunctionName - name of function
ParamCount - count of parameters. Max. number of parameters is 10
Params - zero based array of parameters [0..9]
Value - function return value;

Note:

If no event handler is defined, default function value is 1

The parser also support variables with access to variable value when new value assigned to variable via event handler

TPivotMap.OnCalcMeasureSetVar(MeasureIndex : integer; Col,Row : integer; VarName : string; var Value : double)

where

MeasureIndex - real index of measure which is calulated
Col - column index
Row - row index
VarName - name of variable
Value - new variable value

4.4.4.1 Syntax

begin
var x;
func xy;
expr;
expr;
expr;
Result = expr;
end

Where
Expr = Measure ”+” Measure or
Measure ”-“ Measure or
Measure ”*” Measure or
Measure ”/” Measure or
Measure ”*” Constant or
Measure ”*” Var or
Measure ”*” Func or
Expr “+”(“-“,”*”,”/”) Expr or
Function(Expr)

Where
Measure = MeasureName or
“Measure Name” or
MeasureName[VIEWNAME]

Where VIEWNAME is
VALUE – measure value
RANK_BY_COL – measure rank in column order
RANK_BY_ROW – measure rank in row order
COUNT – number of records, which were aggregated to calculate measure value
ROW - total value by current measure row
COLUMN- total value by current measure column
PREV_BY_ROW – previous member by row order
PREV_BY_COL – previous member by column order
NEXT_BY_ROW – Next member by row order
NEXT_BY_COL – Next member by column order

List of supported functions:

SQRT(value)
ABS(value)
SIN(value)
COS(value)
POWER(value, power)
POWER2(value)
LOG10(value)
LN(value)
EXP(value)
NEG(value)

Example

begin
var a;
var b;
func xy;
a = "Summa";
b = "Count";
Result = xy(a,b);
end

4.4.4.2 How it works

The end-user can add a calculated measure by clicking the 'Fx' button on the measure toolbar.  You can see a list of available measures and views in the bottom half of the dialog.  Double click on a view or measure to add it to the formula.

Before applying the formula, you can check the formula's syntax by clicking the <Check formula> button.

The developer can also add a calculated measure by calling the AddCalculatedMeasure method of TPivotMap.

For example

TPivotMap.AddCalculatedMeasure(‘Summa percents’; ‘=(Summa[Value] / Summa[Column]) * 100’);

To delete a calculated measure, call the DeleteCalculatedMeasure method of TPivotMap. 

4.5 TPivotGrid

TPivotGrid is one of the main components of PivotCube.  It is used to display aggregated data in the form of a flat cross table .

The basic properties and operations performed with TPivotGrid are as follows:

4.5.1 Drilling

This is the process of obtaining more detailed data by expanding the aggregated values of the dimension item.  TPivotGrid uses the term ‘Expand’ to describe the drill-down of a dimension item to a following (lower) level, and ‘Collapse’ for the drill-up process.

4.5.1.1 Expand/Collapse single dimension item

To expand a single dimension item, just click on the '+' button in the dimension's value cell.

Once a dimension item has been expanded, click on the '-' button to collapse it.

 

4.5.1.2 Expand/Collapse all items by dimension

To expand all dimension item, click on the '+' cell in the dimension's header cell (the cell where the dimension name is displayed)..

Note: TPivotGrid always checks if there is at least one value in the dimension which is expanded.  If there is, the button in the dimension's header cell will display '-', to allow the end-user to close all the dimension's items with one action.

4.5.1.3 Expand/Collapse all items in all dimensions on PivotMap

TPivotGrid allows you to expand and collapse all items in all dimensions in one action.  To do this, call the ExpandAll and CollapseAll methods of TPivotGrid.

The syntax of the method is as follows:

PivotGrid.ExpandAll(Row,Column : boolean) ;
PivotGrid.CollapseAll(Row,Column : boolean) ;

where

Row - if True, all dimension items located on the grid's rows are expanded
Column - if True, all dimension items located on the grid's columns are expanded

To expand all dimension items on the grid, use

PivotGrid.ExpandAll(True, True) 

To expand only all dimension items on the grid's rows, use

PivotGrid.ExpandAll(True, False);
4.5.2 Hierarchical dimensions

TPivotGrid also makes it possible to manipulate the items in hierarchical dimensions, very much like a tree view.

By default, a hierarchical dimension of a slice will be collapsed to display only the top-level items.

To display items lower down in the hierarchy, click on the 'treeview' button next to the dimension item.

4.5.3 Subtotals

When the end-user expands the dimension item to display a lower level, the aggregated values will be displayed in the following form:

The first line displays the subtotal of all the lower level items.  By default, TPivotGrid displays subtotals on the same row as the top level item which was expanded.  However, the developer can use the Subtotals.Settings property of the TPivotGrid to customize how subtotals are to be displayed.  This property has the following subproperties:

Enabled Displays or hides subtotals.   Default is True.
ExpandDown Displays the subtotal above the lower level items.  Default is True.

DisableIfOnlyOne   If the dimension has only one lower level item, it does not make sense to expand the dimension item to display the same item.  Use this setting to control whether to display the single item.  Default is True.
SubtotalCell   This property is used only if the ExpandDown property is False. If the value of this property is True, TPivotGrid will display separate cells for the subtotals using the label 'Subtotal’.  Default is False.
IncludeName This property is used only if the ExpandDown property is False.  If the value of this property is True,  the label ‘Subtotal’ will be displayed together with the name of the dimension item which was expanded.  Default is False.


4.5.4 Totals

TPivotGrid allows choose in 4 states for row and column totals - Disabled (no totals), Fixed (totals displayed on right or bottm side of grid window), at Start (totals inserted as first rows/columns of grid cells), at End (totals inserted as last rows/columns of grid cells). Displaying row and column totals can be enabled or disabled by calling the Settings.Specific.RowTotals and Settings.Specific.RowTotals properties of TPivotGrid.

 

4.5.5 Drop-down chart

The use of drop-down chart makes it possible for the end-user to rapidly obtain information about the values of the selected dimension item or measure in the form of a 3D-bar diagram. In order to open the drop-down chart, click on the Chart buttons.

If several dimensions are located in the rows that are opened, the user can quickly choose which dimension item to display in the chart using the tree view on the right..

You can choose to enable or disable the display of a drop-down window by setting the Setings.Specific.ChartDropDownInView and Settings.Specific.ChartDropDownInColumn properties of TPivotGrid.  By default, the values of both these properties are True.

4.5.6 Sorting

TPivotGrid supports 2 methods of sorting.

4.5.6.1 By measure values

In order to sort the slice by a measure's values, simply click on the measure's cell.  You will see a sort indicator appearing.

To reverse the sorting order, click on the measure's cell again.

To cancel the sort, right click on the measure's cell.

4.5.6.2 By the names of dimension items

To sort by a dimension's items captions, click on the dimension's cell.

To reverse the sort order, click on the dimension's cell again.

To cancel the sort, right click on measure's cell.

4.5.7 Cells width and height auto-formatting

TPivotGrid supports manual sizing for the header area only. Also, TPivotGrid provides the ability to automatically format all the columns width or rows height by specifying minimum and maximum cell dimensions.  The following properties of TPivotGrid are used:

4.5.8 Setting visual appearance (drawing styles, colors, fonts etc)

TPivotGrid provides a wide range of visual settings that allows the developer to customize the appearance of the grid.  These visualization settings are found in the following properties of TPivotGrid:

Settings.RowHeader visual settings for row-sided dimensions
Settings.ColumnHeader   visual settings for column-sided dimensions
Settings.Cells visual settings for data cells
Settings.Specific.Dimensions  visual settings for left-top square where dimension names placed
Settings.Totals visual settings for fixed totals (row and column)
Settings.Specific.Background visual settings for PivotGrid’s cells background or unused area.

Note: do not set Settings.Specific.Background.Style property to psWinXP to avoid delays, as a gradient square with PivotGrid.BoundRect size will be drawn before the cells are drawn.

4.5.9 Additional features

Setting name Function
Settings.Specific.ShowViewNames To show or hide rows with view names
Settings.Specific.ShowMeasureNames To show or hide row with Measure names
Settings.Specific.Lines To show or hide grid lines.
Settings.Specific.MultiLineHeader To prevent the use of multi-line text output in grid headers.
Settings.Specific.OddCellColor Color value which will replace cell background color in Odd rows and columns. Oddness is treated as dimension number, not row/column number.
Settings.Specific.ShowZero To disable show zero values regardless on measure format settings.

4.5.10 Working with keyboard

TPivotGrid supports the following keyboard shortcuts when used with the arrow keys.

Up, Down, Left, Right     move focused cell or scroll grid area
Alt +Down try to expand dimension item by current row
Alt+Right try to expand dimension item by current column
Alt+Up try to collapse currently expanded dimension item by row
Alt+Left try to collapse currently expanded dimension item by column

4.5.11 Selection area

TPivotGrid allows you to select a block of cells and perform calculations on them.  You can select a block of cells in two ways:

4.5.11.1 Selection subtotals

Once you have selected a range of cells, PivotCube will calculate subtotals for that range of cells.  By default this is a yellow row and column on the left and bottom of the selection

The subtotals is calculated as simple sum of the selected cells.  You can change the calculation algorithm using the OnCalcSelectionSubtotal event of TPivotGrid.

TCalcSelectionSubTotalEvent = procedure(Direction : boolean; Count : integer; Data : array of double; var Value : double) of object;

where

Direction - True for horizontal calculations, False for vertical calculations
Count - Number of values in array
Data - array of cell values by line of calculations
Value - output value. By default : summary of data elements

To enable / disable selection of cells subtotals, use the TPivotGrid.Settings.Specific.AllowSelectionSubTotals property.

PivotCube User Guide rev. 2004.02.10