Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Selective query for profit Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, January 14, 2005 4:55 PM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Wednesday, February 10, 2016 9:16 AM Points: 550, 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
Post #155737
 Posted Saturday, January 15, 2005 6:33 AM
 SSCommitted Group: Moderators Last Login: Saturday, November 5, 2016 7:09 PM Points: 1,896, Visits: 3,753
 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.
Post #155756
 Posted Tuesday, January 18, 2005 10:16 AM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Wednesday, February 10, 2016 9:16 AM Points: 550, Visits: 184
 Thanks steveDo 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 pictureI 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?
Post #156173
 Posted Tuesday, January 18, 2005 4:16 PM
 SSCommitted Group: Moderators Last Login: Saturday, November 5, 2016 7:09 PM Points: 1,896, Visits: 3,753
 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.
Post #156250
 Posted Monday, January 31, 2005 5:03 PM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Wednesday, February 10, 2016 9:16 AM Points: 550, 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
Post #158663
 Posted Monday, January 31, 2005 9:06 PM
 SSC Rookie Group: General Forum Members Last Login: Tuesday, October 29, 2013 7:55 PM 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]))
Post #158677
 Posted Thursday, February 3, 2005 10:00 AM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Wednesday, February 10, 2016 9:16 AM Points: 550, 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
Post #159418

 Permissions

 Copyright © 2002-2016 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.