List all child records for each parent record on a report

I’ve got a 1:Many relationship between a Parent object and Child object (master detail). I want to write a report that will show each parent object in its own row, with columns for each child record. Is this possible?

Data:

    ParentA
       |
  +----+----+
  |         |
ChildA    ChildB

Desired report output:

Parent Name | Child Name | Child Name | ...(up to n children)
-------------------------------------------------------------
Parent A    | Child A    | Child B    | ...
Parent B    | Child C    | Child D    | ...

Answer

Excellent question.

With basic reporting capabilities – no. As far as I know SF doesn’t offer any way to pivot (turn rows into columns) in SOQL, Apex and in the reporting engine.

If you have a field that’s unique for kids of one parent (ideally some sequential number) you could try faking it by using a bucket field in the report. You’d throw all “kid 1” records into 1st bucket etc… Sounds like it’d require extra preparations / data fixes on your side.

(You’re bit more lucky if your child records are created for example once a month – date fields can be pretty neatly grouped in summary & matrix reports).

Other than that – you might be forced to transform your data yourself. Create a helper object with these duplicated columns and try to fill it in, either with an Analytic Snapshot or with some batch job?

Attribution
Source : Link , Question Author : Nathan Wallace , Answer Author : eyescream

Leave a Comment