DataPilot Drill-Down on Data Field

Specification Status
Author	Kohei Yoshida
Last Change	See wiki history
Status	In progress in CWS koheidatapilot01

Abstract

Foo

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 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.

(TODO: to be continued....)

Original Text (needs editing)

When a cell within the result area of a DataPilot table is double-clicked, it inserts a new sheet containing a subset of rows from the original data source that constitutes the value displayed in that cell.

For instance, when the data field function is selected to be SUM, then the number that is shown in the data field cell must be identical to the sum of all the data field values in the constituent rows displayed in the inserted sheet.

The area that can provide a drill-down sheet spans from the top-left cell in the data field to the bottom-right corner of the data pilot output, including the Total Result row and column.

The same function is available using "Data / Group and Outline / Show Details" from the menu, with the cell cursor on the result cell.

Hidden items ("Hide items" or "Show automatically" in the field options dialog) are not evaluated, the rows for the hidden items are included.

The feature is available for DataPilot tables from cell ranges or database data, but not yet for external service implementations (see API Change below).

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.

[idl,N] 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

[idl,N] /** A single filtering condition used when constructing a drill-down sheet.


   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.
   Note that this struct is typically used in a sequence and is not normally 
   used as a single instance.
  @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

[idl,N] /** This structure contains information on a cell within a DataPilot table.


    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.
   @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;

	//-------------------------------------------------------------------------

    /** This member contains a structure of different types depending on the 
        position type specified in <member>PositionType</member> member.
        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>.
       @see com::sun::star::sheet::DataPiotTableResultData
       @see com::sun::star::sheet::DataPiotTableHeaderData
    */
   any     PositionData;

};

DataPilotTablePositionType

[idl,N] /** 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

[idl,N] /** specifies region type of DataPilot table range


    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.
   @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

[idl,N] /** 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

[idl,N] /** information about a cell positioned within the result area of a DataPilot table.


    <type>DataPilotTableResultData</type> contains information about a 
    particular cell positioned within the result area of a DataPilot table.
   @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.

[idl,N] /** 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 );

    /** 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.
        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.
       @param aAddr address of a result cell

       @returns <void/>
    */
   void insertDrillDownSheet( [in] com::sun::star::table::CellAddress aAddr );

    /** This method returns a different output range of a DataPilot table 
        per specified output range type.
       @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.

[idl,N] /** 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

Calc/Features/DataPilot drill-down on data field

DataPilot Drill-Down on Data Field

Abstract

Contents

References

Contacts

Detailed Specification

Basics

Original Text (needs editing)

API Change (scheduled for 3.0)

DataPilotSource

DataPilotFieldFilter

DataPilotTablePositionData

DataPilotTablePositionType

DataPilotOutputRangeType

DataPilotTableHeaderData

DataPilotTableResultData

XDataPilotTable2

XDrillDownDataSupplier

Migration

Configuration

File Format

Open Issues

Views

Personal tools

Navigation

Search

Tools