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