Consultor Eletrônico

Status: Unverified

GOAL:

How to display other values (zeros for instance) or an expression instead of the default question marks for unknown values in Report Builder.

FIX:

In the first case we assume that a calculated field returns a numeric decimal value where we would like to display 0 instead of the default '?' string when that value is unknown. In the second case we assume that the calculated field returns a character string value where we would like to display the string 'UNKNOWN' instead of the default '?' string when that value is unknown.

Both cases are solved using this Report Builder IIF() function construct to define the calculated fields:
IIF(logical, value, value)

A) In the first example let's assume that our calculated field, OurCalculatedField, is the result of dividing the Customer.Balance by the Customer.CreditLimit, as follows:

OurCalculatedField = ( Balance / CreditLimit )

To display this calculated field as 0 if the result is unknown, you can define it as follows:

IIF(
( Balance / CreditLimit ) = ?,
0,
( Balance / CreditLimit )
)

B) In the second example let's assume that the calculated field is the result of extracting the month of the ShipDate for a given Customer Order. For example:

OurCalculatedField = SUBSTRING( STRING( ShipDate ), 1, 2 )

To display this calculated field as the string "UNKNOWN", if the result is indeed unknown, you would define it as follows:

IIF(
SUBSTRING( STRING( ShipDate ), 1, 2 ) = ?,
" UNKNOWN ",
SUBSTRING( STRING( ShipDate ), 1, 2 )
)