Are VBA (or any external) functions permitted as part of the lookupcube
string?

Example: using the Foodmart 2000 database,

WITH
MEMBER MEASURES.GOOD AS ' VBA!now() '
MEMBER MEASURES.BAD AS ' LookupCube("SALES", """not too bad""")'
MEMBER MEASURES.UGLY AS ' LookupCube("SALES", "now()")'
SELECT { GOOD, BAD, UGLY } ON 0 FROM HR


returns an error
---------------------------
Formula error - syntax error - token is not valid: " now^(^) "
---------------------------

It gets stranger if the definition of UGLY is replaced by
MEMBER MEASURES.UGLY AS 'cStr( LookupCube("SALES", "now()"))'

Any help is greatly appreciated, I am particularly interested in using
the isError function.

-- addup --

Did you try it with Format. Test samples below respectively.

MEMBER MEASURES.UGLY AS 'Now()'
MEMBER MEASURES.UGLY AS 'Format(Now(), "yyyymmdd")'
MEMBER MEASURES.UGLY AS 'LookupCube("Sales", Format(Now(), "yyyymmdd"))'

Ohjoo Kwon

"addup" <adi.jog (AT) gmail (DOT) com> wrote

Here's an example of a lookupcube function i used at our company.

MEMBER [measures].[Top 25 Gross Adds] as '
sum ( [Top 25 Clients All Bill Types] ,
LookupCube ( "[Gross Adds]", "( [ADDS] , [Division
Name].[Our Division], [prior start date].[" + Trim([Margin Time Name]) + "]"
+ ",[Client
Name].[""
+ IIF(trim(
left([client].currentmember.name, instr([client].currentmember.name,"[")-1))
= "CLIENT X", "CLIENT Y",
trim(
left([client].currentmember.name, instr([client].currentmember.name,"[")-1))
)
+ "])"
)
)'

"Ohjoo Kwon" wrote:

thanks, Ohjoo and Mike.

BUT

In both these examples, the functions are being evaluated in the
context of the "local" (or "current"? - not sure of the terminology)
cube, and not the "lookup" cube.

What i am looking for is more along the lines of
MEMBER MEASURES.UGLY AS ' LookupCube("[Rx Weekly Sales]", "
Format(Now(), ""yyyymmdd"" ) " ) '


also, *what* is Yê˜Í�
-2147467259 ??? is it the string
equivalent of the VBA Error value?

-- a --

mike wrote:

The second argument is an expression that should works in the context of the
source cube(set by the first argument).

Its expression must be a string, so you can use mdx functions in the context
of the target cube(current cube) to create it dynamically.

And... Yê˜Í�
-2147467259 ??? is just meaningless value. Because the
argument of Cstr in your sample already returns error. I don't know the real
returned value of the error because it means formula error, syntax error or
invalid token. But is it important? ;-)

If you're interested in using IsError(), then you can try this type of
expression, IIF(IsError(StrToValue("....."), ... , .... )


Ohjoo Kwon


"addup" <adi.jog (AT) gmail (DOT) com> wrote

thanks, Ohjoo and Mike.

BUT

In both these examples, the functions are being evaluated in the
context of the "local" (or "current"? - not sure of the terminology)
cube, and not the "lookup" cube.

What i am looking for is more along the lines of
MEMBER MEASURES.UGLY AS ' LookupCube("[Rx Weekly Sales]", "
Format(Now(), ""yyyymmdd"" ) " ) '


also, *what* is Yê˜Í�
-2147467259 ??? is it the string
equivalent of the VBA Error value?

-- a --

mike wrote:

yes, the second argument to Lookupcube is a string. This string is
evaluated against the other cube, and the result is returned.

In this example

MEMBER MEASURES.UGLY AS 'LookupCube("Sales", Format(Now(),
"yyyymmdd"))'

the actual *string* being evaluated in the context of the other cube is
"20050310"

this string is CREATED by the function Format(Now(), "yyyymmdd")),
which is actually evauated in the context of the current, and *not* the
other cube.

All the VBA functions used to create the string "20050310" were
executed in the current context.

The VBA functions work great while creating the string being evaluated,
but do not seem to work if they are a part of the string itself.

So the example would be
MEMBER MEASURES.UGLY AS 'LookupCube("Sales", "Format(Now(),
""yyyymmdd"")")'

Which would try to evaluate the *string* 'Format(Now(), ""yyyymmdd"")'

no, Yê˜Í�
-2147467259 is not really important.... I was just
hoping it'd be useful to check for errors


IIF(IsError(StrToValue("....."), ... , .... )
works great in the current cube context, but blows up as part of the
lookupcube string :.(

-- a --
NOTE: the doubled quotes are used to escape the quote character

I can not find the way you want. I'm not sure it is by design.

Any way, you can still use IsError() as following,

IIF(VBA!IsError(LookupCube(....), ErrorExpression, LookupCube(....))

Ohjoo Kwon


"addup" <adi.jog (AT) gmail (DOT) com> wrote

yes, the second argument to Lookupcube is a string. This string is
evaluated against the other cube, and the result is returned.

In this example

MEMBER MEASURES.UGLY AS 'LookupCube("Sales", Format(Now(),
"yyyymmdd"))'

the actual *string* being evaluated in the context of the other cube is
"20050310"

this string is CREATED by the function Format(Now(), "yyyymmdd")),
which is actually evauated in the context of the current, and *not* the
other cube.

All the VBA functions used to create the string "20050310" were
executed in the current context.

The VBA functions work great while creating the string being evaluated,
but do not seem to work if they are a part of the string itself.

So the example would be
MEMBER MEASURES.UGLY AS 'LookupCube("Sales", "Format(Now(),
""yyyymmdd"")")'

Which would try to evaluate the *string* 'Format(Now(), ""yyyymmdd"")'

no, Y?˜Í? -2147467259 is not really important.... I was just
hoping it'd be useful to check for errors


IIF(IsError(StrToValue("....."), ... , .... )
works great in the current cube context, but blows up as part of the
lookupcube string :.(

-- a --
NOTE: the doubled quotes are used to escape the quote character