Using LookUpCube and eliminating #VALUE!

When I'm using LookUpCube from one cube to another then I often get #VALUE! which in the Pivot table cell shows the error:

"Formula error - cannot find dimension member ("[Account].[Travel Expenses]") in a name-binding function"

Is there any way I can supress this error to show a NULL instead? I've tried CoalesceEmpty but it doesn't stop the error. It would be useful if there was something like an IsError MDX function. So my MDX calculated member is:


CoalesceEmpty(LookUpCube(
"[ACTUALDETAILS]",
"(Measures.[Actual],
[Account].["+[Account].CurrentMember.Name+"],
[Department].["+[Department].CurrentMember.Name+"],
[Date].["+[Date].CurrentMember.Name+"]
)"),0)

This error is annoying as a lot of the time on a Budget Cube you don't get actuals coming through as some Departments may not have posted any actual transactions yet.

Feedback greatly appreciated,

Alan

You can use a combination of IIF and ISERROR to convert to null. MDX excepts a wide range of VBA functions.

Raulie
hp