mdxmembers

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?


Solution

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