Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
SQL substring funtion to FetchXML RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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 owneridname
    Monday, November 2, 2015 5:08 PM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    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 AM
    Moderator

All replies

  • Question
    Sign in to vote
    1
    Sign in to vote

    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 AM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    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