Aggregating fields from multiple tables in a report / label without duplicates

Hey all,
I’m trying to edit one of our labels in Jaspersoft and Sp7 to include fields from the PreparationAttribute table. Specifically, I want to have a string that includes a mixture of field values from the Preparation table and Prep Attribute table.

I want the values of each preparationAttribute associated with a prep to appear after the count of that prep in the formatted aggregation of preps on a given label.

I’m presenting a simplified example of my problem:

  • Our mollusk collection has 2 main prep types, wet, and dry.
  • Each prep record has following relevant prepAttributes: whole, bodyOnly, and separated.
  • whole = the number of specimens in a wet preparation which have their body and shell intact.
  • bodyOnly = the number of specimens in a wet preparation which have had their shell removed (and stored dry), meaning only the soft body/bodies are stored wet
  • separated = the number of shells in the dry lot which were removed from specimens in the wet lot.
  • dry preps are always null for whole and bodyOnly,
  • wet prep are always null for separated
  • The sum of the prepAttributes always equals the count for the prep.

The desired outcome is to display the counts in the prepAttribute fields within a single string alongside the counts of preparations on the label.
e.g. “Wets - 10 (5 whole; 5 bodyOnly); Dry - 7 (5 separated)”. And to have conditional logic where there’s only 1 prepAttribute value. i.e.: “Wets - 10 whole”, if bodyOnly is null for a given record.

Since it’s not possible to create aggregated/formatted fields using text from multiple tables, table aggregation cannot be used to make the desired strings within specify itself. Currently, I’d only be able to present the Prep aggregate string: “Wets - 10; Dry - 7” and I’d have to add the prepAttribute fields individually elsewhere in the report, not within the prep aggregated string.

A related issue – to create the string I want manually in Jaspersoft, I need each preparation and prepAttribute field to be in the query linked to the label. But because of the one-to-many relationships involved, the query results contain many duplicate rows for each CO. This means the report will generate a label for each row, which is not desirable.

Is there a way around this? It seems generally useful to be able to display counts of parts of preparations on labels. I’m sure someone else must have made a report that does something similar in the past!