Pivot tables can be tricky by nature and a significant portion of my support work has been dedicated to helping customers incorporate pivot tables with ExcelWriter.

One customer wrote in with the following issue: his client had recently imposed a constraint that all reports had to be in the .xls format, so he needed to convert several OfficeWriter reports designed in Excel 2007 to the 2003 format, but he was having trouble switching the pivot tables.

When he tried to save the 2007 format report as a 2003 format report, the pivot tables were locked in a read-only state, wherein users could only edit the pivot tables if the report was resaved in the 2007 format. After doing some testing in Excel 2003 and 2007, I found that this was the native behavior for Excel files as well.

After some more investigation, I found that there is no means of converting a version 12 pivot table (a.k.a. a pivot table developed in Excel 2007) to an earlier version (i.e. version 10 for the Excel 2003). The only option available is to save the file in the earlier format and then recreate the table from scratch.

Luckily, saving the report in the Office 2003 or earlier format and then recreating the pivot table using the instructions for Using Pivot Tables with the ExcelTemplate Object and Data Markers works perfectly.

A word of caution: when creating pivot tables in Excel 2007 for the binary format, make sure that the file is in Compatibilty Mode before creating the table. Otherwise, the table will be a version 12 pivot table and you won’t even know until you go to save and the compatibility checker says there’s been a loss of fidelity. (Excel 2007 will automatically enter compatibility mode if the file opened is in the Excel 2003 format).

Some things you should know about Excel 2007 pivot tables:

-Upgrading from older versions is easy – just save the file or report in the newer format (.xlsx or Office 2007 RDL)

-ExcelWriter no longer throws errors if the data markers get pulled into a version 12 pivot table. The pivot tables do experience issues with the data markers being displayed in the drop-down filters if the data markers are pulled into the pivot table in the template. We recommend following the instructions outlined in the article mentioned earlier.

Pivot Table Resources

• KB Article 386 – the basic instructions for using Pivot Tables in ExcelWriter
• KB Article 1120 – the IE bracket naming problem
• Working with different Pivot Table formats in Office Excel – a MS article describing the differences between Version 10 and Version 12 pivot tables.
• Office 2007 Compatibility Mode Confusion – all about compatibility mode in Word and Excel 2007 (useful for creating v10 pivot tables in Excel 2007).
• Supported/Unsupported Pivot Table features – a list of the Excel 97-2003 and 2007 pivot table features and the versions they are supported in.
• Pivot Table User Manual – a guide to creating and manipulating pivot tables in Excel and in macros
• Debra Dalgleish has a spectacularly comprehensive list of Pivot Table-related posts.
• If you prefer to get your learning on audibly, check out Bill Jelen‘s (aka Mr. Excel) podcasts on the subject.

Pivot tables can be tricky by nature and a significant portion of my support work has been dedicated to helping customers incorporate pivot tables with ExcelWriter. One customer wrote in with the following issue: his client had recently imposed a constraint that all reports had to be in the .xls format, so he needed to convert several OfficeWriter reports designed in Excel 2007 to the 2003 format, but he was having trouble switching the pivot tables. When he tried to save the 2007 format report as a 2003 format report, the pivot tables were locked in a read-only state, wherein users could only edit the pivot tables if the report was resaved in the 2007 format. After doing some testing in Excel 2003 and 2007, I found that this was the native behavior for Excel files as well. After some more investigation, I found that there is no means of converting a version 12 pivot table (a.k.a. a pivot table developed in Excel 2007) to an earlier version (i.e. version 10 for the Excel 2003). The only option available is to save the file in the earlier format and then recreate the table from scratch. Luckily, saving the report in the Office 2003 or earlier format and then recreating the pivot table using the instructions for Using Pivot Tables with the ExcelTemplate Object and Data Markers works perfectly. A word of caution: when creating pivot tables in Excel 2007 for the binary format, make sure that the file is in Compatibilty Mode before creating the table. Otherwise, the table will be a version 12 pivot table and you won’t even know until you go to save and the compatibility checker says there’s been a loss of fidelity. (Excel 2007 will automatically enter compatibility mode if the file opened is in the Excel 2003 format). Some things you should know about Excel 2007 pivot tables: -Upgrading from older versions is easy – just save the file or report in the newer format (.xlsx or Office 2007 RDL) -ExcelWriter no longer throws errors if the data markers get pulled into a version 12 pivot table. The pivot tables do experience issues with the data markers being displayed in the drop-down filters if the data markers are pulled into the pivot table in the template. We recommend following the instructions outlined in the article mentioned earlier. Pivot Table Resources KB Article 386 – the basic instructions for using Pivot Tables in ExcelWriter KB Article 1120 – the IE bracket naming problem Working with different Pivot Table formats in Office Excel – a MS article describing the differences between Version 10 and Version 12 pivot tables. Office 2007 Compatibility Mode Confusion – all about compatibility mode in Word and Excel 2007 (useful for creating v10 pivot tables in Excel 2007). Supported/Unsupported Pivot Table features – a list of the Excel 97-2003 and 2007 pivot table features and the versions they are supported in. Pivot Table User Manual – a guide to creating and manipulating pivot tables in Excel and in macros Debra Dalgleish has a spectacularly comprehensive list of Pivot Table-related posts. If you prefer to get your learning on audibly, check out Bill Jelen‘s (aka Mr. Excel) podcasts on the subject.

Featured Posts End User’s Guide to SharePoint Chronicles of Pitan, Pivot Mage Stories from the WIT Trenches The Booth Babes Debate OfficeWriter or Excel Services                Popular Tags bes bi blackberry c_sharp excel excelwriter hadoop iis officewriter pivot_tables powershell rim sharepoint sharepoint_2007 sharepoint_2010 sharepoint_for_end_users spc11 sql sql_server ssrs twitter visual_studio windows_server_2008_r2 wit xml SA Bloggers Alison Bird Ben Jones Cameron Fischer Chris Baldwin Claire Willett Dan Medeiros David Wihl Dinh-Yen Tran George Schneeloch Greg Ayer Jim Stallings Jonathan Briggs Nick Martin Ramon Armen Sam Haddad Scott Dugas Sean Kermes Tamar Rucham Dan Wieme Popular posts Suspending Development of our BlackBerry client software for Microsoft SharePoint Boston SharePoint Salon–Go Big or Go Home How to create a dynamic Reporting Services shared data source using linked servers Angels at the Gate–Tech Conferences and the Booth Babe Strategy Macro Security Settings in Excel 2007 and 2010 Blogroll SharePoint Andrew Connell Bil Simser Chris O’Brien Fabian Williams Jeremy Thanke Joel Oleson Nothing But SharePoint Todd Klindt Tom Dietz Mark Rackley Marc Anderson Geoff Varosky SQL Adam Machanic Brad McCehee Brent Ozar Grant Fritchey Jamie Thomson Paul Randal Pinalkumar Dave Steve Jones Thomas Larock Excel Services / PowerPivot Dick Kusleika Dick Moffat Jon Peltier Ken Puls Purna Duggirala Rob Collie Stephen Few Torben Seebach RSS Feed

Labels parameters Labels: None excel excel Delete officewriter officewriter Delete pivot_tables pivot_tables Delete Edit Labels Enter labels to add to this page: Looking for a label? Just start typing. Add Comment

Follow

Related posts: