Reports

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:

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:

 

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:

 

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:

 

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

 

 

2 Comments on “MS CRM 2011: T-SQL Union operation and FetchXml based reports

  1. Thanks for writing this, it seems like a novel way of simulating a UNION. But is it possible to specify a second JOIN within the link-entity? For example:
    Select
    AccountId RecordId
    ,Name RecordName
    ,'account' RecordTypeName
    From FilteredAccount INNER JOIN Account_Master ON AccountId = Account_Master.actID
    Union
    Select
    ContactId RecordId
    ,FullName RecordName
    ,'contact' RecordTypeName
    From FilteredContact INNER JOIN Account_Master ON ContactId = Account_Master.actID

    Thanks,
    Bill

  2. Hello Bill,

    In case you will have those middle entity with id inside you can do join from that entity on your account_master entity whatever it is.

    Kind regards,
    Andrii.

Leave a Reply

Your email address will not be published. Required fields are marked *