none
Trying to add pivot filter in vb.net and cant get it right RRS feed

  • Question

  • Hi there,

    I have 2 row headers already set in code: Product and Location.

    Location items in the data fields can add up to 0

    I would like to be able to exclude the zero items from the filter to show fewer items in the pivot.

               pivotField = CType(pivotTbl.PivotFields("Product"), Excel.PivotField)
                With pivotField
                    .Orientation = Excel.XlPivotFieldOrientation.xlRowField
                    .Name = " Product" 'Need space in front of the name
                    .LayoutBlankLine = True
                End With
    
    
                pivotField = CType(pivotTbl.PivotFields("Location"), Excel.PivotField)
                With pivotField
                    .Orientation = Excel.XlPivotFieldOrientation.xlRowField
                    .Name = " Location"
                    .LayoutBlankLine = True
                End With

    Thats the product and location added.

    No to add the datafields:

              pivotField = CType(pivotTbl.PivotFields("Finance Tons"), Excel.PivotField)
                With pivotField
                    .Orientation = Excel.XlPivotFieldOrientation.xlDataField
                    .Function = Excel.XlConsolidationFunction.xlSum
                    .NumberFormat = "### ### ### ##0.000;[Red]-### ### ### ##0.000"
                    .Name = " Financed Tons"
                    .Position = 1
                End With
    
                pivotField = CType(pivotTbl.PivotFields("On Hand"), Excel.PivotField)
                With pivotField
                    .Orientation = Excel.XlPivotFieldOrientation.xlDataField
                    .Function = Excel.XlConsolidationFunction.xlSum
                    .NumberFormat = "### ### ### ##0.000;[Red]-### ### ### ##0.000"
                    .Name = " Stock On Hand"
                    .Position = 2
                End With

    And now add the data item to piece the 2 data fields together

                pivotField = CType(pivotTbl.PivotFields("Data"), Excel.PivotField)
                pivotField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField
                pivotField.Name = "  "

    The pivot is now in place

    The sum of the tonnage in each location could be zero.

    SO I would like to add a filter to exclude them on Location (a value filter) value not equal to 0

    This is the code I add to try that

                pivotField = CType(pivotTbl.PivotFields(" Location"), Excel.PivotField)
                pivotField.PivotFilters.Add(Type:=Excel.XlPivotFilterType.xlValueDoesNotEqual, Value1:=0, DataField:=pivotTbl.PivotFields(" Stock On Hand"))

    I am attempting to add the value filter on the Location field, with my " Stock on Hand" as my data field,

    This keeps breaking here saying the following: Exception from HRESULT: 0x800A03EC

    Please could someone assist with this, have been battling for days now.

    Thursday, August 10, 2017 7:31 AM

All replies

  • Hello,

     >>The sum of the tonnage in each location could be zero.

    Is there a field named as tonnage? What is its data?

    I suggest you create a sample file and share it here or share some sample data here.

    It hard for me to understand your expected result based on your description.

    Regards,

    Celeste 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 11, 2017 5:37 AM
    Moderator
  • Hi JacquesOkes,

    Has your issue been resolved? If it has, it would be appreciated if you could share us your solution and mark it as answer.

    If not, could you share us your current issue.

    First, I suggest you check whether the “Location” and “Stock On Hand” exist. Do these two fields contain empty space?

    Second, have you applied filters before this PivotFilter? If you have, you need to clear the filter first and then apply new filters.

    Here is a simple code in VBA, you could implement similar in vb.net

    ActiveSheet.PivotTables("PivotTable2").PivotFields("X").ClearAllFilters
        ActiveSheet.PivotTables("PivotTable2").PivotFields("X").PivotFilters.Add2 Type _
            :=xlValueDoesNotEqual, DataField:=ActiveSheet.PivotTables("PivotTable2"). _
            PivotFields("Sum of Y"), Value1:=3

    If you have trouble to make it work, it would be helpful if you could share us your demo project with workbook, we will try to make a test with your project.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, August 14, 2017 7:09 AM
  • Hi there,

    No this is has still been resolved, Attached is a screenshot of what the pivot must look like.

    This is what I can generate with the above code (excluding the pivot filter)

    Based on the code above and additional formatting, I get the above.

    The required result is to remove the ZERO On Hand Balances so the report should actually show as follows:

    As you can see the Pivot Filter is a value filter :  Where OnHand <> 0

    Only showing the valid items.

    My issue is getting the pivot filter to apply.

    Every time I run it it gives me a comm error as stated above.

    I hope that sheds light on the situation.


    Tuesday, August 22, 2017 9:07 AM
  • Hi there,

    Thanks for your input, unfortunately still not resolved. I replied to the previous comment with the screen shots and more detail of what I'm trying to achieve and not succeeding with.

    Thanks for your input.

    Tuesday, August 22, 2017 9:08 AM
  • Hello,

    I suggest you check if you could successfully get PivotFields(" Location") and PivotFields(" Stock On Hand").

    You could also try to record a macro when filtering manually to check what code it would use and then modify into VB.NET.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 23, 2017 8:29 AM
    Moderator
  • Hi there,

    Thanks for replying.

    I can successfully get the pivot fields " Location" and " Stock On Hand".

    Can access them and message them out no problem, the fields are fine, just cannot add it to the pivot filter.

    It works in vba 100%, just cannot get the comm class in Vb.net to work. It may have been deprecated and there is also no documentation that I can find with regards to the Excel Comm class for visual studio.  Think this is impossible here and I must move on

     

    Thursday, August 24, 2017 9:27 AM
  • Hello,

    The code works fine for me in VBA or VB.NET. Could you share your file here?

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 25, 2017 5:48 AM
    Moderator