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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy