asked on

Pivot Row Label Field Missing After Conversion

I have an excel workbook (MSO 2003) that I have converted to an xlsm file in 2010, that contains pivot tables sourced from an access database. I have converted the access database (also 2003) to an accdb file in 2010 and need to update the pivot table connection sources to point to this newly converted db. Everytime I go in to update the pivot table connection source, the pivot table refreshes without the field that should be in the row label section. I can easily add the field back into the table, but I am wondering if anyone can explain why this might be happening? I have performed this conversion and connection updating on other spreadsheets and the pivot tables all seem to be fine. Any ideas??

puppydogbuddy

I am assuming that your "conversion" has much in common to the process of using worksheet data to generate a pivot table report. If that is the case, then there could be any of several reasons, most which relate to the worksheet used as the data source. Clink the link below. When the web page comes up, click the hyperlink "Working with source data"; then go to the section on worksheet data. There are tips for handling different types of worksheet data before you generate a Pivot Table report from the worksheet data.
http://office.microsoft.com/en-us/excel-help/overview-of-pivottable-and-pivotchart-reports-HP010177384.aspx#BMworking_with_pivottable_and_pivotchar

meridythe

ASKER

Thanks for the reply puppydogbuddy, but my pivot table is linked to a query in an access db ("I have an excel workbook (MSO 2003) that I have converted to an xlsm file in 2010, that contains pivot tables sourced from an access database"). All I am doing is updating the connection sources that the pivot tables are using, to point to the accdb file instead of the mdb. I am not sourcing my pivot tables from worksheet data.

puppydogbuddy

OK, thanks for the clarificiation.

Firstly, just want to be sure the caution statement below taken from this reference is not related to the missing field in the row label section of the Pivot Table.
http://technet.microsoft.com/en-us/library/cc179188(office.12).aspx
Caution: Users should consider existing links between workbooks before your organization converts existing workbooks to the Open XML Formats. Because earlier versions of Office Excel cannot update links to workbooks that are saved in the Open XML Formats, all linked workbooks should be simultaneously converted. see this link for a codeapproach: http://www.bidn.com/forums/microsoft-business-intelligence/integration-services/167/for-each-loop-container-looping-through-folder-with-excel-files
__________________________________________________________________________________
Secondly, see the notes below from this reference for the Excel Connections Manager Editor. Not sure Whether the xlsm file you converted to is considered to be an Excel data source in this context
http://msdn.microsoft.com/en-us/library/ms186848(v=SQL.90).aspx

Note:
While you use an Excel connection manager to connect to a data source that uses Microsoft Office Excel 2003 or earlier, you cannot use an Excel connection manager to connect to a data source that uses Microsoft Office Excel 2007. Also, you cannot use the Microsoft Jet 4.0 OLE DB Provider to connect to an Excel 2007 data source. To connect to an Excel 2007 data source, use an OLE DB connection manager, and for Provider, select Microsoft Office 12.0 Access Database Engine OLE DB Provider. Then, on the All page of the Connection Manager dialog box, for Extended Properties, enter Excel 12.0.
___________________________________________________________________________________
Thirdly, you may need for to install the Data Connectivity driver at this link:
http://www.microsoft.com/downloads/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en

ASKER CERTIFIED SOLUTION

Jerry Paladino

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

meridythe

ASKER

Hi Jerry,
Thank you soooo much for that Alt-D-P trick!!!!! That is awesome!!! I have been missing that wizard :)

I think you might be on to something with the naming of the field. It looks as if there are two fields with the same name wihtin the data sourcing this pivot, one called only UWDivReg and one with the query name prefixing the field (qryNM.UWDivReg). I am thinking that this duplication is causing the issue, because after the file is converted and relinked, the 2010 version removes the query prefix and renames the second field to UWDivReg2.

Jerry Paladino

Meridythe,
You're welcome and thanks for the points! The Alt-D-P has been a live saver for me too on occasion. I think you are right on the field names. Glad to help...
Jerry