Dimensions within Excel Pivot Table

Hi

I have a cube that has dimensions such as year, company, customer, statustext, employee etc.

In the Browse in Analysis Manager - all the dimensions look fine.

When I access the same cube from Excel after dragging and dropping the dimensions during analysis the dimensions in the Page section are not what is show when dragged to the row section.

For example - i have a display Customer as rows, years as columns. I drag the statustext next to customer and shows customer. The filter in the page section for statustext is correct. I have tried moving the dimension back to the Field List and re-adding, refreshing from the cube makes no difference. The only solution I have been able to come up with is rebuild the Pivot table - not what I want users to have to do.

Unfortunately, at teh moment we are limited to Excel for presentation.

Anyone ever seen this or have any suggestions?

Just remembered, I have experienced "Catastrophic Failure" - in Excel not sure if this is related and is server generated or local to Excel.

Steve

What version of SQL Server and what version of Excel are you using?

Also I did not quite understand the problem you are experiencing. Could you be more specific?

|||

Hi

SQL Server 2000 - Analysis Server 2000 SP3

I have a Pivot table from a cube, this pivot table has multiple dimensions.

For example - Account Manager, Status, Customer, Employee

If I display Sales by Customer (row) by Year (col) - that works.

If I then drag Status to the row - either to replace or in addition to customer - the display is actually Account Manager. The dimension Status is no longer in the Page Header but Account Manager is.

However, if I filter in the Page Header for a specific Status this shows correctly.

The only way I can fix it is to go into the Wizard - choose options - remove all dimensions, re-add them.

Thanks

|||

One possibility is that if your Excel file got corrupted (some mixup with dimensions), this would explain current behavior.

Can you re-produce the problem from scratch (blank spreadsheet)? If you can, how long does it take?

|||

It does happen from scratch but I cannot reproduce it intentionally. I did wonder if it may be because the structure of teh cube within Analysis Server changed. I hope this is not the case.

|||

I have seen a thread where somebody said he regularly experiences problems with pivot becoming corrupt when a datasource changes.

He said it's really common in Excel XP, but does not happen as often with Excel 2003. Since I don't know the Excel version you use, one possibility is to go with Excel 2003.

Also another solution is to always build pivots from scratch. You can even automate this by writing some VBA logic. Hope this helps!