Hello Adrien,
I've got several reports that bring together multiple tables and aggregate
repeatable fields (such as Extent) using SQL. My SQL is not very pretty, but it
might be helpful for you.
Below is the SQL I use to generate a list of Accession records with aggregated
Extents and Agents and another that looks at whether any Archival Objects are
unpublished (an archivist wanted to know whether it was OK to click the
"Publish All" button for any particular Resource).
I also have a couple of shelf lists that might be useful (they pull in Instance
information), as well.
SELECT T0.id AS 'ID',
T1.repo_code AS 'Repository',
T0.title AS 'Title',
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(T0.identifier,'","','-'),'[',''),']',''),'null',''),'"',''),',','')
AS 'Identifier',
T0.accession_date AS 'Accession Date',
GROUP_CONCAT(DISTINCT CONCAT(T2.number, ' ', T3.value) ORDER BY
CONCAT(T2.number, ' ', T3.value) DESC SEPARATOR ', ') AS 'Extent',
GROUP_CONCAT(DISTINCT T2.container_summary ORDER BY T2.container_summary DESC
SEPARATOR '; ') AS 'Container Summary',
GROUP_CONCAT(DISTINCT T2.dimensions ORDER BY T2.dimensions DESC SEPARATOR
'; ') AS 'Dimensions',
GROUP_CONCAT(DISTINCT T5.sort_name ORDER BY T5.sort_name DESC SEPARATOR ';
') AS 'Person',
GROUP_CONCAT(DISTINCT T7.sort_name ORDER BY T7.sort_name DESC SEPARATOR ';
') AS 'Corporate Body',
GROUP_CONCAT(DISTINCT T8.sort_name ORDER BY T8.sort_name DESC SEPARATOR ', ')
AS 'Family',
GROUP_CONCAT(T6.value SEPARATOR ', ') AS 'Agent Role',
T0.suppressed AS 'accessionSuppressed',
T0.publish AS 'Published?',
T0.created_by AS 'Created by',
T0.last_modified_by AS 'Modified by',
CONVERT_TZ(T0.create_time, '+00:00', '-5:00') AS 'Creation time',
CONVERT_TZ(T0.system_mtime, '+00:00', '-5:00') AS 'Modification time'
FROM archivesspace.accession T0
LEFT JOIN archivesspace. linked_agents_rlshp T4
ON T4.accession_id = T0.id
LEFT JOIN archivesspace.name_person T5
ON T4.agent_person_id=T5.agent_person_id AND T5.authorized = '1'
LEFT JOIN archivesspace.enumeration_value T6
ON T4.role_id=T6.id
LEFT JOIN archivesspace.name_corporate_entity T7
ON T4.agent_corporate_entity_id=T7.agent_corporate_entity_id AND
T7.authorized = '1'
LEFT JOIN archivesspace.name_family T8
ON T4.agent_family_id=T8.agent_family_id AND T8.authorized='1'
LEFT JOIN archivesspace.repository T1
ON T0.repo_id=T1.id
LEFT JOIN archivesspace.extent T2
ON T0.id=T2.accession_id
LEFT JOIN enumeration_value T3
ON T2.extent_type_id=T3.id
GROUP BY T0.ID
ORDER BY T0.create_time DESC
This SQL prompts for a Resource ID (WHERE T8.root_record_id=?) and lists all of
the attached Archival Objects, sorted by their Publish value. It's not what
you want, but it illustrates connecting a Resource to Archival Objects and Top
Container information.
SELECT
CONCAT(T9.value, " ", T6.indicator) AS `Container`,
T8.component_id AS `Level`,
T8.display_string AS `Component Title`,
T8.publish AS `Publish?`
FROM archivesspace.archival_object T8
LEFT OUTER JOIN archivesspace.instance T1
ON T1.archival_object_id=T8.id
LEFT JOIN archivesspace.sub_container T2
ON T1.id = T2.instance_id
LEFT JOIN archivesspace.top_container_link_rlshp T3
ON T2.id = T3.sub_container_id
LEFT OUTER JOIN archivesspace.top_container T6
ON T3.top_container_id = T6.id
LEFT JOIN archivesspace.top_container_housed_at_rlshp T4
ON T4.top_container_id = T6.id
LEFT JOIN archivesspace.location T5
ON T4.location_id = T5.id
LEFT JOIN archivesspace.enumeration_value T9
ON T6.type_id=T9.id
WHERE T8.root_record_id=?
ORDER by T8.publish, T9.value, T8.component_id ASC
Karen
Karen D. Miller
Monographic Cataloger/Metadata Specialist
Northwestern University Libraries
Northwestern University
1970 Campus Drive
Evanston, IL 60208
www.library.northwestern.edu
k-mill...@northwestern.edu
874.467.3462
From: archivesspace_users_group-boun...@lyralists.lyrasis.org
<archivesspace_users_group-boun...@lyralists.lyrasis.org> On Behalf Of Hilton,
Adrien
Sent: Monday, November 09, 2020 1:24 PM
To: Archivesspace Users Group <archivesspace_users_group@lyralists.lyrasis.org>
Subject: [Archivesspace_Users_Group] Developing custom reports
Dear Colleagues,
We are considering creating three reports that are essentially data dumps of
various record types in a repository, e.g. all resource record level data; all
accession record level data; and all archival object level data associated with
a single resource record. You can see our use cases and specifications here:
Resource record level data:
https://docs.google.com/document/d/1ndDKXuE27rKdfOycPjCODq6UVOKGlqY4ns3x_S4aFcM/edit?usp=sharing<https://urldefense.com/v3/__https:/docs.google.com/document/d/1ndDKXuE27rKdfOycPjCODq6UVOKGlqY4ns3x_S4aFcM/edit?usp=sharing__;!!Dq0X2DkFhyF93HkjWTBQKhk!FfIDUyeznz-k708gPKJHR0O0BIN_a66u3BPAaxuCE7ZPf_TL8c0v5nAGVJo8vUNT3vZ2qaD1$>
Accession record level data:
https://docs.google.com/document/d/1XxddO8KDJdY97q6g9IzLffbnxjP8ei1U9BpErKP3NZA/edit?usp=sharing<https://urldefense.com/v3/__https:/docs.google.com/document/d/1XxddO8KDJdY97q6g9IzLffbnxjP8ei1U9BpErKP3NZA/edit?usp=sharing__;!!Dq0X2DkFhyF93HkjWTBQKhk!FfIDUyeznz-k708gPKJHR0O0BIN_a66u3BPAaxuCE7ZPf_TL8c0v5nAGVJo8vUNT3vRoZgde$>
Archival object data:
https://docs.google.com/document/d/1D9H31AoPtqmShf14EnrPoNgTimqzOTQa2GtcTcJBn1M/edit?usp=sharing<https://urldefense.com/v3/__https:/docs.google.com/document/d/1D9H31AoPtqmShf14EnrPoNgTimqzOTQa2GtcTcJBn1M/edit?usp=sharing__;!!Dq0X2DkFhyF93HkjWTBQKhk!FfIDUyeznz-k708gPKJHR0O0BIN_a66u3BPAaxuCE7ZPf_TL8c0v5nAGVJo8vUNT3rO84NLj$>
The out-of-the-box reports don't contain all the data we are looking for in
terms of data analysis, editing, and/or reporting purposes. We know there are
some challenging areas to accommodate with linked tables and fields that are
repeatable (e.g. extent) but are hopeful we can manage those. Given our
particular situation reports are more desirable than say read only access to
the database, which I know many people would use to get this data.
First question: has anyone already written reports similar to these three?
Second question: is there any interest in collaborating to develop these?
Thanks in advance,
Adrien
_______________________________________________
Archivesspace_Users_Group mailing list
Archivesspace_Users_Group@lyralists.lyrasis.org
http://lyralists.lyrasis.org/mailman/listinfo/archivesspace_users_group
