Repeating Groups

[#1]

I have four groups. Each group is associated with a different table.

Group 1, Table 1: Fleet ID - a unique number that is never duplicated. All other tables are linked to this table by Fleet ID.

Group 2, Table 2: PO Number - There may be multiple POs associated with each Fleet ID.

Group 3, Table 3: Service Order - There may be multiple Service Orders associated with each Fleet ID.

Group 4, Table 4: Sublet Reference - There may be multiple Sublet References associated with each Fleet ID.

It looks like this...

and I'd like it to look like this...

Can you help?

[DellSC · Accepted solution]

This is going to be a bit of a challenge, but I can think of a couple of ways to do this.

1. If you have reasonably good SQL skills, you could create a Command (SQL Select statement) to pull ALL of the data for the report. It would have a format like this:

Select
  a.FleetID,
  '1-' + b.PO as RecordOrder,
  b.PO,
  null as ServiceOrder,
  null as SubletReference
from <fleet table> a
  inner join <po table> b
    on a.FleetID = b. FleetID
where <conditions for selecting the FleetID and PO>
UNION ALL
Select
  a.FleetID,
  '2-' + b.ServiceOrder as RecordOrder
  null as PO,
  b.ServiceOrder,
  null as SubletReference
from <fleet table> a
  inner join <service order table> b
    on a.FleetID = b. FleetID
where <conditions for selecting the FleetID and PO>
UNION ALL
Select
  a.FleetID,
  '3-' + b.SubletReference as RecordOrder,
  null as PO,
  null as ServiceOrder,
  b.SubletReference
from <fleet table> a
  inner join <sublet reference table> b
    on a.FleetID = b. FleetID
where <conditions for selecting the FleetID and PO>

Group by FleetID and put the Fleet ID in the group header. Order by Record Order, and put the fields across in the details section.

This would be the most efficient method for creating your report and it will export cleanly if you need to.

See https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/ for more info about using commands.

2. Use separate subreports for the Service Order and Sublet Reference data, linking on the FleetID. Group by FleetID, order by PO number. Put the FleetID in the group header, PO number in the details, and the subreports in separate FleetID group footer sections, lined up like the columns you want them in.

Depending on how much data you're reporting on, this report could be significantly slower than the first option and, due to issues with how subreports are exported, it may not export cleanly - especially to Excel.

-Dell

[former_member1343181 · Accepted solution]

Wow, Dell...thank you! My SQL skills might be considered boarder-line "reasonable" (at best) but they are better today thanks to you! I modified your example as needed, grouped and sorted as you recommended, and it produced exactly what I was looking for. And as a bonus, I learned a bit more about SQL. Thank you so much for your help with this!!