Basics
Data pilot object has the concept of dimensions, where each dimension can be one of column, row, data, or page dimension. These dimensions are sometimes also referred to as fields i.e. column field, row field etc. Each dimension also has multiple members, and each member corresponds with each individual value. You can think of a dimension as a column in a tabular data, and a member as a cell. There is also another dimension called the data layout dimension, which is always a row dimension and is the last one in the row dimenstion set. It is normally not displayed unless more than one data dimensions are used, in which case the data layout dimension is displayed in the row header section to label each data.
Inside the core implementation of DataPilot, the term dimension tends to be used to refer to a unit column data set whereas the term field tends to be used outside it such as UNO API and user documentation. But they basically refer to the same thing.
Interfacing with Calc
At higher level, ScPivotShell’s Execute method receives all data pilot related shell requests (delete, refresh, and filter to be precise). Refresh and delete requests are simply routed to ScDBFunc’s RecalcPivotTable and DeletePivotTable methods respectively. Execution of creation and modification of a data pilot, i.e. when the user clicks OK on the main DataPilot dialog, is handled by ScCellShell’s ExecuteDB method (which eventually calls ScDBFunc’s MakePivotTable method). The same method also handles the launching of the dialog itself, associated with the SID_OPENDLG_PIVOTTABLE slot ID.
Top-level data pilot object
Each data pilot in Calc document is represented by ScDPObject.
Class ScDPOutput represents each data pilot view that is actually displayed on the sheet. The instance of this class stores the geometry of the data pilot view it represents. The multi-level column, row, and page fields information are also stored herein. Each field can have up to 256 levels of headers, and instances of ScDPOutLevelData are used to store the field information (aCaption data member stores the label of a field, and aResult data member stores the value of each column or row as an array ordered from left to right). Have a look at pColFields, pRowFields, and pPageFields that are pointer data members of ScDPOutput. The current levels of column, row, and page fields are stored in nColFieldCount, nRowFieldCount, and nPageFieldCount data members, respectively. These numbers get updated only when an instance of ScDPOutput is constructed; in other words, every time a new level gets introduced in a data pilot view, Calc destroys the old instance of ScDPOutput and constructs a new one to take its place.
The data source used to create the data pilot output is stored with the ScDPObject object as an XDimensionsSupplier interface instance, which is implemented by ScDPSource class. A reference to the XDimensionsSupplier instance can be obtained via ScDPObject’s GetSource method. The XNameAccess reference object returned by the XDimensionsSupplier instance (via getDimensions call) is implemented by ScDPDimensions class.
Data source access
To build a data pilot output, you have to parse the original data source at some point.
ScDPTableData abstracts the data source access, which can be either a cell range in the same document or an external data source accessed via the registered database functionality. ScDPTableData has three child classes: ScSheetDPData, ScDatabaseDPData, and ScDPGroupTableData (the last one is just a proxy implementation of ScDPTableData to add grouped items). ScSheetDPData represents a sheet data source within the same document with the data pilot output. Its GetNextRow method makes actual calls to the cell instances to get their values. The GetNextRow method is expected to be called in a loop, and each call retrieves a row, and increments the iterator position.
There is only one instance of ScDPTableData, which gets created in ScDPObject’s CreateObjects method, and is passed to the ScDPSource instance, which takes care of destroying it in the end.
Constructing data pilot output view from source data
The heavy-lifting action of processing the source data and constructing a data pilot output from them is all done within ScDPSource’s CreateRes_Impl method, where an instance of ScDPResultData and two instances of ScDPResultMember (for column and row “roots”) are created, and the source data get scanned. ScDPResultData stores metadata on the data field, but not the individual row values themselves.
There is always an extra row dimention following the last real data row, which is used as a data layout dimension. A given dimension is a data layout dimension when its dimension ID (corresponds to its column ID) is the last data column ID + 1. The data layout dimension, according to the Developer’s Guide, contains the names of the data dimensions.
To get a list of unique entries for each column, ScDPTableData’s virtual function GetColumnEntries gets called. Since this method is pure virtual, it must be overwritten by each derived class. ScSheetDPData’s implementation of GetColumnEntries scans all individual cells and pick up their string values, then use TypedStrCollection container class to reduce them down to the unique set of values. It uses ScDocument’s GetString method to get the string value from each cell, even for numeric cells using the number formatter.
ScDPItemData is used to hold a single cell data during the scanning of source data, but this class is also used in other places.
ScDPResultData
…
ScDPResultMember
ScDPSource maintains two instances of class ScDPResultMember, one for the column, and another for the row. They are instantiated in CreateRes_Impl and kept until the instance of ScDPSource gets destroyed.
Grouping of dimensions
ScDPGroupTableData represents data source with grouped dimensions. You can group, for example, multiple row fields by highlighting them and selecting from the menu Data – Group and Outline – Group. An instance of class ScDPGroupTableData takes a pointer to the real data source instance, keeps it within, and destroys it when the ScDPGroupTableData itself is destroyed. ScDPGroupTableData keeps multiple instances of ScDPGroupDimension and ScDPNumGroupDimension, the former is a dynamic array (vector) while the latter is a static, C-type array.
Double-click on member to expand or shrink
ScDPositionData stores basic information of a given single cell position within the data pilot area. It is used, for example, when the user double-clicks within a data pilot region by passing an empty instance of that class to the GetPositionData method of the corresponding ScDPObject instance, which in turn calls GetPositionData of ScDPOutput class instance.
ScDPObject’s ToggleDetails method handles expanding and shrinking of child data members of the member whose cell is double-clicked on.
Switching page field value
The user can change the page field value from the page field list box located above the main data pilot box. When the user commits the value change, ScGridWindow’s FilterSelect gets called from the list box handler (ScFilterListBox), and subsequently ExecPageFieldSelect of the same class gets called. The ScFilterListBox instance knows which mode it is operating under. Have a look at ScFilterBoxMode enum values for possible mode values for ScFIlterListBox.
ScDBDocFunc’s DataPilotUpdate is the top-level routine that oversees the entire update operation. This is where the source data gets invalidated, and subsequently reloaded (i.e. CreateRes_Impl gets called to re-build the result data from the data source once again). DataPilotUpdate is also called from ScDBFunc::RecalcPivotTable when the output is being refreshed.
Filtering data source
When the user clicks the Filter button at the top of the data pilot area, ScGridWindow’s DoPushButton gets called. (This method also gets called when clicking on other buttons within the data pilot output.) Then the filter dialog gets displayed and returns when done. If the OK is pressed, it creates a new instance of ScSheetSourceDesc with the query parameter from the filter dialog, and passes it to a new instance of ScDPObject. ScDBDocFunc’s DataPilotUpdate gets called in the end to execute the update operation.
