Calc/Features/DataPilot drill-down on data field
DataPilot Drill-Down on Data Field
| Specification Status | |
| Author | Kohei Yoshida |
| Last Change | See wiki history |
| Status | In progress in CWS koheidatapilot01 |
Abstract
This feature enables displaying of those individual records (or rows) that constitute the value displayed in a particular result cell of a DataPilot table, and extends the current DataPilot-related API to make use of this feature, as well as to expose more parameters of a DataPilot table to ease verification of values displayed in the result area.
Contents
References
| Reference Document | Check | Location (URL) |
| Issue ID (required) | available | i57030 i83250 i84349 |
| Test case specification (required) | n/a |
Contacts
| Role | Name | E-Mail Address |
| Developer | Kohei Yoshida | kyoshida@novell.com |
| Quality Assurance | Frank Stecher | frank.stecher@sun.com |
| Documentation | nobody | |
| User Experience | nobody |
Detailed Specification
Basics
This feature enables displaying of those individual records (or rows) that constitute the result value displayed in a particular cell of the result area of a DataPilot table. This feature also extends the current DataPilot-related API to make use of this feature, as well as to expose more parameters of a DataPilot table to ease verification of result values displayed in the result area.
Those rows that are included in the drill-down sheet are referred to as the constituent rows in this specification.
Inserting drill-down sheet
There are two ways to insert a drill-down sheet for a result cell. One way is to double-click on the cell itself which should insert a new sheet with the constituent rows extracted from the source data, while another way is to select Data - Group and Outline - Show Details from the menu.
The new sheet should always be inserted to the immediate left of the current active sheet on which the DataPilot table is.
Calc should not insert a drill-down sheet when the current cell is outside the result area or the result cell contains no constituent rows.
Note for 2.4
This feature is partially integrated into 2.4, but is only available for DataPilot tables from cell ranges or database data, but not for external service implementations. The API changes are also not available for 2.4.
API Change (scheduled for 3.0)
This functionality introduces the following new APIs. All of the following APIs are in scope com::sun::star::sheet.
DataPilotSource
The following three optional properties have been added to the existing DataPilotSource service:
- RowFieldCount - specifies the number of row fields in the data source.
- ColumnFieldCount - specifies the number of column fields in the data source.
- DataFieldCount - specifies the number of data fields in the data source.
Note that these properties are read-only, thus you cannot directly set their values via API.
published service DataPilotSource { (....) //------------------------------------------------------------------------- /** specifies the number of row fields. */ [readonly, property, optional] long RowFieldCount; //------------------------------------------------------------------------- /** specifies the number of column fields. */ [readonly, property, optional] long ColumnFieldCount; //------------------------------------------------------------------------- /** specifies the number of data fields. */ [readonly, property, optional] long DataFieldCount; };
DataPilotFieldFilter
/** A single filtering condition used when constructing a drill-down sheet. <p>Each instance of <type>DataPilotFieldFilter</type> represents a filtering condition. It is used when evaluating a row in a data source whether or not to include it in a filtered subset of the original data. For example, for a given row to be included in the output of a drill-down data table, the row must match every single instance of <type>DataPilotFieldFilter</type> to be qualified. If the field specified by the value of <member>FieldName</member> is not found, then that filter condition will not be evaluated.</p> <p>Note that this struct is typically used in a sequence and is not normally used as a single instance.</p> @see <method>XDrillDownDataSupplier::getDrillDownData</method> @see <type>DataPilotTableResultData</type> @since OOo 3.0.0 */ struct DataPilotFieldFilter { /** Field name. @see <type>DataPilotField</type> */ string FieldName; /** String value to match against. */ string MatchValue; };
DataPilotTablePositionData
/** This structure contains information on a cell within a DataPilot table. <p>This structure contains information on a particular cell within a DataPilot table, and is used to retrieve its metadata. The <member>PositionType</member> member specifies in which sub-area of the table the cell is positioned, which in turn determines the type of metadata contained in the <member>PositionData</member> member.</p> @see com::sun::star::sheet::DataPilotTablePositionType @see com::sun::star::sheet::DataPiotTableResultData @see com::sun::star::sheet::DataPiotTableHeaderData @since OOo 3.0.0 */ struct DataPilotTablePositionData { //------------------------------------------------------------------------- /** This parameter specifies which sub-area of a DataPilot table a given cell is positioned. See <type>DataPilotTablePositionType</type> for how to interpret the value of this parameter. @see com::sun::star::sheet::DataPilotTablePositionType */ long PositionType; //------------------------------------------------------------------------- /** <p>This member contains a structure of different types depending on the position type specified in <member>PositionType</member> member.</p> <p>When the value of <member>PositionType</member> is <const>DataPilotTablePositionType::RESULT</const>, <member>DataPilotTablePositionData::PositionData</member> contains an instance of type <type>DataPilotTableResultData</type>, whereas when the value of <member>DataPilotTablePositionData::PositionType</member> is either <const>DataPilotTablePositionType::ROW_HEADER</const> or <const>DataPilotTablePositionType::COLUMN_HEADER</const>, then the <member>PositionData</member> member contains an instance of type <type> DataPilotTableHeaderData</type>.</p> @see com::sun::star::sheet::DataPiotTableResultData @see com::sun::star::sheet::DataPiotTableHeaderData */ any PositionData; };
DataPilotTablePositionType
/** specifies in which sub-area a cell is positioned within a DataPilot table. @see com::sun::star::sheet::DataPilotTablePositionData @see com::sun::star::sheet::DataPilotTableResultData @see com::sun::star::sheet::DataPilotTableHeaderData @since OOo 3.0.0 */ constants DataPilotTablePositionType { //------------------------------------------------------------------------ /** indicates that the specified cell is not in the DataPilot table. */ const long NOT_IN_TABLE = 0; //------------------------------------------------------------------------ /** indicates that the specified cell is within the result area. */ const long RESULT = 1; //------------------------------------------------------------------------ /** indicates that the specified cell is within the row header area. */ const long ROW_HEADER = 2; //------------------------------------------------------------------------ /** indicates that the specified cell is within the column header area. */ const long COLUMN_HEADER = 3; //------------------------------------------------------------------------ };
DataPilotOutputRangeType
/** specifies region type of DataPilot table range <p>This constant set is used to indicate the type of output range desired when <method>XDataPilotTable2::getOutputRangeByType</method> is called, which returns a different cell range depending upon the value passed to it as the argument.</p> @see com::sun::star::sheet::XDataPilotTable2 @since OOo 3.0.0 */ constants DataPilotOutputRangeType { //------------------------------------------------------------------------ /** whole DataPilot output range including the header area above the table where the filter and page field buttons are located. */ const long WHOLE = 0; //------------------------------------------------------------------------ /** whole table but without the header area where the filter and page field buttons are located. */ const long TABLE = 1; //------------------------------------------------------------------------ /** result area where the result values are displayed. This also includes the column and row subtotal areas when they are displayed. */ const long RESULT = 2; };
DataPilotTableHeaderData
/** information about a cell within the column or row header area of a DataPilot table. This struct contains information about a particular cell located within the column or row header area of a DataPilot table. This is the type that is contained in <member>DataPilotTablePositionData::PositionData</member> when the value of <member>DataPilotTablePositionData::PositionType</member> is either <const>DataPilotTablePositionType::ROW_HEADER</const> or <const>DataPilotTablePositionType::COLUMN_HEADER</const>. @see com::sun::star::sheet::DataPilotTablePositionData @see com::sun::star::sheet::DataPilotTablePositionType @see com::sun::star::sheet::DataPilotFieldFilter @see com::sun::star::sheet::DataResult @since OOo 3.0.0 */ struct DataPilotTableHeaderData { /** number of dimensions */ long Dimension; /** hierarchy */ long Hierarchy; /** level */ long Level; /** flag */ long Flags; /** member name */ string MemberName; };
DataPilotTableResultData
/** information about a cell positioned within the result area of a DataPilot table. <p><type>DataPilotTableResultData</type> contains information about a particular cell positioned within the result area of a DataPilot table.</p> @see com::sun::star::sheet::DataPilotTablePositionData @see com::sun::star::sheet::DataPilotTablePositionType @see com::sun::star::sheet::DataPilotFieldFilter @see com::sun::star::sheet::DataResult @since OOo 3.0.0 */ struct DataPilotTableResultData { //------------------------------------------------------------------------ /** This is a set of filter criteria that can be used to re-create those data rows that contribute to the value shown in the cell. @see com::sun::star::sheet::DataPilotFieldFilter */ sequence< DataPilotFieldFilter > FieldFilters; //------------------------------------------------------------------------ /** This is a 0-based index that specifies which data field the data displayed in the cell is for; the value of 0 means the cell is for the first data field, 1 for the second, and so on. */ long DataFieldIndex; //------------------------------------------------------------------------ /** more information about the result contained in the <type>DataResult</type> type. @see com::sun::star::sheet::DataResult */ DataResult Result; };
XDataPilotTable2
The interface com.sun.star.sheet.XDataPilotTable2 extends the existing com.sun.star.sheet.XDataPilotTable interface to provide additional method getDrillDownData() so that the client code can obtain constituent rows from the data pilot table. The existing com.sun.star.sheet.DataPilotTable service will support this new interface.
/** additional methods to extend <type scope="com::sun::star::sheet">XDataPilotTable</type>. <type scope="com::sun::star::sheet">XDataPilotTable2</type> extends the old <type scope="com::sun::star::sheet">XDataPilotTable</type> interface with additional methods. @see com::sun::star::sheet::XDataPilotTable @since OOo 3.0.0 */ interface XDataPilotTable2: com::sun::star::sheet::XDataPilotTable { /** When the address of a cell within the result area is given, <method>XDataPilotTable2::getDrillDownData</method> returns its drill-down output table that includes only those rows that contribute to the value of that cell. @param aAddr cell address within the result area of a DataPilot table. @returns drill-down output as 2-dimensional sequence, including the header row. @see <method>XDataPilotTable2::insertDrillDownSheet</method> */ sequence< sequence< any > > getDrillDownData( [in] com::sun::star::table::CellAddress aAddr ); /** Given a cell address, it returns the information about that cell. The type of information returned depends upon whether the cell is within the result area or column/row header area. @param aAddr address of the cell whose information is to be returned. @returns <type>DataPilotTablePositionData</type> which contains the position type and the information for that cell position. @see com::sun::star::sheet::DataPilotTablePositionData @see com::sun::star::sheet::DataPilotTableHeaderData @see com::sun::star::sheet::DataPilotTableResultData */ DataPilotTablePositionData getPositionData( [in] com::sun::star::table::CellAddress aAddr ); /** <p>This method inserts a new sheet to display the drill-down data for a specified result cell. A drill-down data for a result cell consists of a subset of rows from the original data source that contribute to the value displayed in that cell.</p> <p>The new sheet is always inserted to the immediate left of the current sheet where the DataPilot table is. Note that when the drill-down data is empty, no new sheet is inserted.</p> @param aAddr address of a result cell @returns <void/> */ void insertDrillDownSheet( [in] com::sun::star::table::CellAddress aAddr ); /** <p>This method returns a different output range of a DataPilot table per specified output range type.</p> @returns <type scope="com::sun::star::table">CellRangeAddress</type> depicting the range specified. See <type>DataPilotOutputRangeType</type> for a set of possible output range types. @see com::sun::star::sheet::DataPilotOutputRangeType */ com::sun::star::table::CellRangeAddress getOutputRangeByType( [in] long nType ); };
XDrillDownDataSupplier
The com.sun.star.sheet.DataPilotSource service will support the XDrillDownDataSupplier interface to provide a 2-dimensional array data consisting of a filtered subset of the original data source table that satisfies a given set of filtering criteria. A sequence of DataPilotFieldFilter provides desired filtering criteria to getDrillDownData() method. An external UNO package can optionally implement this interface so that it can provide drill-down data when requested.
/** supplies a filtered subset of the original data source based on filtering criteria. A service that acts as a DataPilot data source can optionally implement this interface to allow drill-down of result data. The method this interface provides is used internally when calling <method>XDataPilotTable2::getDrillDownData</method> or <method>XDataPilotTable2::insertDrillDownSheet</method>. If the data source service does not implement this interface, then the aformentioned two methods will have no effect. @see com::sun::star::sheet::DataPilotSource @since OOo 3.0.0 */ interface XDrillDownDataSupplier: com::sun::star::uno::XInterface { /** This method returns filtered subset of the original source data based on a given set of filtering criteria. @param aFilters filtering criteria @returns a filtered subset of the origintal source data as 2-dimensional sequences of <type scope="com::sun::star::uno">Any</any>. The first row must be the header row. Each <type scope="com::sun::star::uno">Any</type> instance must contain either <type>double</type> value for a numeric cell, or a <type>string</type> value for a string cell. @see com::sun::star::sheet::DataPilotFieldFilter @see com::sun::star::sheet::XDataPilotTable2 */ sequence< sequence< any > > getDrillDownData( [in] sequence< com::sun::star::sheet::DataPilotFieldFilter > aFilters ); };
Migration
This does not affect migration as it is a completely new feature. However, it may help migration from Excel as Excel already has similar feature in place.
Configuration
This feature re-uses the existing "Enable drill to details" option within the DataPilot dialog to toggle on/off.
File Format
This feature will not introduce a file format change.
Open Issues
Hidden items
Hidden items ("Hide items" or "Show automatically" in the field options dialog) are currently not evaluated, and the rows for the hidden items are included in the drill-down sheet. This is applicable only when the result cell is a subtotal cell. The correct behavior is to not include the hidden items.
