locked
Report displaying all Customer Relationships for an Account and Contacts at that Account RRS feed

  • Question

  • I would like to create a report (using SQL Reporting Services) based on the Accounts, Contacts and the Customer Relationship entities.  


    For any selected Account, I would like to see *all* relationships related to that Account whether it is:

    Account A to Account B

    Account A to Contact B

    Contact A at Account A to Account B

    Contact A at Account A to Contact B at Account B

     

    Really, I wish the Relationship view on the Account would roll up to display all relationships at the Account level the way the Activities entity rolls up all Contact and Account History at the Account level.

     

    As that is not available as a view, I would like to build a report to show those relationships. I need help on the logistics of linking contacts in the either the customer or partner position of the customer relationship entity back up via the Contacts entity to the Account.  I would like to avoid building separate SQL tables  to stash the data for reporting (one for contacts in the customerIDname postion, another for contacts in the parterIDname postion, similar for Accounts in the customer and partner positions)

    Your ideas?
    Thanks for your help.

     

    Sandy

    Tuesday, March 10, 2009 6:23 PM

Answers

  • When you build the query in your report, you will end up having the Contact entitiy in your query multiple times (once for each of the actual relationships) and it will need to be aliased so that you can refer to them uniquely.  It's not going to be a straight forward query though.  Using a stored proc as you data source may be the easier approach as using a temp table to build your resultset might end up being easier than trying to do this in a single query.
    Matt, MVP - Dynamics CRM
    Monday, March 16, 2009 6:13 PM
    Moderator

All replies

  • When you build the query in your report, you will end up having the Contact entitiy in your query multiple times (once for each of the actual relationships) and it will need to be aliased so that you can refer to them uniquely.  It's not going to be a straight forward query though.  Using a stored proc as you data source may be the easier approach as using a temp table to build your resultset might end up being easier than trying to do this in a single query.
    Matt, MVP - Dynamics CRM
    Monday, March 16, 2009 6:13 PM
    Moderator
  • I am re-visiting this.  You are correct; SQL stored procedure must be used.  Queries on Filtered Views with unions take too long to run.  Has anyone built this yet?  I believe I'll need to collect the following, but let me know if I am missing something.

    Account to Account Relationships

    Account to Contact Relationships

    Contact to Contact Relationships

    I think Microsoft should include this aggregation in the product.

    Wednesday, June 2, 2010 2:51 PM
  • I solved this by scaling back on the number of columns of data I wanted returned, then used CRM auto-filtering to create a report available only at the Account level.  For the query, I created a union of Account relationships and Contact Relationships.  Using the hidden CRM_URL parameter, I embedded a hyperlink in the report (for Party2) containing the CustomerRelationshipID so that users may open any relationship and drill down into the related contacts or accounts from there thus eliminating the need to include additional columns of data in the report.

     

    Here’s the query to collect all relationships.  The CRM  attributes are all standard out of the box.

     

     

    SELECT     TOP (100) PERCENT FilteredAccount.accountid, FilteredAccount.name AS accountidname, FilteredAccount.name AS Party1,

                          dbo.FilteredCustomerRelationship.customerroleidname AS [Party1 Role],

                          dbo.FilteredCustomerRelationship.customerroledescription AS [Party1  Description], dbo.FilteredCustomerRelationship.partneridname AS Party2,

                          dbo.FilteredCustomerRelationship.partnerroleidname AS [Party2 Role],

                          dbo.FilteredCustomerRelationship.partnerroledescription AS [Party2 Description], dbo.FilteredCustomerRelationship.createdon AS [Referral Date],

                          dbo.FilteredCustomerRelationship.customerid, dbo.FilteredCustomerRelationship.partnerid, dbo.FilteredCustomerRelationship.converserelationshipid,

                          dbo.FilteredCustomerRelationship.customerrelationshipid

    FROM         dbo.FilteredAccount AS FilteredAccount INNER JOIN

                          dbo.FilteredCustomerRelationship ON FilteredAccount.accountid = dbo.FilteredCustomerRelationship.customerid

    UNION

    SELECT     TOP (100) PERCENT FilteredContact.accountid, FilteredContact.accountidname, FilteredContact.fullname AS Party1,

                          FilteredCustomerRelationship_1.customerroleidname AS [Party1 Role],

                          FilteredCustomerRelationship_1.customerroledescription AS [Party1  Description], FilteredCustomerRelationship_1.partneridname AS Party2,

                          FilteredCustomerRelationship_1.partnerroleidname AS [Party2 Role], FilteredCustomerRelationship_1.partnerroledescription AS [Party2r Description],

                          FilteredCustomerRelationship_1.createdon AS [Referral Date], FilteredCustomerRelationship_1.customerid, FilteredCustomerRelationship_1.partnerid,

                          FilteredCustomerRelationship_1.converserelationshipid, FilteredCustomerRelationship_1.customerrelationshipid

    FROM         dbo.FilteredContact AS FilteredContact INNER JOIN

                          dbo.FilteredCustomerRelationship AS FilteredCustomerRelationship_1 ON

                          FilteredContact.contactid = FilteredCustomerRelationship_1.customerid

    ORDER BY accountidname

     

    • Proposed as answer by Sandy in Oaks Thursday, August 19, 2010 5:49 PM
    Thursday, August 19, 2010 5:49 PM