SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Selective query for profit


Selective query for profit

Author
Message
Mithrandir
Mithrandir
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1210 Visits: 184
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
stevefromOZ
stevefromOZ
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: Moderators
Points: 15846 Visits: 3757
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
Mithrandir
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1210 Visits: 184

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
stevefromOZ
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: Moderators
Points: 15846 Visits: 3757

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
Mithrandir
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1210 Visits: 184

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
bbhuang
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 88
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
Mithrandir
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1210 Visits: 184

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search