Some Help getting started with MDX

  • 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

  • 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

  • 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

  • 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