web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Report to Show contact...
Microsoft Dynamics CRM (Archived)

Report to Show contacts that belong to Account and Contacts connected to account

(0) ShareShare
ReportReport
Posted on by TrevorB 105

Hi All

Using Dynamic 365 online V9. I trying to create a report that will show contacts that belong to Account (employees) and Contacts connected to account.

The scenario would be a company with employees and board members of the company which we use connections for.

I display these in separate reports but cant seem to work out how to do this in the one report.

Can this be achieved with a report wizard.

Any help appreciated.

Regards 

Trevor

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Adrian Begovich Profile Picture
    Adrian Begovich 1,027 Moderator on at

    Hi Trevor,

    I do not think it is easy to achieve this with a singlular report wizard report, but you can implement this with a SQL Server Reporting Services (SSRS) report.

  • TrevorB Profile Picture
    TrevorB 105 on at

    Hi Adrian

    Thanks for your reply and hope you have enjoyed the holiday season. Didn’t think OTB was an option but was worth asking in case I missed the obvious.

    I have written a few custom SSRS reports and familiar with Fetchxml Builder and VSTS but my knowledge is somewhat limited and seeking some help with this if anyone can assist.

    I have managed to get the query for Contacts that belong to Account and the second query for Contacts that are connected to the Account but can’t work out how to put these together into a single query that returns the desired result.

    (Orgs+Contacts(return all active contacts associated to an Organisation))

    <fetch distinct="false" no-lock="false" mapping="logical" >

      <entity name="account" >

        <attribute name="name" alias="name" />

        <attribute name="accountid" />

        <filter type="and" >

          <condition attribute="name" operator="eq" value="ABC Company" />

          <condition entityname="contact1" attribute="statecode" operator="eq" value="0" />

        </filter>

        <link-entity name="contact" to="accountid" from="parentcustomerid" link-type="outer" alias="contact1" >

          <attribute name="fullname" alias="contact1_fullname" />

          <attribute name="contactid" />

          <attribute name="jobtitle" />

        </link-entity>

      </entity>

    </fetch>

     

    (Orgs+Connections(return all active contacts connected to an Organisation))

    <fetch distinct="false" no-lock="false" mapping="logical" >

      <entity name="account" >

        <attribute name="name" alias="name" />

        <attribute name="accountid" />

        <filter type="and" >

          <condition attribute="name" operator="eq" value=" ABC Company />

          <condition entityname="contactconnection" attribute="statecode" operator="eq" value="0" />

        </filter>

        <link-entity name="connection" to="accountid" from="record2id" link-type="outer" alias="connection1" >

          <attribute name="record1roleid" alias="connection1_record1roleid" />

          <link-entity name="contact" from="contactid" to="record1id" link-type="outer" alias="contactconnection" >

            <attribute name="fullname" alias="contactconnection_fullname" />

          </link-entity>

        </link-entity>

      </entity>

    </fetch>

     

    One of my many attempts at pulling these together is like below.

    (Contacts+Orgs+Connections2)

    <fetch distinct="true" no-lock="false" mapping="logical" >

      <entity name="account" >

        <attribute name="name" alias="name" />

        <attribute name="accountid" />

        <attribute name="parentaccountid" />

        <filter type="and" >

          <condition attribute="name" operator="eq" value="ABC Company" />

          <condition entityname="ContactOrg" attribute="statecode" operator="eq" value="0" />

        </filter>

        <link-entity name="connection" to="accountid" from="record2id" link-type="outer" alias="connection1" >

          <attribute name="record1roleid" alias="connection1_record1roleid" />

          <attribute name="record2roleid" alias="connection1_record2roleid" />

          <attribute name="connectionid" />

          <attribute name="name" />

          <link-entity name="contact" from="contactid" to="record1id" link-type="outer" alias="contactconnection" >

            <attribute name="fullname" alias="contactconnection_fullname" />

          </link-entity>

        </link-entity>

        <link-entity name="contact" from="parentcustomerid" to="accountid" link-type="outer" alias="ContactOrg" >

          <attribute name="fullname" />

        </link-entity>

      </entity>

    </fetch>

     

    My test ABC Company has 3 contacts that belong to the account and 2 contacts connected to the account but what this returns is my 3 contacts by 2 in the ContactOrg.fullname column and the 2 connections by 3 in the contactconnection.fullname column.

    (Image off returned fetch with XRM Fetch Builder)

    Image-off-returned-fetch-with-XRM-Fetch-Builder1.JPG

    I don’t think I am anywhere near on the right track here but and have tried multiple different queries switching these around and building other queries using contact entity to start with.

     The end result I am trying to achieve is a list of contacts in column 1 (5 contacts), the account they belong to in column 2 (ABC Company for the 3 contacts that belong to the account), and the account they are connected to in column 3 (ABC Company for the 2 contacts that are connected to the account).

    Have spent considerable time searching the net for an answer but haven't been able to find a solution.

    If anyone can provide me with some guidance on how to achieve this I would greatly appreciate it.

     Regards

     Trevor

  • Suggested answer
    TrevorB Profile Picture
    TrevorB 105 on at

    Hi All

    Just a quick update on my Contacts that belong to Account and Contacts connected to Accounts report. I had all but given up on this until I stumbled across this great post by Megan Walker

    https://community.dynamics.com/crm/b/meganwalker/archive/2018/08/07/creating-dynamics-365-reports-with-multiple-entities

    My query was correct even though it displayed duplicate rows. The trick was in using Parent and Child groupings to show the unique values in the report. By adding a Group, Add Group>Row Group>Parent Group [Name], then adding a Child Group [ContactOrg_fullname] selecting to add a Header and a Footer. Then right clicking [ContactOrg_fullname] under Row Groups and Add Group>Adjacent After.

    Contacts-_2D00_-Connections-2.JPG

    My report now returns unique rows for each contact and connection all grouped under the Company.

    Contacts-_2D00_-Connections-2.JPG

    Thanks to Megan for the great example of Reports With Multiple Entities and hopefully this might help others not having to spend as much time as I did on getting a solution when using Contacts and Connections.

    Regards Trevor

  • Verified answer
    Dynamics365 Rocker Profile Picture
    Dynamics365 Rocker 7,755 on at

    You have to create SSRS report for this.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
SA-08121319-0 Profile Picture

SA-08121319-0 4

#1
Calum MacFarlane Profile Picture

Calum MacFarlane 4

#3
Alex Fun Wei Jie Profile Picture

Alex Fun Wei Jie 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans