Conditional Formatting with a Null DateTime field

[Pawz]

What's the best way to add conditional formatting for a datetime field that may be null?

I wanted to highlight all records that had a date in the past, for records that actually *have* a date. How do I do this?

[Milen | Product Manager @DX]

Hello Pawz,

It is a little difficult to workout the appropriate expression, since the comparison operators do not allow comparison with Null or DBNull value. This area needs improvement, and will be improved in the future versions.

However, here is the rule that fits to your requirement:

- For Expression of the rule pass =IsNull(Fields.DateField, "9999-12-31")
This way if the field has Null value, it will be replaced with the max DateTime value.

- For Operator choose   <   (LessThan)

- For Value pass =Now()
to compare the value of the Expression with the current date.

Hope this information helps.

Sincerely yours,
Milen
the Telerik team

Instantly find answers to your questions at the new Telerik Support Center

[Jayesh Goyani]

Hello Milen,

i got this Error "An error has occurred while processing TextBox 'textBox3': Cannot perform '='  " when i use following expression for handling DBnull / Null value in Report.

1)
Expression ->  = IsNull(Fields.Saturday, "True")   
Operator ->      =
Value ->          = True

2)
Expression ->  =IIF(Fields.Saturday= "Test",True,IsNull(Fields.Saturday,True)) 
Operator ->      =
Value ->          = True

i want to set background color in TextBox when Fields.Saturday.value is DBNull or "Test".

how can i do this ?

[Peter]

Hello jayesh goyani,

The formatting rule you've wrote is trying to compare values from different types. To avoid this our suggestion is to omit the equal operator from the value's expression. By omitting the equal sign the value will be interpreted as a string literal and the comparison will be valid as shown in the following formatting rules:

Expression	Operator	Value
=IsNull(Fields.Saturday, "True")	=	True
=IIF(Fields.Manufacturer = "Test",True,IsNull(Fields.Manufacturer,True))	=	True

All the best,
Peter
the Telerik team

[Jayesh Goyani]

hi Peter ,

thanks for your help

by this expression i m able to set Background image with conditional formatting but it not display record.

Expression : = Fields.Saturday
Conditional formatting : =IIF(Fields.Saturday = "Test",True,IsNull(Fields.Saturday,True))        =      True

See below (attach file) image.
1. error.jpg
2. data-sample.png (in below post)

[Jayesh Goyani]

sample file with record.

[Peter]

Hi jayesh goyani,

The suggested expressions work as expected locally. This leads us to think that the issue could be in the returned data itself. For further assistance we will appreciate if you open a support ticket and send us a sample runnable report with some dummy data that exhibits the erroneous behavior this way we can debug it locally.  

All the best,
Peter
the Telerik team