none
#VALUE! shows in calc'd measure but there is no associated error

    Question

  • Hi.  I just added a calc'd measure as ([A]) + ([B]) all associated with the same measure group.  A & B come from the star and have display characteristic of currency but storage type of double.  When I hover over the new measure (display currency) I see the correct sum in the pop up instead of an error message but #VALUE! in the pivot cell.  I cannot see an error.  The value is around 42 million dollars.  A and B show fine.  What am I missing?  There are no nulls in the star's A and B columns.  The cube processes fine.


    • Edited by db042188 Monday, May 23, 2011 2:52 PM chgd money to currency
    Monday, May 23, 2011 2:19 AM

Answers

  • I think I found it but dont have absolute proof.  It is working though.

    Column B was recently introduced as part of this effort too.

    On the DSV design diagram where I "refreshed" and ssas detected earlier that I was adding column B (one of the measures used in the new calc), I right clicked the new column B and chose it's properties.

    The Friendly Name was blank and that looked like the only dsv property attribute that differed from another participant's dsv properties used in a very similar calculation.  I entered it and reprocessed and things look fine.  I have no idea if that made the difference and why ssas didnt display the error when I first pivoted.

    Interestingly, before the fix, when I dragged column B to the pivot area for initial testing, it and Column A displayed fine.  I'm baffled and may look up what / if MS considers the dsv's Friendly Name important and why it didnt simply default on refresh.  The cube design panel had no problem allowing me to add column B to the measure group's list of measures when the problem was unfolding and before I added the calc'd measure.     

    Monday, May 23, 2011 2:14 PM

All replies

  • Hi db,

    If you create the measure like this it should be fine:

    CREATE MEMBER CURRENTCUBE.[Measures].[new measure] AS
      [Measures].[A]+[Measures].[B],
    FORMAT_STRING="Currency";

    If you can't get it to work can you please post the calc member script here? 


    Boyan Penev --- http://www.bp-msbi.com
    Monday, May 23, 2011 5:03 AM
  • Thx Boyan.  Yes it was already coded like this

    CREATE

    MEMBER CURRENTCUBE.[Measures].[A + B]

     

    AS ([Measures].[A]) + ([Measures].[B]),

    FORMAT_STRING

    = "Currency",

    VISIBLE

    = 1 , ASSOCIATED_MEASURE_GROUP = 'Fact X' ;

    before I posted.   The sql data types of A and B in the star are "money".  Their ssas display attribute is currency.

    I probably didnt need the parens but I have them coded that way on another calc'd member in a different mg that works fine.  I'm going to  poke around a little more too.  This is baffling me a little. 

    Monday, May 23, 2011 1:17 PM
  • I think I found it but dont have absolute proof.  It is working though.

    Column B was recently introduced as part of this effort too.

    On the DSV design diagram where I "refreshed" and ssas detected earlier that I was adding column B (one of the measures used in the new calc), I right clicked the new column B and chose it's properties.

    The Friendly Name was blank and that looked like the only dsv property attribute that differed from another participant's dsv properties used in a very similar calculation.  I entered it and reprocessed and things look fine.  I have no idea if that made the difference and why ssas didnt display the error when I first pivoted.

    Interestingly, before the fix, when I dragged column B to the pivot area for initial testing, it and Column A displayed fine.  I'm baffled and may look up what / if MS considers the dsv's Friendly Name important and why it didnt simply default on refresh.  The cube design panel had no problem allowing me to add column B to the measure group's list of measures when the problem was unfolding and before I added the calc'd measure.     

    Monday, May 23, 2011 2:14 PM