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
Product
Qty
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??????
