Page 1 of 1
Formatting a null numeric database value
Posted: Tue Jan 15, 2013 5:37 pm
by 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.
Re: Formatting a null numeric database value
Posted: Wed Jan 16, 2013 6:37 am
by 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.
Re: Formatting a null numeric database value
Posted: Wed Jan 16, 2013 11:32 pm
by 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
Re: Formatting a null numeric database value
Posted: Thu Jan 17, 2013 6:44 am
by 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.
Re: Formatting a null numeric database value
Posted: Thu Jan 17, 2013 10:51 pm
by Scottioioio
Aleksey, that did the trick! Thanks
Re: Formatting a null numeric database value
Posted: Fri Jan 18, 2013 5:50 am
by HighAley
Hello.
We are always glad to help you.
Let us know if you need any additional help.
Thank you.