Monday, May 23, 2011 2:19 AMHi. 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.
Monday, May 23, 2011 5:03 AM
If you create the measure like this it should be fine:
CREATE MEMBER CURRENTCUBE.[Measures].[new measure] AS
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 1:17 PM
Thx Boyan. Yes it was already coded like this
MEMBER CURRENTCUBE.[Measures].[A + B]
= 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 2:14 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.
- Marked As Answer by Jerry NeeModerator Tuesday, June 07, 2011 7:50 AM