<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
p.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
span.EmailStyle18
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.EmailStyle19
{mso-style-type:personal-reply;
font-family:"Arial",sans-serif;
color:#1F497D;
font-weight:normal;
font-style:normal;
text-decoration:none none;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D">Hello Adrien,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D">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.
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D">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).
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D">I also have a couple of shelf lists that might be useful (they pull in Instance information), as well.
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D">SELECT T0.id AS 'ID',
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> T1.repo_code AS 'Repository',
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> T0.title AS 'Title',
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(T0.identifier,'","','-'),'[',''),']',''),'null',''),'"',''),',','') AS 'Identifier',
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> T0.accession_date AS 'Accession Date',<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D">GROUP_CONCAT(DISTINCT CONCAT(T2.number, ' ', T3.value) ORDER BY CONCAT(T2.number, ' ', T3.value) DESC SEPARATOR ', ') AS 'Extent',<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D">GROUP_CONCAT(DISTINCT T2.container_summary ORDER BY T2.container_summary DESC SEPARATOR '; ') AS 'Container Summary',
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> GROUP_CONCAT(DISTINCT T2.dimensions ORDER BY T2.dimensions DESC SEPARATOR '; ') AS 'Dimensions',
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> GROUP_CONCAT(DISTINCT T5.sort_name ORDER BY T5.sort_name DESC SEPARATOR '; ') AS 'Person',
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> GROUP_CONCAT(DISTINCT T7.sort_name ORDER BY T7.sort_name DESC SEPARATOR '; ') AS 'Corporate Body',
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D">GROUP_CONCAT(DISTINCT T8.sort_name ORDER BY T8.sort_name DESC SEPARATOR ', ') AS 'Family',
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> GROUP_CONCAT(T6.value SEPARATOR ', ') AS 'Agent Role',<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> T0.suppressed AS 'accessionSuppressed',
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> T0.publish AS 'Published?',<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> T0.created_by AS 'Created by',
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> T0.last_modified_by AS 'Modified by',
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> CONVERT_TZ(T0.create_time, '+00:00', '-5:00') AS 'Creation time',
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D">CONVERT_TZ(T0.system_mtime, '+00:00', '-5:00') AS 'Modification time'
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> FROM archivesspace.accession T0
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> LEFT JOIN archivesspace. linked_agents_rlshp T4
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> ON T4.accession_id = T0.id
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> LEFT JOIN archivesspace.name_person T5
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> ON T4.agent_person_id=T5.agent_person_id AND T5.authorized = '1'<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> LEFT JOIN archivesspace.enumeration_value T6
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> ON T4.role_id=T6.id
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> LEFT JOIN archivesspace.name_corporate_entity T7
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> ON T4.agent_corporate_entity_id=T7.agent_corporate_entity_id AND T7.authorized = '1'<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> LEFT JOIN archivesspace.name_family T8<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> ON T4.agent_family_id=T8.agent_family_id AND T8.authorized='1'<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> LEFT JOIN archivesspace.repository T1
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> ON T0.repo_id=T1.id
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> LEFT JOIN archivesspace.extent T2
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> ON T0.id=T2.accession_id
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> LEFT JOIN enumeration_value T3
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> ON T2.extent_type_id=T3.id
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> GROUP BY T0.ID
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> ORDER BY T0.create_time DESC<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D">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.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D">SELECT
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> CONCAT(T9.value, " ", T6.indicator) AS `Container`,
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> T8.component_id AS `Level`,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> T8.display_string AS `Component Title`,
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> T8.publish AS `Publish?`<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> FROM archivesspace.archival_object T8
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> LEFT OUTER JOIN archivesspace.instance T1
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> ON T1.archival_object_id=T8.id
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> LEFT JOIN archivesspace.sub_container T2
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> ON T1.id = T2.instance_id
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> LEFT JOIN archivesspace.top_container_link_rlshp T3<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> ON T2.id = T3.sub_container_id<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> LEFT OUTER JOIN archivesspace.top_container T6
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> ON T3.top_container_id = T6.id
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> LEFT JOIN archivesspace.top_container_housed_at_rlshp T4
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> ON T4.top_container_id = T6.id<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> LEFT JOIN archivesspace.location T5
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> ON T4.location_id = T5.id<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> LEFT JOIN archivesspace.enumeration_value T9
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> ON T6.type_id=T9.id
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> WHERE T8.root_record_id=?<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"> ORDER by T8.publish, T9.value, T8.component_id ASC<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D">Karen<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<div>
<p class="MsoNormal" style="text-autospace:none"><b><span style="font-family:"Arial",sans-serif;color:#272323">Karen D. Miller<o:p></o:p></span></b></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:black">Monographic Cataloger/Metadata Specialist<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-family:"Arial",sans-serif;color:#272323">Northwestern University Libraries<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-family:"Arial",sans-serif;color:#4E2A85">Northwestern University<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-family:"Arial",sans-serif;color:black">1970 Campus Drive<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-family:"Arial",sans-serif;color:black">Evanston, IL 60208<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-family:"Arial",sans-serif;color:#272323">www.library.northwestern.edu<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span style="font-family:"Arial",sans-serif;color:black">k-miller3@northwestern.edu<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#272323">874.467.3462</span><span style="color:#1F497D"><o:p></o:p></span></p>
</div>
<p class="MsoNormal"><span style="font-family:"Arial",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b>From:</b> archivesspace_users_group-bounces@lyralists.lyrasis.org <archivesspace_users_group-bounces@lyralists.lyrasis.org>
<b>On Behalf Of </b>Hilton, Adrien<br>
<b>Sent:</b> Monday, November 09, 2020 1:24 PM<br>
<b>To:</b> Archivesspace Users Group <archivesspace_users_group@lyralists.lyrasis.org><br>
<b>Subject:</b> [Archivesspace_Users_Group] Developing custom reports<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Dear Colleagues, <o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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:<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Resource record level data: <a href="https://urldefense.com/v3/__https:/docs.google.com/document/d/1ndDKXuE27rKdfOycPjCODq6UVOKGlqY4ns3x_S4aFcM/edit?usp=sharing__;!!Dq0X2DkFhyF93HkjWTBQKhk!FfIDUyeznz-k708gPKJHR0O0BIN_a66u3BPAaxuCE7ZPf_TL8c0v5nAGVJo8vUNT3vZ2qaD1$">
https://docs.google.com/document/d/1ndDKXuE27rKdfOycPjCODq6UVOKGlqY4ns3x_S4aFcM/edit?usp=sharing</a><o:p></o:p></p>
<p class="MsoNormal">Accession record level data: <a href="https://urldefense.com/v3/__https:/docs.google.com/document/d/1XxddO8KDJdY97q6g9IzLffbnxjP8ei1U9BpErKP3NZA/edit?usp=sharing__;!!Dq0X2DkFhyF93HkjWTBQKhk!FfIDUyeznz-k708gPKJHR0O0BIN_a66u3BPAaxuCE7ZPf_TL8c0v5nAGVJo8vUNT3vRoZgde$">
https://docs.google.com/document/d/1XxddO8KDJdY97q6g9IzLffbnxjP8ei1U9BpErKP3NZA/edit?usp=sharing</a><o:p></o:p></p>
<p class="MsoNormal">Archival object data: <a href="https://urldefense.com/v3/__https:/docs.google.com/document/d/1D9H31AoPtqmShf14EnrPoNgTimqzOTQa2GtcTcJBn1M/edit?usp=sharing__;!!Dq0X2DkFhyF93HkjWTBQKhk!FfIDUyeznz-k708gPKJHR0O0BIN_a66u3BPAaxuCE7ZPf_TL8c0v5nAGVJo8vUNT3rO84NLj$">
https://docs.google.com/document/d/1D9H31AoPtqmShf14EnrPoNgTimqzOTQa2GtcTcJBn1M/edit?usp=sharing</a><o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">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.
<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">First question: has anyone already written reports similar to these three?<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Second question: is there any interest in collaborating to develop these?<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Thanks in advance, <o:p></o:p></p>
<p class="MsoNormal">Adrien<o:p></o:p></p>
</div>
</body>
</html>