Recreating charts in ASP page

[Mark G]

I have a web page with an OWC10 Pivot Table and Chart bound to that Pivot
Table. After the user adds the data that they want to the Pivot Table and
adjusts the chart to look the way they want, they save the Pivot Table and
Chartspace definitions (.XMLData property) to the database. I have another
process that periodically grabs these Pivot Table and Chartspace definitions
and tries to recreate them on the server side in an ASP page in order to
spit out a chart image using the ExportPicture function of the Chartspace
object. My problem is that I cannot get the charts to display any data.
I'm not sure whether it is the Pivot Table unable to retrieve data from the
Analysis Services cube or if it is a problem with the linkage between the
Pivot Table and Chartspace.

Does anyone have any ideas?

Here is a snippet of the code:

Dim ptable, cspace, sFileName
Dim c
Dim rs, Conn
Dim dsc

'********** set up pivot table **************
Set ptable = CreateObject("OWC10.PivotTable")
ptable.ConnectionString = "Provider=MSOLAP.2; Persist Security Info=True;
Data Source=MyOLAPServer; Initial Catalog=Foodmart 2000;"

'*********** set up chart space **************
Set cspace = CreateObject("OWC10.Chartspace")
cspace.datasource = ptable
Set c = cspace.Constants

Set Conn = CreateObject("ADODB.Connection")
Conn.Open "provider=microsoft.jet.oledb.4.0;data source=c:\Reports.mdb"

'** get list of PivotTable/Chartspace definitions stored in the database
Set rs = Conn.Execute("SELECT SnapshotID, CubeName, ReportDef, ChartDef
FROM Snapshots where cubename='Sales' ORDER BY CubeName")

Do While Not rs.EOF

ptable.XMLData = rs.Fields("ReportDef")
ptable.DataMember = rs.Fields("CubeName")
cspace.XMLData = rs.Fields("ChartDef")

cspace.PlotAllAggregates = c.chPlotAggregatesSeries '***** this
occasionally throws an error indicating that the "external data source
failed to connect." Even if I don't get an error here, I still get an empty
chart with no data.

sFileName = rs.Fields("CubeName") & "_" & rs.Fields("SnapshotID") &
".gif"

'************* save chart to file *****************
ExportChart cspace, "gif", 300, 400, sFileName '** this function calls
the ExportPicture function of the chartspace object
rs.MoveNext

Loop

TIA,
Mark

 

[Mark G]

Nevermind. I figured it out. I'm not exactly sure why, but recreating the
Pivot Table on the server side using the saved XMLData requires using a
DataSource Control (OWC10.DataSourceControl). It may be because the
original Pivot Table's data source was an OWC Data Source Control.
Although, there is no direct reference to it in the XMLData definition.

 

[Mark G]

Hi Rabbani,

I found that you *do* need to use the datasource control to get the charts
to work on the server side. I didn't see any explicit reference to the
datasource control in the PivotTable XML, but it would not retrieve the OLAP
data without it. Setting the PivotTable connection string didn't work.
Just creating an instance of the datasource control did the trick.

-Mark

Hi Mark,

I am working on the same problem mentioned by you. For some reason, I am

unable to assign the XMLData to Chart on the server side.

As mentioned by you, i am not using DataSource Control any where. Do you

think i must use it to get this thing work.

 

[Sastry]

Hi Mark & Rabbani,

my requirement also as such.

how to use PIVOT TABLE in ASP.NET ?
how to fill SQL data into PIVOT TABLE ?

here i do not want to use OLAP, i wanna use XML, is that possible?

pls help me guys

Thx alot

Sastry

 

[Mark G]

Hi Sastry,

There are some old posts about using OWC in ASP.NET as well as using an XML
data island as the data source. Try searching through the archives. If
that doesn't work, try starting a new thread in this newsgroup as this is a
totally different topic.

-Mark