Link to home
Start Free TrialLog in
Avatar of martinibbo
martinibbo

asked on

Can you filter a pivot table based on another pivot tables results?

Hi,

I have two pivot tables and I want to be able to filter my second pivot table ("SOs")  based on the first pivot tables ("POs") results using vba.

The code I have so far is as follows:
    Dim MyArray As Variant
    MyArray = ActiveSheet.PivotTables("POs").PivotFields("[New_PO_2].[PO Number].[PO Number]").VisibleItemsList
    ActiveSheet.PivotTables("SOs").PivotFields("[New SO 2].[PO Number].[PO Number]").VisibleItemsList = MyArray

Open in new window


The issue with this however, is that each element in the array references the first pivot tables data source, e.g. [New_PO_2].[PO Number].&[10255], where the second pivot table uses a different source and would require the element changing to [New_SO_2].[PO Number].&[10255].

I am thinking something along the lines the following, but I cannot get it to work:
For i = LBound(MyArray) To UBound(MyArray)
MyArray(i, 1) = Replace(MyArray(i, 1), "[New_PO_2]", "[New_SO_2]")
Next i

Open in new window


I would also need to do some error checking in case certain items were not found in the second pivot table so that they were ignored.

Thanks
Avatar of Professor J
Professor J

Have you considered using pivot slicers?
Avatar of martinibbo

ASKER

I'm not sure how I would do this accross two pivots using different data sources - could you provide an example workbook / walkthrough / link?

I think that the problem is that the two pivot tables use different Connections as a Data Source.

I also need it to be automated so that when the first pivot is filtered by the customers name and the specific item number, the second pivot is filtered by the PO Numbers that customer has ordered (i.e. results of Pivot Table 1)

Thanks
Can you post your sample file.. Also i'm assuming this is what you are looking for you got pivot-1 and pivot-2..Now the values which you have selected in pivot-1..you only want to show those values in pivot-2.. Is this what you are looking for??

Saurabh...
Hi Saurabh,

It is difficult for me to post the actual file as the data held within is commercially sensitive, so I have created a basic one attached.

Pivot Source 1, is the raw data for PivotTable1 which is held in a workbook Query (Connection Only)
Pivot Source 2, is the raw data for PivotTable2 which is held in a workbook Query (Connection Only)

When PivotTable1 is filtered, I want the resulting PO Numbers to be the filters on PivotTable2 as shown in the "mocked up" desired result.
The Code within Module1 does not work because the source data is in different workbooks. i.e. the individual elements in the array point at different sources.

What you describe seems in line with my desired results, where the source data is two separate connections.

Is there not a way using vba to change each elements name within an array as described above, or perhaps to write the array to a worksheet, rename each element, then reload to the array?

Hope this helps.

Many thanks
EE---Pivot-Tables.xlsm
Ok, so I got there, but I would appreciate input whether there is a more efficient way of doing it rather than writing to a range first and testing that each item exists in pivot2.

code now looks like this:
Sub test()

Dim MyArray As Variant
    MyArray = ActiveSheet.PivotTables("PivotTable1").PivotFields("[Table1].[PO Number].[PO Number]").DataRange

LastRow = UBound(MyArray)

Range("L1:L" & UBound(MyArray)) = MyArray
Range("L1:L" & UBound(MyArray)).Select

 Dim c As Range
 For Each c In Selection
 If c.Value <> "" Then c.Value = "[Table5].[PO Number].&[" & c.Value & "]"
 If bFieldItemExists(c.Value) = False Then c.Clear
 Next
        
Dim myArray2 As Variant
        
myArray2 = Application.Transpose(Range("L1: L" & LastRow).Value)

ActiveSheet.PivotTables("PivotTable2").PivotFields("[Table5].[PO Number].[PO Number]").VisibleItemsList = myArray2
Range("L1: L" & LastRow).Clear

Range("E1").Select


End Sub

Function bFieldItemExists(strName As String) As Boolean
  
  Dim strTemp As String
  On Error Resume Next
  strTemp = ActiveSheet.PivotTables("PivotTable2").PivotFields("[Table5].[PO Number].[PO Number]").PivotItems(strName)
  If Err = 0 Then bFieldItemExists = True Else bFieldItemExists = False

End Function

Open in new window


I have reattached the excel sheet for reference.
EE---Pivot-Tables.xlsm
Martin,

Caught in couple of things at the moment..Give me time till thursday will post you a solution which you going to love..

Saurabh...
SOLUTION
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I guess then all good and like i said i don't understand it myself why it's happening because it should work..But glad able to make it work..

I believe then you are good to go on this..

Saurabh...
Saurabh's efforts solved 99% of my question and without his input I would not have a solution. Thanks :)