Andrew Whitechapel

You've been kicked (a good thing) - Trackback from DotNetKicks.com

How can I loop through all the PivotFields of a Pivot table in C# ?

I currently need to find all the PivotFields of an already built PivotTable.

thank you for the prompt reply,

I found out about the optional parameter while looking through msdn a couple of hours after asking here, but I still have one problem.

I now can find the column fields and the row fields.

If i understand how this works,

There is a root PivotField for the row and column, the subsequent PivotFields in each are then added as a child PivotField and so on. please correct me if i'm wrong.

I do not seem to be able to get the DataFields, "xlDataField" is there something special I must do to access these fields.

At the moment I am trying to crawl the pivot table so that I may be able to record what fields are in what Orientation. I am doing this so that I may create a PivotTable definition, which can be used at a later time to recreate the PivotTable.

Best regards,

for those who have the same questions as I've been having.

How do i make this functionality work for SSAS OLAP Cube

Hi, I tried your code posted as “Creating a PivotTable Programmatically”, it works perfectly. I need to get exactly similar functionality with OLAP Cube. Below is the code I tried but I get error at line marked as (*****), when i try to add Pivot Table. I get exception as  “Exception from HRESULT: 0x800A03EC”.

Microsoft.Office.Interop.Excel.Application app;

       app = new Microsoft.Office.Interop.Excel.Application();

       Microsoft.Office.Interop.Excel.Workbook wkbk = (Microsoft.Office.Interop.Excel.Workbook)app.Workbooks.Add(Type.Missing);

       Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)wkbk.ActiveSheet;

       string connection = @"OLEDB; Provider=msolap; Integrated Security=SSPI ; Datasource=localhost; Initial Catalog=MSLMKTG_DemoCube; UID = Administrator; Password = cybage@123";

       string command = "CubeUserMSLMKTG";

       Microsoft.Office.Interop.Excel.PivotCache pivotCache;

       pivotCache = wkbk.PivotCaches().Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlExternal,Type.Missing);

       pivotCache.Connection = connection;

       pivotCache.MaintainConnection = true;

       pivotCache.CommandType = Microsoft.Office.Interop.Excel.XlCmdType.xlCmdCube;

       pivotCache.CommandText = command;        

       Microsoft.Office.Interop.Excel.PivotTables pvtTables = (Microsoft.Office.Interop.Excel.PivotTables)sheet.PivotTables(Type.Missing);

       (*****)Microsoft.Office.Interop.Excel.PivotTable pvt = pvtTables.Add(pivotCache,sheet.Cells[1,1], "PivotTable1", true, Type.Missing);

Please help urgently

Thanks,

Shreyas

Hi,

once again mailing you.

In my previos code if i change my adding pivot code to

Microsoft.Office.Interop.Excel.PivotTable pvt = pivotCache.CreatePivotTable(sheet.Cells[8, 3], "PivotTable1", Type.Missing, Type.Missing);

I get error something as OLE DB error saying problem in opening dialog box and the second time execute the same code i get the error as "Exception from HRESULT: 0x800A03EC"

Im not sure if this was answered in the previous codes as I am new to VB. If i were to try to access all the datafields in my pivotfield to make changes to formatting how could i do that?

Also I want to remove the "sum of" out of each datafield. So it says Billpayers instead of Sum of Billpayers. Is there a way to do that to all datafields at once?

Has any one done this.......see the above threads :)

Thanks,

Shreyas

Thanks for this blog post!

To Shreyas.

It can be used to create connection to OLAP cube.

Connection string I used in test case was:

"OLEDB;Provider=MSOLAP.1; Location=ServerName;Connect Timeout=60; Initial Catalog=Analysis Services ProjectTest;Client Cache Size=25";"

Properties:

pivotCache.CommandText = "name of the cube";

pivotCache.CommandType = XlCmdType.xlCmdCube;

And assignment of fields:

pivotTable.CubeFields["Name of cube dimension"].Orientation = XlPivotFieldOrientation.xlRowField;

Thanks for this blog post!

To Shreyas.

It can be used to create connection to OLAP cube.

Connection string I used in test case was:

"OLEDB;Provider=MSOLAP.1; Location=ServerName;Connect Timeout=60; Initial Catalog=Analysis Services ProjectTest;Client Cache Size=25";"

Properties:

pivotCache.CommandText = "name of the cube";

pivotCache.CommandType = CmdType.xlCmdCube;

And assignment of fields:

pivotTable.CubeFields["Name of cube dimension"].Orientation = XlPivotFieldOrientation.xlRowField;

Hello Andrew,

I'm having a couple of issues with a PivotTable:

1) I get the following error when setting the Orientation property of the fifth PivotField: "Unable to set the Orientation property of the PivotField class".  It seems that the error is related to the number of fields, not to the type of orientation being used.

