locked
Same data set, set filters on Pivot table tabs RRS feed

  • Question

  • I need to create a pivot table with filter function in tab.

    The data source is from Microsoft database. The data includes stores, UPC, sales.

    There are two tabs in this pivot table, the first tab ‘all’ includes all of the data information, with a subtotal for each store; the second tab ‘topSale’ only includes stores with more than 100 unit sales (SubTotal > 100).

    Question:

    1.       Can I set a filter for the subtotal in the page field? What I am thinking it’s that this is a calculated field but not the raw data from the data source, it may not work. Correct me if I am wrong.

    2.       If I don’t want the customer to see / control the filter in the page field? Is there any place I can apply this filter in the ‘topSale’ tab.  

    Since I am using the same data set.  I don’t think it’s a good idea to create 2 pivot caches, one for each tab.  Can anyone tell me a solution or provide some reference how to handle this problem?  Thanks a advance.

    Monday, October 3, 2011 8:29 PM

Answers

  • Hi Mindy,

    Right click the cell you want to set filter in the pivot table->filter, then you could set the filter condition. Could you please describe this issue more clearly? What kind of filter do you want to set on Pivot table tabs? Could you please provide an image to explain more detail? In addition, which Excel edition do you use?

    By the way, more information about filtering data programmatically using PivotTables from MSDN:

    http://msdn.microsoft.com/en-us/library/ff536099(v=office.12).aspx (Filtering Data Programmatically in Excel 2007 by Using PivotTables)

    Have a nice day.

    Best regards


    Liliane Teng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Edited by Liliane Teng Friday, October 7, 2011 9:56 AM
    • Proposed as answer by Bruce Song Thursday, October 20, 2011 11:49 AM
    • Marked as answer by Liliane Teng Thursday, October 27, 2011 9:19 AM
    Friday, October 7, 2011 9:51 AM

All replies

  • Hi Mindy,

    Right click the cell you want to set filter in the pivot table->filter, then you could set the filter condition. Could you please describe this issue more clearly? What kind of filter do you want to set on Pivot table tabs? Could you please provide an image to explain more detail? In addition, which Excel edition do you use?

    By the way, more information about filtering data programmatically using PivotTables from MSDN:

    http://msdn.microsoft.com/en-us/library/ff536099(v=office.12).aspx (Filtering Data Programmatically in Excel 2007 by Using PivotTables)

    Have a nice day.

    Best regards


    Liliane Teng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Edited by Liliane Teng Friday, October 7, 2011 9:56 AM
    • Proposed as answer by Bruce Song Thursday, October 20, 2011 11:49 AM
    • Marked as answer by Liliane Teng Thursday, October 27, 2011 9:19 AM
    Friday, October 7, 2011 9:51 AM
  • Hi Liliane,

    Thanks for your reply.

    I am using Excel 2007 now.

    Here is the more detailed sample:

    TAB1 --- ALL

    Sale Person

    QTY

    Smith

    100

    Rosa

    50

    Matt

    20

    Fei

    80

    Lucy

    75

    Jay

    30

     

    TAB2 --- TOP SALE (QTY > 50)

    Sale Person

    QTY

    Smith

    100

    Fei

    80

    Lucy

    75

    In the pivot table, I can use two separate queries for each tab. But actually, data in tab2 is just part of data in tab1. I am wondering if I can use only one query (one pivot table cache) and there is a way to set a filter in tab2, making tab2 only shows QTY > 50.

    Thanks in advance.

                   

    Thursday, October 20, 2011 6:19 PM
  • Hi Mindy,

    Please click the arrow besides Row Labels and select Value Filter, then in the Value Filter window, you could set the condition is greater than 50. The result is as the second image shows.

    If you have any concerns, please feel free to follow up.

    Have a nice day.

    Best regards


    Liliane Teng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, October 21, 2011 5:37 AM
  • Hi Mindy,

    When you come back, if I misunderstood this issue, please feel free to follow up. We will continue to work with you on this issue.

    Have a nice day.

    Best regards


    Liliane Teng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, October 27, 2011 9:20 AM