Results 1 to 2 of 2

Thread: Help with RollUpChildren!!!!!!!

  1. #1
    Join Date
    Sep 2010

    Help with RollUpChildren!!!!!!!

    I'm new to Analysis Services (about a month) and MDX (about a week), but I've been working through the excellent "Step-by-Step SQL Server 2000 Analysis Services" book and the superb articles on here by William Pearson, and BOL and anything I can find on the Internet, but I've ground to a big fat halt. I can't fin much at all about the proper use of the RollUpChildren function and I've messed around for days with it with no success.

    Please please please point me in the right direction!

    I have a fact table populated with sales order line data, this being:

    Customer ID
    Sales Order ID
    Line No
    Required Date
    Despatched Date
    On Time

    This is for a report I want to show on time in full (OTIF) delivery as a percentage. The On Time field has either a 0 or 1 to signify late (0) or on time (1). That's my basic measure.

    In Analysis Services, I have a customer dimension, with the levels:

    Customer Account
    Sales Order Number

    Because I want the user to be able to look at OTIF for the customer, then per sales order and then use drillthrough to see the individual lines.

    I have the basics working OK. OTIF for a sales order is basically the number of on time lines (1) / total number of lines for the order, so 8 out of 10 lines delivered on time scores 0.8 or 80%.

    At the sales order level, my calculated member (called [SO OTIF]) is:

    Iif(Ancestor([Customer].CurrentMember, [Customer].[Sales Order]).Name = [Customer].Currentmember.Name, CDbl([Measures].[On Time]/[Measures].[Total Lines]), Null)

    And that works. It only displays the OTIF value if the current member is a sales order.

    For the customer, I want it to rollup all those sales order OTIF values and divide it by the number of distinct orders and display it only at the customer level, so for example, where a customer has 5 orders with 2 scoring 90%, 2 scoring 100% and 1 scoring 95%, the score for the customer is:

    2 / 5 * 0.90 = 0.36
    2 / 5 * 1.00 = 0.40
    1 / 5 * 0.95 = 0.19

    which makes 0.36 + 0.40 + 0.19 = 0.95 or 95% overall.

    All the rollup has to do is sum the individual scores and divide by the number of distinct sales orders, so:

    (0.90 + 0.90 + 1.00 + 1.00 + 0.95) / 5 = 0.95

    And my calculated member MDX is this:

    Iif(Ancestor([Customer].CurrentMember, [Customer].[Account No]).Name = [Customer].Currentmember.Name, RollupChildren([Measures].[On Time #0], "+")/[Measures].[Distinct SOs], Null)

    But, this returns nothing. If I remove the RollUpChildren bit so it leaves the distinct SOs measure, it returns that value against the customer account member, so I guess that
    the rest of it is working OK.

    Any help with this please??????

  2. #2
    Join Date
    Sep 2010
    Sorry, that second bit of MDX should read:

    Iif(Ancestor([Customer].CurrentMember, [Customer].[Account No]).Name = [Customer].Currentmember.Name, RollupChildren([Measures].[On Time], "+")/[Measures].[Distinct SOs], Null)

    Please ignore the [Measures].[On Time #0] in the original one I posted, it should be [Measures].[On Time]

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts