InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 07:11:27 GMT20http://www.sqlservercentral.com/Forums/Topic780795-169-1.aspxHi All,Can anyone help me out in this.I have below result,C1 C1 C126-Nov-12 3-Dec-12 10-Dec-12Count 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.thanksSwappThu, 24 Jan 2013 01:04:54 GMTswapphttp://www.sqlservercentral.com/Forums/Topic780795-169-1.aspxI 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 pastMon, 05 Nov 2012 03:32:16 GMTWalter Wartenweilerhttp://www.sqlservercentral.com/Forums/Topic780795-169-1.aspxI'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,BrianThu, 03 Sep 2009 12:33:57 GMTBRIAN.ZWILLINGhttp://www.sqlservercentral.com/Forums/Topic780795-169-1.aspxI have a measure circ that should roll up along some dimensions but there are threedimensions 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 newcalculated 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 cannotnest 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 ofthing should be easy in OLAP but I'm having trouble getting started. Can anyone offer a suggestion? Thanks in Advance!Regards,BrianTue, 01 Sep 2009 09:02:46 GMTBRIAN.ZWILLING