Adding a PivotTable using data connection to a OLAP Cube

[openmindParticipant]

Have been trying hard to to use Open Xml 2.7.1 to get that work, but the generated Excel package just keep failing. Has anyone tried that and any code sample we can browse and learn?

We started with an Excel package that has worksheets and data connections created already, and then simply add a new empty pivot table thru Open Xml. We also manually created an empty PivotTable using the same connection thru Excel GUI and used it as the benchmark, and tried to have the same content (underlying Xml files) as much as we can.

I know Eric had some sample of creating PivotTable, but seems not based on a data connection to a OLAP Cube, and also in older Open Xml version

[Jim SnyderParticipant]

I haven’t seen much useful traffic on here recently. I have not connected to OLAP with OpenXML and cannot help except to tell you to look elsewhere.

[openmindParticipant]

Thanks, Jim, for reply.

And that’s the other thing: where else do you see more active discussion/help? I did some extensive search and much to my disappointment, and more accurately, to my shock: this is very essential thing and no single sample can be found, and official doc from Microsoft is just a simple syntax/semantic explanation of various objects, without connecting dots…

[Jim SnyderParticipant]

There have been some active threads at StackOverflow and CodeProject. Neither has a forum, but searching for OpenXML will pull up a few.

[openmindParticipant]

Thanks again. Just checked out both sites and StackOverflow has a little more recent questions related to OpenXml Pivot Table but not much discussion/answering. Will check that anyway as one can also find something from questions or posted code.

Regarding my original question, I re-watched the video from Bob/Eric, and found the root cause: mishandled relationship id when adding pivot cache entry to the workbook Xml file, partly due to the fact I didn’t know there are multiple .rel files related to a document/package (under the parent node, like a workbook or worksheet).

[Anonymous]

Here’s how to create a PivotTable by using an existing external data connection:
Click any cell on the worksheet.
Click Insert > PivotTable.
In the Create PivotTable dialog box, under Choose the data that you want to analyze, click Use an external data source.
Click Choose Connection.

[Author]

Posts