Please find enclosed my MDX Below.

I want to modify this in such a way that it gives me the first Day of
Each Month In My Time Dimension.

My Time Dimension structure is as follows.

Level 1 MOC Year
Level 2 MOC Quarter
Level 3 MOC TDP- TDP here means Ten Day Period
Level 4 Day

Please Help

WITH MEMBER [Measures].[DaysOnHand] AS '([Measures].[Depot Stk Qty In
Tons]/([Measures].[eB Primary Sales Forecast Qty In Tons]/30))'


SELECT
{[Measures].[Depot Stk Qty In Tons],[Measures].[eB Primary Sales
Forecast Qty In Tons],[Measures].[DaysOnHand]} ON COLUMNS,

crossjoin ({[PRODUCT].[CATEGORY].[Business
Area].members},{Descendants([TIME].[MOC].[All MOC
TIME].[MOCYR.2005],[TIME].[MOC].[Day]).Item(0)}) ON ROWS
FROM hpc_image

Hi Hemanth -

One way to do this is to create a calculated member that refers to the
firstchild of the current month and sums up whichever measure you'd like.
Unfortunately it's not very dynamic, you'd have to create a calc for each
measure you'd like this to apply to, but for now, without restructuring your
cube, you could do something like:

with member [measures].[SumOfFirstDayOfMonthForMeasureX] AS
'SUM({[time].[MOC].CurrentMember.FirstChild},[Measures].[X])'

SELECT
{[Measures].[SumOfFirstDayOfMonthForMeasureX] ON COLUMNS,

crossjoin ({[PRODUCT].[CATEGORY].[Business
Area].members},{[Time].[MOC].[Month].members } ) ON ROWS
FROM hpc_image



I know my calc member name is ridiculously long, I just did it for clarity,
you can modify the name to be whatever you'd like. I'm not sure how your
Time.MOC hierarchy is set up, I just assumed your month level is called
"Month" in the above example. Also, your calc member divides by 30, I'm not
sure if that would make sense at the day level.

Also you may want to create another calc member that does something like:

with member [measures].[FirstDayOfMonthName] as
'[time].[MOC].CurrentMember.FirstChild.name'

....and select it on columns too, so that the end user would know which day
the summed amount belongs to.


Good luck.

- Phil


"Hemanth" wrote: