Some Help getting started with MDX

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » SQL Server Newbies » Some Help getting started with MDX

Some Help getting started with MDX

Rate Topic

Display Mode

Topic Options

Author

Message

BRIAN.ZWILLING

Posted Tuesday, September 01, 2009 9:02 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, December 14, 2009 8:26 AM
Points: 2, Visits: 45

I have a measure circ that should roll up along some dimensions but there are three
dimensions where I do not want it rolling up to parent levels. When viewing the
parent I want to see the same value that is at the leaf level. I am creating a new
calculated member in my cube to hold the correct value. This MDX code works for one level:

iif(isleaf([Enr Mo].CURRENTMEMBER) ,[Measures].[Circ],[Enr Mo].CURRENTMEMBER.LastChild)

The other dimensions are ATCC which has two levels and [proj horizon] also with two levels.

I am having trouble getting this to work for more than one dimension. I cannot
nest IIF statements and I can't get CASE WHEN statements to work either. I am using
SQL Server Analysis Services Version 8 which I think is SQL Server 2000. I think this sort of
thing should be easy in OLAP but I'm having trouble getting started. Can anyone offer a
suggestion?

Thanks in Advance!

Regards,

Brian

Post #780795

BRIAN.ZWILLING

Posted Thursday, September 03, 2009 12:33 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, December 14, 2009 8:26 AM
Points: 2, Visits: 45

I've spent time getting my MDX to work; here is the code so far:

iif(isleaf([Enr Mo].CURRENTMEMBER) and isleaf([Atcc].CURRENTMEMBER) and isleaf([Proj Horizon in Years].CURRENTMEMBER)
,
[Measures].[Circ]
,
iif(not(isleaf([Enr Mo].CURRENTMEMBER)) and isleaf([Atcc].CURRENTMEMBER) and isleaf([Proj Horizon in Years].CURRENTMEMBER),
([measures].[circ],[Enr Mo].Children.Item(37),[Atcc].CURRENTMEMBER,[Proj Horizon in Years].CURRENTMEMBER)
,
iif(not(isleaf([Enr Mo].CURRENTMEMBER)) and not(isleaf([Atcc].CURRENTMEMBER)) and isleaf([Proj Horizon in Years].CURRENTMEMBER),
([measures].[circ],[Enr Mo].Children.Item(37),[Atcc].CURRENTMEMBER.LastChild,[Proj Horizon in Years].CURRENTMEMBER)
,
iif(not(isleaf([Enr Mo].CURRENTMEMBER)) and not(isleaf([Atcc].CURRENTMEMBER)) and not(isleaf([Proj Horizon in Years].CURRENTMEMBER)),
([measures].[circ],[Enr Mo].Children.Item(37),[Atcc].CURRENTMEMBER.LastChild,[Proj Horizon in Years].CURRENTMEMBER.LastChild)
,
iif(isleaf([Enr Mo].CURRENTMEMBER) and not(isleaf([Atcc].CURRENTMEMBER)) and not(isleaf([Proj Horizon in Years].CURRENTMEMBER)),
([measures].[circ],[Enr Mo].CURRENTMEMBER,[Atcc].CURRENTMEMBER.FirstChild,[Proj Horizon in Years].CURRENTMEMBER.LastChild)
,
iif(isleaf([Enr Mo].CURRENTMEMBER) and isleaf([Atcc].CURRENTMEMBER) and not(isleaf([Proj Horizon in Years].CURRENTMEMBER)),
([measures].[circ],[Enr Mo].CURRENTMEMBER,[Atcc].CURRENTMEMBER,[Proj Horizon in Years].CURRENTMEMBER.LastChild)
,
iif(not(isleaf([Enr Mo].CURRENTMEMBER)) and isleaf([Atcc].CURRENTMEMBER) and not(isleaf([Proj Horizon in Years].CURRENTMEMBER)),
([measures].[circ],[Enr Mo].Children.Item(37),[Atcc].CURRENTMEMBER,[Proj Horizon in Years].CURRENTMEMBER.LastChild)
,
iif(isleaf([Enr Mo].CURRENTMEMBER) and not(isleaf([Atcc].CURRENTMEMBER)) and isleaf([Proj Horizon in Years].CURRENTMEMBER),
([measures].[circ],[Enr Mo].CURRENTMEMBER,[Atcc].CURRENTMEMBER.LastChild,[Proj Horizon in Years].CURRENTMEMBER)
,

0

))))))))

Now my follow up question is: Does anyone have an example of finding the position number of a member in a list. In other words I want to find the position of the first member that is not empty and substitute it in the Item(37) above, instead of hardcoding a value.

Sorry if my questions are so basic!

Thanks,

Brian

Post #782477

Walter Wartenweiler

Posted Monday, November 05, 2012 3:32 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, November 05, 2012 3:31 AM
Points: 2, Visits: 4

I don't know the exact answer to your question but maybe have a look here: http://www.iccube.com/support/documentation/mdx_tutorial/gentle_introduction.html - had quite some success when needed in the past

Post #1380958

swapp

Posted Thursday, January 24, 2013 1:04 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 6:40 AM
Points: 8, Visits: 17

Hi All,

Can anyone help me out in this.
I have below result,

C1 C1 C1
26-Nov-12 3-Dec-12 10-Dec-12
Count Of Product 196 162 210
% L Count 45.18% 40.49% 49.49%
% C Count 54.82% 59.51% 79.51%
% New Items 0.00% 0.00% 0.00%

Here in % New items i want to write MDX to show the product present for 10-Dec-12 but not in 3-Dec-12,03-Dec-12 but not in 26-Nov-12 according to this product present for 26-Nov-12 are 100% bcoz no previous date is selected.

thanks
Swapp

Post #1410967

« Prev Topic | Next Topic »

Permissions