Formatting a null numeric database value

[Scottioioio]

I am displaying the results of a database query using a Data Source that returns several columns. One column is a decimal(16,6) type in SQL Server and allows NULL values. Another column is an nvarchar(10) column that specifies the units the decimal value is in (feet, inches, meters, etc.).

What I need to be able to do is the following...

If the decimal value in the database is NOT NULL then show the decimal value formatted as a number (N0) and show the units next to it, but if the decimal value is NULL then show nothing.

How would I go about doing this?

I tried this expression:

Code: Select all

{Switch(MyDataSource.NumericColumn == null, string.Empty, MyDataSource.NumericColumn != null, MyDataSource.NumericColumn.ToString("N0") + " " + MyDataSource.UnitsColumn)}

Which works splendidly for the non-NULL decimal values but for NULL ones it shows "0 feet" (or whatever the units are) even though the underlying database value is NULL and not 0.

[HighAley]

Hello.

Please, try to use next expression:

Code: Select all

{Switch(MyDataSource.NumericColumn == DBNull.Value, string.Empty, MyDataSource.NumericColumn != null, MyDataSource.NumericColumn.ToString("N0") + " " + MyDataSource.UnitsColumn)}

Thank you.

[Scottioioio]

Aleksey, that does not work. When I tried specifically what you had I got an error:

error CS0019: Operator '==' cannot be applied to operands of type 'decimal' and 'System.DBNull'

If I changed it to:

Code: Select all

{Switch(MyDataSource.NumericColumn.Equals(DBNull.Value), string.Empty, MyDataSource.NumericColumn != null, MyDataSource.NumericColumn.ToString("N0") + " " + MyDataSource.UnitsColumn)}

I don't get an error, but when the database returns a NULL value it shows 0 feet rather than showing nothing.

Keep in mind that the column returned from the Data Source is configured as type decimal. I tried changing the type to string but for those it still reports that the string is NOT DBNull.Value, but rather an empty string.

It appears that when the Data Source is populated it uses the default value for the specified data type in the case that the column returned from the database is NULL. But I need to be able to determine in the report designer whether the value from the database happens to be the default value (0) or whether the value in the database is NULL. I can do some ugly hack, like in my query do an ISNULL(Column, -99999) and then in the designer do a Switch on -99999, but I'm hoping there's something more graceful.

Thanks

[Alex K.]

Hello,

Please try to use the following expression if you need to check null value

Code: Select all

DataSourceName["ColumnName"] == DBNull.Value

Thank you.

[Scottioioio]

Aleksey, that did the trick! Thanks

[HighAley]

Hello.

We are always glad to help you.
Let us know if you need any additional help.

Thank you.