#VALUE! error in pivot table when connecting to cube

• karthik.theman

  SSC Journeyman

  Points: 81

  More actions

  December 6, 2009 at 11:51 pm

  #213280

  Hi All

  I have a dimension named "Time Version" which stores the members for time calculations like YTD, MAT, MAA etc. I have written calculations for each of these in my cube. The default member is "Current" to which all my fact records are mapped to.

  There is one member called "Inception to date" in the time version dimension for which the definition is specified below,

  [Time Version].[Time Version].&[7] = VBA!CInt(sum({StrToMember("[Value Date].[Calendar Year Hierarchy].[Calendar Date].&[" + CStr([Fund].[Fund Name].CURRENTMEMBER.FIRSTCHILD.PROPERTIES("Name")) + "]"):TAIL(DESCENDANTS([Value Date].[Calendar Year Hierarchy].CURRENTMEMBER,,leaves),1).item(0)}*[Time Version].[Time Version].&[1]))

  The dimension "Fund" has a lowest level called inception date and "fund name" is one level above inception dates. A fund can have multiple inception dates. "Inception to date" is calculated for the current member of time dimension ([Value Date] dimension) as sum of measures from inception date to current date. The calendar year hierarchy structure is "Year-->Quarter-->Month-->Date".

  When I browse the cube, the calculation looks fine. Also, if I write a mdx query, the calculation looks fine. But, if I connect to the cube using excel pivot table and browse this calculation, I am getting "#VALUE!" error. This happens only for the "Inception to date" calculation, other calculations are looking fine.

  Any help would be very much appreciated.

  Thanks in advance.

  Karthik