Problems binding chartspace to pivot table when fully server-side

[Notachance]

The graphing page in my web application allows users with OWC to design
graphs using a client-side PivotTable and ChartSpace and then persist
them to a database. I'd like to allow users without OWC to at least see
a GIF image, so I've been trying to get the server to create a
PivotTable and ChartSpace in server-side memory, fill their XMLData
properties, bind them together and do an ExportPicture. However, all I
get is an empty chart of gridlines, which implies the datasource
binding isn't working properly, I think.

I've posted example code below. I suspect I'm making an obvious
mistake, so any pointers would be gratefully received!

Dim oPivotTable As New Microsoft.Office.Interop.Owc11.PivotTable
Dim oChartSpace As New Microsoft.Office.Interop.Owc11.ChartSpace

oPivotTable.XMLData = GetXMLData(msLayoutsuid, "pivot").InnerXml
oChartSpace.XMLData = GetXMLData(msLayoutsuid, "chart").InnerXml

'One of the following:
oChartSpace.DataSource = oPivotTable ' Doesn't seem to work
oChartSpace.DataSource = oPivotTable.GetOcx ' Intellisense can't see it
oChartSpace.DataSource = CType(oPivotTable,
msdatasrc.DataSource).GetOcx ' Errors saying pivottableclass doesn't
have getocx()

oChartSpace.ExportPicture("C:\output\chart.gif", "gif", 600, 350)

 

[Notachance]

The graphing page in my web application allows users with OWC to

design...

Er, anyone? Or is this just a case of reiterating snippets from Alvin
Bruney's Black Book until I find something similar that works?

 

[Alvin Bruney [MVP - ASP.NET]]

