Loading OWC 11 PivotTable with XML data

[Pau Larsen]

Hi,

I have some XML data and I want to load an OWC 11 PivotTable based on that
data.

I am thinking that loading using the XMLData property is the way to go. But
I can not figure out the necessary schema for the XML to load. I have
searched and searched with no result.

One way to get the XML format could of course be to read the content of the
XMLData property, but I can only figure out how to populate the pivot by
f.ex. connecting to a database. The XMLData then only contains the
connectionstring and of course no data.

I am thinking that maybe the pivot can not contain data itself and MUST be
connected to a data source?

I was then trying to connect the pivot to the spreadsheet component as I
have managed to populate that component using XMLData. I did so using the
DataSource property that according to the documentation also applies to the
pivot table. Like this:

set PivotTable1.DataSource = Spreadsheet1.Object

No errors but also no results!

Does anyone know where to find the PivotTable Schema for XMLData input?
Does anyone know how to bind a Spreadsheet to a Pivottable?
Does anyone have better ideas for how to populate a Pivottable from a XML
file?

/Pau Larsen

 

[ChrisHarrington]

Hi Pau,

I used the approach of getting the value of XMLData to populate a ChartSpace
control. You can find the details in one of my blog postings -
http://www.activeinterface.com/b2004_12_2.html

It is possible that the PivotTable cannot work in a disconnected fashion
like the other OWC components. That would explain why you see no data in
XMLData.

Microsoft has made public the Office schemas - they may have included the
OWC schemas in that collection.

Chris

 

[Pau Larsen]

Hi Chris

I followed up on your ideas with some level of success, but I can still not
get the PivotTable to show anything in a disconnected state although it seems
to accept the feeding of both settings and data from XMLData. See details
below questions.

Does anyone know how to populate a disconnected PivotTable with data using
the XMLData property?
Does anyone know how to get in contact with the creators or someone with
full knowledge of OWC to clarify if this is possible at all?
If it is not possible does anyone then have a suggestion for the best
strategy for getting the XML data loaded?

Background:
I found the Excel 2003 XML schemas called "SpreadsheetML" here:
http://www.microsoft.com/downloads/...52-3547-420A-A412-00A2662442D9&displaylang=en
(referenced here: http://www.microsoft.com/office/xml/default.mspx).

Although the XML I get from XMLData, after configuring my PivotTable using
an ODBC source, is slightly different than the pivot xml standard described
in SpreadsheetML it is basically structured alike. I would therefore think
that the OWC PivotTable can be feed with XML through XMLData using that
format.

In the SpreadsheetML the PivotField element can contain a PivotItem element.
As far as I can understand the PivotItem element contains data for the data
series for the PivotField. I therefore added PivotItem elements to the XML
data that I originally got from XMLData. I then removed the connection
settings for the ODBC source and loaded the XMLData back into the PivotTable.
No errors but also no results – just an empty PivotTable.

One interesting thing is that if I then retrieve the XMLData again my
PivotItems are maintained. If I misspell them before loading they are gone
when retrieving. That tells me that the format is actually recognized.

Please see next step questions in the top.

/Pau Larsen

 

[Alvin Bruney [MVP]]

wow,
a lot of questions. let me make my way thru them. see inline.

Does anyone know how to populate a disconnected PivotTable with data using
the XMLData property?

Yes, but that is a trade secret. It costs a lot of money.

Does anyone know how to get in contact with the creators or someone with
full knowledge of OWC to clarify if this is possible at all?

No, these people are dead and gone.

If it is not possible does anyone then have a suggestion for the best
strategy for getting the XML data loaded?

No clue, you are probably in the wrong forum.

okkkkkaaaaaayyyyyyy, i couldn't resist a few jabs.
Seriously, it is possible to load xmldata. Here is a snippet of code from my
upcoming book The Microsoft Office Web Components Black Book with .NET
(chapter 12 p. 447) due out very, very shortly.

If you have an XML file that conforms to valid Pivot Table schema, you can
load the xml into the pivot table list object using the following code.




Code-listing 12.0b XML data load




C# Snippet




string strCN = "provider=mspersist";

string strdirname = "c:\\temp\\test.xml";

OWC11.PivotView objPivotView;

axPivotTable1.XMLData=strdirname;

axPivotTable1.ConnectionString = strCN;

axPivotTable1.CommandText = strdirname;




VB.NET Snippet




string strCN = "provider=mspersist"

string strdirname = "c:\\temp\\test.xml"

OWC11.PivotView objPivotView

axPivotTable1.XMLData=strdirname

axPivotTable1.ConnectionString = strCN

axPivotTable1.CommandText = strdirname







Please note that the usual security restrictions apply for loading files
locally, from the server or in different domains. We have thoroughly
discussed these issues and they will not be re-hashed here.




Ofcourse, this assumes that you have valid pivot schema in test xml. If you
don't, there are a couple of ways to create it. Load the pivot table with a
small sample of data and write an xml file using XMLDATA property. Then
crack open the file to view the schema. You will have to add the header and
footer tags if you intended to create pivot xml. If you have data from a
relational database, use the mspersist utility to convert the relational
database to valid schema. Or, you can build the schema by hand.

You can find knowledgeable personel at Microsoft Product Support Group.
Simply open a report incident. If you have a legitimate copy of Microsoft
Office on your system, you are entitled to 3 free support incidents (it
costs about $50 US otherwise). Use this link
https://webresponse.one.microsoft.com/oas/public/assistedintro.aspx

hope this helps. (I apologize in advance if my humor seemed insensitive)