Add YTD for number of cubes?

Hello,

We have number of cubes sharing one Time dimension; we need add YTD, QTD, and MTD to each cube, is there a easy way to add YTD, QTD to Time dimension one time instead of add them to the cube one by one?

Thanks in advance for the advice.

Yes - you can add the member holders for YTD, MTD, QTD etc to the corresponding attribute of the Time dimension, and add all the MDX formulas to the dedicated column in the dimension table, and the use CustomFormulaColumn property on the attribute to point to that column. After that, every cube which includes this dimension will have all these formulas automatically.|||Thank you very much Mosha for the help.

1. add YTD column to the Time dimension
2, add all the MDX formulas to the YTD column
(
[All Periods].[YTD].[Year to Date],
[All Periods].[Year].[Year].Members,
[All Periods].[PK Date].Members
) =

Aggregate(
{ [All Periods].[YTD].DefaultMember } *
PeriodsToDate(
[All Periods].[Year - Quarter - Month].[Year],
[All Periods].[Year - Quarter - Month].CurrentMember
)
);

You mean all these code add into column?
3, use CustomFormulaColumn property on the attribute.
where can I find 'CustomFormulaColumn' property?
I checked BI Studio, and could not find that.

Thanks.
|||

You can only put the MDX expression, not the MDX statement into this column So in your case it would be the right hand side of the assignment.

The real name of the attribute's property is CustomRollupColumn.

|||I put MDX expression in YTD column, and point CustomRollupColumn property to All_Periods.YTD. After processing, YTD just show the text which are the 'MDX expression' I put in and measure.order became '#VALUE'.

What else I am missing?

Thanks.
|||I did Google search for CustomRollupColumn Property, some people said that it's work in SSAS 2000, but not in SSAS 2005. Is it true?

|||CustomRollupColumn property works fine in SSAS 2005. Probably it is the best for you to look at example to understand how it should be set up. In the Adventure Works sample, take a look at Accounts dimension, Accounts attribute - it has custom rollups defined.