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
, anddry
. - Each prep record has following relevant prepAttributes:
whole
,bodyOnly
, andseparated
. whole
= the number of specimens in awet
preparation which have their body and shell intact.bodyOnly
= the number of specimens in awet
preparation which have had their shell removed (and storeddry
), meaning only the soft body/bodies are storedwet
separated
= the number of shells in the dry lot which were removed from specimens in the wet lot.dry
preps are always null forwhole
andbodyOnly
,wet
prep are always null forseparated
- 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!