Beringer Associates Technology Blog

Creating Custom Reports for Microsoft Dynamics CRM 2011 Online

Posted by on in Microsoft Dynamics CRM

custom reportingCreating Custom Reports for MSCRM 2011 online can be pretty tricky. Since you are stuck using FetchXML to write your queries, you are limited to what you can do (as opposed to using a SQL Query). One limitation is using a Union. There is no Union in the FetchXML query language. What to do?

Here’s an example form a recent report:

I am tasked with writing a report to show all Opportunities associated to an Account using the system 1:N relationship from Account to Opportunity. I also need to show all of the Opportunities associated to an Account using a custom N:N relationship from Account to Opportunity. I can’t use a “Union All” to get both from one Dataset.

  1. I start by creating an “Account” Dataset and use “enableprefiltering” in order to allow the dataset to get the AccountID from the Account record it is running under and filter the Account dataset.
  2. I create an “AccountID” parameter to hold the AccountID value pulled from the Account Dataset.
  3. I then create an “Opportunity1N” datatset to gather all the Opps associated to the Account (based on the system 1:N relationship) —
  4. In this DataSet, I filter using the FetchXML condition <condition attribute=”customerid” operator=”eq” value=”@AccountID” />
  5. I now create an “Opps1N” Parameter to hold the multi-value list of Opp1N opportunityid GUIDS
  6. I then create an “OpportunityNN” datatset to gather all the Opps associated to the Account (based on the custom N:N relationship) — I need to use a “link-entity” to join my custom N:N relationship Entity to the Opps Entity.
    <fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”true”>
    <entity name=”new_opportunity_account” >
    <attribute name=”accountid” />
    <link-entity name=”opportunity” from=”opportunityid” to=”opportunityid” alias=”Opp”>
    <attribute name=”name” />
    <attribute name=”opportunityid” />
    </link-entity>
    <filter type=”and”>
    <condition attribute=”accountid” operator=”eq” value=”@AccountID” />
    </filter>
    </entity>
    </fetch>
  7. In this DataSet, I filter using the FetchXML condition<condition attribute=”accountid” operator=”eq” value=”@AccountID” />
  8. I now create an “OppsNN” Parameter to hold the multi-value list of OppNN opportunityid GUIDS
  9. My last DataSet grabs all the Opportunities who’s GUIDs are either in the Opps1N or OppsNN parameter
  10. <fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”true”>
    <entity name=”opportunity”>
    <attribute name=”name” />
    <attribute name=”opportunityid” />
    <attribute name=”estimatedvalue” />
    <attribute name=”estimatedclosedate” />
    <attribute name=”actualvalue” />
    <attribute name=”actualclosedate” />
    <attribute name=”statuscode” />
    <filter type=”or”>
    <condition attribute=”opportunityid” operator=”in” value=”@Opps1N” />
    <condition attribute=”opportunityid” operator=”in” value=”@OppsNN” />
    </filter>
    </entity>
    </fetch>

All looks good. However, here’s where the gotcha happens. If either the Opps1N or the OppsNN dataset return no records, they don’t return a NULL value to the Parameter since you can’t select “Allow null value” when also selecting “Allow multiple values” under the report parameter options. The report just fails and gives you a message that a parameter was missing a value.

So what’s the work-around?

Since there is no way to allow a NULL value for a multivalued parameter (when no records re returned), I have found it best to create a record in CRM whos entity is being used in the parameter lookup. In other words, if I have a query that looks at Opportunities and a Parameter that pulls in the Opportuny IDs from that query, I need to make sure it gets a value even if none are returned . I create an Account record with a Topic of “DO NOT DELETE – DEFAULT ACCOUNT FOR REPORT”. I also create an Opportunity record with a Topic of “DO NOT DELETE – DEFAULT OPPORTUNITY FOR REPORT”. I make the Account the Customer for this default Opportunity. For my custom N:N relationship, I also add the Account under the Opportunity. I then adjust my fetchXML to look at the default record through the filter. So if it doesn’t find an Opportunity based on the first condition, it will find it on the second because I’m using an existing GUID.

Query – Opportunity1:N
<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”true”>
<entity name=”opportunity” >
<attribute name=”name” />
<attribute name=”opportunityid” />
<filter type=”or”>
<filter type=”and”>
<condition attribute=”customerid” operator=”eq” value=”@AccountID” />
</filter>
<filter type=”and”>
<condition attribute=”opportunityid” operator=”eq” value=”F2600ED2-73E8-E111-8BA2-78E3B5101E8B” />
</filter>
</filter>
</entity>
</fetch>

Query – OpportunityN:N

<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”true”>
<entity name=”new_opportunity_account” >
<attribute name=”accountid” />
<link-entity name=”opportunity” from=”opportunityid” to=”opportunityid” alias=”Opp”>
<attribute name=”name” />
<attribute name=”opportunityid” />
</link-entity>
<filter type=”or”>
<filter type=”and”>
<condition attribute=”accountid” operator=”eq” value=”@AccountID” />
</filter>
<filter type=”and”>
<condition attribute=”accountid” operator=”eq” value=”8BC4BEC5-73E8-E111-8BA2-78E3B5101E8B” />
</filter>
</filter>
</entity>
</fetch>

If the default opportunity does end up in the records of the dataset, I can then HIDE this default Opportunity by using the “Row Visibility” property in the report.

Row Visibility Expression:

=IIF(UCase(Fields!opportunityid.Value)=”F2600ED2-73E8-E111-8BA2-78E3B5101E8B”,true,false)

Share