C
CindyPsych
We have a pivot table in Excel 2007 that is based on an SQL OLAP
Cube. If a user changes one of the Row Labels by typing in the cell,
a Refresh of the pivot doesn't change it back.
Imagine that the row label is Country, for instance, and the first one
is labeled Australia. If you click on that cell and type "Banana",
then refresh the pivot, or remove Country and put it back, or do
anything to it, it stays as "Banana". Even if you move the dimension
from the Row Labels to the Report Fillter, you see "Banana" in the
drop-down, not "Australia".
1) Can anyone tell me how to refresh this and get it back to
Australia? If I make a new pivot it says Australia, but the concern
is that if this is given to an end-user they may inadvertently change
something and a refresh doesn't put it back to how it should be.
2) Can anyone tell me how to prevent this from happening? I've been
experimenting with protecting the sheet, but even if you check "Use
pivot table reports", if you try to change the Report Filter it gives
me an error message stating "That command cannot be performed on a
protected sheet because the source data for this Pivot Table requires
a data refresh." So how can I stop users from changing the Row Labels
while still allowing them to change the Report Filters?
Any help would be appreciated. Thanks.
- Cindy
Cube. If a user changes one of the Row Labels by typing in the cell,
a Refresh of the pivot doesn't change it back.
Imagine that the row label is Country, for instance, and the first one
is labeled Australia. If you click on that cell and type "Banana",
then refresh the pivot, or remove Country and put it back, or do
anything to it, it stays as "Banana". Even if you move the dimension
from the Row Labels to the Report Fillter, you see "Banana" in the
drop-down, not "Australia".
1) Can anyone tell me how to refresh this and get it back to
Australia? If I make a new pivot it says Australia, but the concern
is that if this is given to an end-user they may inadvertently change
something and a refresh doesn't put it back to how it should be.
2) Can anyone tell me how to prevent this from happening? I've been
experimenting with protecting the sheet, but even if you check "Use
pivot table reports", if you try to change the Report Filter it gives
me an error message stating "That command cannot be performed on a
protected sheet because the source data for this Pivot Table requires
a data refresh." So how can I stop users from changing the Row Labels
while still allowing them to change the Report Filters?
Any help would be appreciated. Thanks.
- Cindy