How to correctly use InStr with ActivePivot?

Question

I am trying to use the INSTR() function with ActivePivot. I am testing by using an Instr call that should always return > 0.

Here is my initial MDX query that works fine:

 SELECT
   {
     {[CODE].[ALL].[AllMember]}
   } ON ROWS
 FROM [Cube]
 WHERE ([Measures].[contributors.COUNT])

Here is my test InStr query:

SELECT
  NON EMPTY
    Generate
    (
      [CODE].[ALL].[AllMember]
      ,(
        Instr
        (
          "Test"
          ,"es"
        ) > 0
      )
    )ON ROWS
FROM [Cube]
WHERE ([Measures].[contributors.COUNT])

Please can you help me create a working example for the Instr MDX query in ActivePivot?

Many thanks

Edit: What I wanted to do

SELECT
NON EMPTY Hierarchize({[CODE].[CODE].Members}) ON ROWS,
NON EMPTY Hierarchize({Filter([RELEVANCE].Members,      InStr([RELEVANCE].CurrentMember.Name, "n/a") > 0)}) ON COLUMNS
FROM [Cube]
WHERE ([Measures].[contributors.COUNT])

FYI: I want to be able to select members from one dimension but filtering on a string in another dimension. I've put my code in above for what I wanted. — doc, Oct 08 '12 at 11:21

score 2 · Accepted Answer · answered Oct 08 '12 at 11:07

I'm not sure about what you try to achieve with your MDX but here is an example which may be helpful:

Before, I consider all places with a positive contributors.COUNT:

SELECT
NON EMPTY Hierarchize({Filter([PlaceDimension].[Continent].Members, [Measures].[contributors.COUNT] > 0)}) ON ROWS
FROM [TwitterCube]
WHERE ([Measures].[contributors.COUNT])

After, I keep only places with an "a" in their name:

SELECT
NON EMPTY Hierarchize({Filter([PlaceDimension].[Continent].Members, InStr([PlaceDimension].CurrentMember.Name, "a") > 0)}) ON ROWS
FROM [TwitterCube]
WHERE ([Measures].[contributors.COUNT])

How to correctly use InStr with ActivePivot?

1 Answers1