Issue in MDX

Question

Hello All,

I have implemented the below MDX and it works fine when there is 2 measures while browsing. However, when I try to access this measure alone it gives error. 

Please check the syntax 

[Measures].[T1]/ 
        ([Measures].[T1],Axis(1).Item(0).Item(Axis(1).Item(0).Count - 1).Hierarchy.CurrentMember.Parent)

Kindly check the below screen shot for more reference.

Kindly revert with your valued suggestions.

Regards,

---

Hiren Parikh

Answer 1

The root of your problem is the way Excel generates MDX queries it sends to the cube. Basically, the axes in such queries can differ from where the results are plotted. The MDX behind the second report is likely along the way of "select dimension.members on 0 where measures.t1", thus having empty Axis(1).

---

Expect me to help you solve your problems, not to solve your problems for you.

Answer 2

Hi Hiren,

Thanks for your question.

In this scenario, I would suggest you to create a dynamic set, something like below:

Create Dynamic Set CurrentCuber.[SelectedProducts] As
EXISTING [Product].[Product].[Product].Members;

Then you can create this calculated measure as below:

[Measures].[T1]/ 
         ([Measures].[T1],TAIL([SelectedProducts]).Item(0).Parent)

---

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail]

Answer 3

Willson, this use of the Axis function implies ratio to dynamic parent - whatever hierarchy the user has put on rows - so hard-coding a specific hierarchy is probably not an answer.

As a simple workaround, this calculation can be done in Excel with pivot tables' custom calculation functionality (right click any cell showing measure -> Show Values As -> % of Parent Row Total).

---

Expect me to help you solve your problems, not to solve your problems for you.

Answer 4

Hello Alexei,

Thanks a lot for the valued suggestion.

After doing some further analysis I am able to work on the measure with 2 blocking point.

1 - If I work with single measure on the cube without any dimension. It wont work and gives output  = #VALUE!

2 - When I take any other measure along with this measure it works fine with multiple dimensions as well, even from another measure group. So alone measure is not working.

Please check the below mentioned code for further reference: 

[Measures].[T1] / 
( [Measures].[T1], Axis(1)(0)( Axis(1)(0).Count-1))

I truly appreciate for the valued suggestion you have given. If possible can you please comment further on this.

Regards,

---

Hiren Parikh

Answer 5

Hello Willson,

Thanks a lot for your valued suggestion.

With reference to your suggestion. I am looking for a solution which works with all related dimension (dynamic parent). 

Hence, I have not deployed MDX suggested by you. 

Currently I have deployed the below MDX:

[Measures].[T1] / 
( [Measures].[T1], Axis(1)(0)( Axis(1)(0).Count-1))

However, there are 2 blocking points :

1 - If I work with single measure on the cube without any dimension. It wont work and gives output  = #VALUE!

2 - When I take any other measure along with this measure it works fine with multiple dimensions as well, even from another measure group. So alone measure is not working.

Please revert with your valued suggestion.

Regards,

---

Hiren Parikh

Answer 6

I've already suggested doing the 'ratio to parent' calculation by means of standard functionality in Excel's pivot tables, which I believe is the best way to solve this problem.

Excel is an extremely versatile OLAP client, and this versatility makes it hard to pre-code everything at the cube level. The users, wishing to benefit from on-the-fly designing of cube-based reports, should also be willing to use its functionality to bridge these hard-to-pre-code gaps.

---

Expect me to help you solve your problems, not to solve your problems for you.