Custom Rollup formula and All Member Formula property

For SSAS 2000 ,to specify a custom rollup formula in any level except the All Level, the Custom Rollup Formula property of the level has to be used.

Then to specify it for the All Level the All Member Formula property of the dimension has to be used.

But at SSAS 2005, to specify it for the All Level , I dont know where is the All Member Formula property of the dimension .

Anyone know where is the All Member Formula property of the dimension at SSAS 2005.

Thanks

How about making a cube MDX script assignment to the Root() of the dimension?

SQL Server 2005 Books Online

Working with Custom Member Formulas

Note: To apply an MDX expression to members of a particular level instead of to members of all levels based on a particular attribute, you can define the expression as an MDX script on the level.

|||

May I know why the CustomRollupColumn property or UnaryOperatorColumn property after configure can not be remove if I dont want it to be done this way. I tried to remove from table ID and columnid as show below, but I not see none or any other setting to remove it .I only able to change the setting , but not remove the setting.

UnaryOperatorColumn

- Sources

--TableID

--ColumnID

CustomRollupColumn

- Sources

--TableID

--ColumnID

For this moment,I just able have to delete the dimension and recreate it again because of fail to remove the above property setting.

Anyone know how to remove the setting.Thanks

|||

Anyone know about above question?

Appreciate that anyone able to provide opnion.

Thanks

Custom Rollup Dimension for AVG

I am trying to build a dimension and a cube that contains a measure of Avg Family Size and a dimension that group different types of families into different categories.

The dimension is a parent-child type but the parent should be the AVG of all children instead of SUM.

I tried to use Custom Rollup feature in dimension (there are 2 places, one during the dimension setup in pop-up window, there is check box then to define with MDX or use a column with unitary operator.Another place is in dimension Properties after dimension is setup).I cannot get it to work no matter what I do in dimension with MDX.The cube display #ERR for each cell.

Also, on cube setup, measure setup has a property of Aggregation.It has SUM, MAX, MIN, COUNT, DISTINCT COUNT but just does not have AVG.

So far, the only way is to define a calc measure with such MDX:

IIF(IsLeaf([Test1].CurrentMemeber), [Measure].size, AVG([Test1].CurrentMember.Children, [Measuer].size) )

This is sort of awkward because it renders the original Measure useless.

Any ideas to do this directly in Dimension or in Mesaure? This is for AS 2000.

One of the standard way of doing an average in AS is to set up 2 measures, a SUM and a COUNT and then create a calculated measure for the average and set it up as the SUM / COUNT. If you don't need the original SUM and COUNT measures you can set their visible properties to false. This would create an average for a single measure.

If you had a lot of measures and they all have to be averaged you could try something like one the following which I think should work across all measures (but I have not tested it)

AVG([Test1].CurrentMember,1))

OR

SUM(descendants([Test1].CurrentMember,1))/COUNT(descendants([Test1].CurrentMember,1))

The descendants(<member>,1) will go down 1 level if there is one, otherwise it will return the member itself, which helps avoid the IIF(IsLeaf(...)) test. And not explicitly stating a measure *should* mean that the query will use the current measure context.