Answered by:
SQL substring funtion to FetchXML

Question
-
I have a scenario where I have to perform join on two tables and show all the matching records from joined table in a single column using strong concatenation. How to achieve this in fetchxml?
For example here is how sql query look like(see the column with alias optionalattendees and requiredattendees):
select * from
(
select DISTINCT
activitypointer0.activityid as ''activityid'',
activitypointer0.ownerid as ''ownerid'',
activitypointer0.owneridname as ''owneridname'',
activitypointer0.regardingobjectid as ''regardingobjectid'',
activitypointer0.regardingobjectidname as ''regardingobjectidname'',
activitypointer0.subject as ''subject'',
activitypointer0.scheduledend as ''scheduledend'',
activitypointer0.statuscode as ''statuscode'',
activitypointer0.statuscodename as ''statuscodename'',
activitypointer0.statecode as ''statecode'',
activitypointer0.statecodename as ''statecodename'',
activitypointer0.activitytypecode as ''activitytypecode'',
activitypointer0.activitytypecodename as ''activitytypecodename'',
activitypointer0.description as ''description'',
substring((select '', '' + a.partyidname
from (
select distinct partyidname from FilteredActivityParty
where activityid = activitypointer0.activityid and participationtypemask = 5
) as a order by a.partyidname
for xml path('''')), 3, 10000) as ''requiredattendees'',
substring((select '', '' + a.partyidname
from (
select distinct partyidname from FilteredActivityParty
where activityid = activitypointer0.activityid and participationtypemask = 6
) as a order by a.partyidname
for xml path('''')), 3, 10000) as ''optionalattendees'',
activitypointer0.createdon as ''createdon'',
activitypointer0.modifiedon as ''modifiedon'',
(case when partyid = activitypointer0.ownerid then activitypointer0.owneridname else partyidname end) as ''relatedparty''
from (' + @CRM_FilteredActivityPointer + ') as [activitypointer0]
left outer join ' + @CRM_SQLSubquery +
') as x
where subject not like ''CRM Notification%''
and activitytypecodename not like ''Opportunity Close''
order by owneridnameMonday, November 2, 2015 5:08 PM
Answers
-
You can't do any concatenation in FetchXml. Instead you have 3 options:
- If you just wanted the recipients of the email entity, then you can (I think) retrieve the torecipients, ccrecipients etc. attributes, which are a concatenated string, but this doesn't work for other party types
- Write a FetchXml query on the activityparty entity, with a Link-Entity to the activitypointer entity. This would return one record per party, so you'd have to parse the results in code / in a report to concatenate the activityparty data, and group the activitypointer data
- Execute 2 FetchXml queries - one on the activitypointer and one on the activityparty, and again parse the results in code to concatenate the data
Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk
- Proposed as answer by Mayank PujaraEditor Tuesday, November 3, 2015 12:24 PM
- Marked as answer by Sunil5715 Wednesday, November 4, 2015 3:23 PM
Tuesday, November 3, 2015 2:46 AMModerator
All replies
-
You can't do any concatenation in FetchXml. Instead you have 3 options:
- If you just wanted the recipients of the email entity, then you can (I think) retrieve the torecipients, ccrecipients etc. attributes, which are a concatenated string, but this doesn't work for other party types
- Write a FetchXml query on the activityparty entity, with a Link-Entity to the activitypointer entity. This would return one record per party, so you'd have to parse the results in code / in a report to concatenate the activityparty data, and group the activitypointer data
- Execute 2 FetchXml queries - one on the activitypointer and one on the activityparty, and again parse the results in code to concatenate the data
Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk
- Proposed as answer by Mayank PujaraEditor Tuesday, November 3, 2015 12:24 PM
- Marked as answer by Sunil5715 Wednesday, November 4, 2015 3:23 PM
Tuesday, November 3, 2015 2:46 AMModerator -
Yes I had second and third approach in mind. I tried both I was not able to concatenate the data using thrid approach since SSRS expression functions(I used Lookupset) does not allow to use fields from multiple dataset. Option 2 is working as expected. Thanks for pointing in right direction!Wednesday, November 4, 2015 3:23 PM