CURRENTMEMBER function gives an error in MDX

Question

I have my dimension as below:

I want to get one market at a time.

When I use this query, I am getting the list of all the members as I am using .MEMBERS function:

SELECT [MARKET BASE].[Market Base].[Market Base].MEMBERS ON 1,
[Measures].[% OTC Sales] ON 0
FROM [PharmaTrend Monthly Ext];

But when I use the following query to get only the current member then I get an error saying: The CURRENTMEMBER function expects a hierarchy expression for the 1 argument. A member expression was used.

SELECT [MARKET BASE].[Market Base].[Market Base].CURRENTMEMBER ON 1,
[Measures].[% OTC Sales] ON 0
FROM [PharmaTrend Monthly Ext];

UPDATE:

When I use the below query, I get the result with All member:

WITH 
MEMBER [Market] AS
    [MARKET BASE].[Market Base].[Market Base].CURRENTMEMBER

SELECT [Measures].[% OTC Sales] ON 0,
        [Market] ON 1
FROM [PharmaTrend Monthly Ext];

How can I go about resolving this?

What do you think is "the current member"? Unless you're actually iterating over members of the dimension, there isn't any. It's not clear what you want the result of your query to be. — Jeroen Mostert
What I actually want it is, all the dimension members are given in some other language. I want to iterate each of them one by one and give the English translation for each of them in my SSRS report. — Yousuf Sultan
The above query that I am trying is just get an idea about how to iterate each of them dynamically — Yousuf Sultan
In that case, your last query is on the right track, but you still need to include [MARKET BASE].[Market Base].[Market Base].MEMBERS (or CHILDREN, if you want to exclude All). [Market] can then be your translation, and it can use CURRENTMEMBER. — Jeroen Mostert

SouravA SouravA · Accepted Answer · 2016-06-15T09:06:22

CURRENTMEMBER is implicitly picked if you have any member from the hierarchy in scope and laid out on axis. By default it is the ALL member.

WITH MEMBER [Measures].[Market] AS
[MARKET BASE].[Market Base].CURRENTMEMBER.NAME

SELECT [MARKET BASE].[Market Base].[Market Base].MEMBERS ON 1,
{[Measures].[% OTC Sales], [Measures].[Market]} ON 0
FROM [PharmaTrend Monthly Ext];

This will return the name of the current market selected or on axis(in scope).

CURRENTMEMBER function gives an error in MDX

2 Answers