MS CRM 2011: T-SQL Union operation and FetchXml based reports

I believe every person who developed complex reports for CRM used T-SQL Union operation to display different types of records at the same table.

In T-SQL I used following T-SQL Query to get results:

Select
    AccountId RecordId
    ,Name RecordName
    ,'account' RecordTypeName
From FilteredAccount
Union
Select
    ContactId RecordId
    ,FullName RecordName
    ,'contact' RecordTypeName
From FilteredContact

And results were:

When CRM 2011 was released we got possibility to create reports for CRM Online but it is limited to usage of FetchXml reports that doesn’t have Union operation. Following article describes workaround.


Customizations
Create custom entity as it shown at following screenshots:

Add custom text field to created entity:

Publish created entity.

Plugin
Create and build assembly that will contain following class:

using System;
using System.ServiceModel;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using System.Linq;

namespace XrmReportUnionHelper.Plugins
{
    public class Handler: IPlugin
    {
        public void Execute(IServiceProvider serviceProvider)
        {
            IPluginExecutionContext context = 
                (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
            IOrganizationServiceFactory factory = 
                (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
            IOrganizationService service = factory.CreateOrganizationService(null);

            if (context.MessageName == "Create")
            {
                Entity target = context.InputParameters["Target"] as Entity;

                Entity proxy = new Entity("xrm_proxy");
                proxy["xrm_id"] = target.Id.ToString();
                proxy["xrm_entitytypename"] = target.LogicalName;

                service.Create(proxy);
            }
            else if (context.MessageName == "Delete")
            {
                EntityReference target = context.InputParameters["Target"] as EntityReference;

                QueryByAttribute query = new QueryByAttribute("xrm_proxy")
                {
                    ColumnSet = new ColumnSet(false)
                };
                query.AddAttributeValue("xrm_id", target.Id.ToString());
                query.AddAttributeValue("xrm_entitytypename", target.LogicalName);

                Entity proxy = service.RetrieveMultiple(query).Entities.FirstOrDefault();

                if (proxy != null)
                    service.Delete(proxy.LogicalName, proxy.Id);
            }
        }
    }
}

Register assembly and for every entity you want to use in report register 2 steps shown at following screenshots:

Report

During the design of report use following FetchXml query:

<fetch mapping='logical'>
  <entity name='xrm_proxy'>
    <attribute name='xrm_id'/>
    <attribute name='xrm_entitytypename'/>
    <link-entity name='account' from='accountid'
       to='xrm_id' link-type='outer' alias='account'>
      <attribute name='name' alias='accountname'/>
    </link-entity>
    <link-entity name='contact' from='contactid'
       to='xrm_id' link-type='outer' alias='contact'>
      <attribute name='fullname' alias='contactname'/>
    </link-entity>
  </entity>
</fetch>

Next point is to show correct information depends on the type of record in row. This was done using Reporting Services expressions. In my case name of account is shown in case current record is account and full name shown in case record is contact:

=iif(Fields!xrm_entitytypename.Value.Equals("account"), Fields!accountname.Value, Fields!contactname.Value)

Demonstration
I have created 2 records - 1 contact and 1 account and ran created report: