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])
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])