Recently, I was asked to write a calculation that calculates the ratio to parent on rows that doesn’t depend on cube structure and that also works in OWC. The first part I managed to dig somewhere from the past, the second proved to be much more demanding task. Having bypassed several obstacles and spent significant portion of time on this subject, I decided to pass the knowledge in form of a blog article. Here’s the calculation:
CREATE MEMBER CURRENTCUBE. [Measures].[Universal share percent for OWC in BIDS %] AS iif( IsError( Extract( Axis(0), Measures ).Item(0) ), iif( IsError( Extract( Axis(1), Measures ).Item(0) ), iif( Axis(1).Item(0).Item( Axis(1).Item(0).Count - 1 ) .Hierarchy.CurrentMember.Level.Ordinal = 0, 1, Extract( Axis(2), Measures ).Item(0) / ( Axis(1).Item(0).Item( Axis(1).Item(0).Count - 1 ).Hierarchy.CurrentMember.Parent, Extract( Axis(2), Measures ).Item(0) ) ), iif( Axis(0).Item(0).Item( Axis(0).Item(0).Count - 1 ) .Hierarchy.CurrentMember.Level.Ordinal = 0, 1, Extract( Axis(1), Measures ).Item(0) / ( Axis(0).Item(0).Item( Axis(0).Item(0).Count - 1 ).Hierarchy.CurrentMember.Parent, Extract( Axis(1), Measures ).Item(0) ) ) ), iif( IsError( Axis(1).Item(0) ), 1, iif( Axis(1).Item(0).Item( Axis(1).Item(0).Count - 1 ) .Hierarchy.CurrentMember.Level.Ordinal = 0, 1, Extract( Axis(0), Measures ).Item(0) / ( Axis(1).Item(0).Item( Axis(1).Item(0).Count - 1 ).Hierarchy.CurrentMember.Parent, Extract( Axis(0), Measures ).Item(0) ) ) ) ),FORMAT_STRING = "Percent", VISIBLE = 1;
What’s good about it is that it can be used in any project.
I also enhanced it a bit, to support other clients/controls as well (SSMS included).
Finally, it can be used as a template for other calculations that use Axis() function and are intended to work in Cube Browser.
ATTENTION: The improved variant of the same calculation can be found in a more recent blog article here.
ITs a good one and seems workabel, I will deploy it and will keep you posted. Thanks Again
Hi, this is Nizar Again, I have deployed the solution you gave and it was as success. There is one issue I have, you see I am using Dundas for the cube browser, the problem is, with the above solution you have to put some measure before you put the percentage measure which is ok but some time it does not give right values, I believe it is something to do with dundas as it is not capable to identify the axis (1).current measure…….
Can you do me a favor and send me some instructions(or code) to how to deploy the above solution(Calculating universal percentage in OWC) on ‘Known Measure’ , lets say I have a measure called ‘Consumer Count’ and I want to see the Percentage of the consumer count without having to put ‘Consumer Count’ first on the analysis sheet and then the percentage measure, is there is a way to hard code the ‘consumer count’ instead of Extract( Axis(2), Measures ).Item(0)…
I will sincerely appreciate your help, Thanks in advance
Nizar
Hi Nizar,
have you seen the improved version of this solution? The link is near the end of the article. It also covers the scenario where you can have hard-coded measure and a calculated member that tests where the measures are, on which axis.
Anyway, regarding this article, here’s how you can put a known measure in it.
Replace these parts with your measure:
Extract( Axis( x ), Measures ).Item(0)
where x = 2 in upper part/block, 1 in middle part and 0 in lower part. In each part it is used twice: as numerator and as second member in tuple in denominator. The first two lines should be left intact, no replacement there, they are used to detect measures on axes.
In short, the ratio syntax is written 3 times, each time for different axis. It can be reduced to only one if you know or can guarantee that measures will always be i.e. on columns. That way you can simplify it by taking only one part of it, the one that fits. If the measures are never on axis 2, you can reduce the calculation to only two parts, lower two, and remove one of the two lines above.
This, however, might not work in case there’s something special regarding how Dundas generates MDX behind. In that case, I can download a demo (if available) and give you a precise code for that product/control.
If you’re experienced with Profiler, you can catch MDX sent to server by Dundas by yourself. Make few variations, see how it behaves and then you’ll know which scenarios occur and when. So you’ll know how to construct the calculation. That’s how I would do it.
Waiting for your comment …
This solution doesn’t work with Excel as client tool when using filter – because the filter isn’t applied to the grand total. Do you have any ideas to get that working?
Have you seen the improved version of the solution? Maybe that helps.
Or do you mean because in Excel filter context is placed in subselect? If that’s the case, then my answer is – unfortunatelly no.
This calculation works well for the first level, but if I put the Share for 3 different columns for example:
BRAND PRODUCT GROUP —- Share (Percent of column)
What would it be? Because lost by expanding the top-level Share.
Have you checked the improved version of this? There’s a link at the bottom of the post.
Fantastic bit of code. Will save me hours.
Many thanks
i use SpagoBI and mysql data base but the functions (item(), Hierarchy, Axis()) are not execute correctly on my server
someone can help me ?
These functions are probably not implemented in SpagoBI.