CODEWITHSUNDEEP
mdxmembers
Yousuf Sultan
Yousuf Sultan

Reputation: 3257

CURRENTMEMBER function gives an error in MDX

I have my dimension as below:

Market Base Dimension

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?

Upvotes: 3

Views: 2437

Answers (2)

SouravA
SouravA

Reputation: 5243

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

Upvotes: 2

whytheq
whytheq

Reputation: 35557

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.

Upvotes: 1

Related Questions