Dynamics CRM 2011 Build Unmatch query SSRS report using FetchXML

One of the limitations of the Advanced Find feature of Microsoft Dynamics CRM and by extension, Fetch XML using the SSRS Report Authoring Tool is that you cannot do an unmatch type of query.  This could be a huge bummer (if not for this article) for CRM online clients because you cannot write custom reports using SQL, where an unmatch query is a piece of cake using a left outer join and looking for nulls (countless articles out there on this).   I say huge bummer, but first, let’s give Microsoft huge kudos for providing the Report Authoring Extension.   I’ve worked with other cloud ERP and cloud CRM systems where you cannot do this type of custom reporting without using some data extract tool to move all the data locally or there is a primative retrieve from a very limited set of web service methods.

An example of an unmatch query is, you want to find all Account records that have not had an activity for the last 6 months.

You can do this using a static Marketing List, but the big drawback is that you have to add and subtract members every time you want this information.  Real quick, if you’re interested in doing this, simply create a static Marketing List and Add Members using Advanced Find.  Then, use Remove Members using Advanced Find.  In the example above, you would add all the account records you are interested in and then you would remove all account records that have had an activity in the last 6 months.  Again, there are countless sources out there that show you how to do this.

Your first thought may be, hey, there is a system view that shows Accounts without an order for the last 6 months, I’ll start with that one and see how they did it.  Not so fast my friend, you will not see this option show up in the list of views you can start with when creating an advanced find (aka personal view).  I’ve even tried looking at the XML for the Account entity to see if they gave away the fetch xml, but you won’t find it there.  I suspect they are cheating here and using some other mechanism for this view.  Your next thought is, I’ll start with the one that says  “No campaign activities for the last 6 months”.  Again, you are rebuffed because they are taking advantage of a property called “Last Date Included in Campaign”, so they are really once again, cheating here.  Don’t fret, I will show you exactly how to do this using SSRS and Fetch XML.

First of all, let me state that I’m not smart enough to say that there definitely is not a way to do this using FetchXML.  There is a Fetch XML builder that I use all the time, but I have not figured out a way to do an unmatch using this tool, writing an advanced find or writing the fetchxml myself.   What I describe below is a way to do this using some SSRS tricks in conjunction with FetchXML.  I’ve used SSRS tricks to get past other FetchXML limitations such as a SQL Union, which I also have not been able to accomplish with pure FetchXML.  If you’re interested in how to emulate a SQL union, leave a comment.  For the steps below, I assume you already know how to write a custom SSRS report for Dynamics CRM Online using the Report Authoring extension.  Again, many sources for that including Custom SSRS Reports for Dynamics CRM 2011 Online.  The example below is to find Orders that do not have Order Products.  I recently needed this for a CRM online client that was trying to validate the migration of Sales Order data.  There is no other way to do this that I know of.

1. Create a new SSRS Report using the “Microsoft Dynamics CRM Fetch” Data Source type.
2. Create a FetchXML Dataset called “OrderProducts” using the Sales Order Detail lines and join to the Sales Order entity. FetchXML is provided below.
3. Create a parameter called orderlines that allows for multiple values.  For now, leave it visible so you can see the results.  Later, you can change this to a hidden or internal parameter, so it will not be displayed to end user.
4. For the “Available Values” of the orderlines parameter, choose “Get Values from a query” and select the OrderProducts as the dataset.  Choose the salesorderid as the value field and the name field as the label (I chose both from the joined in Sales Order entity).
5. For the “Default Values” of the orderlines parameter, choose “Get Values from a query” and select the OrderProducts as the dataset and choose the salesorderid field for the value field.
6. Create another FetchXML dataset called “OrdersWithoutProducts”.  Use advanced find or the Fetch XML Builder to create your dataset using the Sales Order entity.  Choose whatever filters and whatever columns you want as part of the dataset.  It is not necessary to include any other entities unless you want to.  The key here is that for our condition, we are going to use as the attribute, the salesorderid, use the “not-in” as our operator and use the @orderlines (our parameter) as the value.  Like so:  <filter type=”and”>
   <condition attribute=”salesorderid” operator=”not-in” value=”@orderlines” /></filter>
7. There you have it.  Now, just put a table on your design surface and use the “OrdersWithoutProducts” dataset to drag fields over to it.

The SSRS trick here was to leverage a multi-value parameter as our “other table” to do our comparison and took advantage of the not-in comparison operator to find orders without products.

Please let me know if you have any questions or problems and let me know if this was useful to you by providing a comment.

By: Bill Caldwell – Denver, Colo. Microsoft Dynamics CRM partner

Follow Bill on Google+

Order Products FetchXML

<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”true”>
<entity name=”salesorderdetail”>
<link-entity name=”salesorder” from=”salesorderid” to=”salesorderid” visible=”false” link-type=”outer” alias=”a_6ec0e72e4c104394bc627456c6412838″>
<attribute name=”ordernumber” />
<attribute name=”salesorderid” />
<attribute name=”name” />
<order attribute=”name” descending=”false” />
</link-entity>
</entity>
</fetch>

OrdersWithoutProducts FetchXML

<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”false”>   <entity name=”salesorder”>     <attribute name=”name” />     <attribute name=”customerid” />     <attribute name=”statuscode” />     <attribute name=”totalamount” />     <attribute name=”salesorderid” />    <attribute name=”ordernumber” /> <filter type=”and”>    <condition attribute=”salesorderid” operator=”not-in” value=”@orderlines” />     </filter>

<order attribute=”name” descending=”false” />   </entity> </fetch>

RSM Staff