2) I don't know how to put the data fields as columns (as when you right-click the "Data" cell, select Order and choose "Move to Column").  Can this be accomplished programatically?

Thanks!

Dear Gaby,

I was facing the exact same problem a few weeks ago.

This is what I found:

You need to create the pivot table in steps. The first of these steps is add all the data fields:

if (orientation.Equals("xlDataField"))

{

    string source = field.Attribute("SourceName").Value;

    string function = field.Attribute("Function").Value;

    pivot.AddDataField(pivot.PivotFields(source), name, SelectFunction(function));

}

Then set the orientation of the PivotFields. Be sure not to set the orientation of the "Data" named PivotField.

Once all the fields have been set, you may then set the orientation of the "Data" field. You need to do this last since by placing PivotFields the value PivotField is automatically generated.

Please correct me if I'm wrong.

Now you can set the Position and Caption of the fields.

This is how i went about it. Let me know if this works.

Best regards,

Alexandre Brisebois

Hello Alexandre,

It didn't work.  It seems that error # 1 appears because there are too many rows.  I'll try to sort that out later.

Do you have any idea on how to accomplish # 2 ("Move to Columns" programatically)?

Regards,

Gaby

I made it work with this line

table1.PivotFields("Data").Orientation = Excel.XlPivotFieldOrientation.xlColumnField

before setting the RowColumn that caused the problem with too many rows.

Thanks.

Hi Teme,

Thanks...i tried your suggested code.

I am getting error when assigning dimensions to the pivot table. The error is "Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))"

pvt.CubeFields["Data Source Name"].Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlColumnField;

here "Data Source Name" is the name of the dimension but it comes under dimension Dim Source, it is a hierachy under this dimension.

Would be helpful if you can send me your working code

My mail id is bhagatshreyas@gmail.com

Thanks,

Shreyas

Thanks Teme....finally it worked :)

Thanks,

Shreyas

Hi Teme,

I am done with the implementation but when i deploy it on IIS server, nothing is getting generated in the excel.Please share your contact information.

Thanks,

Shreyas

Shreyas,

Why are you generating these on IIS, this might turn out to be very resource hungry?

Have you installed the VSTO components on the server ? Haven you Installed Excel 2007 on the server ?

Best regards,

Alexandre Brisebois

I have been working with PivotCharts,

and I'm trying to wrap my head around getting the chart layout. I have only found the ApplyLayout [ ApplyLayout(1,Type.Missing); ] method of the Chart Object. Is if possible for me to retrieve this integer value ?

Best regards,

Alexandre Brisebois

Hi,

when i run my code in Visual studio it works perfectly fine...but when i deploy it on IIS server then my excel sheet doesn't show anything.How do get this done.Some where i read about Microsoft.Office.Interop.Excel which IIS does not support.Please let me know the detail steps.

Thanks,

Shreyas

Hi

Sorry haven't had the time to check replies. It can be done on IIS. Have you included all the dlls that needed to Bin folder of website? I had Microsoft.Office.Interop.Excel.dll and office.dll.

For first time I tried it on IIS I ran to the language version bug. Before using Excel operations set your culture to US.

System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");

You must disable all popup questions of Excel because IIS runs it without console. So no one can reply to question -> process hangs.

_exelApp.DisplayAlerts = false;

Have you set user rights for folders that are used?

-Teme

For detailed questions:

teme1011@gmail.com

Andrew,

after some time i decided on setting the '1' layout for all my charts and have the user customize it before printing it out.

Thanks for the response. I do think that this would be an interesting addition to future versions.

Hi,

If I already have a dataset with the data, how can I create the pivot table?

I tried the code and works but when I tried the line:

               Excel.PivotTable pivotTable = pivotTables.Add(

                   pivotCache, Globals.ThisAddIn.Application.ActiveCell, "PivotTable1",

                   missing, missing);

the programs shows me an SQL connection windwos ask me for a databaseName, id an password?

Why??

How can I get to the PivotItems of the individual PivotFields objects? I am trying to convert some VBA logic to C# .NET. In the VBA code I could loop through the PivotItems that were created for each PivotField and make them Visible or not depending on the caption. I cannot seem to find the right object/method to get to those same PivotItem objects in .NET VSTO.

Thanks so much! That worked like a charm. I did end up using it as a foreach (PivotItem item in items) but the principle is the same. The collections that are available in the Excel .NET tools feel a little different than the normal .NET collections. I always forget to try to use the Type.Missing object in place of an index.

Thanks again!

Andrew - great article!

I like to create a Pivot table from a SSAS cube and wonder if you could post the complete code here. I suppose you have it anyways.

