Calc/Features/DataPilot drill-down on data field

From Apache OpenOffice Wiki
< Calc‎ | Features
Jump to: navigation, search

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.

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

Activating drill-down on result data from menu

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

Position type within a DataPilot table.
/** 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

Output range (WHOLE)
Output range (TABLE)
Output range (RESULT)
/** 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.

Personal tools