Circulation report source to include in-house(non cat), and non cat circ

[Blake GH]

Some of our members would like to be able run a single report and get circulation statistics for:

Regular Circulation
Non-Cat Circulation
In House
In House Non Cat

[Terran McCanna (tmccanna)]

Our libraries would love this, too!

[Blake GH (bmagic)]

Give this a whirl
http://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/blake/LP1599634_Circulation_report_source_to_include_in-house_and_non_cat_circ

[Holly Brennan (hollyfromhomer)]

This would be a tremendous help to me. I rely on both in-house and circ stats for tracking magazine use. Since magazines are heavily used in-house, compared to books, I have to run both reports and combine the stats in Excel before getting a picture of how they are circulating.

[Michele Morgan (mmorgan)]

Blake, this looks like a very useful reporter source, but I notice the view doesn't include aged circulation, which should also be represented. I'd suggest adding another union to the view to action.aged_circulation. Great start!

[Blake GH (bmagic)]

Michele - Yeah, I considered including aged_circulation, and I don't really know why I didn't. You're right, I think it should be included. I have done that now. The link above is still valid.

[Chris Sharp (chrissharp123)]

Blake,

We have already been using this for our annual reports on 2.9.1(ish) and are very happy with it. I made a slight tweak to the upgrade script since it will error out for any database that doesn't already have a view with that name (also added .sql to the name of the upgrade script). Here's my signoff branch with that change:

http://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/csharp/LP1599634_Circulation_report_source_to_include_in-house_and_non_cat_circ_signoff

Thanks for doing this!

Chris

[Galen Charlton (gmc)]

Looks very promising. One suggestion I have is to change the id column in the view to just be the ID from the source tables (promoted to BIGINT in some cases). This would gain us two things:

- it removes a redundancy, as circ_type already specifies the source table
- it would make it easier to join this view against the circulation tables when writing SQL queries

[Galen Charlton (gmc)]

If folks agree with my suggestion, I've pushed a branch containing it as well as signoffs of Blake's and Chris' patches to the working/Evergreen branch user/gmcharlt/lp1599634_tweak_aacct, along with a patch moving the release notes entry:

http://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/gmcharlt/lp1599634_tweak_aacct

[Rogan Hamby (rogan-hamby)]

+1

On Thu, Aug 11, 2016 at 10:46 AM, Galen Charlton <email address hidden> wrote:

> If folks agree with my suggestion, I've pushed a branch containing it as
> well as signoffs of Blake's and Chris' patches to the working/Evergreen
> branch user/gmcharlt/lp1599634_tweak_aacct, along with a patch moving
> the release notes entry:
>
> http://git.evergreen-
> ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/
> user/gmcharlt/lp1599634_tweak_aacct
>
> ** Changed in: evergreen
> Status: New => Confirmed
>
> ** Changed in: evergreen
> Importance: Undecided => Wishlist
>
> ** Changed in: evergreen
> Milestone: 2.next => 2.11-beta
>
> --
> You received this bug notification because you are subscribed to
> Evergreen.
> Matching subscriptions: evergreenbugs
> https://bugs.launchpad.net/bugs/1599634
>
> Title:
> Circulation report source to include in-house(non cat), and non cat
> circ
>
> Status in Evergreen:
> Confirmed
>
> Bug description:
> Some of our members would like to be able run a single report and get
> circulation statistics for:
>
> Regular Circulation
> Non-Cat Circulation
> In House
> In House Non Cat
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/evergreen/+bug/1599634/+subscriptions
>

[Blake GH (bmagic)]

Galen,

The issue is that those tables could* share the same ID

[Galen Charlton (gmc)]

Doesn't matter, IMO: if you're writing an SQL query that joins aacctt on one of the base circulation tables, you would need to filter by on circ_type anyway; it's not necessary for id to be a unique value within the view itself.

[Blake GH (bmagic)]

Galen,

I agree except when the reporting engine outputs identical rows, they are collapsed into one row. This would occur if the report didn't include something unique. It's possible that they wouldn't include the circ_type. It's really the only reason I setup the ID's the way I did. Otherwise I would have just used the bare ID. You're probably right, users would ever do that! LOL.

[Galen Charlton (gmc)]

> I agree except when the reporting engine outputs identical rows, they
> are collapsed into one row.

Whoops, you're right (though now I'm going to file a wishlist bug to
add an option to reporter templates to specify that results should not
be auto-grouped if no aggregate function is being used :) ).

That said, since most of the time users presumably would be applying
an aggregate transform when using the new reporting source, would you
think a caution in the release notes would suffice?

[Mike Rylander (mrylander)]

IMO, if a template didn't include or filter on circ_type, then it's not providing enough information to disambiguate. Concatenating the id and type doesn't really help, because the user can choose to not include the id column as well. While we don't use them much in EG, multi-column primary keys (which id + type is, effectively) are perfectly valid.

In any case, though, we really do need to include the bare id, whether we provide a combined id/type column or not.

[Blake GH (bmagic)]

Go ahead and remove the ID concatenation. I believe that will work just fine. +1 Galen branch

[Blake GH (bmagic)]

Rebased to master and squashed.

http://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/blake/LP1599634_Circulation_report_source_to_include_in-house_and_non_cat

[Galen Charlton (gmc)]

Pushed to master for inclusion in 3.0. Thanks, Blake!