Also I was able to get the MDX scripts that Excel generates and saved those. Now, what would I have to do to recreate a Pivot table with that. Would that be the COMMAND text?

Your help in this matter is greatly appreciated.

Thanks, Dirk

Dirk again. I missed to mention that I have created an Excel 2007 Add-in that I like to use to create these Pivot tables based on the MDX queries saved beforehand.

Andrew -

thanks for the quick feedback. It appears that Excel was not build to support my idea.

Thanks,

Dirk

Andrew -

could you please take a quick look at my code and tell me why it is not working. I all it from within an Excel Add-in.

I really would appreciate your help.

Thanks in advance,

Dirk

--

   Sub CreateOLAPPivotTable()

       'Declare variables

       Dim objMyPivotCache As Excel.PivotCache

       Dim objMyPivotTable As Excel.PivotTable

       Dim App As Excel.Application

       App = Globals.ThisAddIn.Application

       'Create PivotCache

       objMyPivotCache = App.ActiveWorkbook.PivotCaches.Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlExternal)

       'Retrieve data

       With objMyPivotCache

           .Connection = "OLEDB;Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Data Source=server;Initial Catalog=DuPont_EMEA"

           .CommandText = "server cube FreeMining"

           .CommandType = Excel.XlCmdType.xlCmdCube

       End With

       'Create PivotTable

       objMyPivotTable = App.ActiveSheet.PivotTables.Add( _

       PivotCache:=objMyPivotCache, _

'

'I believe the RANGE is crashing?

'Exception from HRESULT: 0x800A03EC

'

       TableDestination:=App.Range("A1"), _

       TableName:="OLAP PivotTable")

       objMyPivotTable.CubeFields("[REVIEWPERIOD]").Orientation = Microsoft.Office.Core.XlPivotFieldOrientation.xlDataField

       objMyPivotTable.CubeFields("[DMDUNIT]").Orientation = Microsoft.Office.Core.XlPivotFieldOrientation.xlRowField

End Sub

--

Hi,

How can I create the pivotcache from DataSet?

the dataset is created in the code, not from the external database.

Thanks,

Boris

How do I programatically change the SQL Server connection used by an existing pivot table?

The way you programatically change the connection is like this:

http://support.microsoft.com/kb/327572

The support article only updates the server name. If you want to change the whole string, it must be in the following format. Otherwise you'll get a cryptic 0x800A03EC exception.

ODBC;DRIVER=SQL Server;SERVER=<<yourserver>>;DATABASE=<<yourdatabase>>;Network=DBMSSOCN;Trusted_Connection=Yes

The above connection string was truncated. Here it is again.

ODBC;DRIVER=SQL Server;SERVER=<<your server>>;DATABASE=<<your database>>;Network=DBMSSOCN;Trusted_Connection=Yes

how to remove/hide datafields from a pivot table using vba code??

How do i publish a pivot by code to a sharepoint site.In excel 2007

Does this work ok with excel 2003?

Can the same technique be used for non pivot query tables?

Ho do I use a table from a Dataset as a source data in a pivot table properties?

Did anyone have a solution for shreyas HRESULT: 0x800A03EC error? I'm having the same problem and have been unable to solve it. I believe the error has to do with the second parameter in PivotTables.Add() which is the tableDefinition. Application.ActiveCell doesn't seem to work and any other explicit cell location also doesn't work.

Thanks!

Hi!

Great article! I habe build a Cube PivotTable i can browse any PivotItem. Thats fine byu i'm not able to set any PivotFilter and also not able so set some attributes visible false. Has anyone an idee what i'm doing wrong.

I got every time the same HRSEULT Error like Matt.

Thx for any help

Hi, thank you for your great article.

Someone know how can I hide the connection string from the "user's eyes"? The PivotTable continue working, updating data from database etc. but I must hide the connection string (or encrypt it) because the user can't see it (the credentials used to connect to database). If he go to the datasource's properties he can see what user and password was used to connect to the database, or if I can denied access to this property's dialog?

Thanks for any help.

I have a pivot table that is populated from an Oracle database thru a rather cumbersome query. I would like to use the raw data in the pivot table in a custom function to summarize the data. The majority of the time, the data population exceeds 65,536, which makes it impossible to show the detailed records. Does anybody know how to access the data from the pivot table memory?

Thanks andreww for this great article! It helped me to build a pivot table programatically on my local machine but when I publish the project on my server and try to do the same from server side it gives me a blank excel file. Is this due to MSQuery permissions or what? Did anybody had the same issue?

Thanks in advance for any help you can provide me!

Hello, you help me?

andreww

I need not show options in PivoteTable; Data; connection properties to prevent the connection string can not be viewed by the user. You might tell me how to do

Solved! The problem was due to the lack of permissions. The solution was to define local impersonation of the published site and voilá!