Difference between revisions of "Calc/Features/DataPilot drill-down on data field"

From Apache OpenOffice Wiki
< Calc‎ | Features
Jump to: navigation, search
m (added skeleton for spec template.)
m (fixed color coding of IDL language.)
 
(55 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
= DataPilot Drill-Down on Data Field =
 
= DataPilot Drill-Down on Data Field =
 +
{| border="2" cellpadding="4" cellspacing="0" style="margin: 1em 1em 1em 0;  border: 1px #cccccc solid; border-collapse: collapse; width: 100%"
 +
 +
|- align="left"
 +
| colspan="2" bgcolor="#cccccc"  | '''Specification Status'''
 +
|-
 +
| width="150" | '''Author''' || [[User:Kohei|Kohei Yoshida]]
 +
|-
 +
| width="150" | '''Last Change''' || ''See wiki history''
 +
|-
 +
| width="150" | '''Status''' || In progress in [http://eis.services.openoffice.org/EIS2/cws.ShowCWS?Path=SRC680%2Fkoheidatapilot01 CWS koheidatapilot01]
 +
|-
 +
|}
  
 
== Abstract ==
 
== Abstract ==
−
Foo
+
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.
 +
 
 +
__TOC__
  
 
== References ==  
 
== References ==  
−
Fill this.
+
{| border="2" cellpadding="4" cellspacing="0" style="margin: 1em 1em 1em 0;  border: 1px #cccccc solid; border-collapse: collapse; width: 100%"
 +
|-
 +
| width="300" bgcolor="#dddddd" | '''Reference Document''' || bgcolor="#dddddd" | '''Check''' || bgcolor="#dddddd" | '''Location (URL)'''
 +
|-
 +
|'''Issue ID''' (required)
 +
|available
 +
|[http://qa.openoffice.org/issues/show_bug.cgi?id=57030 i57030] [http://qa.openoffice.org/issues/show_bug.cgi?id=83250 i83250] [http://qa.openoffice.org/issues/show_bug.cgi?id=84349 i84349]
 +
|-
 +
|-
 +
| '''[[Test case specification]]''' (required)
 +
| n/a
 +
|
 +
|-
 +
 
 +
|}
  
 
== Contacts ==
 
== Contacts ==
−
Fill this.
+
{| border="2" cellpadding="4" cellspacing="0" style="margin: 1em 1em 1em 0;  border: 1px #cccccc solid; border-collapse: collapse; width: 100%"
 +
|-
 +
| width="300" bgcolor="#dddddd" | '''Role''' || bgcolor="#dddddd" | '''Name''' || bgcolor="#dddddd" | '''E-Mail Address'''
 +
|-
 +
| '''Developer'''
 +
| [[User:Kohei|Kohei Yoshida]]
 +
| kyoshida@novell.com
 +
|-
 +
| '''Quality Assurance'''
 +
| Frank Stecher
 +
| frank.stecher@sun.com
 +
|-
 +
| '''Documentation'''
 +
| nobody
 +
|
 +
|-
 +
| '''User Experience'''
 +
| nobody
 +
|
 +
|-
 +
|}
  
−
== Description ==
+
== Detailed Specification ==
−
When a cell within the data field is double-clicked, it inserts a new sheet containing a subset of rows from the original data source that constitutes the result data 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.
+
[[Image:CalcDataPilotDrillDownByMenu.png|thumb|right|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.
  
−
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.
+
Those rows that are included in the drill-down sheet are referred to as the ''constituent rows'' in this specification.
  
−
The same function is available using "Data / Group and Outline / Show Details" from the menu, with the cell cursor on the result cell.
+
=== 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.
  
−
Hidden items ("Hide items" or "Show automatically" in the field options dialog) are not evaluated, the rows for the hidden items are included.
+
The new sheet should always be inserted to the immediate left of the current active sheet on which the DataPilot table is.
  
−
The feature is available for DataPilot tables from cell ranges or database data, but not yet for external service implementations (see API Change below).
+
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.
  
−
== API Change (not for 2.4) ==
+
=== 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.
  
−
This functionality introduces the following new API.
+
== API Change (scheduled for 3.0) ==
  
−
<code>[cpp,N]
+
This functionality introduces the following new APIs.  All of the following APIs are in scope <code>com::sun::star::sheet</code>.
−
module com {  module sun {  module star {  module sheet {
+
  
−
interface XDataPilotTable2: com::sun::star::sheet::XDataPilotTable
+
=== DataPilotSource ===
−
{
+
The following three optional properties have been added to the existing '''DataPilotSource''' service:
−
    sequence< sequence< any > > getDrillDownData( [in] com::sun::star::table::CellAddress aAddr );
+
−
};
+
  
−
}; }; }; };
+
* '''RowFieldCount''' - specifies the number of row fields in the data source.
−
</code>
+
* '''ColumnFieldCount''' - specifies the number of column fields in the data source.
 +
* '''DataFieldCount''' - specifies the number of data fields in the data source.
  
−
<code>[cpp,N]
+
Note that these properties are read-only, thus you cannot directly set their values via API.
−
module com {  module sun {  module star {  module sheet {
+
  
−
interface XDrillDownDataSupplier: com::sun::star::uno::XInterface
+
<source lang="idl">
 +
published service DataPilotSource
 
{
 
{
−
     sequence< sequence< any > > getDrillDownData(  
+
     (....)
−
        [in] sequence< com::sun::star::sheet::DataPilotFieldFilter > aFilters );
+
 
 +
    //-------------------------------------------------------------------------
 +
 
 +
    /** 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;
 +
 
 
};
 
};
 +
</source>
 +
 +
=== DataPilotFieldFilter ===
 +
<source lang="idl">
 +
/** 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
−
</code>
+
  used as a single instance.</p>
  
−
<code>[cpp,N]
+
  @see <method>XDrillDownDataSupplier::getDrillDownData</method>
−
module com {  module sun {  module star {  module sheet {
+
  @see <type>DataPilotTableResultData</type>
  
 +
  @since OOo 3.0.0
 +
*/
 
struct DataPilotFieldFilter
 
struct DataPilotFieldFilter
 
{
 
{
−
     /** Field name. */
+
     /** Field name.
 +
 
 +
        @see <type>DataPilotField</type>
 +
    */
 
     string FieldName;
 
     string FieldName;
  
−
     /** String value that needs to match against. */
+
     /** String value to match against.
 +
    */
 
     string MatchValue;
 
     string MatchValue;
 
};
 
};
 +
</source>
  
−
}; }; }; };
+
=== DataPilotTablePositionData ===
−
</code>
+
<source lang="idl">
 +
/** This structure contains information on a cell within a DataPilot table.
  
−
The ''com.sun.star.sheet.DataPilotSource'' service will support the ''XDrillDownDataSupplier'' interface to provide a 2-dimensional array data consisting of a 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.  This interface is intended for an external UNO package so that it can overwrite the interface to provide constituent rows when requested.
+
    <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;
 +
};
 +
</source>
 +
 +
=== DataPilotTablePositionType ===
 +
[[Image:CalcDataPilotPositionType.png|thumb|right|Position type within a DataPilot table.]]
 +
<source lang="idl">
 +
/** 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;
 +
 +
    //------------------------------------------------------------------------
 +
};
 +
</source>
 +
 +
=== DataPilotOutputRangeType ===
 +
[[Image:CalcDataPilotOutputRangeWhole.png|thumb|right|Output range (WHOLE)]]
 +
[[Image:CalcDataPilotOutputRangeTable.png|thumb|right|Output range (TABLE)]]
 +
[[Image:CalcDataPilotOutputRangeResult.png|thumb|right|Output range (RESULT)]]
 +
<source lang="idl">
 +
/** 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;
 +
};
 +
</source>
 +
 +
=== DataPilotTableHeaderData ===
 +
<source lang="idl">
 +
/** 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;
 +
};
 +
</source>
 +
 +
=== DataPilotTableResultData ===
 +
<source lang="idl">
 +
/** 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;
 +
};
 +
</source>
 +
 +
=== 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.
 
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.
 +
 +
<source lang="idl">
 +
/** 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 );
 +
};
 +
</source>
 +
 +
=== 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.
 +
 +
<source lang="idl">
 +
/** 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 );
 +
};
 +
</source>
  
 
[[Category:Calc|Features/DataPilot drill-down on data field]]
 
[[Category:Calc|Features/DataPilot drill-down on data field]]
Line 71: Line 463:
  
 
== Migration ==
 
== 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 ==
 
== Configuration ==
 +
This feature re-uses the existing "Enable drill to details" option within the DataPilot dialog to toggle on/off.
  
 
== File Format ==
 
== File Format ==
 +
 +
This feature will not introduce a file format change.
  
 
== Open Issues ==
 
== 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.
  
 
[[Category:Calc|Features/DataPilot drill-down on data field]]
 
[[Category:Calc|Features/DataPilot drill-down on data field]]
 
[[Category:Feature|DataPilot drill-down on data field]]
 
[[Category:Feature|DataPilot drill-down on data field]]

Latest revision as of 16:56, 21 July 2008

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.

Retrieved from "https://wiki.openoffice.org/w/index.php?title=Calc/Features/DataPilot_drill-down_on_data_field&oldid=87521"
Personal tools