Does anyone know if it's possible to convert a numeric to a string?
Or have an iif statement that returns either a numeric or a string?
Thanks for any help.

David

Hi David,

You can use all the standard VBA functions to convert types in calculated members, eg CINT, CSTR etc. As for the IIF statement, strictly speaking it can only return either two strings or two numeric values, but there is a way round this by getting the IIF statement to return the values of two calculated measures. Here's an example that shows everything in action:

WITH
MEMBER MEASURES.TRUERESULT AS '"Is Jones"'
MEMBER MEASURES.FALSERESULT AS '0'
MEMBER MEASURES.COMPARE AS
'IIF(VBA!CSTR(STORE.CURRENTMEMBER.PROPERTIES("Stor e Manager"))="Jones", MEASURES.TRUERESULT, MEASURES.FALSERESULT)'
SELECT {MEASURES.COMPARE} ON 0,
STORE.[STORE NAME].MEMBERS ON 1
FROM SALES

HTH,

Chris

"David Hwang" wrote:

Thanks a lot, Chris. That was exactly what I needed.

"Chris Webb" <OnlyForPostingToNewsgroups (AT) hotmail (DOT) com> wrote

How do you use the "with..." statement in the Calculated Member
Builder in Analsys Manager? I don't really want to specify any
dimensions in that value expression.

"Chris Webb" <OnlyForPostingToNewsgroups (AT) hotmail (DOT) com> wrote

How do you use the "with..." statement in the Calculated Member
Builder in Analsys Manager? I don't really want to specify any
dimensions in that value expression.

"Chris Webb" <OnlyForPostingToNewsgroups (AT) hotmail (DOT) com> wrote

Sorry for the late reply... in the calculated member all you need to do is copy and paste the calculated member definition from within the single quotes in the WITH clause, ie
"Is Jones"
and
0
for the two calculated members in the query I gave below.

Chris

"David Hwang" wrote:

Actually, I meant is it still possible to use a WITH clause to
dynamically generate new calculated members while using the Calculated
Member Builder. I don't want to actually save calculated members just
for a string or to use NULL. Basically, I would like to paste that
entire statement below into the Calculated Member Builder and store
that as my new calculated member, but it's giving me an error because
it doesn't seem to like the WITH statement.

"Chris Webb" <OnlyForPostingToNewsgroups (AT) hotmail (DOT) com> wrote

Actually, I meant is it still possible to use a WITH clause to
dynamically generate new calculated members while using the Calculated
Member Builder. I don't want to actually save calculated members just
for a string or to use NULL. Basically, I would like to paste that
entire statement below into the Calculated Member Builder and store
that as my new calculated member, but it's giving me an error because
it doesn't seem to like the WITH statement.

"Chris Webb" <OnlyForPostingToNewsgroups (AT) hotmail (DOT) com> wrote

Hi David,

I'm not sure what exactly you want to do, but certainly a WITH clause can only be used with an MDX SELECT statement. The query below would translate to three different calculated members, each created separately, on the server.

Regards,

Chris

"David Hwang" wrote:

Actually, you answered my question. I didn't want to have to save
three different calculated members on the server. I was hoping to
save only one calculated member on the server that used a WITH clause
to create two temporary calculated members within it's value
expression. But I guess it's not possible.

"Chris Webb" <OnlyForPostingToNewsgroups (AT) hotmail (DOT) com> wrote