September 1, 2009 at 9:02 am
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
September 3, 2009 at 12:33 pm
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
November 5, 2012 at 3:32 am
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
January 24, 2013 at 1:04 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply