Hello
Can anyone please help me out with MDX functions? I need to sort a calculated set with percentage in a bar graph. The % is specific to a member. How do you write an MDX calculation to sort the following set.
Expression:
set(member([Ent %], 'Ent 1', 'Ent'), member([ML %], 'ML1', 'ML'), member([XYZ %], 'XYZ 1', 'XYZ'), member([ABC %], 'ABC 1', 'ABC'), member([Other %], 'Other1', 'Other'))
This is a simple bar graph where y-axis has products and the bar has the % & I need to sort the bars in desc order. Since the % calculation is specific to the product, I can't pull one common value in the sort order.
Any help with this is greatly appreciated!!
Thanks much!
S
Quote from: Sue on 18 May 2012 04:35:24 PM
Hello
Can anyone please help me out with MDX functions? I need to sort a calculated set with percentage in a bar graph. The % is specific to a member. How do you write an MDX calculation to sort the following set.
Expression:
set(member([Ent %], 'Ent 1', 'Ent'), member([ML %], 'ML1', 'ML'), member([XYZ %], 'XYZ 1', 'XYZ'), member([ABC %], 'ABC 1', 'ABC'), member([Other %], 'Other1', 'Other'))
This is a simple bar graph where y-axis has products and the bar has the % & I need to sort the bars in desc order. Since the % calculation is specific to the product, I can't pull one common value in the sort order.
Any help with this is greatly appreciated!!
Thanks much!
S
Off the top of my head (and not having access to C10 on a Sunday morning) I would suggest you look at the order() function and add it into your expression. I can't verify that this works or even if it is correct right now, though.
Regards,
MF.
Sent from my iPad using Tapatalk HD
MF is correct, you do need the order function.
But what you're saying is not entirely accurate. Your common value would be the all member from the product dimension. The nice thing about OLAP is almost anything can be treated as a value if used in the correct context. In this case the order function would attempt to do a tuple of ([Ent 1],[All Member]), thus returning the value.
Well, that's it in theory. I don't have access to a C10 installation until tonight.
MFGF & PaulM - First, Thanks so much for your response!! Truly appreciate it! :)
Well, I tried the order() function but in C10, the pages goes blank which means that it doesn't like the calculation. So I had to create a set for all these sets and I created a dataitem with percentage (). On the chart, I had to manually sort this dataitem. It worked.
MDX programming is fun & challenging!
Thanks once again!!
Sue