none
Pivot Table Criteria - Report Header RRS feed

  • Question

  • I have a pivot table that summarizes sales information by Customer Product, and date ranges.

    I'm attempting to find a method of showing above the pivot table what criteria has been selected so that the report can be printed showing the criteria that was selected (i.e. Date Range, Specific Products, etc.)

    Is there a way of pulling the criteria that has been selected in the pivot table and including that in a report header, or just a few select cells?

    Any suggestions would be appreciated.

    Thanks.

    JMData Consultant

    Thursday, November 13, 2014 6:15 PM

Answers

  • Hi JMData,

    For this requirement, you could iterate the PivotItem (for select criteria) and AvtiveFilter (for filter), then add these to the corresponding field (e.g. report header)

    For example, for the selected criteria, the value of that PivotItem is true.

    To get the ActiveFilters:

    Dim PvtTbl As PivotTable
    Set PvtTbl = ActiveSheet.PivotTables(1)
    For i = 1 To PvtTbl.ActiveFilters.Count
    With PvtTbl.ActiveFilters(i)
    MsgBox "Filter Item: " & i & vbCrLf & "Filter Type: " & .FilterType & vbCrLf & "Filter Field: " & .PivotField & vbCrLf & "Filter Value1: " & .Value1 & vbCrLf & "Filter Value2: " & .Value2 & vbCrLf & "Filter Name: " & .Name & vbCrLf & "Filter Description: " & .Description & vbCrLf & "Filter MemberProperty: " & .IsMemberPropertyFilter & vbCrLf & "Filter Parent: " & .Parent
    End With
    Next i

    To get PivotItem:

    Dim PvtTbl As PivotTable
    Dim oPi As PivotItem
    Dim s As String
    Set PvtTbl = ActiveSheet.PivotTables(1)
    MsgBox PvtTbl.PivotFields("CreateDate").PivotItems.Count
    For Each oPi In PvtTbl.PivotFields("CreateDate").PivotItems
     s = oPi.Value & ";" & oPi.Visible
    Next oPi

    Best Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, November 14, 2014 9:15 AM
    Moderator

All replies

  • Hi JMData,

    For this requirement, you could iterate the PivotItem (for select criteria) and AvtiveFilter (for filter), then add these to the corresponding field (e.g. report header)

    For example, for the selected criteria, the value of that PivotItem is true.

    To get the ActiveFilters:

    Dim PvtTbl As PivotTable
    Set PvtTbl = ActiveSheet.PivotTables(1)
    For i = 1 To PvtTbl.ActiveFilters.Count
    With PvtTbl.ActiveFilters(i)
    MsgBox "Filter Item: " & i & vbCrLf & "Filter Type: " & .FilterType & vbCrLf & "Filter Field: " & .PivotField & vbCrLf & "Filter Value1: " & .Value1 & vbCrLf & "Filter Value2: " & .Value2 & vbCrLf & "Filter Name: " & .Name & vbCrLf & "Filter Description: " & .Description & vbCrLf & "Filter MemberProperty: " & .IsMemberPropertyFilter & vbCrLf & "Filter Parent: " & .Parent
    End With
    Next i

    To get PivotItem:

    Dim PvtTbl As PivotTable
    Dim oPi As PivotItem
    Dim s As String
    Set PvtTbl = ActiveSheet.PivotTables(1)
    MsgBox PvtTbl.PivotFields("CreateDate").PivotItems.Count
    For Each oPi In PvtTbl.PivotFields("CreateDate").PivotItems
     s = oPi.Value & ";" & oPi.Visible
    Next oPi

    Best Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, November 14, 2014 9:15 AM
    Moderator
  • Hello Starain,

    Thank you very much for your response.  I'm attempting to use the code that you provided, and I'm not getting any results.  I'm fairly new to VBA Coding, so I'm wondering if there are Reference Libraries or something that have to be enabled in order for it to work.

    The code that you provided, does it  have to be customized to the fields that are in the pivot table that I'm attempting to use it on?

    Again, I appreciate your response and efforts to help, and look forward to solving this.

    JMData Consultant

    Sunday, November 16, 2014 12:29 AM
  • Hi JMData,

    The code that I provided is just a sample to get filter and specify PivotItem (e.g. The CreateDate is a field in my pivot table), you need base on your exist file and requirements to achieve that.

    >> so I'm wondering if there are Reference Libraries or something that have to be enabled in order for it to work

    You need to enable macros to run.

    >> does it have to be customized to the fields that are in the pivot table that I'm attempting to use it on

    You don’t need to custom field.

    You may have a button and add the local (e.g. get filter of PivotTable) in click event.

    On the other hand, you could learn VBA first then achieve that.

    Best Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, November 18, 2014 9:29 AM
    Moderator
  • I want to try and simplify this a little.

    There are three filters that I set on this pivot table that I'm attempting to create this summary for:

    Field Name: Link (usually just one selection is made, but there are occasions for multiple selections)

    Field Name: Item (several selections are always made)

    Field Invoice Date: Is between a Start and End Date

    Instead of a message box, there a way to put the filtered criteria in a cell?  For those items that have multiples, is it possible to loop through and also put them in a field separate by a comma?

    I'd like to have the information be printed with the pivot table.

    Thanks.

    JMData Consultant

    Friday, November 21, 2014 5:15 PM
  • Hi JMData,

    As I said that that is just a sample and in that sample it uses message box to display the message, you need to modify it to meet your requirement, such as set in the cell with comma.

    To set the data to the Range, we could use Range.Value.

    # Range Object (Excel)

    http://msdn.microsoft.com/en-us/library/office/ff838238(v=office.15).aspx  

    If you still have the issue of how to set the data to Range or Cell, please create a new thread.

    Best Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, November 25, 2014 9:40 AM
    Moderator