August 31, 2005 at 5:10 pm
Hi to everybody!!!
I have a little problem and i would like to know if anyone can help me. The situation is like this:
I have a cube with 3 dimensions (geography, time and market), and my facts table. Inside the facts table there are measures like sales or units (which aggregations is normal), and another measure called orders.
zone market sales orders
4 1 100 10
4 2 100 2
4 3 100 3
4 4 100 1
4 5 100 4
The special thing with this measure (orders) is that inside the market dimension the lowest level is submarket with 5 possible values (look example), and when i want to aggregate to the next level called zone the sales must be 500 and the orders 10 (not 20) because the only orders that count are the ones of sumarket 1. So when submarket level appear i should see the 20 orders like in the example, and when the submarket level doesn't appear i should see 10 orders.
Zone sales orders
4 500 10
Does anyone knows how to achive this?.
Thanks a lot for your time!!!
September 1, 2005 at 6:47 am
If your submarket "1" is always the first child of each Zone then you could use a custom rollup formual for the level within the dimension (ie set the rollup to be something like [Market].CurrentMember.FirstChild). If it's not, you coul use custom member rollups, which means creating a column in your source and populating the rollup formula for the members.
HTH,
Steve.
September 1, 2005 at 7:01 am
Thanks Steve for your answer!!! but can i ask you if you can explain me a little more deep this solution.
I think i'm lost and i don't know what do you mean by "custom member rollups".
Thanks for your time!!!
Felipe
September 1, 2005 at 6:12 pm
Hi Felipe,
With the custom rollup at the 'level' level (ie every member within the level), if you know that the submarket is always going to be the first child of each and every zone, then you can apply a more geneeric custom rollup at the level (ie applies to all members within that level of the dimension, so in your case applies to all zones in the zone level).
If it's potentially a different sub market for each zone that needs to be used, then you will probably need to use a customer member rollup, which means per member you specify the rollup you want it to have. So in your case, you would need to have a rollup statement that rolls up only the appropriate submarket within each zone.
Books on Line (BOL) has a reasonable definition and some light samples of how to implement these rollups (type into the index 'custom rollup' (without the quotes) and you should see the index entries for custom rollup members etc.
Cheers,
Steve.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply