[Archivesspace_Users_Group] Reporting update request

Thu Jan 21 12:01:32 EST 2016

Hi Mark,

The resources we had on the project team for reports has transitioned off the project with the expectation that the community would be creating their own reports. Jasper was seen as a primary way for doing this, but there seems to be some mixed reactions to that. So, you have the SQL query and you're comfortable using Jasper...why haven't you made this into a report? What is the road block?

In regards to getting data out from the API, can do it with the aspace API (which would give you JSON) but it's also actually pretty easy to do with the Solr API.

The query :

http://sandbox.archivesspace.org/advanced_search?advanced=true&dop1=greater_than&dop2=lesser_than&f0=keyword&f1=create_time&f2=create_time&f3=suppressed&filter_term%5B%5D=%7B%22primary_type%22%3A%22accession%22%7D&op1=AND&op2=AND&op3=AND&t0=text&t1=date&t2=date&t3=boolean&v0=pape%2A&v1=2015-06-30&v2=2016-07-01&v3=false,

Essential is just passed to Solr as:

http://localhost:8090/collection1/select?q=%28%28%28fullrecord:%28pape*%29+AND+create_time:[2015-06-30T00:00:00Z%2B1DAY+TO+*]%29+AND+create_time:[*+TO+2016-07-01T00:00:00Z-1MILLISECOND]%29+AND+suppressed:%28false%29%29&facet.limit=100&facet.field=primary_type&facet.field=creators&facet.field=subjects&start=0&fq=repository:%22/repositories/2%22+OR+repository:global&fq=-exclude_by_default:true&sort=&rows=10&wt=json&facet=true

All you have to do is change the wt ( response format ) to csv and the rows to something like 100000.
<http://localhost:8090/collection1/select?q=%28%28%28fullrecord:%28pape*%29+AND+create_time:[2015-06-30T00:00:00Z%2B1DAY+TO+*]%29+AND+create_time:[*+TO+2016-07-01T00:00:00Z-1MILLISECOND]%29+AND+suppressed:%28false%29%29&facet.limit=100&facet.field=primary_type&facet.field=creators&facet.field=subjects&start=0&fq=repository:%22/repositories/2%22+OR+repository:global&fq=-exclude_by_default:true&sort=&rows=10&wt=json&facet=true>http://localhost:8090/collection1/select?q=%28%28%28fullrecord:%28pape*%29+AND+create_time:[2015-06-30T00:00:00Z%2B1DAY+TO+*]%29+AND+create_time:[*+TO+2016-07-01T00:00:00Z-1MILLISECOND]%29+AND+suppressed:%28false%29%29&facet.limit=100&facet.field=primary_type&facet.field=creators&facet.field=subjects&start=0&fq=repository:%22/repositories/2%22+OR+repository:global&rows=10000&wt=csv

