CODEWITHSUNDEEP
sql-serverssasmdxolap-cube
Maxime1984
Maxime1984

Reputation: 21

MDX where clause vs From clause

I do not understand why I do not get the same result between these 2 codes below :

I have a date dimension with a year attribute composed of members: 2016, 2017, 2018 and 2019

Code 1 :

with
Member [Measures].[MembersInWhereClause] AS
SETTOSTR(EXISTING([Dim Date].[Calendar].[Year].members))

select [Measures].[MembersInWhereClause] on 0
from [ODGG2Vente]
WHERE ([Dim Date].[Calendar].[Year].&[2018]:[Dim Date].[Calendar].[Year].&[2019])

I get as a result this set : "2018 and 2019". It works well !

Code 2

with
Member [Measures].[MembersInFromClause] AS
SETTOSTR(EXISTING([Dim Date].[Calendar].[Year].members))

select [Measures].[MembersInFromClause] on 0
FROM 
(
    SELECT (
    {[Dim Date].[Calendar].[Year].&[2018]
    ,[Dim Date].[Calendar].[Year].&[2019]}
    ) ON 0  
    FROM [ODGG2Vente]
)

I get as a result this set : "2016,2017,2018,2019" ??? I was hoping to get only the members specified in the from clause...

Thanks for your help

Upvotes: 1

Views: 336

Answers (2)

Maxime1984
Maxime1984

Reputation: 21

Finally, i found a solution using a named set. It works ! It can read the context of my subcube

dynamic set [FirstMemberInASet] as
EXISTS([Dim Date].[Calendar].[Month],EXISTING([Dim Date].[Calendar]. 
[Date].members)).item(0)
Member [Measures].[FirstMember] as
SETTOSTR([FirstMemberSet])

I obtain this result with a subcube :

{[Dim Date].[Calendar].[Month].&[1]&[2018]}

But the problem i need a member because i have to used parallelperiod.

Is it possible to convert a set containing a single value {[Dim Date].[Calendar].[Month].&[2018]}

to a member like this :

[Dim Date].[Calendar].[Month].&[1]&[2018] ?

Thx for ur help

Upvotes: 1

MoazRub
MoazRub

Reputation: 2911

That is becuase in your second query your main query has overwitten the context of your subquery. In MDX when you provide a subquery, it defines a subcube for ypur main query to operate. So in your second query the sub cube is made on 2018 and 2019. But the sub cube doesnt restrict your query to access the entire dimension list. In your second query when the execution for main query begins, the query space\or dimensional space says default members for all dimensions. Hence in your measure the "existing" keyword will not work. However in your first query the query space \or dimensional space said 2018 and 2019 in the year space. To better understand try the queries below.

This query will return the same string as the second query. with Member [Measures].[MembersInFromClause] AS SETTOSTR(EXISTING([Dim Date].[Calendar].[Year].members))

select [Measures].[MembersInFromClause],[Measures].[SomeMeasureInCube] on 0
FROM 
(
    SELECT (
    {[Dim Date].[Calendar].[Year].&[2018]
    ,[Dim Date].[Calendar].[Year].&[2019]}
    ) ON 0  
    FROM [ODGG2Vente]
)

This query will however return filtered string.

with
Member [Measures].[MembersInWhereClause] AS
SETTOSTR(EXISTING([Date].[Calendar].[Calendar Year].members))
Member [Measures].[MembersInWhereClause2] AS
SETTOSTR(([Date].[Calendar].[Calendar Year].members))
select {[Measures].[SomeMeasureInCube],
Measures.[MembersInWhereClause],Measures.[MembersInWhereClause2]}  on 0
,[Dim Date].[Calendar].[Year].members on 1 
FROM 
(
  SELECT (
  {[Dim Date].[Calendar].[Year].&[2018]
  ,[Dim Date].[Calendar].[Year].&[2019]}
  ) ON 0  
  FROM [ODGG2Vente]
)

My second query, date on row axis forces the query to execute in the cube space. But notice how my third dimension still is able to bypass the context due to missing existing

Upvotes: 1

Related Questions