If you are looking to bind a chart to a pivot table from the server and
stream the object to the client, here is some code that will work for you.
(The code has been ripped and customized from the June issue of OWC meets
ASP.NET - Aspnet pro magazine)
//start with some bare bones functionality to give the general idea, then
customize laterif(Request.Browser.ActiveXControls){//1
Response.Write("<OBJECT id='pTable' style='Z-INDEX: 109; LEFT: 8px; WIDTH:
502px; POSITION: absolute; TOP: 8px; HEIGHT: 217px' height='217' width='502'
classid='clsid:0002E55A-0000-0000-C000-000000000046'VIEWASTEXT></OBJECT>");
Response.Write("<OBJECT id='chart1' style='Z-INDEX: 109; LEFT: 503px; WIDTH:
502px; POSITION: absolute; TOP: 8px; HEIGHT: 217px' height='217' width='502'
classid='clsid:0002E55D-0000-0000-C000-000000000046'VIEWASTEXT></OBJECT>");//2
//send the load instructions
Response.Write(@"<script>document.all.pTable.ConnectionString =
'Provider=MSDASQL.1;Persist Security Info=True;Extended Properties=\'DSN=MS
Access Database;DBQ=C:\\Program Files\\Microsoft
Office\\OFFICE11\\SAMPLES\\Northwind.mdb;\';Initial Catalog=C:\\Program
Files\\Microsoft Office\\OFFICE11\\SAMPLES\\Northwind'</script>");//3
//Response.Write(@"<script>document.all.pTable.ConnectionString =
'Provider=MSDASQL.1;Persist Security Info=False;Extended
Properties=\'DSN=sampledb;\''</script>");
Response.Write("<script>document.all.pTable.DataMember =
'SampleDb'</script>");
Response.Write("<script>document.all.chart1.DataMember =
'pTable'</script>");}For your customization, you will need to change //2 to
bind to your datasource, the current line assumes that we are connecting to
a sql data store.
Then, customize line 3 to hook that chart to the pivot table.

Now, in my case, I am streaming live objects. In your case, you want to make
some modifications so remove all the response.write object and simply
declare an in-memory pivot table and chart object to replace line 1. Then
connect them in lines 2 and 3. At this point, you introduce your first
Response.Write line that basically does something like
Response.Write(axPivotTable1.HTMLDATA).

So basically, something like this:

//1 PivotTable pTable; Chart chart1;//2 pTable.ConnectionString
= 'Provider=MSDASQL.1;Persist Security Info=True;Extended
Properties=\'DSN=MS Access Database;DBQ=C:\\Program Files\\Microsoft
Office\\OFFICE11\\SAMPLES\\Northwind.mdb;\';Initial Catalog=C:\\Program
Files\\Microsoft Office\\OFFICE11\\SAMPLES\\Northwind'</script>");//3
pTable.DataMember = 'SampleDb'; chart1.DataMember = pTable;
Response.Write(pTable.HTMLDATA);CAUTION: I didn't test this code so I don't
expect it to run. However, this is the general idea that should work with a
bit of tweaking.-- Regards,Alvin Bruney - ASP.NET MVP
[Shameless Author Plug]
The Microsoft Office Web Components Black Book with .NET
Now available @ www.lulu.com/owc, Amazon.com etc

 

[Notachance]

If you are looking to bind a chart to a pivot table from the server and
stream the object to the client, here is some code that will work for

you.

Thanks for responding! What you've posted is very similar to what I'm
doing, and the problem seems to be in the act of binding the chartspace
to the pivottable. In the example code, you've got:

chart1.DataMember = pTable;

This doesn't work for me as DataMember requires a string, not an
object. On the client-side, I could just use "chart1.DataSource =
pTable" and all is well, but this doesn't seem to work on the
server-side. I think I'm probably just setting the wrong property, or
setting it in the wrong way. Again, any advice much appreciated!

 

[Notachance]

I think my problem is simply this:

oChartSpace.DataSource = oPivotTable

generates the following error:

Cannot convert to 'Interface DataSource'.

If I try:

oChartSpace.DataSource = CType(oPivotTable, msdatasrc.DataSource)

I get the same error.

I've tried OWC10 & OWC11, but the error persists. I've searched the
newsgroups, but the only promising answers use PivotTable.object or
oPivotTable.GetOcx(), and my object has neither.

 

[Devesh Aggarwal]

Hi There,

did you get any solution to your issue of binding chartspace to pivot table,
as i am getting the same issue in VB.Net. The same works fine in ASP.Net.

Appreciate if you update on any resolution.

Devesh Aggarwal

 

[Notachance]

did you get any solution to your issue of binding chartspace to pivot table,
as i am getting the same issue in VB.Net. The same works fine in ASP.Net.

Sorry - no, I never got a resolution. Binding those components in
ASP.NET is trivial, binding in code-behind clearly isn't. Alvin's book
doesn't seem to address the problem.

Without assistance from this newsgroup or Microsoft itself, I've come
to the conclusion that the OWCs are excellent components ruined by
their lack of support and documentation. I pretty much knew I was
doomed when Alvin's post was no help - there's nobody to turn to after
him! If you succeed where I have failed, please post.

 

[Alvin Bruney [MVP - ASP.NET]]

I don't understand why this isn't working for you. We can work this out, no
need to jump off the cliff.

Can you post a small but complete sample of what you are trying to do, I'll
run it and see what I can come up with. If I can't fix it, I'll submit it to
product support for resolution.

--
Regards,
Alvin Bruney - ASP.NET MVP

[Shameless Author Plug]
The Microsoft Office Web Components Black Book with .NET
Now available @ www.lulu.com/owc, Amazon.com etc

 

[Eric Kuijper]

Hi,

I had the same problem with the ExportPicture routine. The problem seems to
be that when you create a new object for the chart and pivottable, the chart
object doesn't 'link' to the pivottable whether or not you use the exact
XMLData string from the original chart and pivottable. What I did is I
created a HTML page with both objects already in it (did not use
CreateObject..) and then loaded the XMLData string for the chart first and
pivottable second. Didn't do anything with the datasource of the graph.

strXML = .Fields("XML Graph").Value
PivotGraph.XMLData = strXML

strXML = .Fields("XML PivotTable").value
PivotTable.XMLData = strXML

PivotGraph.ExportPicture strFileName, strFileType, lngWidth, lngHeight
PivotTable.ExportPicture strFileName, strFileType, 65535, 65535

 

[Notachance]

Can you post a small but complete sample of what you are trying to do, I'll
run it and see what I can come up with. If I can't fix it, I'll submit it to
product support for resolution.

Alvin,

Sorry for the belated response. Having flung my Office installation CD
away in disgust, I haven't been monitoring this post as closely as I
should be. Any help is much appreciated, as I've made no headway
whatsoever! So, to summarise;

I have a web page containing a client-side pivot table and chartspace,
with the chartspace datasource bound to the pivot table. This works
fine. I've added a "save to database" facility that allows a user to
persist the current chart and pivot table layouts, which essentially
records the XMLDATA property of the two components as a row in the
database, and can send them back out to "reload" the layout. This too
works fine.

Now to the problem; we cannot be confident that the client machine is
capable of hosting OWC components. Where this happens, we need the
server to be able to display a list of charts created by users who DO
have the OWCs, allow the user to select one and then render the chart
on the server-side and stream it to the client as a GIF image. For this
to happen, the components must be installed server-side and must be
able to read the persisted XMLDATA and render a chart from it.

I'm wondering whether XMLDATA generated by components embedded in a
webpage at the client-side is inappropriate for loading into
server-side components. The data source for the pivot table is a URL
that returns XML, so perhaps a server-side control isn't able to access
this.

Providing exact source code is going to be difficult as it's part of a
very large application, but I can provide you the XMLData strings and
XML returned by the URL data source. When used with the source snippet
provided above, you can reproduce the problem.

As an aside, I've noticed that the chart.gif I get is identical to how
the chartspace looks before everything has loaded and bound.

 

[Alvin Bruney [MVP - ASP.NET]]

The data source for the pivot table is a URL

that returns XML, so perhaps a server-side control isn't able to access
this.

It should load once the XML is valid OWC XML. It will not load pure XML. And
ofcourse, the usual permissions need to be granted to the worker process to
effect the file read.

Providing exact source code is going to be difficult as it's part of a
very large application,

You do not have to provide exact source code, you only need to provide me a
sample application that demonstrates the problem. I don't want to have to
spend 2 hours writing code to reproduce the problem. I'd like you to give me
a small, coded solution that clearly demonstrates the problem so I can spend
2 hours trying to fix it.

--
Regards,
Alvin Bruney - ASP.NET MVP

[Shameless Author Plug]
The Microsoft Office Web Components Black Book with .NET
Now available @ www.lulu.com/owc, Amazon.com etc