2

I'd like to create a dynamics CRM 2013 report which basically joins one table to a union of two other tables, with a left outer join. I believe that the outer join is now not a problem with the latest version, however there is still no union equivalent. What would be the best way to achieve this? I need the union as I want to sort data from two tables on a date column that is in both. Please see below for a tsql representation.

select * from 
Record as rec
LEFT OUTER JOIN
(select * from Data1 as d1
UNION
select * from Data2 as d2) as data
on
rec.recordId = data.RecordId
order by rec.recordId, data.Date
Arun Vinoth-Precog Tech - MVP
  • 22,364
  • 14
  • 59
  • 168
Ross Buggins
  • 195
  • 1
  • 11
  • 1
    I'm facing the missing UNION in FetchXml myself and wanted to try [using Lookup function described here](http://www.magnetismsolutions.co.nz/blog/nathan-eccless-blog/2012/11/04/crm_2011_ssrs_lookup_function_merge_aggregate_datasets) but cannot yet confirm that works. – Filburt Feb 05 '15 at 17:22
  • 1
    I'm afraid @Filburt is correct, there is no way to union two different sets of data in Fetch XML, so you would have to go with a method like Lookup, but that can get really painful. – BlueSam Feb 05 '15 at 17:52
  • Thanks for confirming that - I feared that may be the case. However, have you got any pointers on how to achieve this with a lookup please? – Ross Buggins Feb 10 '15 at 00:48

0 Answers0