CRM Reports: Grouping by a related Entity

Question

There is an N<>N relationship between Contacts and Complaints.

My report currently looks like this:

      Status 1    Status 2    Status 3    Status 4
         3           4           32          34

With the following query:

SELECT 
    SUM(case WHEN status = 1 then 1 else 0 end) Status1,
    SUM(case WHEN status = 2 then 1 else 0 end) Status2,
    SUM(case WHEN status = 3 then 1 else 0 end) Status3,
    SUM(case WHEN status = 4 then 1 else 0 end) Status4,
    SUM(case WHEN status = 5 then 1 else 0 end) Status5
FROM [DB].[dbo].[Contact]

This is listing the number of contacts in each status. I'm now trying to GROUP BY a field in a related entity in CRM - complaints.

                                         Status 1    Status 2    Status 3    Status 4
Contact.Complaints.CreatedBy[1]             3           4           32          34
Contact.Complaints.CreatedBy[2]             3           4           32          34
Contact.Complaints.CreatedBy[3]             3           4           32          34
Contact.Complaints.CreatedBy[4]             3           4           32          34

I'm not sure where to get started in my GROUP BY statement - any pointers would be awesome. I feel like I have to have another FROM statement pointing to the NN relationship, or at least Complaints.

Answer 1

It should be as easy as adding a JOIN to Complaints (thru the N:N) table. I completely agree with James, just make sure you execute the report as a CRM user, otherwise Filtered views return 0 rows.

SELECT
MyComplaintType,
...existing Sum(Case) stuff

FROM
FilteredContacts c
JOIN
Filterednew_Contacts_new_Complaint_new_complaints r1 (whatever your N:N is)
  ON c.contactId = r1.contactId
JOIN
Filterednew_Complaint comp
  ON r1.new_complaintId = comp.new_complaintId
GROUP BY
MyComplaintType