I have a Report Opportunity with Contact Role type. When the report does not filter on Expired Date (Opportunity's field) and Expired Date Renew Opp (Contact's field), the report data is right.

Right data without filter

But if I add 2 filters above, the data is wrong. It does not show the Expired Date Renew Opp value and Full Name. And with this filter, I don't want to the report show the "Voola test" record (Voola test and Voola test 1 are the opportunity for 1 contact)

Wrong data with filter

Filter Opportunity with Contact role

I don't understand why it wrong. Please help me clarify this issue. Thanks in advance.

1 Answer 1


This is expected behavior when the value of the filtered field is null. From Notes about Filtering on Types of Fields and Values:

Filtering on Blank or Null Values

When you use the “less than,” "greater than,” “less or equal,” or “greater or equal” operators on fields that have numeric values, records with blank or “null” values are returned as if their value is zero (0). For example, if you create a workflow rule or a lead assignment rule for accounts with the criteria Annual Revenue less than 100000, account records match if their Annual Revenue is blank.

To limit results to records that are blank or contain “null” values for a particular field, choose the field and the “equals” or “not equal to” operators, leaving the third field blank. For example, Amount equals returns records with blank amount fields. You can search for blank values or other specified values at the same time. For example, Amount equals 1,,2 returns records where the Amount is blank or contains the value “1” or “2”.

The null field is treated as if it had a zero value, and therefore matches the filter. You'll need to add additional criteria to exclude records with null field values.

  • Thanks for your answer David Reed. I still wondering why the FullName and ExpiredDateRenewOpp fields return blank value when the report has the filter although it has the value when the report does not have filter. Mar 5, 2019 at 4:37
  • @TruongThanh Those appear to be different records with the same name.
    – David Reed
    Mar 5, 2019 at 12:28
  • Sorry David, I missed information, the FullName field is the Account Full Name. And as the 1st image above, the "Voola test" and "Voola test 1" records have the same Account and all fields are showing (it's ok without filter). But the 2nd image with the filter, it shouldn't show the "Voola test" record here. And when the "Voola test" is showed in the 2nd image, why the FullName and ExpiredDateRenewOpp are blank. Sorry if my question is not clear. Thanks Mar 6, 2019 at 7:21

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .