Hi all,

I have a peculiar issue which doesn't seem to make any sense to me and
hoping that someone out there has an idea . . .

In short we have a requirement where we want to display any of four
attributes as a Page Filter in an Excel PivotTable. The four
attributes are:
(a) Our product version number
(b) the date the date is current as of
(c) the date of the version build
(d) the name of the environment (i.e. Dev/QA/UAT/Prod)

To accomplish this we have a System Info Dimension where we store the
four attributes and update the the second attribute [(b) the date the
date is current as of] with the current date so that the dimension key
doesn't change. In the Analysis Services dimension each of the four
attributes reference a static/constant dimension key for the KeyColumns
and the actual attribute description for the NameColumn. Note: We have
found that this static dimension key setting in the KeyColumns is
necessary otherwise you get the Excel error "The item cannot be found"
and the only way around this is to remove the dimension from the
PivotTable and then replace it.

But what we are finding is that the display of the date doesn't change
on a daily basis (through a 'Refresh on Open') even though if you view
the members in the drop down box in the Page Filter the new date is
shown. We would obviously prefer to have it update without have to
reselect it as the users may not always think to do that.

Clearly Excel looking at the KeyColumns value and seeing that it is
still there and not refreshing the member display. The interesting
thing is that you see the same behaviour in an Office Web Components
PivotTable if you only refresh; however, if you reconnect it updates
properly.

To illustrate the problem I have created a similiar example using the
Adventure Works DW database.

In this case we decide to rename the 'Budget' scenario to 'Budget 1'
and keep the existing dimension key intact.

1. Create a regular data source to the Analysis Services Adventure
Works DW database
2. Select the Finance cube
3. Structure the Pivot Table with the 'Scenario' Dimension in the
Page Section and 'Amount' in the Data Section (Note: There are no
dimensions displayed in the Row or Column sections)
4. Result of PivotTable should show 'Actual' with Internet Sales
of $21,609,503.00
5. From the Category Page Filter select 'Budget' (the Amount
should be $5,583,900.00)
6. Keep the Excel Workbook open with the PivotTable intact
7. In the SQL Server database run the following update statement:

USE AdventureWorksDW
GO

UPDATE dbo.DimScenario
SET ScenarioName = 'Budget 1'
WHERE ScenarioKey = 2

8. Perform a Process Update of the Scenario Dimension.
9. After successfully processing the dimension refresh your Excel
PivotTable.
10. Notice that the data amount of $5,583,900.00 remains and the
display of 'Budget' member continues to show in the Page Filter
field. When you select the drop down to list the valid values the
member 'Budget' doesn't exist but the 'Budget 1' value exists
and can be selected.
11. Why doesn't the Excel Page Filter display update on the refresh

Even a full process of the dimension and the Adventure Works Cube that
is related sees the same behaviour.

Is there a way to force the 'reconnect' behaviour in Excel that Office
Web Components performs?

Any ideas/thoughts are appreciated.

Barry