Replace NULL with 0 in a Measure

Question

Post reply

Replace NULL with 0 in a Measure

markuss21

Old Hand

Points: 340
More actions
March 31, 2006 at 6:38 am

#112609

Hi !
I get the following problem: I load my cube in a pivot table in access. Now I can't programm the pivot talbe to replace NULL with 0.
Is it possible to do this in the measure in the analysis manager ? But I don't mean the format-string.
Thanks in advance for help,
Markus

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ima hassima Grasshopper Points: 11 More actions · Answer 1

Hi

Create a calculeted member = Iif([Measures].[YourMeasure] = null, 0,[Measures].[YourMeasure])

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 2

You might also want to review why you're doing this. If you coerce all empty/null cells to having a value, how can a user determine the difference between a heavily discounted sale (ie discounted to zero) or even where there was a positive transaction and then a negative reversal (netting to zero) and where you have now placed a zero? You could force the user to always look at say a quantity field as well but if you're coercing your sales figures to zero then you may do the same with quantity.

Zero's and empty's hold quite different meanings within a cube depending on what you're looking for. Typically if you leave things as they are, an empty cell indicates that at that intersection of dimensional values, there was no transaction whereas a zero can represent an aggregate of values that net to zero or even a zero source value (that could be valid).

If you do want to coerce them, you may want to try the IsEmpty function, BOL indicates that it's the only reliable test for empty cells. Sample usage could be Iif(IsEmpty([Measures].[myMeasure]), 0, [Measures].[myMeasure])

Steve.

sql junior Newbie Points: 7 More actions · Answer 3

To all:

To create a calculated member will generate an error to my DMX. I will show my MDX statement below. If any one can help me, I am really appreciated.

The code below works just fine, but where can I put "Iif([Measures].[MMeasure] = null, 0,[Measures])? I have try it on the begining of this statement using "with member [Measures].[mymeasure] as 'Iif([Measures].[myMeasure] = null, 0,[Measures]'". However, it's no luck at all.

select

{

[dimention1].[L1member1].[L2member1]

,......

[dimention1].[L1member1].[L2member5] } on columns,

{drilldownmember({ [time].[2006],[time].[2006].[Q2].[April]:[time].[2006].[Q2].[May] }, { [Time]})}

on rows

from mycube

where (

[Measures].[Mymeasure]

)

========================

To Steve:

For anomaly trackiing systems, they will not generate data every week or even months. More records you got, less quality to the organization performances. Usually the quality won't be that bad. Therefore, there will be null values in the cubes by the time people created them.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 4

Did you try using IsEmpty versus trying for equality to null?

I understand where you're coming from re: anomaly tracking however I'd argue that if you're truly tracking the anomalies, then surely the dataset would be very dense as every record would pertain to an anomoly? Why would you enter records in an anomoly tracking system to indicate that there *wasn't* an anomaly? This owuld be like creating sales records in a sales system for each product for days where it wasn't sold

Steve.