web
You’re offline. This is a read only version of the page.
close
Skip to main content
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)
  • Aiden Kaskela Profile Picture
    Aiden Kaskela 19,696 on at
    RE: How to pull connections based on different Criteria in SQL reports

    Awesome, I'm happy that worked out.

  • Community Member Profile Picture
    Community Member on at
    RE: How to pull connections based on different Criteria in SQL reports

    Thanks Aiden,this worked

  • Verified answer
    Aiden Kaskela Profile Picture
    Aiden Kaskela 19,696 on at
    RE: How to pull connections based on different Criteria in SQL reports

    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

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

News and Announcements

Dynamics 365 Community Calls Return
Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft
Responsible AI policies for the Community

Quick Links

Responsible AI policies

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

Andrés Arias – Community Spotlight

We are honored to recognize Andrés Arias as our Community Spotlight honoree for…

Congratulations to the August Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
Community Member Profile Picture

Community Member 2

#2
Christoph Pock Profile Picture

Christoph Pock 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans