CODEWITHSUNDEEP
ssasmdx
John Mc
John Mc

Reputation: 2933

MDX query with multiple filters using OR

I have the following MDX query which I have been asked to extend:

    SELECT {} ON COLUMNS,
CROSSJOIN(
    [Waterbody].[Waterbody Code].[Waterbody Code],
    [Waterbody].[Waterbody Name].[Waterbody Name],
    [Waterbody].[Waterbody Type].[Waterbody Type],
    filter([Waterbody].[Waterbody Full Name].allmembers,instr([Waterbody].[Waterbody Full Name].currentmember.member_caption,'Test Waterbody Name')>0)
    )  ON ROWS FROM [Waterbody Data]

There is another column called "Waterbody Name" and I need to return the rows where the the given value is in either "Waterbody Full Name" or "Waterbody Name".

I'm handy enough at SQL but this MDX stuff is completely new to me. I spent a large part of yesterday trying different things but got nowhere. I'd really appreciate some help.

Thanks John

Upvotes: 0

Views: 1292

Answers (1)

FrankPl
FrankPl

Reputation: 13315

Just put the members you want to return into a set (noted by curly braces), and that's it. No need for Filter:

SELECT {} ON COLUMNS,
CROSSJOIN(
    [Waterbody].[Waterbody Code].[Waterbody Code],
    [Waterbody].[Waterbody Name].[Waterbody Name],
    [Waterbody].[Waterbody Type].[Waterbody Type],
    { 
      [Waterbody].[Waterbody Full Name].[Waterbody Full Name],
      [Waterbody].[Waterbody Full Name].[Waterbody Name]
    }
    )  ON ROWS
FROM [Waterbody Data]

If you are using the MDX window in SQL Server Management Studio to edit your query, you can drag the members from the list in the left to your script. In this case, depending on cube configuration details, the generated syntax may be slightly different - like containing an ampersand, but both should work. However, using this feature of the the MDX window avoids typos.

Upvotes: 1

Related Questions