PivotTable Filter Automaticly adds new data

[erdal akbulut]

Hi,

I have a strange problem.

MS Excel 2003 pivot table linked to SSAS 2005 Cube. One of the dimensions
let's say Customer is filtered on the pivot table. 5 from total of 100
Customers are selected in filter. Next day assume 2 new customers added to
customer dimension and when the pivot refreshed those 2 new customers are
automaticly added to filter and the result is 7 customers selected out of
102 total.

Do you know any ways to prevent this and keeping the only original filter
without adding new records?

Thanks in advance

erdal

 

[bart.strubbe]

Hi,

I have a strange problem.

MS Excel 2003 pivot table linked to SSAS 2005 Cube.  One of the dimensions
let's say Customer is filtered on the pivot table. 5 from total of 100
Customers are selected in filter.  Next day assume 2 new customers addedto
customer dimension and when the pivot refreshed those 2 new customers are
automaticly added to filter and the result is 7 customers selected out of
102 total.

Do you know any ways to prevent this and keeping the only original filter
without adding new records?

Thanks in advance

erdal

this is a macro I made to filter accounts that are (almost) equal to 0
in a pivottable. I don't know your pivot, but you can make it work in
your.
If you have problems, send an example to my emailadress

Sub VerbergenPivotItems()
Sheets("pivot").Select
Dim item, rngTableItem As Range, Terr As Boolean
[A5].Select 'ga zeker ergens in de draaitabel staan
For Each item In
ActiveSheet.PivotTables("PivotTable1").PivotFields("grootboekr.").PivotItems
On Error GoTo fout
Terr = False
Set rngTableItem = ActiveCell.PivotTable.GetPivotData("bedrag",
"grootboekr.", item.Value)
On Error GoTo 0
If Not Terr Then
If Abs(rngTableItem.Value) < 0.0001 Then

ActiveSheet.PivotTables("PivotTable1").PivotFields("grootboekr.").PivotItems(item.Value).Visible
= False
End If
End If
Next
fout:
If Err.Number = 1004 Then Terr = True: Resume Next
End Sub

greetings

bart

 

[erdal akbulut]

Hi,

I have a strange problem.

MS Excel 2003 pivot table linked to SSAS 2005 Cube. One of the dimensions
let's say Customer is filtered on the pivot table. 5 from total of 100
Customers are selected in filter. Next day assume 2 new customers added
to customer dimension and when the pivot refreshed those 2 new customers
are automaticly added to filter and the result is 7 customers selected out
of 102 total.

Do you know any ways to prevent this and keeping the only original filter
without adding new records?

Thanks in advance

erdal

Ok, I have recorded a macro while doing some filtering, here is the result.

ActiveSheet.PivotTables("PivotTable1").PivotFields("[Cusstomer]"_
).HiddenItemsList = Array("[AAA]", _
"[BBB]", _
"[CCC]")

So appereantly Excel keeps a hidden item list when filtering therefore it is
normal that new items appearing in the filter cause they are not included in
the hiddenitemlist.

While working on owc I have used allincludeexclude property and
includeditems collection to filter reports programaticly, I could not find
similar in Excel Pivot Tables.

Does somebody know equalients of allincludeexclude property and
includeditems collection of OWC in excel ?

Thanks,

erdal