I've got two lists there's a one-to-many relationship between the two lists based on a common key field.

I'd like to create a report that shows each record from the main list followed by all its secondary records:

Main record #1 Field1 Field2 Field3....

1.         Secondary record related to main record #1, Field1, Field2, Field3...
2.         Secondary record related to main record #1, Field1, Field2, Field3...
3.         Secondary record related to main record #1, Field1, Field2, Field3...

                .......

Main record #2 Field1 Field2 Field3....

1.         Secondary record related to main record #2, Field1, Field2, Field3...

                 ......

Is this possible to do OOB with either SP or SP Designer?

Once you have the list on a page, you can edit the page, then select the Web Part tab in the ribbon and link it to the secondary list.  Once linked, you should be able to select a Main record and the secondary records will filter to only show the ones that are linked to the main record via a lookup field.

Thanks - that's a good start.  However, i was hoping for a report showing all the Main records along with their linked data.

Hi ajw,

I understand you want to display all the main items with linked items. However, OOB way is unable to achieve your requirement.

If you want to display like this, you need to achieve it programmatically. You could retrieve list items and display them depend on link using SharePoint Client Object Model.

Introduction about SharePoint 2010 Client Object Model:

https://msdn.microsoft.com/en-us/library/office/ee537247(v=office.14).aspx

Demos about CSOM how to retrieve/update list items etc:

http://www.codeproject.com/Articles/399156/SharePoint-Client-Object-Model-Introduction

Since development may be complex, I suggest you use connected web parts.

About how to connect data in web parts:

https://support.office.com/en-nz/article/Connect-data-in-Web-Parts-54a36fda-019c-49b3-ad33-0cd94bf70c1e

Best Regards,

Dean Wang