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

MDX trouble in a calculated member Expand / Collapse
Author
Message
Posted Saturday, January 19, 2013 9:34 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 12:07 PM
Points: 67, Visits: 128
Hi to all.
I use SSAS 2008 R2 and i built a cube.
In this cube i have a little trouble with a calculated member i have to do.
My new calculate member is named AllSalesAmountOfWholeCompany
I need to calculate the total amoun of sales of all company.
I'd like to be able my users, to display in the same moment sales amount fo a single store of the company and in the same row the total amount of all company...
(A buyer asked me that and i found interesting to do !!!)

I built calculate member in MDX and i was able to do that BUT my trouble is to hide the row of stores that has SALES AMOUNT as NULL or 0.....(In the example member BOLOGNA Store...)
How can i do that ???? Inside Calculation member definition or where ????
I have the same problem in Excel and SQL SERVER MANAGEMENT STUDIO too...
I suppose i have to force NULL or 0 my calculated member AllSalesAmountOfWholeCompany, in this way, Excel will display only stores with a Sales Amount > 0.
But i tried i was not able,,,i am not a super MDX man ,,,i know

DIM STORES --- Sales Amount of a Month ---- AllSalesAmountOfWholeCompany

Turin Store ----- 100 --- 120.000
Bologna Store ----- NULL ---- 120.000
Venice Store ----- 300 ---- 120.000

Here is my MDX that it works correctly

SUM

(

{
( [Company].[All Company].[COMET],
[Stores].[Stores].[Stores]
)

}
,[Measures].[Sales Amount])


Thanks in advance...
Post #1409208
Posted Sunday, January 20, 2013 8:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 13, 2014 7:34 AM
Points: 117, Visits: 499
You could use a case statement or iif statement. I'd also use the non_empty_behavior clause as well

IIF(ISEMPTY([Measures].[Sales Amount] )
,SUM ({ [Company].[All Company].[COMET]},[Measures].[Sales Amount])
,NULL)

,NON_EMPTY_BEHAVIOR = {[Measures].[Sales Amount]}

Mack
Post #1409283
Posted Sunday, January 20, 2013 10:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 13, 2014 7:34 AM
Points: 117, Visits: 499
Think I've got the iif statement the wrong way around

IIF(ISEMPTY([Measures].[Sales Amount] )
,NULL
,SUM ({ [Company].[All Company].[COMET]},[Measures].[Sales Amount]) )

,NON_EMPTY_BEHAVIOR = {[Measures].[Sales Amount]}

Apologies
Mack
Post #1409290
Posted Monday, January 21, 2013 10:39 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 12:07 PM
Points: 67, Visits: 128
Ciao
Many thanks to you ..
It works !!!

Thanks again !!!!
Post #1409646
Posted Monday, January 21, 2013 8:22 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:07 PM
Points: 2,844, Visits: 2,427
I think that something like the following might do what you are after...

with member measures.AllSalesAmountOfWholeCompany AS
(SUM
( { ( [Company].[All Company].[COMET],
[Stores].[Stores].[Stores]
)
}
,[Measures].[Sales Amount])
), NON_EMPTY_BEHAVIOR = 'Sales Amount'
select {[Measures].[Sales Amount] ,
[Measures].[AllSalesAmountOfWholeCompany]
} on columns,
{[Stores].[Stores].Members} on rows
from [Your Cube]



Post #1409767
Posted Tuesday, January 22, 2013 7:23 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 12:07 PM
Points: 67, Visits: 128
Many thanks.
I think it is a good soluton too.

Someone advice me that is it works the same...

IIF(ISEMPTY([Measures].[Sales Amount] )
,NULL
,SUM ({ [Company].[All Company].[COMET]},[Measures].[Sales Amount]) )

,NON_EMPTY_BEHAVIOR = {[Measures].[Sales Amount]}


Thanks a lot.
Sorry but even if i use SSAS 2008 R2 from a long time (5 years) , i am not so good in MDX.

i HAVE TO IMPROVE MY knowledge.
Post #1410024
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse