+ Start a Discussion

How can I get aggrigation of Child Object Data in Parent Report

We had a custom multi select pick list filed in Contact. Due to the data growth, we made the pick list into a new child object. Now a contact can be associated with many child records.

Ex: Contact_A can be interested in Custom_X, Custom_Y, Custom_Z. Initially this would typically have 1 contact record with X, Y, Z as values in pick list. But now, there are 4 records - 1 contact record & 3 child records associated with the 1 parent contact record.

The functionality works great with the child object displayed in the related list of contact detail page.
But I am having issues in Reporting when it comes to aggregation of child objects at parent level (something similar to inner-join query)

Some of the situation I need to report are:
1. Pull out all the contacts who are associated with both Custom_X and Custom_Y alone (this is not OR - AND alone)
2. Pull out all the Contacts who are associated with Custom_X and Custom_Y but not in Custom_Z. (This should looks for all Child Object records which are associated with same contact, but filter the above condition)

I couldn’t able to do this directly into a Report. Currently one of the option I could think is updating back Contact (due to 255 characters limit in text & text area cannot be used in report filters, In my case updating a multi select pick list again) with the child object value. But this doesn’t seem to be a correct solution.