( you can drop the facet stuff, since you're not needing to display that ).

Solr query syntax is pretty well documented. <https://wiki.apache.org/solr/SolrQuerySyntax>

And there's the Solr control panel that is available at port :8090 .

A pro tip is to have apace running and watch the log as you submit some queries. You'll see the request go to the frontend and then see it pass to the backend api, then passed to Solr.

For Solr search, the log will look like :

INFO: [collection1] webapp= path=/select params={ ....

Take whats in params ( it'll start with a ?q= ) and just past that into http://your.aspace.org:8090/collection1/select?q......

Make sense?

b,chris.

Chris Fitzpatrick | Developer, ArchivesSpace
Skype: chrisfitzpat  | Phone: 918.236.6048
http://archivesspace.org/

________________________________
From: archivesspace_users_group-bounces at lyralists.lyrasis.org <archivesspace_users_group-bounces at lyralists.lyrasis.org> on behalf of Custer, Mark <mark.custer at yale.edu>
Sent: Thursday, January 21, 2016 3:06 PM
To: Archivesspace Users Group
Subject: Re: [Archivesspace_Users_Group] Reporting update request

Chris,

Speaking as a former member of the UAC Reports subteam, we found that the most (only?) useful report for former AT users was the print-screen report.  Given that, and in addition to feedback from staff here, it sounds to me like the biggest help of all for reporting would be the following:

·         Staff could specify what columns they would like to display in a the search result screen (and multiple dates could display as a list in a single column, whereas description from the notes table couldn’t display, just like they didn’t in the AT)

·         Staff could take any search result that they produced, like this one, http://sandbox.archivesspace.org/advanced_search?advanced=true&dop1=greater_than&dop2=lesser_than&f0=keyword&f1=create_time&f2=create_time&f3=suppressed&filter_term%5B%5D=%7B%22primary_type%22%3A%22accession%22%7D&op1=AND&op2=AND&op3=AND&t0=text&t1=date&t2=date&t3=boolean&v0=pape%2A&v1=2015-06-30&v2=2016-07-01&v3=false, and then export those results as a CSV file.  And let’s pretend, in this case, that the user has also added columns for the Identifier and the Accession Date.  Also, every page of results would need to be exported.  In this example, there are just 3 results, but even if there were 3,333 results, then all of those results should be exported into a single CSV file.

>From my understanding, that’s the biggest user request:  as a staff user, I want to select what fields display for my search results (title isn’t enough), perform an advanced search, and then export my results.

Right now, for our statistical reports, we just run these “reports” to get our data out with a read-only MySQL user with a few SQL scripts, like this one:

select value as 'Accession type'

, COUNT(*) as 'Total accessions measured in linear feet'

, ROUND(SUM(extent.number), 2) as 'Linear feet'

from accession

left join extent on accession.id = extent.accession_id

left join enumeration_value on acquisition_type_id = enumeration_value.id

where

(extent_type_id IN (select id

from enumeration_value

where LOWER(value) like '%linear%'))

and repo_id = 11 #hardcoded value for now

and accession.accession_date >= '20151001' #change dates as needed

and accession.accession_date <= '20151231'

group by acquisition_type_id;

Mark

P.S.  All that said, I really like Jasper, actually!  But until it’s easy to pass in different parameters in the staff interface when running a report (date ranges, search terms, etc.), I don’t think it’ll be as useful to most ASpace users as being able to export all of their search results in some fashion.

From: archivesspace_users_group-bounces at lyralists.lyrasis.org [mailto:archivesspace_users_group-bounces at lyralists.lyrasis.org] On Behalf Of Chris Fitzpatrick
Sent: Thursday, January 21, 2016 7:51 AM
To: Archivesspace Users Group
Subject: Re: [Archivesspace_Users_Group] Reporting update request

I can help in regards to getting data out via the API. What are some things you're wanting to get out?

b,chris.

Chris Fitzpatrick | Developer, ArchivesSpace
Skype: chrisfitzpat  | Phone: 918.236.6048
http://archivesspace.org/

________________________________

From: archivesspace_users_group-bounces at lyralists.lyrasis.org<mailto:archivesspace_users_group-bounces at lyralists.lyrasis.org> <archivesspace_users_group-bounces at lyralists.lyrasis.org<mailto:archivesspace_users_group-bounces at lyralists.lyrasis.org>> on behalf of Carll, Johanna <jcarll at radcliffe.harvard.edu<mailto:jcarll at radcliffe.harvard.edu>>
Sent: Wednesday, January 20, 2016 5:46 PM
To: Archivesspace Users Group
Subject: [Archivesspace_Users_Group] Reporting update request

Hi all

As we begin the second half of the fiscal year and plan for how we will produce our end of year statistical reports, it would be useful to have an update on AS reports.  Specifically, is there a timeline for when we can expect a release that includes improved functionality of the existing reports (date limiting, improved csv exports, etc.)?  Also, have there been any further developments on the approach proposed in the below report from the Reports sub-group in the UAC minutes from November 5th?

currently testing reports to assist features prioritization sub-team; team wants to approach reports in a different way–get data out to use in own way; Brad Westbrook will talk to programmers about getting data out via API; sub-team feels that Jasper is not user friendly–stored reports are difficult to edit or customize unless you are a programmer and difficult to write canned reports that can be used by multiple repositories

Thanks

Johanna

Johanna Carll
Archivist and Metadata Specialist
Schlesinger Library
Radcliffe Institute for Advanced Study
Harvard University
10 Garden Street
Cambridge, MA 02138
617-495-8524
jcarll at radcliffe.harvard.edu<mailto:jcarll at radcliffe.harvard.edu>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lyralists.lyrasis.org/pipermail/archivesspace_users_group/attachments/20160121/cad17a1c/attachment.html>