Pivot table Report filtering based on range of values

subrahmanyam

New Member
Joined
Feb 21, 2015
Messages
14
I had a pivot table with Report filter which has 8k items trying to filter and it is taking he'll lot of time if I give the last item of the pivot table in the range.

Could someone help me on this.

Thanks
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,010
  • Where is the normalized data table that is the source for your pivot table?
  • Filtering a huge pivot is expected to be slow. You may consider the following workaround:


  • Run a SQL query to filter the source table with the desired criteria.
  • Create the pivot based on the filtered data. It should give you the same result, but much faster.
  • VBA can be used to automate this process. I will prepare some sample code.
 

subrahmanyam

New Member
Joined
Feb 21, 2015
Messages
14
Hi Worf,

Thanks for responding.

My answers to your query

1.The source of the pivot is excel it self.

Thanks for the workaround.

Could you send me the sample files so that I will have a look.


Thanks a lot in advance.

Regards,
Subrahmanyam
 
Last edited:

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,010
Can you adapt this example to your specific configuration or would you like help with this?



Code:
Sub rahmanyam()
RunSEL          ' filter raw data
PTable          ' create pivot table
End Sub


Sub RunSEL()
Dim cn As Object, rs As Object, i%
Set cn = CreateObject("ADODB.Connection")
With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & _
    ";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    .Open
End With
Set rs = cn.Execute("SELECT * FROM [sheet3$] WHERE Amount > 200")
i = 2
Do
    Cells(i, 8) = rs(0)
    Cells(i, 9) = rs(1)
    Cells(i, 10) = rs(2)
    Cells(i, 11) = rs(3)
    i = i + 1
    rs.Movenext
Loop Until rs.EOF
rs.Close
cn.Close
Set cn = Nothing
Set rs = Nothing
End Sub


Sub PTable()
Dim lr, sh As Worksheet, sdata$, dest$, pt As PivotTable
Set sh = Worksheets("sheet3")
lr = sh.Range("h" & Rows.Count).End(xlUp).Row
sdata = sh.Name & "!" & sh.[h1].CurrentRegion.Address(ReferenceStyle:=xlR1C1)
dest = sh.Name & "!" & sh.Cells(lr + 10, 8).Address(ReferenceStyle:=xlR1C1)
ActiveWorkbook.PivotCaches.Create(xlDatabase, sdata, 5).CreatePivotTable _
dest, "MyPivot", , xlPivotTableVersion15
Set pt = sh.PivotTables("MyPivot")
With pt.PivotFields("Region")
    .Orientation = xlPageField
    .Position = 1
End With
With pt.PivotFields("Employee")
    .Orientation = xlRowField
    .Position = 1
End With
pt.AddDataField pt.PivotFields("Amount"), "Sum", xlSum
pt.TableStyle2 = "PivotStyleMedium5"
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,032
Messages
5,526,355
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top