I have a Pivot table connected to an OLAP data source.

The table shows financial details from a number of different stores.

I am using the Store Dimension as a Page field, and I wish to progamatically
restrict access to this page field so that managers cannot see details from
other stores.

There are approximately 50 stores in the group and I dont want to have to
create seperate cubes for each store.

Is there any way that I can set the page field to the correct store and then
restrict anyone from altering that setting

Thank you in advance

TB

I have finally managed to find a solution to this.

It was such a pain in the backside to work it out that I thought that I
should share it with the newsgroup

1. Use the PivotFields CurrentPageName property to set the Page Item to
what you want.

2. Set the CubeFields ShowInFieldList property to False to prevent
users selecting the item from the field list.

3. Use
Application.CommandBars("PivotTable").Controls("Pi vottable").Enabled = False
to disable the user from accessing the Pivottable Wizard button

****Note that the following only works in Office XP service Pack 3. It will
give a runtime error if done in SP2 *************

4. Set the PivotFields EnableItemSelection property to False to disable
the dropdown

5. Use the following code to prevent the user dragging the page field
to another area on the Pivot table to circumnavigate this protection

With objWorksheet.PivotTables(1).PivotFields("[Store]")
.DragToData = False
.DragToHide = False
.DragToPage = False
.DragToRow = False
End With

Hope someone finds this useful

TB

"TonyBall" wrote in message
...
I have a Pivot table connected to an OLAP data source.

The table shows financial details from a number of different stores.

I am using the Store Dimension as a Page field, and I wish to
progamatically
restrict access to this page field so that managers cannot see details
from
other stores.

There are approximately 50 stores in the group and I dont want to have to
create seperate cubes for each store.

Is there any way that I can set the page field to the correct store and
then
restrict anyone from altering that setting

Thank you in advance

TB