Trying to understand pivot tables in XLSX

[David Thielen]

AARGH!

Is there something that goes into more detail than the XLSX docs. I'm
trying to figure this out but not having much luck.

For example pivotTableDefinition/colFields/field is used like:

<colFields count="1">
<field x="1"/>
</colFields>

And the docs say:

Specifies the index to a pivotField item value. There will be as many
x elements as there are item values in any particular column. Note
that these x elements sometimes are not explicitly written, but
instead "inherited" from the previous column or i element, via the
value of @r. The pivotField items don't list values explicitly, but
instead reference a shared item value in the pivotCacheDefinition
part. The first instance of x has no attribute value @v associated
with it, so the default value for @v is assumed to be "0".

The possible values for this attribute are defined by the XML Schema
int datatype.

How do you determine the value for x?

@r is not even documented - how is it used?

Any help both for the above specific question and the more general one
of where is their additional documentation would be much appreciated.

thanks - dave

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Jie Wang [MSFT]]

Hi David,

What "document" are you currently referencing? The Open XML Format SDK 2.0
doc? Or something else? Could you give me a link to it?

How do you determine the value for x?

I think you should have some pivotFields defined right? The value for x is
the index to of the pivotField item's index.

Not very sure about @r and @v, but if you can point me to the doc you're
using, I will try to find out more.

Thanks,
Jie Wang

Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business days is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[David Thielen]

Hi David,

What "document" are you currently referencing? The Open XML Format SDK 2.0
doc? Or something else? Could you give me a link to it?


I think you should have some pivotFields defined right? The value for x is
the index to of the pivotField item's index.

Not very sure about @r and @v, but if you can point me to the doc you're
using, I will try to find out more.

Hi;

What do you mean by the pivotField item's index? I think I don't
understand the basic design of the pivot table data.

For reference I am used the document Office Open XML Part 4 - Markup
Language Reference (tagged).pdf which I downloaded about 2 years ago.
Is there a more up to date reference set?

thanks - dave


david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Jie Wang [MSFT]]

Hi David,

A more updated document is the 2nd edition of ECMA-376 Office Open XML File
Formats, dated Dec. 2008.

You can get both 1st & 2nd editions from the ECMA website:
http://www.ecma-international.org/publications/standards/Ecma-376.htm

What do you mean by the pivotField item's index?

When you define column fields, you need to reference the pivotField section
(see 18.10.1.69 pivotField (PivotTable Field) for definition).

And you may want to read 18.10.1.73 pivotTableDefinition (PivotTable
Definition) for an overview of the pivot table structure.

Besides, the OpenXmlDeveloper.org and its forums are also nice places to
find resources & helps.

Regards,
Jie Wang

Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business days is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.