How to remove all characters after first space for OLAP dimension members

Question

I have a dimension that consists of members with the following format 100 - Sales, 200 - Purchase etc. What I wish to do, is to substring starting from the left and remove everyting after the first space. So the result would be "100" and "200" for the two examples. I have done this in sql before with the following code syntax: LEFT(MyField, CHARINDEX('', MyField) - 1)

But it doesnt seem to work when I apply the same logic in SSAS. LEFT([MyField].[MyField].Members,CHARINDEX('',[MyField].[MyField].Members)-1).

Does anyone know the syntax to accomplish the same thing in SSAS?

Best regards, Rubrix

Answer 1

You should use INSTR function (instead of CHARINDEX), so something like this on Adventure Works database:

with member Measures.[Short Name]
as
left("Aaron A. Allen", instr("Aaron A. Allen", " " ) - 1)

select Measures.[Short Name] on 0,
[Customer].[Customer].members on 1
from [Adventure Works];

or like this:

with member Measures.[Customer Short Name]
as
left([Customer].[Customer].currentmember.MEMBER_CAPTION, instr([Customer].[Customer].currentmember.MEMBER_CAPTION, " " ) - 1)

select {Measures.[Customer Short Name], [Measures].[Customer Count]} on 0,
[Customer].[Customer].members on 1
from [Adventure Works]