CRM 2011: SSRS Lookup Function - Merge Aggregate Datasets

Nathan Eccles, 04 November 2012

A significant limitation of SSRS when building reports for CRM Online is the inability to use 2 different Datasets in 1 table. Often this is not a problem, as we can build queries which will retrieve the information we need from multiple entities. However sometimes this presents problems when attempting to do totals. 

In the following scenario I wish to look at a list of Accounts and see their total amount Ordered, total amount Invoiced, and amount Ordered but not yet Invoiced.

If we try to do one FetchXML query to retrieve information about all Orders and Invoices associated with an Account we run into problems when we have two Invoices associated with one Order. This is because the two Invoices come through as separate lines of data, both containing the same information for the one Order. While this duplicate data can be removed using Grouping within SSRS, or Aggregating the FetchXML, totals are thrown off by these phantom records.

The way around this is to use two separate Datasets, however SSRS does not allow us to reference two Datasets in a single table, and if we split the data into two tables we are no longer able to reference the cells when attempting to calculate the “Ordered but not Invoiced” totals.

Thankfully SSRS does include this wonderful “Lookup” function which essentially merges two Datasets based on a common value, in this case “accountid”. 

The first step is to create 2 separate Datasets using FetchXML Aggregates. 

<!--OrderTotal-->
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true" aggregate="true">
  <entity name="account" enableprefiltering="1">
    <attribute name="accountid" groupby="true" alias="id" />
    <attribute name="name" groupby="true" alias="account" />
    <link-entity name="salesorder" from="customerid" to="accountid" alias="Order" link-type="outer">
      <attribute name="totalamount" aggregate="sum" alias="sumAmount" />
    </link-entity> 
  </entity>
</fetch> 

<!--InvoiceTotal-->
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true" aggregate="true">
  <entity name="account">
    <attribute name="accountid" groupby="true" alias="id" />
    <attribute name="name" groupby="true" alias="account" />
    <link-entity name="invoice" from="customerid" to="accountid" alias="Invoice" link-type="outer">
      <attribute name="totalamount" aggregate="sum" alias="sumAmount" />
    </link-entity> 
  </entity>
</fetch> 

Once these have been created we need to create a table to display them. This table should use the dominant Dataset to ensure that all data is pulled through. In this scenario neither is dominant as they both link directly to the Account. For this example I will use the “OrderTotal” Dataset. 

A column can be created for the Account Name from the “OrderTotal” Dataset.
=Fields!account.Value

A column can be created for the Order Total.

Because we have aggregate functions, no expression needs to be used for returning the Order Total.
=Fields!sumAmountValue.Value

A column should be created for the Invoice Total implementing the Lookup Function to merge the 2 Aggregate Datasets.

The Lookup function works by linking the datasets using the first provided value from the current Dataset with the second provided value from the Dataset specified at the end of the call. The third field specified is the field you wish displayed from the newly linked Dataset.

In this scenario we link the accountid from the “OrderTotal” Dataset to the accountid from the “InvoiceTotal” Dataset and return the summed amount of the Invoices for that accountid.
=Lookup(Fields!id.Value, Fields!id.Value, Fields!sumAmountValue.Value, "InvoiceTotal")

A column should be created for “Ordered but not Invoiced” cell references used to calculate the totals.

The Name of the cell can be identified by right clicking the cell and opening the Text Box Properties.
=ReportItems!sumOrders.Value - ReportItems!sumInvoices.Value

When we run the report on Accounts with associated Orders and Invoices we can see that it is displaying the correct data, and that the totals are accurate.

If you have any feedback please feel free to post below. I’d love to learn from you as I hope you’ve learnt from me.