Excel pivot tables

[Eradic8or]

Hopefully, this message is in the correct forums.
I have pondered over this for 2 days now and cannot find a resolution.

I have a oracle stored procedure which I am pulling into a ADODB recordset. I then create a pivot table based on the recordset.
This all works fine. If however, I apply a filter to the recordset, I can loop through the records in debug mode and see that the returned records are indeed filtered but when I refresh the pivot table, all the unfiltered records are shown.

I have tried refreshing the pivot table but still nothing.
The code is attached if you want to have a look.

Private Sub cmdAllSites_Click()
Dim rst As ADODB.Recordset
Dim db As Connection
Dim pvtTable As PivotTable
Dim pvtField As PivotField
Dim pvtCache As PivotCache
Dim rsTemp As ADODB.Recordset
Dim SQL As String
Dim i As Long
Set db = New Connection

With db
.CursorLocation = adUseClient
.ConnectionString = "Provider=MSDAORA;" & _
"Data Source=SOURCE;" & _
"User ID=#########;Password=#######;"
db.Open

End With

Set rst = New ADODB.Recordset
SQL = "estores_stock"
rst.Open SQL, db, adOpenStatic, adLockOptimistic

'Filter records depending on choice
rst.Filter = "HospitalCode LIKE 'EDI'"

'make pivot table
Set pvtCache = ActiveWorkbook.PivotCaches.Add(xlExternal)
Set pvtCache.Recordset = rst
Set rsTemp = pvtCache.Recordset

' Delete previous piivot table
Sheets("Sheet1").Select
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select

With pvtCache
.CreatePivotTable Range("C2"), "Site Inventory"
End With

Set pvtTable = ActiveSheet.PivotTables("Site Inventory")


'loop through pivotfields to se if they match ADO recordset
For Each pvtField In pvtTable.PivotFields
Debug.Print pvtField.Name
Next

With pvtTable
.SmallGrid = False

With .PivotFields("Code")
.Orientation = xlRowField
.Position = 1
End With

With .PivotFields("Product")
.Orientation = xlRowField
.Position = 2
End With

With .PivotFields("RH Group")
.Orientation = xlColumnField
.Name = "Blood Group"
.Position = 1
End With

With .PivotFields("Count")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
End With
End With
Set rst = Nothing
End Sub

 

[SkipVought]

Hi,

1. Your PT source is table estores_stock.

2. You are filtering via your program.

So if you merely refresh, your PT looks at the source without the filtering, unless your filtering is build into your PT parameters.

I would only refresh the PT via your code with filtering.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Eradic8or]

Thanks for replying but I still think I am missing something.
I thought the filtered recordset is what was being used to populate the Pivot Table as in the code below.

'Filter records depending on choice
rst.Filter = "HospitalCode LIKE 'EDI'". <- This redeuces the number of records.

    'make pivot table
    Set pvtCache = ActiveWorkbook.PivotCaches.Add(xlExternal)
    Set pvtCache.Recordset = rst <- I though this is what populated the pivot Table. IF that's the case, why is it displaying all the records rather than just the filtered ones?

 

[PHV]

I'd try this:

...
Set pvtCache.Recordset = rst
Set rsTemp = pvtCache.Recordset
rsTemp.Filter = "HospitalCode='EDI'"
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Does this full table refresh happen if your save the workbook, close and reopen?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Eradic8or]

I tried as suggested with
rsTemp.Filter = "HospitalCode='EDI'"

This made no difference.

And no, the table does not refresh on close and open as I have the code attached to a command button the worksheet.

 

[SkipVought]

I asked what happens when you OPEN the workbook and refresh the PT without running any other code?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Eradic8or]

The ability to refresh from the menu is greyed out when I just open the workbook. The only way I can get new data in is to run the code.
I assume this is because the pivot table is unbound as I create it programmatically.

 

[SkipVought]

I assume, then, that with the xlExternal property constant, it LINKS to the TABLE, once your code executes and refreshes with from the TABLE, as the filtering of the original source is in the code and not in the PT.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Eradic8or]

You may well be right, unfortunately, I still don't know how to resolve it

 

[SkipVought]

Set the criteria in the SQL!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Eradic8or]

I cant unless you can use a SELECT statement around an existing stored procedure.
I am pulling in the recordset from an Oracle stored procedure of which I have no access to amend. The DBA won't change it either because it is used to other things.

 

[SkipVought]

Then put the resultset on a sheet and use THAT table as the PT INTERNAL source.

dim iCol as integer, fld as ADODB.Field
for each fld in rst.Fields
   SomeSheetObject.Cells(1,iCol+1).Value = fld.name
   iCol=iCol+1
Next
SomeSheetObject.Cells(2,1).CopyFromRecordset rst

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Eradic8or]

Thanks for that. I will try tomorrow and let you know how I get on.

 

[SkipVought]

But a more basic question would be, why use PT refresh at all, unless your QUERY returned the exact resultset required? My last post is not really a solution to your problem. The ONLY solution I see, is to query your db with exactly the criteria you require. Otherwise, I do not believe that a PT refresh will do what you expect.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Eradic8or]

The reason I am using this method is that I want to return all the records in the stored procedure and then present the user with 5 buttons which select a different criteria.
Rather than change the SQL and keep interrogating the database, I felt it would be easier to pull back all the records and then use the recordset filter propery and then refresh the pivot table.
The filter actually work as it reduced the number of records but I would need to loop through the set and populate excel cells with the information.

I thought a pivot table would be better as it does all the neccesary sorting.

Didn't honetly think my request would be that difficult