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, Jun 15 '16 at 06:40
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, Jun 15 '16 at 06:46
The above query that I am trying is just get an idea about how to iterate each of them dynamically — Yousuf Sultan, Jun 15 '16 at 06:47
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, Jun 15 '16 at 07:00
nopes... the english name isn't in the cube... it needs to be hard-coded — Yousuf Sultan, Jun 15 '16 at 08:59
@YousufSultan ok - in that case all you're going to need is either of the following on rows `[MARKET BASE].[Market Base].[Market Base].MEMBERS` or `[MARKET BASE].[Market Base].[All].CHILDREN` (these two statements are equivalent) — whytheq, Jun 16 '16 at 09:32

SouravA · Accepted Answer · 2016-06-15T09:11:22.717

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).

score 1 · Answer 2 · answered Jun 15 '16 at 08:41

Just to be precise chaps - currentmember does not iterate over anything in mdx. None of the mdx I see in the original post is using any sort of iteration.

This function is as close as you get to a loop in mdx:

GENERATE

Also Filter can be thought of as an iterator.

CURRENTMEMBER is generally used in a WITH clause for claculations. What it does is pick up the current member for each member that is in context - this basically means what you see in the result table.

CURRENTMEMBER function gives an error in MDX

2 Answers2