CODEWITHSUNDEEP
reporting-servicesssasmdx
Erik Sierra
Erik Sierra

Reputation: 21

Combining Members from Different Levels in the Same Hierarchy in MDX Query

I'm trying to write an MDX query in SSAS to combine two members from different levels of the same dimension/hierarchy in my filter, but I'm running into issues as they return no data. When I write separate queries for each member, they work as expected.

Dimensions/Members:

Site: [Process].[Equipment Hierarchy].[Site].&[PGT]

Plant: [Process].[Equipment Hierarchy].[Plant].&[PGT]&[3]

Initial Query Attempt:

NON EMPTY { 
        CROSSJOIN(
            {[Process].[Equipment Hierarchy].[Site].&[PGT].&[Plant 3]},

I understand that combining members from different levels in the same hierarchy might be the issue. How should I write the query to correctly combine the Site and Plant members?

Thanks in advance!

Upvotes: 0

Views: 40

Answers (1)

nsousa
nsousa

Reputation: 4544

As they’re in the same hierarchy I think a set is just enough:

SELECT 
{[Process].[Equipment Hierarchy].[Site].&[PGT].&[Plant 3], [Process].[Equipment Hierarchy].[Plant].&[PGT]&[3]}
(…)

You can have sets with members on different levels; it’s just if they’re on different hierarchies that it may pose a problem.

Crossjoim is when you want to do the cartesian product of sets made of members on different hierarchies or dimensions.

My MDX knowledge is based on Pentaho Mondrian only, not SSAS, so apologies if my answer is obviously wrong.

Upvotes: 0

Related Questions