OWC Charts and Pivot Tables

[bigHairy]

Hello all.

Apologies if this has been answered somewhere else but I have looked
and can not find anything that helps.

I am blundering through the murky underworld of OWC and ASP.NET at the
moment and have had some success in dynamic charts. I have used an
online tutorial and have output the chart as an image

Response.BinaryWrite(oChartSpace.GetPicture("jpg", 400, 350))

and then shown that image on my page. I am very pleased with this.

I then had a play in SharePoint and FrontPage and found that I could
manually create a pivotchart where the user can slice and dice their
data as they see fit. I have looked at the code it generates and it
looks something like this...

<OBJECT id="ChartSpace1"
classid="clsid:0002E55D-0000-0000-C000-000000000046" VIEWASTEXT>
<PARAM NAME="XMLData" VALUE='<xml
xmlns:x="urn:schemas-microsoft-comffice:excel">

<x:ChartSpace>
<x:OWCVersion>11.0.0.6555
</x:OWCVersion>
<x:Width>15240</x:Width>

<x:Height>10160</x:Height>

<x:AllowPropertyBrowser/>
<xataSource>

<x:Type>InternalPivotList</x:Type>


and so forth.... (incidentally when you view the source of the page you
can see the password to the datasource in clear text this way which was
worrying).

What I can not see is how I call this chart programmatically from my
vb.net code. Can I create a dynamic, on the fly version of this type of
pivotchart? Does this make sense?

 

[Alvin Bruney [MVP]]

Yes, you can. Consider
Response.Write("<OBJECT id='ChartSpace1'
classid='clsid:0002E55D-0000-0000-C000-000000000046'VIEWASTEXT></object>");

That will give you an empty chart on a form. that's the basic approach. You
can perform the rest of the activities using that approach.

 

[bigHairy]

Hello Alvin. Thanks for getting back to me.

Just want to make sure I have understood you correctly. I am running
the following code:

Response.Write("<OBJECT id='ChartSpace1'
classid='clsid:0002E55D-0000-0000-C000-000000000046' VIEWASTEXT>")
Response.Write("<PARAM NAME='XMLData' VALUE='<xml
xmlns:x='urn:schemas-microsoft-comffice:excel'>
.......
and so on

for each line of the code that I got from looking at the original. I
have tried various ways of doing this (putting it into a string, 1 line
string, etc...) and all I am getting is an empty ChartSpace. Am I
missing something obvious? It seems to prefer having " rather than ' in
the string - is there an easy work around for this?

 

[Alvin Bruney [MVP]]

You are on the right track. You need the object tag. The param tag and all
the other lines you can safely skip that. However, at this point, all you
have is an empty block on screen. You will now need to bind the chart to a
datasource or explicitly fill it with data. The easiest way to do that is to
examine server side code and perform the same operation in script.

 

[bigHairy]

Alvin you are being a great help and I really do apologise for
mithering you but I am having no joy. I have done the response.write to
put the object tag on my page, but how do I then reference that object?
How do I programmatically apply the parameters?

 

[Alvin Bruney [MVP]]

So remember that the object sits in the client, you can't simply write
server code to address it because server code runs on the server. The
activex pivot table runs on the client. This is the reason why you need to
build script on the server and stream it to the client. So for instance, its
easier to build/hard-code your script in the page like so:

//aspx inline code
function GetPivotXml(string name)
{
var pt = this.document.getElementById(name);
document.scriptResult = pt.XMLData;
return pt.XMLData;
}

function GetPivotHtml(name)
{
var pt = this.document.getElementById(name);
document.scriptResult = pt.HTMLData;
return pt.HTMLData;
}

in your code behind file, when you want to perform an action on the pivot
table, you simply stream the command out like so:

private void Page_Load(object sender, eventargs e)
{
if(!Page.IsPostback)
{
string nameOfTable = "myName";
Response.Write("<script>GetPivotHtml(" + nameOfTable +
")</script>" );
}
}
and so on and so forth.

 

[bigHairy]

Sorry about delay in response, Alvin. Thanks again for your help. Are
you aware of any complete examples online? I reckon I almost there,
very frustratingly close but I can not seem to get past the empty
chartspace. Sorry.

 

[Alvin Bruney [MVP]]

You can search the black book on amazon.com to get an idea of the code
snippet. You won't be able to copy paste but you can view the snippets.

 

[bigHairy]

You are a good man, Alvin - splendid stuff, making really good progress
now. Really appreciate your help here.

 

[bigHairy]

OK, last question I promise.

I have made some excellent progress and I am very pleased with the
results. I work on the assumption that our users are not too bright so
rather than right clicks I create nice graphics for them. With this in
mind I am allowing them to choose chart type etc... through our own
interface. As part of this I would like to hide the drop zones that
appear on my chart. I have looked at the source data generated through
a manually built chart and there is a parameter called
<x:HidePivotFields/> . I have tried to use this in my response.write
with "<script>Chart1.HidePivotFields</script>" and variations thereof.
In this form it does not throw an error but it doesn't hide the pivot
fields either. Any ideas?

 

[bigHairy]

Ignore me - I was over thinking a very simplistic problem.
Chart1.DisplayFieldButtons = 'False' does the job very nicely.

 

[Alvin Bruney [MVP]]

You are a good man, Alvin
i am not. i'm a horrible horrible man

--
Regards,
Alvin Bruney
------------------------------------------------------
Shameless author plug
Excel Services for .NET is coming...
OWC Black book on Amazon and
www.lulu.com/owc

 

[bigHairy]

i am not. i'm a horrible horrible man

you saved me many hours of swearing at my staff and throwing stuff -
you ARE a good man!