PRB: Division by Zero Displays Results in Strange Format (219176)

SYMPTOMS

If a 'division by zero' operation is performed in a MDX query, the results appear in a strange format such as "1.#J". Some examples follow:

with member measures.x as '1/0'
select { measures.x } on columns from [bobs video store] returns 1.#INF
with member measures.x as '1/0', FORMAT_STRING = ''
select { measures.x } on columns from [bobs video store] returns 1.#INF
with member measures.x as '1/0', FORMAT_STRING = 'Standard'
select { measures.x } on columns from [bobs video store] returns 1.#J
with member measures.x as '1/0', FORMAT_STRING = 'Fixed'
select { measures.x } on columns from [bobs video store] returns 1.#J
with member measures.x as '1/0', FORMAT_STRING = 'Percent'
select { measures.x } on columns from [bobs video store] returns 1#I.NF%
with member measures.x as '1/0', FORMAT_STRING = 'Scientific'
select { measures.x } on columns from [bobs video store] returns 1.#JE+00

The following table shows query return values from using different FORMAT_STRING's in an expression involving 'division by zero' (tested on Intel platforms):

Format Strings	Query Return Values
FORMAT_STRING="	1.#INF
FORMAT_STRING='Standard'	1.#J
FORMAT_STRING='Fixed'	1.#J
FORMAT_STRING='Percent'	1#I.NF%
FORMAT_STRING='Scientific'	1.JE+00

WORKAROUND

In general, avoid division by zero whenever possible. Inside a calculated member, use explicit checking before dividing. For example, instead of the expression 'Sales / Units', use the function 'iif' such as 'iif ( Units = 0, 0, Sales / Units )'.

NOTE: The explicit checking may not always work. It will work for integer values, but may fail for real values.

PRB: Division by Zero Displays Results in Strange Format (219176)

SYMPTOMS

WORKAROUND

MORE INFORMATION