Excel Pivot Table

Hi all,

I have upgrade my AS 2000 cube to As 2005, but there has some thing wrong with the Excel Pivot Table. In the cube I have one Time dimension with three attributes, Year, Month, Day. Then I create a Hierachies with three levels, Year, Month, and Day. Finally I retrieve the data though Excel Pivot Table. I put the Time Hierachies in the page field, and put the Year attribtues in the column filed. But no matter how I filter in the page fields, ex select January 2006 and 2007, the column field will display the whole year of data for 2006 and 2007. I didn't have this problem in AS 2000, anyone know the reason.

Thanks,

Tomas

My first guess is the relationships between the attribute hierarchies may not be correct. Could you describe the attribute relationships explicitly defined in the dimension? Could you also clarify if Month is modeled as January, February, etc. or January 2006, February 2006, ..., January 2007, February 2007, etc.?

Thanks,
Bryan

|||

The attributes relationship as follow:

Date(Usage: key)

|__Calendar Month

ex, 2007-1-1

Calendar Month(Usage: Regular)

|__Calendar Year

ex, January 2007

Calendar Year(Usage: Regular)

ex, 2007

Hierachies

*Calendar Year

**Calendar Month

***Date

Thanks,

Tomas

|||

Thomas,

This post was marked as answered. Has the problem been resolved?

Thanks,
Bryan

|||

sorry my mistake to marked as answered. The problem has not been resolved.

Thanks

|||

Not really sure what's going on with this. I'd suggest seeing if this is a problem in other browsers. If it is, try replacing the dimension and see if the problem persists. You may also want to open profiler to snag the query being submitted by Excel to see if it is just assembling a weird statement.

B.

|||

Here is what Excel generate:

WITH MEMBER [Time].[Year - Quarter - Month - Date].[XL_QZX] AS 'Aggregate ( { [Time].[Year - Quarter - Month - Date].[Quarter].&[2005-01-01T00:00:00] , [Time].[Year - Quarter - Month - Date].[Quarter].&[2004-01-01T00:00:00] , [Time].[Year - Quarter - Month - Date].[Quarter].&[2003-01-01T00:00:00] } )' SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Time].[Year].[All]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [MaxMinSales] WHERE ([Measures].[Store Sales], [Time].[Year - Quarter - Month - Date].[XL_QZX])

But the result is the whole year, instead of Quarter 1.

|||

I'm not sure exactly what the problem is, but quarter wasn't in the hierarchy description from above. Take a look at the relationship from month to quarter and then quarter to year. I'm thiking that's where the problem is likely at.

B.