Search code examples
ssasmdx

Implementing IN /LIKE in MDX

I have a dimension called [Band] and it can have several different values:

[Band].&[A]&[Under $400]
[Band].&[B]&[$400 - $1,000]
[Band].&[C]&[$1,000 - $2,500]
[Band].&[D]&[$2,500 - $3,500]
...

I'm trying to write a query where I can cut by a sublist of these values.

Here's the query that is not working because the .isin function doesn't exist in MDX, but you'll see what I'm trying to do:

SELECT 
  NON EMPTY {[Measure A], [Measure B]} ON COLUMNS, 
  NON EMPTY {([Band].isin(['Under $400', '$400 - $1,000']).ALLMEMBERS)} --fail on .isin(
  DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS
FROM (
  SELECT 
    ({[Foo].&[Bar]}) ON COLUMNS 
  FROM
    [CUBE]
)

Now, here's a query that is working, but it only gives me one [Band] value:

SELECT 
  NON EMPTY {[Measure A], [Measure B]} ON COLUMNS, 
  NON EMPTY {([Band].&[A]&[Under $400])}
  DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS
FROM (
  SELECT 
    ({[Foo].&[Bar]}) ON COLUMNS 
  FROM
    [CUBE]
)

This returns a valid result of:

              Measure A    Measure B
Under $400    1795.67%     58.48%

But I'd like to see results where it returns the aggregated values of multiple [Band] dimension values. How can this be done in MDX?

As you can probably tell I've never used MDX before, but as I search on this issue I see things like taking the intersect, or using ChildrenSet. But it doesn't seem very intuitive.

Can you point me in the right direction?

Solution

  • So following is a list of how IsIn funcationality can be implemented in MDX

    1)you want to filter using in clause and show the members too. In this example I want to see the internet sales for bikes and clothing category from adventurewroks sample db

    select 
[Measures].[Internet Sales Amount]
on columns,
{[Product].[Category].&[1]
,[Product].[Category].&[3]}
on rows 
from 
[Adventure Works]

    Result

    enter image description here

    2)I want to filter by the IN clause but dont want to show the members In this example I want to see yearly the internet sales for bikes and clothing category from adventurewroks sample db. The result is to be broken by years.

    select 
[Measures].[Internet Sales Amount]
on columns,
non empty 
[Date].[Calendar Year].[Calendar Year]
on rows 
from 
[Adventure Works]
where 
{[Product].[Category].&[1]
,[Product].[Category].&[2]}

    Result

    enter image description here

    You achive the same by using subquery 

    select 
[Measures].[Internet Sales Amount]
on columns,
non empty 
[Date].[Calendar Year].[Calendar Year]
on rows 
from 
(select {[Product].[Category].&[1],[Product].[Category].&[2]} on 0 from [Adventure Works])

    Result

    enter image description here

    3)When you want to implement the IN clause based on name In this example I want to see the internet sales for bikes and clothing category from adventurewroks sample db, but in this case I am using the caption

    select 
[Measures].[Internet Sales Amount]
on columns,
filter(
[Product].[Category].[Category],
[Product].[Category].currentmember.name='Bikes' or [Product].[Category].currentmember.name='Clothing'
)
on rows 
from 
[Adventure Works]

    Result :

    enter image description here

    4) When you implement IN clause based on name and your condition is looking for a particular text (Like Clause ) In this example I want to see the internet sales for bikes and clothing category from adventurewroks sample db, but in this case I am searching the the caption name for a piece of string.

    select 
[Measures].[Internet Sales Amount]
on columns,
FILTER([Product].[Category].[Category],
Instr([Product].[Category].currentmember.name, 'Bik') > 0
or 
Instr([Product].[Category].currentmember.name, 'oth') > 0
)
on rows 
from 
[Adventure Works]

    Result

    enter image description here