SQL Server Performance Forum – Threads Archive

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

---

]]>