I am trying to format an MDX UPDATE CUBE statement based on a cell selected
in a pivot table defined over this cube. When the selected cell is a not a
totaled cell I do not have an Issue. However, when a total cell is selected
on the pivot table, I cannot seem to format the MDX as I would expect.

There is a time dimension named TIMEPERIOD which contains the standard
attributes YEAR QTR MONTH. There is also a hierarchy in this dimension
called YEARQTRMON which has the order: YEAR ->QTR>MONTH

YEAR QTR MON
2006 Q1 01
2006 Q1 02
2006 Q1 03
2006 Q2 01
2006 Q3 01
2006 Q4 01

I want to distribute 5000 units to all items which make up one aggregate
cell on the pivot, so I can make an MDX statement such as

UPDATE CUBE [CubeName]
SET ([Measures].[Sales],[TimePeriod].[yearqtrmon].[Year].&[2006].&[Q2])= 5000
USE_WEIGHTED_ALLOCATION;

The above statement nicely distributes the 5000 units to all rows in year
2006, qtr Q2.

However, if a 'totaled' pivot cell totaled along the QTR axis is choosen,
along with some explicit filters such as Q1 and Q2 only, rather than
Q1,Q2,Q3,Q4) In other words, distribute the 5000 to all rows which form the
aggregation for the Q1 and Q2 quarters of year 2006. I niavely thought the
MDX would be something like:

UPDATE CUBE [CubeName]
SET
([Measures].[Sales],[TimePeriod].[yearqtrmon].[Year].&[2006].&[Q1],[TimePeriod].[yearqtrmon].[Year].&[2006].&[Q2] )= 5000
USE_WEIGHTED_ALLOCATION;

But I get The message 'YearQtrMon' hierarchy appears more than once in the
tuple.

So I broke out the attributes thinking the hiearchy was an issue....
UPDATE CUBE [CubeName]
SET
([Measures].[Sales],[TimePeriod].[Year].&[2006],[TimePeriod].[QTR].&[Q1],
TimePeriod.[QTR].&[Q2] )= 5000
USE_WEIGHTED_ALLOCATION;

Same issue…

So how can I distribute 5000 along a partial QTR axis. (Q1 and Q2, but not
Q3,Q4)

Hello,

Based on my scope, you need to use "turple" in under the siatuation such as
[TimePeriod].[yearqtrmon].[Year].&[2006].&[Q2]. You could not use "set" for
the stiuation.

You may need to create a level such as "half year" to meet your requirements

Best Regards,

Peter Yang
MCSE2000, MCSA, MCDBA
Microsoft Partner Online Support

Get Secure! - www.microsoft.com/security

================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
od].[yearqtrmon].[Year].&[2006].&[Q2] )= 5000