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
You have to create SSRS report for this.
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
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.
My report now returns unique rows for each contact and connection all grouped under the Company.
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
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)
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
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.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,235
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (