From 71ff93a6c260c4dd59431068775528de26937dc8 Mon Sep 17 00:00:00 2001
From: Michael Ritter <mikejritter@users.noreply.github.com>
Date: Thu, 22 Jun 2023 19:41:09 -0600
Subject: [PATCH] Aggregation for repeating fields in reports (#357)

---
 .../src/main/resources/accessions.jrxml       |  27 ++-
 .../src/main/resources/deaccessions.jrxml     |  17 +-
 .../src/main/resources/deed_of_gift.jrxml     |   5 +-
 .../src/main/resources/referral.jrxml         |  27 ++-
 .../resources/tombstone_with_budget.jrxml     | 162 +++++++++++++-----
 .../resources/tombstone_with_creator.jrxml    |  63 +++----
 6 files changed, 204 insertions(+), 97 deletions(-)

diff --git a/services/report/3rdparty/jasper-cs-report/src/main/resources/accessions.jrxml b/services/report/3rdparty/jasper-cs-report/src/main/resources/accessions.jrxml
index ddd81e58e..fa6c34fa7 100644
--- a/services/report/3rdparty/jasper-cs-report/src/main/resources/accessions.jrxml
+++ b/services/report/3rdparty/jasper-cs-report/src/main/resources/accessions.jrxml
@@ -19,7 +19,7 @@
 	<style name="Column header" fontName="SansSerif" fontSize="12" isBold="true"/>
 	<style name="Detail" fontName="SansSerif" fontSize="12"/>
 	<parameter name="deurnfields" class="java.lang.String" isForPrompting="false">
-		<defaultValueExpression><![CDATA["approvalgroup,approvalindividual,acquisitionsource,approvalstatus"]]></defaultValueExpression>
+		<defaultValueExpression><![CDATA["acquisitionsource"]]></defaultValueExpression>
 	</parameter>
 	<parameter name="tenantid" class="java.lang.String" isForPrompting="false">
 		<defaultValueExpression><![CDATA["1"]]></defaultValueExpression>
@@ -39,7 +39,6 @@
     acq.creditline,
     acq.acquisitionprovisos,
     source.item AS acquisitionsource,
-    appgroup.approvalgroup, appgroup.approvalindividual, appgroup.approvalstatus, appgroup.approvaldate,
     acd_sdg.datedisplaydate AS accessiondate,
     acdl_sdg.datedisplaydate AS acquisitiondate
   FROM acquisitions_common acq
@@ -47,13 +46,22 @@
   INNER JOIN collectionspace_core core ON misc.id = core.id AND core.tenantid = $P{tenantid}
   INNER JOIN hierarchy hier ON acq.id = hier.id
   LEFT JOIN acquisitions_common_acquisitionsources source ON source.id = acq.id AND source.pos = 0
-  LEFT JOIN hierarchy app_hier ON app_hier.parentid = acq.id AND app_hier.primarytype = 'approvalGroup' AND app_hier.pos = 0
-  LEFT JOIN approvalgroup appgroup ON appgroup.id = app_hier.id
   LEFT JOIN hierarchy acd_hier ON acd_hier.parentid = acq.id AND acd_hier.name = 'acquisitions_common:accessionDateGroup'
   LEFT JOIN structureddategroup acd_sdg ON acd_sdg.id = acd_hier.id
   LEFT JOIN hierarchy adgl_hier ON adgl_hier.parentid = acq.id AND adgl_hier.name = 'acquisitions_common:acquisitionDateGroupList' AND adgl_hier.pos = 0
   LEFT JOIN structureddategroup acdl_sdg ON acdl_sdg.id = adgl_hier.id
 	$P!{whereclause}
+), approvals AS (
+  SELECT
+    acq.csid AS acquisitioncsid,
+    string_agg(regexp_replace(approval.approvalgroup, '^.*\)''(.*)''$', '\1'), '|') AS approvalgroup,
+    string_agg(regexp_replace(approval.approvalindividual, '^.*\)''(.*)''$', '\1'), '|') AS approvalindividual,
+    string_agg(regexp_replace(approval.approvalstatus, '^.*\)''(.*)''$', '\1'), '|') AS approvalstatus,
+    string_agg(to_char(approval.approvaldate, 'YYYY-MM-DD'), '|') AS approvaldate
+  FROM acquisitions acq
+  INNER JOIN hierarchy app_hier ON app_hier.parentid = acq.id AND app_hier.primarytype = 'approvalGroup'
+  INNER JOIN approvalgroup approval ON approval.id = app_hier.id
+  GROUP BY acq.csid
 ), related_objects AS (
   SELECT
     hier.name AS csid,
@@ -88,10 +96,10 @@ SELECT
   acq.acquisitiondate,
   acq.acquisitionmethod,
   acq.acquisitionsource,
-  acq.approvalgroup,
-  acq.approvalindividual,
-  acq.approvalstatus,
-  acq.approvaldate,
+  approval.approvalgroup,
+  approval.approvalindividual,
+  approval.approvalstatus,
+  approval.approvaldate,
   acq.acquisitionnote,
   acq.acquisitionprovisos,
   acq.creditline,
@@ -103,6 +111,7 @@ SELECT
   obj.objecthistorynote,
   media.mediacsid
 FROM acquisitions acq
+LEFT JOIN approvals approval ON approval.acquisitioncsid = acq.csid
 LEFT JOIN related_objects obj ON obj.acquisitioncsid = acq.csid
 LEFT JOIN related_media media ON media.objcsid = obj.csid]]>
 	</queryString>
@@ -146,7 +155,7 @@ LEFT JOIN related_media media ON media.objcsid = obj.csid]]>
 		<property name="com.jaspersoft.studio.field.label" value="approvalstatus"/>
 		<property name="com.jaspersoft.studio.field.tree.path" value="approvalgroup"/>
 	</field>
-	<field name="approvaldate" class="java.sql.Timestamp">
+	<field name="approvaldate" class="java.lang.String">
 		<property name="com.jaspersoft.studio.field.name" value="approvaldate"/>
 		<property name="com.jaspersoft.studio.field.label" value="approvaldate"/>
 		<property name="com.jaspersoft.studio.field.tree.path" value="approvalgroup"/>
diff --git a/services/report/3rdparty/jasper-cs-report/src/main/resources/deaccessions.jrxml b/services/report/3rdparty/jasper-cs-report/src/main/resources/deaccessions.jrxml
index 237840cb4..0fea9e71b 100644
--- a/services/report/3rdparty/jasper-cs-report/src/main/resources/deaccessions.jrxml
+++ b/services/report/3rdparty/jasper-cs-report/src/main/resources/deaccessions.jrxml
@@ -10,7 +10,7 @@
 	<style name="Column header" fontName="SansSerif" fontSize="12" isBold="true"/>
 	<style name="Detail" fontName="SansSerif" fontSize="12"/>
 	<parameter name="deurnfields" class="java.lang.String" isForPrompting="false">
-		<defaultValueExpression><![CDATA["deaccessionapprovalgroup,deaccessionapprovalindividual,deaccessionapprovalstatus,disposalmethod"]]></defaultValueExpression>
+		<defaultValueExpression><![CDATA["disposalmethod"]]></defaultValueExpression>
 	</parameter>
 	<parameter name="tenantid" class="java.lang.String" isForPrompting="false">
 		<defaultValueExpression><![CDATA["1"]]></defaultValueExpression>
@@ -75,14 +75,15 @@
 ), approvals AS (
   SELECT
     exit.csid as exitcsid,
-    approval.deaccessionapprovalgroup,
-    approval.deaccessionapprovalindividual,
-    approval.deaccessionapprovalstatus,
-    approval.deaccessionapprovaldate,
-    approval.deaccessionapprovalnote
+    string_agg(regexp_replace(approval.deaccessionapprovalgroup, '^.*\)''(.*)''$', '\1'), '|') AS deaccessionapprovalgroup,
+    string_agg(regexp_replace(approval.deaccessionapprovalindividual, '^.*\)''(.*)''$', '\1'), '|') AS deaccessionapprovalindividual,
+    string_agg(regexp_replace(approval.deaccessionapprovalstatus, '^.*\)''(.*)''$', '\1'), '|') AS deaccessionapprovalstatus,
+    string_agg(to_char(approval.deaccessionapprovaldate, 'YYYY-MM-DD'), '|') AS deaccessionapprovaldate,
+    string_agg(approval.deaccessionapprovalnote, '|') AS deaccessionapprovalnote
   FROM objectexits exit
   INNER JOIN hierarchy hier on hier.parentid = exit.id AND hier.primarytype = 'deacApprovalGroup'
   INNER JOIN deacapprovalgroup approval on approval.id = hier.id
+  GROUP BY exit.csid
 ), related_objects AS (
   SELECT
     hier.name AS csid,
@@ -216,7 +217,7 @@ LEFT JOIN related_intakes intake ON intake.objcsid = obj.csid]]>
 		<property name="com.jaspersoft.studio.field.label" value="deaccessionapprovalstatus"/>
 		<property name="com.jaspersoft.studio.field.tree.path" value="deacapprovalgroup"/>
 	</field>
-	<field name="deaccessionapprovaldate" class="java.sql.Timestamp">
+	<field name="deaccessionapprovaldate" class="java.lang.String">
 		<property name="com.jaspersoft.studio.field.name" value="deaccessionapprovaldate"/>
 		<property name="com.jaspersoft.studio.field.label" value="deaccessionapprovaldate"/>
 		<property name="com.jaspersoft.studio.field.tree.path" value="deacapprovalgroup"/>
@@ -759,7 +760,7 @@ LEFT JOIN related_intakes intake ON intake.objcsid = obj.csid]]>
 				<reportElement style="Detail" x="900" y="0" width="100" height="30" uuid="7f3c6b34-7d67-40a9-941d-9ab1bac2c0e6">
 					<property name="com.jaspersoft.studio.unit.y" value="px"/>
 				</reportElement>
-				<textFieldExpression><![CDATA[$F{deaccessionapprovaldate}]]></textFieldExpression>
+				<textFieldExpression><![CDATA[$F{deaccessionapprovalnote}]]></textFieldExpression>
 			</textField>
 			<textField textAdjust="StretchHeight" isBlankWhenNull="true">
 				<reportElement style="Detail" x="1000" y="0" width="100" height="30" uuid="427c6ba1-a7b7-4053-91f8-a968e2578786">
diff --git a/services/report/3rdparty/jasper-cs-report/src/main/resources/deed_of_gift.jrxml b/services/report/3rdparty/jasper-cs-report/src/main/resources/deed_of_gift.jrxml
index 5cdc6ecd9..1d0cf9011 100644
--- a/services/report/3rdparty/jasper-cs-report/src/main/resources/deed_of_gift.jrxml
+++ b/services/report/3rdparty/jasper-cs-report/src/main/resources/deed_of_gift.jrxml
@@ -38,17 +38,18 @@
 	$P!{whereclause}
 ), person_owners AS (
   SELECT ag.*,
-    primaryterm.termdisplayname as primarydisplayname,
+    string_agg(primaryterm.termdisplayname, '|') as primarydisplayname,
     intake.csid as intakecsid
   FROM intakes intake
   INNER JOIN intakes_common_currentowners co ON co.id = intake.id AND co.pos = 0
   INNER JOIN persons_common person ON person.refname = co.item
   INNER JOIN hierarchy hier ON hier.id = person.id
-  INNER JOIN hierarchy term_hier ON term_hier.parentid = person.id AND term_hier.primarytype = 'personTermGroup' AND term_hier.pos = 0
+  INNER JOIN hierarchy term_hier ON term_hier.parentid = person.id AND term_hier.primarytype = 'personTermGroup' AND (term_hier.pos = 0 OR term_hier.pos = 1)
   INNER JOIN persontermgroup primaryterm ON primaryterm.id = term_hier.id
   LEFT JOIN contacts_common contact ON contact.initem = hier.name
   LEFT JOIN hierarchy addr_hier ON addr_hier.parentid = contact.id AND addr_hier.primarytype = 'addressGroup' AND addr_hier.pos = 0
   LEFT JOIN addressgroup ag ON ag.id = addr_hier.id
+  GROUP BY intake.csid, ag.addressplace1, ag.addressplace2, ag.id, ag.addresscountry, ag.addressmunicipality, ag.addresspostcode, ag.addressstateorprovince, ag.addresstype
 ), related_objects AS (
   SELECT object.collection,
     ong.objectname,
diff --git a/services/report/3rdparty/jasper-cs-report/src/main/resources/referral.jrxml b/services/report/3rdparty/jasper-cs-report/src/main/resources/referral.jrxml
index 8fa0ca0b4..c905b692b 100644
--- a/services/report/3rdparty/jasper-cs-report/src/main/resources/referral.jrxml
+++ b/services/report/3rdparty/jasper-cs-report/src/main/resources/referral.jrxml
@@ -10,7 +10,7 @@
   <style name="Column header" fontName="SansSerif" fontSize="12" isBold="true"/>
   <style name="Detail" fontName="SansSerif" fontSize="12"/>
   <parameter name="deurnfields" class="java.lang.String" isForPrompting="false">
-    <defaultValueExpression><![CDATA["entrymethod,approvalgroup,approvalindividual,approvalstatus,currentlocation"]]></defaultValueExpression>
+    <defaultValueExpression><![CDATA["entrymethod,currentlocation"]]></defaultValueExpression>
   </parameter>
   <parameter name="tenantid" class="java.lang.String" isForPrompting="false">
     <defaultValueExpression><![CDATA["1"]]></defaultValueExpression>
@@ -29,8 +29,8 @@
   approval.approvalgroup,
   approval.approvalindividual,
   approval.approvalstatus,
-  approval.approvaldate AS approvaldate,
-  approval.approvalnote AS approvalnote,
+  approval.approvaldate,
+  approval.approvalnote,
   currentloc.currentlocation,
   person_term.termdisplayname,
   address.addresstype,
@@ -52,8 +52,6 @@ INNER JOIN hierarchy hier on hier.id = intake.id
 INNER JOIN misc on misc.id = hier.id AND misc.lifecyclestate != 'deleted'
 INNER JOIN collectionspace_core core on core.id = misc.id AND core.tenantid = $P{tenantid}
 LEFT JOIN intakes_common_entrymethods entrymethod ON entrymethod.id = intake.id AND entrymethod.pos = 0
-LEFT JOIN hierarchy approval_hier ON approval_hier.parentid = intake.id AND approval_hier.primarytype = 'approvalGroup' AND approval_hier.pos = 0
-LEFT JOIN approvalgroup approval ON approval.id = approval_hier.id
 LEFT JOIN hierarchy location_hier ON location_hier.parentid = intake.id AND location_hier.primarytype = 'currentLocationGroup' AND location_hier.pos = 0
 LEFT JOIN currentlocationgroup currentloc ON currentloc.id = location_hier.id
 -- owner display name and address
@@ -65,6 +63,20 @@ LEFT JOIN hierarchy addr_hier ON addr_hier.parentid = contact.id AND addr_hier.p
 LEFT JOIN addressgroup address ON address.id = addr_hier.id
 LEFT JOIN hierarchy term_hier ON term_hier.parentid = person.id AND term_hier.primarytype = 'personTermGroup' AND term_hier.pos = 0
 LEFT JOIN persontermgroup person_term ON person_term.id = term_hier.id
+-- approval, aggregated
+LEFT JOIN (
+  SELECT
+    approval_hier.parentid,
+    string_agg(regexp_replace(approval.approvalgroup, '^.*\)''(.*)''$', '\1'), '|') AS approvalgroup,
+    string_agg(regexp_replace(approval.approvalindividual, '^.*\)''(.*)''$', '\1'), '|') AS approvalindividual,
+    string_agg(regexp_replace(approval.approvalstatus, '^.*\)''(.*)''$', '\1'), '|') AS approvalstatus,
+    string_agg(to_char(approval.approvaldate, 'YYYY-MM-DD'), '|') AS approvaldate,
+    string_agg(approval.approvalnote, '|') AS approvalnote
+  FROM hierarchy approval_hier
+  INNER JOIN approvalgroup approval ON approval.id = approval_hier.id
+  WHERE approval_hier.primarytype = 'approvalGroup'
+  GROUP BY approval_hier.parentid
+) approval ON approval.parentid = intake.id
 -- related obj
 LEFT JOIN (
   SELECT
@@ -95,7 +107,8 @@ LEFT JOIN (
   INNER JOIN misc ON misc.id = relation.id AND misc.lifecyclestate != 'deleted'
   WHERE relation.objectdocumenttype = 'Media' AND relation.subjectdocumenttype = 'CollectionObject'
 ) media ON media.subjectcsid = objects.csid
-$P!{whereclause}]]>
+$P!{whereclause}
+]]>
   </queryString>
   <field name="entrynumber" class="java.lang.String">
     <property name="com.jaspersoft.studio.field.name" value="entrynumber"/>
@@ -137,7 +150,7 @@ $P!{whereclause}]]>
     <property name="com.jaspersoft.studio.field.label" value="approvalstatus"/>
     <property name="com.jaspersoft.studio.field.tree.path" value="approvalgroup"/>
   </field>
-  <field name="approvaldate" class="java.sql.Timestamp">
+  <field name="approvaldate" class="java.lang.String">
     <property name="com.jaspersoft.studio.field.name" value="approvaldate"/>
     <property name="com.jaspersoft.studio.field.label" value="approvaldate"/>
     <property name="com.jaspersoft.studio.field.tree.path" value="approvalgroup"/>
diff --git a/services/report/3rdparty/jasper-cs-report/src/main/resources/tombstone_with_budget.jrxml b/services/report/3rdparty/jasper-cs-report/src/main/resources/tombstone_with_budget.jrxml
index 509806719..5f770a6da 100644
--- a/services/report/3rdparty/jasper-cs-report/src/main/resources/tombstone_with_budget.jrxml
+++ b/services/report/3rdparty/jasper-cs-report/src/main/resources/tombstone_with_budget.jrxml
@@ -9,9 +9,6 @@
 	<property name="com.jaspersoft.studio.property.dataset.dialog.DatasetDialog.sash.w2" value="361"/>
 	<style name="Column header" fontName="SansSerif" fontSize="12" isBold="true"/>
 	<style name="Detail" fontName="SansSerif" fontSize="12"/>
-	<parameter name="deurnfields" class="java.lang.String" isForPrompting="false">
-		<defaultValueExpression><![CDATA["collection,owner,installationtype,worktype,material,creator,acquisitionfundingcurrency"]]></defaultValueExpression>
-	</parameter>
 	<parameter name="tenantid" class="java.lang.String" isForPrompting="false">
 		<defaultValueExpression><![CDATA["5000"]]></defaultValueExpression>
 	</parameter>
@@ -23,7 +20,8 @@
 		<defaultValueExpression><![CDATA[$P{csids} != "NOVALUE" ? ( "WHERE hier.name IN (" + $P{csids} + ")" ) : ""]]></defaultValueExpression>
 	</parameter>
 	<queryString language="SQL">
-		<![CDATA[WITH artists AS (SELECT
+		<![CDATA[WITH artists AS (
+  SELECT
     artist_hier.parentid,
     artist_hier.id,
     artist.*
@@ -33,64 +31,144 @@
   INNER JOIN publicartproductionpersongroup artist ON artist.publicartproductionpersonrole = item.refname
   INNER JOIN hierarchy artist_hier ON artist_hier.id = artist.id
   WHERE vocab.shortidentifier = 'prodpersonrole' AND item.shortidentifier IN ('artist')
+), objects AS (
+  SELECT
+    obj.id,
+    obj.objectnumber,
+    hier.name AS objcsid
+  FROM collectionobjects_common obj
+  INNER JOIN hierarchy hier on hier.id = obj.id
+  INNER JOIN misc on misc.id = hier.id AND misc.lifecyclestate != 'deleted'
+  INNER JOIN collectionspace_core core on core.id = misc.id AND core.tenantid = $P{tenantid}
+	$P!{whereclause}
+), titles AS (
+ SELECT
+  obj.objcsid,
+  string_agg(title.title, '|') AS title
+  FROM objects obj
+  LEFT JOIN hierarchy title_hier ON title_hier.parentid = obj.id AND title_hier.primarytype = 'titleGroup'
+  LEFT JOIN titlegroup title ON title.id = title_hier.id
+  GROUP BY obj.objcsid
+), descriptions AS (
+ SELECT
+  obj.objcsid,
+  string_agg(bd.item, '|') AS briefdescription
+  FROM objects obj
+  LEFT JOIN collectionobjects_common_briefdescriptions bd ON bd.id = obj.id
+  GROUP BY obj.objcsid
+), creators AS (
+ SELECT
+  obj.objcsid,
+  string_agg(regexp_replace(creator.publicartproductionperson, '^.*\)''(.*)''$', '\1'), '|') AS creator
+  FROM objects obj
+  INNER JOIN hierarchy creator_hier ON creator_hier.parentid = obj.id AND creator_hier.primarytype = 'publicartProductionPersonGroup'
+  INNER JOIN publicartproductionpersongroup creator ON creator.id = creator_hier.id
+  GROUP BY obj.objcsid
+), collections AS (
+ SELECT
+  obj.objcsid,
+  string_agg(regexp_replace(coll.item, '^.*\)''(.*)''$', '\1'), '|') AS collection
+  FROM objects obj
+  INNER JOIN collectionobjects_publicart_publicartcollections coll ON coll.id = obj.id
+  GROUP BY obj.objcsid
+), owners AS (
+ SELECT
+  obj.objcsid,
+  string_agg(regexp_replace(owner.item, '^.*\)''(.*)''$', '\1'), '|') AS owner
+  FROM objects obj
+  INNER JOIN collectionobjects_common_owners owner ON owner.id = obj.id
+  GROUP BY obj.objcsid
+), installations AS (
+ SELECT
+  obj.objcsid,
+  string_agg(regexp_replace(inventory.item, '^.*\)''(.*)''$', '\1'), '|') AS installationtype
+  FROM objects obj
+  INNER JOIN collectionobjects_common_inventorystatuslist inventory ON inventory.id = obj.id
+  GROUP BY obj.objcsid
+), worktypes AS (
+ SELECT
+  obj.objcsid,
+  string_agg(regexp_replace(namegroup.objectname, '^.*\)''(.*)''$', '\1'), '|') AS worktype
+  FROM objects obj
+  INNER JOIN hierarchy namegroup_hier on namegroup_hier.parentid = obj.id and namegroup_hier.primarytype = 'objectNameGroup'
+  INNER JOIN objectnamegroup namegroup on namegroup.id = namegroup_hier.id
+  GROUP BY obj.objcsid
+), materials AS (
+ SELECT
+  obj.objcsid,
+  string_agg(regexp_replace(material.material, '^.*\)''(.*)''$', '\1'), '|') AS material
+  FROM objects obj
+  INNER JOIN hierarchy material_hier on material_hier.parentid = obj.id and material_hier.primarytype = 'materialGroup'
+  INNER JOIN materialgroup material on material.id = material_hier.id
+  GROUP BY obj.objcsid
+), dimensions AS (
+ SELECT
+  obj.objcsid,
+  string_agg(dimension.dimension, '|') AS dimension,
+  string_agg(dimension.value::text, '|') AS dimensionvalue,
+  string_agg(dimension.measurementunit, '|') AS dimensionunit
+  FROM objects obj
+  INNER JOIN hierarchy measured_hier on measured_hier.parentid = obj.id and measured_hier.primarytype = 'measuredPartGroup'
+  INNER JOIN hierarchy dimension_hier on dimension_hier.parentid = measured_hier.id and dimension_hier.primarytype = 'dimensionSubGroup'
+  LEFT JOIN dimensionsubgroup dimension on dimension.id = dimension_hier.id
+  GROUP BY obj.objcsid
+), dates AS (
+ SELECT
+  obj.objcsid,
+  string_agg(sd.datedisplaydate, '|') AS artworkdate
+  FROM objects obj
+  INNER JOIN hierarchy pdg_hier on pdg_hier.parentid = obj.id AND pdg_hier.primarytype = 'publicartProductionDateGroup'
+  INNER JOIN hierarchy sdg_hier on sdg_hier.parentid = pdg_hier.id AND sdg_hier.primarytype = 'structuredDateGroup'
+  INNER JOIN structureddategroup sd on sd.id = sdg_hier.id
+  GROUP BY obj.objcsid
 )
 SELECT
   obj.objectnumber,
   title.title,
-  creator.publicartproductionperson AS creator,
-  bd.item as briefdescription,
-  coll.item AS collection,
-  owner.item AS owner,
-  inventory.item AS installationtype,
-  namegroup.objectname AS worktype,
+  bd.briefdescription,
+  creator.creator,
+  collection.collection,
+  owner.owner,
+  installation.installationtype,
+  worktype.worktype,
   material.material,
   dimension.dimension,
-  dimension.value AS dimensionvalue,
-  dimension.measurementunit AS dimensionunit,
-  sd.datedisplaydate AS artworkdate,
+  dimension.dimensionvalue,
+  dimension.dimensionunit,
+  date.artworkdate,
   media.objectcsid AS mediacsid,
   acquisition.acquisitionfundingcurrency,
   acquisition.acquisitionfundingvalue
-FROM collectionobjects_common obj
-INNER JOIN hierarchy hier ON hier.id = obj.id
-INNER JOIN misc ON misc.id = hier.id AND misc.lifecyclestate != 'deleted'
-INNER JOIN collectionspace_core core ON core.id = misc.id AND core.tenantid = $P{tenantid}
-LEFT JOIN hierarchy title_hier ON title_hier.parentid = obj.id AND title_hier.primarytype = 'titleGroup' AND title_hier.pos = 0
-LEFT JOIN titlegroup title ON title.id = title_hier.id
-LEFT JOIN collectionobjects_publicart_publicartcollections coll ON coll.id = obj.id AND coll.pos = 0
-LEFT JOIN artists creator ON creator.parentid = obj.id
-LEFT JOIN collectionobjects_common_briefdescriptions bd ON bd.id = obj.id AND bd.pos = 0
-LEFT JOIN collectionobjects_common_owners owner ON owner.id = obj.id AND owner.pos = 0
-LEFT JOIN collectionobjects_common_inventorystatuslist inventory ON inventory.id = obj.id AND inventory.pos = 0
-LEFT JOIN hierarchy namegroup_hier ON namegroup_hier.parentid = obj.id AND namegroup_hier.primarytype = 'objectNameGroup' AND namegroup_hier.pos = 0
-LEFT JOIN objectnamegroup namegroup ON namegroup.id = namegroup_hier.id
-LEFT JOIN hierarchy material_hier ON material_hier.parentid = obj.id AND material_hier.primarytype = 'materialGroup' AND material_hier.pos = 0
-LEFT JOIN materialgroup material ON material.id = material_hier.id
-LEFT JOIN hierarchy measured_hier ON measured_hier.parentid = obj.id AND measured_hier.primarytype = 'measuredPartGroup' AND measured_hier.pos = 0
-LEFT JOIN hierarchy dimension_hier ON dimension_hier.parentid = measured_hier.id AND dimension_hier.primarytype = 'dimensionSubGroup' AND dimension_hier.pos = 0
-LEFT JOIN dimensionsubgroup dimension ON dimension.id = dimension_hier.id
-LEFT JOIN hierarchy pdg_hier ON pdg_hier.parentid = obj.id AND pdg_hier.primarytype = 'publicartProductionDateGroup' AND pdg_hier.pos = 0
-LEFT JOIN hierarchy sdg_hier ON sdg_hier.parentid = pdg_hier.id AND sdg_hier.primarytype = 'structuredDateGroup'
-LEFT JOIN structureddategroup sd ON sd.id = sdg_hier.id
+FROM objects obj
+LEFT JOIN titles title on title.objcsid = obj.objcsid
+LEFT JOIN descriptions bd on bd.objcsid = obj.objcsid
+LEFT JOIN creators creator on creator.objcsid = obj.objcsid
+LEFT JOIN collections collection on collection.objcsid = obj.objcsid
+LEFT JOIN owners owner on owner.objcsid = obj.objcsid
+LEFT JOIN installations installation on installation.objcsid = obj.objcsid
+LEFT JOIN worktypes worktype on worktype.objcsid = obj.objcsid
+LEFT JOIN materials material on material.objcsid = obj.objcsid
+LEFT JOIN dimensions dimension on dimension.objcsid = obj.objcsid
+LEFT JOIN dates date on date.objcsid = obj.objcsid
 LEFT JOIN (
   SELECT relation.*
   FROM relations_common relation
   INNER JOIN misc ON misc.id = relation.id AND misc.lifecyclestate != 'deleted'
   WHERE relation.objectdocumenttype = 'Media' AND relation.subjectdocumenttype = 'CollectionObject'
-) media ON media.subjectcsid = hier.name
+) media ON media.subjectcsid = obj.objcsid
 LEFT JOIN (
   SELECT
     relation.subjectcsid,
-    funding.acquisitionfundingcurrency,
-    funding.acquisitionfundingvalue
+    string_agg(regexp_replace(funding.acquisitionfundingcurrency, '^.*\)''(.*)''$', '\1'), '|') AS acquisitionfundingcurrency,
+    string_agg(funding.acquisitionfundingvalue::text, '|') AS acquisitionfundingvalue
   FROM relations_common relation
   INNER JOIN misc ON misc.id = relation.id AND misc.lifecyclestate != 'deleted'
   INNER JOIN hierarchy hier ON hier.name = relation.objectcsid
-  LEFT JOIN hierarchy funding_hier ON funding_hier.parentid = hier.id AND funding_hier.primarytype = 'acquisitionFunding' AND funding_hier.pos = 0
+  LEFT JOIN hierarchy funding_hier ON funding_hier.parentid = hier.id AND funding_hier.primarytype = 'acquisitionFunding'
   LEFT JOIN acquisitionfunding funding ON funding.id = funding_hier.id
   WHERE relation.objectdocumenttype = 'Acquisition' AND relation.subjectdocumenttype = 'CollectionObject'
-) acquisition ON acquisition.subjectcsid = hier.name
-$P!{whereclause}]]>
+  GROUP BY relation.subjectcsid
+) acquisition ON acquisition.subjectcsid = obj.objcsid]]>
 	</queryString>
 	<field name="objectnumber" class="java.lang.String">
 		<property name="com.jaspersoft.studio.field.name" value="objectnumber"/>
@@ -142,7 +220,7 @@ $P!{whereclause}]]>
 		<property name="com.jaspersoft.studio.field.label" value="dimension"/>
 		<property name="com.jaspersoft.studio.field.tree.path" value="dimensionsubgroup"/>
 	</field>
-	<field name="dimensionvalue" class="java.lang.Double">
+	<field name="dimensionvalue" class="java.lang.String">
 		<property name="com.jaspersoft.studio.field.name" value="dimensionvalue"/>
 		<property name="com.jaspersoft.studio.field.label" value="dimensionvalue"/>
 		<property name="com.jaspersoft.studio.field.tree.path" value="dimensionsubgroup"/>
@@ -167,7 +245,7 @@ $P!{whereclause}]]>
 		<property name="com.jaspersoft.studio.field.label" value="acquisitionfundingcurrency"/>
 		<property name="com.jaspersoft.studio.field.tree.path" value="acquisitionfunding"/>
 	</field>
-	<field name="acquisitionfundingvalue" class="java.lang.Double">
+	<field name="acquisitionfundingvalue" class="java.lang.String">
 		<property name="com.jaspersoft.studio.field.name" value="acquisitionfundingvalue"/>
 		<property name="com.jaspersoft.studio.field.label" value="acquisitionfundingvalue"/>
 		<property name="com.jaspersoft.studio.field.tree.path" value="acquisitionfunding"/>
diff --git a/services/report/3rdparty/jasper-cs-report/src/main/resources/tombstone_with_creator.jrxml b/services/report/3rdparty/jasper-cs-report/src/main/resources/tombstone_with_creator.jrxml
index 5a5c2cf98..131775082 100644
--- a/services/report/3rdparty/jasper-cs-report/src/main/resources/tombstone_with_creator.jrxml
+++ b/services/report/3rdparty/jasper-cs-report/src/main/resources/tombstone_with_creator.jrxml
@@ -10,7 +10,7 @@
   <style name="Column header" fontName="SansSerif" fontSize="12" isBold="true"/>
   <style name="Detail" fontName="SansSerif" fontSize="12"/>
   <parameter name="deurnfields" class="java.lang.String" isForPrompting="false">
-    <defaultValueExpression><![CDATA["creator,creatorrole,collection,owner,installationtype,worktype,material"]]></defaultValueExpression>
+    <defaultValueExpression><![CDATA["collection,owner,installationtype"]]></defaultValueExpression>
   </parameter>
   <parameter name="tenantid" class="java.lang.String" isForPrompting="false">
     <defaultValueExpression><![CDATA["5000"]]></defaultValueExpression>
@@ -26,13 +26,12 @@
     <![CDATA[SELECT
   obj.objectnumber,
   title.title,
-  creator.publicartproductionperson AS creator,
-  creator.publicartproductionpersonrole AS creatorrole,
+  creator.creator,
   bd.item as briefdescription,
   coll.item AS collection,
   owner.item AS owner,
   inventory.item AS installationtype,
-  namegroup.objectname AS worktype,
+  namegroup.worktype,
   material.material,
   dimension.dimension,
   dimension.value AS dimensionvalue,
@@ -46,21 +45,45 @@ INNER JOIN collectionspace_core core on core.id = misc.id AND core.tenantid = $P
 LEFT JOIN hierarchy title_hier ON title_hier.parentid = obj.id AND title_hier.primarytype = 'titleGroup' AND title_hier.pos = 0
 LEFT JOIN titlegroup title ON title.id = title_hier.id
 LEFT JOIN collectionobjects_publicart_publicartcollections coll ON coll.id = obj.id AND coll.pos = 0
-LEFT JOIN hierarchy creator_hier ON creator_hier.parentid = obj.id AND creator_hier.primarytype = 'publicartProductionPersonGroup'
-LEFT JOIN publicartproductionpersongroup creator ON creator.id = creator_hier.id
 LEFT JOIN collectionobjects_common_briefdescriptions bd ON bd.id = obj.id AND bd.pos = 0
 LEFT JOIN collectionobjects_common_owners owner ON owner.id = obj.id AND owner.pos = 0
 LEFT JOIN collectionobjects_common_inventorystatuslist inventory ON inventory.id = obj.id AND inventory.pos = 0
-LEFT JOIN hierarchy namegroup_hier on namegroup_hier.parentid = obj.id and namegroup_hier.primarytype = 'objectNameGroup' and namegroup_hier.pos = 0
-LEFT JOIN objectnamegroup namegroup on namegroup.id = namegroup_hier.id
-LEFT JOIN hierarchy material_hier on material_hier.parentid = obj.id and material_hier.primarytype = 'materialGroup' and material_hier.pos = 0
-LEFT JOIN materialgroup material on material.id = material_hier.id
 LEFT JOIN hierarchy measured_hier on measured_hier.parentid = obj.id and measured_hier.primarytype = 'measuredPartGroup' and measured_hier.pos = 0
 LEFT JOIN hierarchy dimension_hier on dimension_hier.parentid = measured_hier.id and dimension_hier.primarytype = 'dimensionSubGroup' and dimension_hier.pos = 0
 LEFT JOIN dimensionsubgroup dimension on dimension.id = dimension_hier.id
 LEFT JOIN hierarchy pdg_hier on pdg_hier.parentid = obj.id AND pdg_hier.primarytype = 'publicartProductionDateGroup' and pdg_hier.pos = 0
 LEFT JOIN hierarchy sdg_hier on sdg_hier.parentid = pdg_hier.id AND sdg_hier.primarytype = 'structuredDateGroup'
 LEFT JOIN structureddategroup sd on sd.id = sdg_hier.id
+LEFT JOIN (
+  SELECT
+    creator_hier.parentid,
+    string_agg(
+      regexp_replace(creator.publicartproductionperson, '^.*\)''(.*)''$', '\1') || ';' ||
+      regexp_replace(creator.publicartproductionpersonrole, '^.*\)''(.*)''$', '\1'),  '|'
+    ) AS creator
+  FROM hierarchy creator_hier
+  INNER JOIN publicartproductionpersongroup creator ON creator.id = creator_hier.id
+  WHERE creator_hier.primarytype = 'publicartProductionPersonGroup'
+  GROUP BY creator_hier.parentid
+) creator ON creator.parentid = obj.id
+LEFT JOIN (
+  SELECT
+    namegroup_hier.parentid,
+    string_agg(regexp_replace(namegroup.objectname, '^.*\)''(.*)''$', '\1'), '|') AS worktype
+  FROM hierarchy namegroup_hier
+  INNER JOIN objectnamegroup namegroup on namegroup.id = namegroup_hier.id
+  WHERE namegroup_hier.primarytype = 'objectNameGroup'
+  GROUP BY namegroup_hier.parentid
+) namegroup ON namegroup.parentid = obj.id
+LEFT JOIN (
+  SELECT
+    material_hier.parentid,
+    string_agg(regexp_replace(material.material, '^.*\)''(.*)''$', '\1'), '|') AS material
+  FROM hierarchy material_hier
+  INNER JOIN materialgroup material on material.id = material_hier.id
+  WHERE material_hier.primarytype = 'materialGroup'
+  GROUP BY material_hier.parentid
+) material ON material.parentid = obj.id
 LEFT JOIN (
   SELECT relation.subjectcsid, relation.objectcsid
   FROM relations_common relation
@@ -84,11 +107,6 @@ $P!{whereclause}]]>
     <property name="com.jaspersoft.studio.field.label" value="creator"/>
     <property name="com.jaspersoft.studio.field.tree.path" value="publicartproductionpersongroup"/>
   </field>
-  <field name="creatorrole" class="java.lang.String">
-    <property name="com.jaspersoft.studio.field.name" value="creatorrole"/>
-    <property name="com.jaspersoft.studio.field.label" value="creatorrole"/>
-    <property name="com.jaspersoft.studio.field.tree.path" value="publicartproductionpersongroup"/>
-  </field>
   <field name="briefdescription" class="java.lang.String">
     <property name="com.jaspersoft.studio.field.name" value="briefdescription"/>
     <property name="com.jaspersoft.studio.field.label" value="briefdescription"/>
@@ -180,14 +198,7 @@ $P!{whereclause}]]>
           <property name="com.jaspersoft.studio.unit.width" value="px"/>
         </reportElement>
         <textElement markup="styled"/>
-        <text><![CDATA[Creator]]></text>
-      </staticText>
-      <staticText>
-        <reportElement style="Column header" x="300" y="0" width="100" height="44" uuid="5c7f491d-2585-47ef-9d79-bd02e6bc1079">
-          <property name="com.jaspersoft.studio.unit.width" value="px"/>
-        </reportElement>
-        <textElement markup="styled"/>
-        <text><![CDATA[Creator Role]]></text>
+        <text><![CDATA[Creator and role]]></text>
       </staticText>
       <staticText>
         <reportElement style="Column header" x="400" y="0" width="100" height="44" uuid="6e5e825c-de22-47e1-89c5-af83b8822ac7">
@@ -289,12 +300,6 @@ $P!{whereclause}]]>
         </reportElement>
         <textFieldExpression><![CDATA[$F{creator}]]></textFieldExpression>
       </textField>
-      <textField textAdjust="StretchHeight" isBlankWhenNull="true">
-        <reportElement style="Detail" x="300" y="0" width="100" height="30" uuid="62fbe3a1-a1bf-4106-85bc-9350fe383626">
-          <property name="com.jaspersoft.studio.unit.y" value="px"/>
-        </reportElement>
-        <textFieldExpression><![CDATA[$F{creatorrole}]]></textFieldExpression>
-      </textField>
       <textField textAdjust="StretchHeight" isBlankWhenNull="true">
         <reportElement style="Detail" x="400" y="0" width="100" height="30" uuid="a5df8077-4ddb-44ed-a65b-942c4cbdb294">
           <property name="com.jaspersoft.studio.unit.y" value="px"/>
-- 
2.47.3