[Archivesspace_Users_Group] Developing custom reports

[Karen Miller]

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-miller3 at northwestern.edu
874.467.3462

From: archivesspace_users_group-bounces at lyralists.lyrasis.org <archivesspace_users_group-bounces at lyralists.lyrasis.org> On Behalf Of Hilton, Adrien
Sent: Monday, November 09, 2020 1:24 PM
To: Archivesspace Users Group <archivesspace_users_group at 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lyralists.lyrasis.org/pipermail/archivesspace_users_group/attachments/20201109/3bc1bd54/attachment.html>