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) / How to pull connection...
Microsoft Dynamics CRM (Archived)

How to pull connections based on different Criteria in SQL reports

(0) ShareShare
ReportReport
Posted on by Community Member

HI Everyone

I  am currently writing a report and in my report I need to show the connections based on different filter criteria.e.g

for instance I have an account in that specific account I have a sub grid showing opportunities specific to this account,I have another showing contacts,then another showing team member(filter based on user or contact and status equals active.all this is via connections.

How will I write my query in SQL to say give me the account,then all opportunities against that account,then give me the contacts,then the team members,do I go directly from the account to the connection if so how because this contact is connected to the account via the connection

Any assistance will be much appreciated.

Thanks.

*This post is locked for comments

I have the same question (0)
  • Verified answer
    Aiden Kaskela Profile Picture
    Aiden Kaskela 19,696 on at

    Hi again,

    It sounds like you want to display this data in different tables, so you would have to set up a Data Set for each type of data you want back (connections, contacts, etc). You want to set up your report to run on the Account record, so when you open an account you'll see the report as able to run. This also means you'll get your Account ID as an input parameter, and you'll be able to run queries right on that record.

    Here are some basics to get you started.

    To get back connections for an account with id = 'E22....

    select
 filteredcontact.fullname,
 filteredcontact.emailaddress1,
 filteredconnection.Record2RoleIdname,
 filteredconnection.*
from filteredconnection
 join filteredcontact 
  on filteredconnection.record2id = filteredcontact.contactid
 join filteredConnectionRole
  on filteredconnection.Record2RoleId = filteredConnectionRole.connectionroleid
where Record1IdObjectTypeCode = 1 -- 1 is the OTC for account
and Record1Id = 'E223E683-3651-4089-875D-A02FD2EE0538' -- This would be your input parameter


    This would give you opportunities for the account:

    select
 filteredopportunity.name
from filteredaccount
 join filteredopportunity
  on filteredaccount.accountid = filteredopportunity.accountid


    Hope this helps! If so I'd appreciate if you would mark this as a Verified answer.

    Thanks,

      Aiden

  • Community Member Profile Picture
    Community Member on at

    Thanks Aiden,this worked

  • Aiden Kaskela Profile Picture
    Aiden Kaskela 19,696 on at

    Awesome, I'm happy that worked out.

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