Generate Pivot table then export to excel

[moon_67ss]

Hello,

I want to automate 2 common tasks.
1. I would like to generate a PivotTable that connects to an OLAP data
source.
2. I would like to export/stream the results of this pivot table to Excel
on the client.

I think I've figured out #1:
_pt = new PivotTableClass();
//_pt.AutoFit = true;
_pt.ConnectionString = "Provider=MSOLAP.3;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=Adventure Works DW
Standard Edition;Data Source=slc-mooneyj;";
_pt.CubeProvider = "MSOLAP.3";
_pt.DataMember = "[Adventure Works]";
_pt.DisplayFieldList = true;

_pt.CommandText = "SELECT non empty { [Product].[Product
Categories].[Category]} ON COLUMNS, non empty { [Date].[Calendar].[Calendar
Year]} ON ROWS FROM [Adventure Works]";

_pv = _pt.ActiveView;

_ActiveXParamControl.TagName = "param";
_ActiveXParamControl.ID = "xmldata";
_ActiveXParamControl.EnableViewState = true;
_ActiveXParamControl.Attributes[ "name" ] = "XMLData";
_ActiveXParamControl.Attributes[ "value" ] = _pt.XMLData;

_ActiveXControl.TagName = "object";
_ActiveXControl.Attributes[ "classid" ] =
"CLSID:0002E55A-0000-0000-C000-000000000046";
_ActiveXControl.ID = "pTable";
_ActiveXControl.Controls.Add( _ActiveXParamControl );

_tcMain.Controls.Add( _ActiveXControl );
_trMain.Cells.Add( _tcMain );
_tblMain.Rows.Add( _trMain );
Controls.Add( _tblMain );


But, I'm stumped on #2. I have found the XmlData property, but I don't know
how to get that into an Excel spreadsheet.

Any suggestions?

Thanks,

Jay

 

[Alvin Bruney [MVP]]

I'd suggest you do it the other way around. Drag the pivot table to the form
and configure it's properties to point to the mdx cube. It doesn't require
code.

--
Regards,
Alvin Bruney
------------------------------------------------------
Shameless author plug
Excel Services for .NET is coming...
https://www.microsoft.com/MSPress/books/10933.aspx
OWC Black Book www.lulu.com/owc
Professional VSTO 2005 - Wrox/Wiley