SQL Server Performance

Using LookUpCube and eliminating #VALUE!

Discussion in 'Analysis Services/Data Warehousing' started by aeager, Mar 28, 2005.

  1. aeager New Member

    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
  2. Raulie New Member

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

    Raulie
    hp

Share This Page