Selective query for profit

Question

Selective query for profit

Mithrandir

SSCrazy

Points: 2628
More actions
January 14, 2005 at 4:55 pm

#109126
Hello all.
I have a cube with this dimension (eliminate others):
[Document type] (with childs= [Document type].[Devolution] and [Document type].[sale])
And this measures:
[Invoiced]
[Mfg Cost]
And a calculated member:
```
[Profit] = ([Measures].[Invoiced]-[Measures].[Mfg Cost])/[Measures].[Invoiced]
```
Unil here all work fine, but a problem has raised; If [Document type] is [Devolution], [Profit] should be 0.
This I've done with:
```
[Profit] = IIf([Document type].CurrentMember.Name = "Devolution", 0, ([Measures].[Invoiced]-[Measures].[Mfg Cost])/[Measures].[Invoiced])
```
Wich works ok, but only works for direct member look (Devolution branch). The summarys keep original calculation and no change is reflected.
How can I create this calculated member to put Profit=0 to all devolutions, and reflect this in summary totals too?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

have you thought of doing either calculated cells or a custom rollup? Calc cells will let you specify a subset of the cube (eg anything under and including devolution) and set the value to be 0. A custom rollup could be better in that it lets you specify unary operators (+, -, /, *, ~_ where the ~ means don't rollup.

Steve.

Mithrandir SSCrazy Points: 2628 More actions · Answer 2

Thanks steve

Do you have any idea of why Cube Editor -> Insert -> Calculated Cells menu doesn't exist in my machine?

I've been reading some docs to try this, but that I've found no way to get it. Ex. this picture

Menu not exists

I don't have that menu option in my machine

On the other hand: I've tryed Custom Rollups, but this seems to affect ALL measures, I need only to apply rule to one single measure.

Is there something you can advice?

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

It is the Edition of MS AS that you're running, most likely Standard Edition (do a search for 'Features Supported by the Editions' in BOL to see what each edition supports). So the bottom line is that without changing editions, calculated cells is not going to be an option.

After looking at BOL again, you may want to look at Custom members within the dimension. BOL has an example ->

IIf(Employees.CurrentMember.Parent.Name = "SalesPersons", Sales * 0.10,

RollupChildren(Employees.CurrentMember,

Employees.CurrentMember.Properties("UNARY_OPERATOR"))

that looks reasonably similar to what you're trying to achieve, you would of course need to do a check for both the measure name being used and the member being used.

Steve.

Mithrandir SSCrazy Points: 2628 More actions · Answer 4

Sorry for my HUGE delay with this.

I've been looking to achieve this with Custom Members, but as far as I undestand, this is to dinamically change values for Dimension Members, as do the Customm Rollup formula (only this apply to all dimension members).

But this applies to ALL MEASURES, and what I really want is to affect ONE SINGLE MEASURE with this procedure.

Is there any other trick to be used and get a dynamic measure building without disturb all other measures?

Thanks

bbhuang SSC Rookie Points: 36 More actions · Answer 5

Check the current member first. If it's leaf member, apply your formual, otherwise, aggregate. Try the formula below:

IIF(NOT ISLEAF([Document type].CurrentMember), ROLLUPCHIILDREN([Document type].CurrentMember, "+"), IIf([Document type].CurrentMember.Name = "Devolution", 0, ([Measures].[Invoiced]-[Measures].[Mfg Cost])/[Measures].[Invoiced]))

Mithrandir SSCrazy Points: 2628 More actions · Answer 6

Seems that this is finally done. I recived help to resolve it using Calculated Members (my MDX skills still sucks)

Sum(Descendants([Document Type].CurrentMember,,LEAVES),

Iif([Document type].CurrentMember is [Devolution], 0,

([Measures].[Invoiced]-[Measures].[Mfg Cost])

/[Measures].[Invoiced]))

Thank you both for your time