Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Selective query for profit Expand / Collapse
Author
Message
Posted Friday, January 14, 2005 4:55 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 10, 2014 9:48 AM
Points: 548, Visits: 167
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

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: 2 days ago @ 8:56 AM
Points: 1,815, Visits: 3,455
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

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 10, 2014 9:48 AM
Points: 548, Visits: 167

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?

Post #156173
Posted Tuesday, January 18, 2005 4:16 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: 2 days ago @ 8:56 AM
Points: 1,815, Visits: 3,455

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

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 10, 2014 9:48 AM
Points: 548, Visits: 167

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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 10, 2014 9:48 AM
Points: 548, Visits: 167

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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse