I think allmembers should return members of a given level. For the parent.allmembers, because only one parent, so allmembers cannot give out the result as you expect. If you try parent.children, it will work :-). like follow example. Anyway, just parent self also works.
with member measures.total As 'sum([product].p1.[product name].currentmember.parent.children, measures.[amount sold])' member measures.parentname As '[product].p1.[product name].currentmember.parent.properties("NAME")' member measures.[pct] As 'measures.[amount sold]/measures.[total]', format_string='#.###;' select product.[product name].members on 1, {measures.[amount sold],measures.parentname, measures.[total],measures.[pct]} on 0 from holefoods
Revenue parentname total pct
1 Bagels (dozen) $25,534.37 Snack 3,111,815.51 0.008
2 Bundt Cake $1,284,895.19 Snack 3,111,815.51 0.413
3 Calamari (frozen$2,655,134.41 Seafood 2,655,134.41 1.000
4 Cheerios (box) $513,770.68 Cereal 513,770.68 1.000
5 Donuts (dozen) $230,228.68 Snack 3,111,815.51 0.074
6 Free-range Donut $767,295.26 Snack 3,111,815.51 0.247
7 Fruit Loops (box $513,581.19 Fruit 744,168.60 0.690
8 Lifesavers (roll $149,187.22 Candy 149,187.22 1.000
9 Onion ring $577,490.13 Vegetable 884,450.97 0.653
10 Onion ring $306,960.84 Vegetable 884,450.97 0.347
11 Penne (box) $151,839.08 Pasta 1,940,939.62 0.078
12 Pineapple Rings $230,587.41 Fruit 744,168.60 0.310
13 Pretzels (bag) $460,421.74 Snack 3,111,815.51 0.148
14 Swiss Cheese (sl $518,826.37 Dairy 518,826.37 1.000
15 Tortellini (froz $904,496.54 Pasta 1,940,939.62 0.466
16 Unsalted Pretzel $343,440.27 Snack 3,111,815.51 0.110
17 Ziti (box) $884,604.00 Pasta 1,940,939.62 0.456
---------------------------------------------------------------------------
Elapsed time: .470672s
Hi Evgeny
level_number is one of intrinsic member properties compared with user defined properties like UnitPrice. It is the distance of the member from the root of the hierarchy. The root level is zero.
I tried to find out some Cache defined MDX intrinsic member properties here (Maybe document team can give a full list)
with member measures.Parentlevel As '[Product].P1.[Product Name].CurrentMember.Parent.Properties("LEVEL_NUMBER")' member measures.ParentID As '[Product].P1.[Product Name].CurrentMember.Parent.Properties("ID")' member measures.ParentKey As '[Product].P1.[Product Name].CurrentMember.Parent.Properties("KEY")' member measures.Price As '[Product].P1.[Product Name].CurrentMember.Properties("UnitPrice")' select [product].[product name].members on 1, {measures.parentlevel,measures.parentid, measures.Parentkey, measures.Price} on 0 from holefoods
Parentlevel ParentID ParentKey Price
1 Bagels (dozen) 2 4 Snack 2.95
2 Bundt Cake 2 4 Snack 19.95
3 Calamari (frozen 2 6 Seafood 22.95
4 Cheerios (box) 2 1 Cereal 3.95
5 Donuts (dozen) 2 4 Snack 2.95
6 Free-range Donut 2 4 Snack 12.95
7 Fruit Loops (box 2 8 Fruit 4.95
8 Lifesavers (roll 2 3 Candy 1.15
9 Onion ring 2 7 Vegetable 4.95
10 Onion ring 2 7 Vegetable 5.95
11 Penne (box) 2 5 Pasta 1.95
12 Pineapple Rings 2 8 Fruit 8.95
13 Pretzels (bag) 2 4 Snack 3.95
14 Swiss Cheese (sl 2 2 Dairy 5.95
15 Tortellini (froz 2 5 Pasta 6.95
16 Unsalted Pretzel 2 4 Snack 4.25
17 Ziti (box) 2 5 Pasta 1.95
---------------------------------------------------------------------------
Hello Chris,
Thank you so much for this series.
In this part, the SQL CURSOR, you declared to WidgetCurs, however when you open and fetch, the cursor name changed to Feedbackcurs.