FetchXML Retrieving Nulls in a Linked Entity for use in Dynamics CRM Reports

Zoe Sands, 23 July 2015

When writing custom FetchXML reports in Dynamics CRM, it is sometimes useful to find out what isn't there as opposed to what is. For example, you may want to find the Contacts that haven't had any activities recorded against them for a specified period of time. Unfortunately, there are no out of the box tools available to achieve this, so we need to create a custom report.

In this example, I want to see which Clients in CRM haven't had any Meeting Summaries recorded against them. I have built a simple FetchXML query with an outer join between the Client and Meeting Summary entities.

The report data will be displayed in a table. Because I want to show only the Clients that don't have any Meeting Summaries, I can filter the data in the table using the following steps:

1. Go to the tables properties.
   
2. Go to filters
   
3. Add a filter where the value you're looking for is equal to "nothing". Note that we use "nothing" instead of "null".

The "link-type=outer" property of the FetchXML query will return a null value if there is no matching Meeting Summary for a Client. The table filter will recognise this and will allow us to display those Clients without any Meeting Summaries.