none
PivotField.PivotFilters.Count is always 0 RRS feed

  • Question

  • I'm using Visual Studio 2010 to develop an Excel Addin in C# that allows a user to
     connect to and browse an Analysis Services database.
     
    I'm able to connect to the cube database and create a pivot table in C#, but when I update
     the pivot table in Excel to use the "Top 10" value filter, I can't access this filter programmatically.
     
    Does anyone know why the PivotField.PivotFilters.Count seems to always be 0, even when the
     Top 10 filter is applied to the field in Excel? In the code below, the pivot table created from the Analysis Services database is called pivotTable.
     
    The pivot fields are returned correctly and the pivotTable.MDX method returns the MDX with
     the Top 10 filter, but the "int count = field.PivotFilters.Count;" is always 0 when I'm expecting a filter count of 1.  Does anyone know why the field.PivotFilters.Count is 0?  How should I be getting a pivot table's filters?
     
    Any help is appreciated.
     
    ...
     
    foreach (PivotField field in pivotTable.PivotFields())
                     {
     
                        if (field.Name.ToUpper().Contains("[MEASURES]") || field.Name.ToUpper()=="DATA")
                             continue;
     
                      
                        PivotFilter filterTopCount = null;
                         PivotFilter filterTopPercent = null;
                         PivotFilter filterBottomCount = null;
                         PivotFilter filterBottomPercent = null;
     
                        try
                         {
     
      //This count is always 0, even when I have the Top 10 filter applied.                                         
     
                            int count = field.PivotFilters.Count;
                             filterTopCount = FindFilterType(field.PivotFilters, XlPivotFilterType.xlTopCount);
                             filterTopPercent = FindFilterType(field.PivotFilters, XlPivotFilterType.xlTopPercent);
     
                            filterBottomCount = FindFilterType(field.PivotFilters, XlPivotFilterType.xlBottomCount);
                             filterBottomPercent = FindFilterType(field.PivotFilters, XlPivotFilterType.xlBottomPercent);
                           
                        }
                         catch (Exception ex)
                         {
                             MessageBox.Show("Error trying to identify PivotTable's filters. " + ex.Message);
                             continue;
                         }
       }
     
    ....
    Tuesday, February 12, 2013 4:00 PM

Answers

  • I found what I was doing wrong.  When I created the PivotCache, I didn't specify the version of Excel.  Because of this, the PivotTable.PivotFilters were not available.  The correct way to create the cache so that you can specify the Excel version while creating it is:

               ...

               //Create the connection in the active workbook

               activeWorkbook.Connections.Add(newSheet.Name, "", connectionString, _cube, 1);

              //Create the Pivot Cache

                Microsoft.Office.Interop.Excel.PivotCache pivotCache =
                    activeWorkbook.PivotCaches().Create(
                    Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlExternal, activeWorkbook.Connections[newSheet.Name], Microsoft.Office.Interop.Excel.XlPivotTableVersionList.xlPivotTableVersion14);

    I found the solution here: http://social.msdn.microsoft.com/Forums/ar/exceldev/thread/0635e00b-fcee-40ed-992a-4a57321d431b.

    After I specified the Excel version while creating the PivotCache, the PivotFields.PivotFilters.Count was no longer 0.

    • Marked as answer by J.C.D Thursday, February 14, 2013 12:19 AM
    Thursday, February 14, 2013 12:19 AM

All replies

  • I found what I was doing wrong.  When I created the PivotCache, I didn't specify the version of Excel.  Because of this, the PivotTable.PivotFilters were not available.  The correct way to create the cache so that you can specify the Excel version while creating it is:

               ...

               //Create the connection in the active workbook

               activeWorkbook.Connections.Add(newSheet.Name, "", connectionString, _cube, 1);

              //Create the Pivot Cache

                Microsoft.Office.Interop.Excel.PivotCache pivotCache =
                    activeWorkbook.PivotCaches().Create(
                    Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlExternal, activeWorkbook.Connections[newSheet.Name], Microsoft.Office.Interop.Excel.XlPivotTableVersionList.xlPivotTableVersion14);

    I found the solution here: http://social.msdn.microsoft.com/Forums/ar/exceldev/thread/0635e00b-fcee-40ed-992a-4a57321d431b.

    After I specified the Excel version while creating the PivotCache, the PivotFields.PivotFilters.Count was no longer 0.

    • Marked as answer by J.C.D Thursday, February 14, 2013 12:19 AM
    Thursday, February 14, 2013 12:19 AM
  • Hi,

    I'm glad to hear that you've solved your issue.

    Thank you for sharing your solution. It might be very helpful to community members who have similar issue.

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, February 14, 2013 2:32 AM
    Moderator