[Evergreen-reports] Dewey Tens Call Number Report with Null Values
Boyer, Jason A
JBoyer1 at library.IN.gov
Thu May 1 14:59:09 EDT 2014
The dewey_tens_range in the reporter.classic_current_circ view is only generated when there's a circ to tie it to, so there's no good way to join anything together and get it to fill in missing ranges. To get what you want would probably either require 2 reports or a new reporter definition.
Using two reports you'd have to run one from the classic circ view that lists circs per dewey range, and one from classic item list that lists all of the dewey 10 ranges that have items. Then you would have to merge them together to get 1 finished report.
The new reporter definition method could be done by adding a dewey tens field to the regular items source or adding a link to All Circulations to the classic items list. After that you would need to start from the modified item source, display the dewey tens range and for the circ count follow a child-nullable (left join) link to All Circulations and use a Count Distinct transform on the id (and also add a circ date range filter -following the same link- to specify the circs you want). I don't know what kind of server access you have but this change requires changing fm_IDL.xml (at least) and I believe a services restart to get working.
Jason
--
Jason Boyer
Indiana State Library
http://library.in.gov/
317-234-2128
-----Original Message-----
From: evergreen-reports-bounces at list.evergreen-ils.org [mailto:evergreen-reports-bounces at list.evergreen-ils.org] On Behalf Of Beth Willis
Sent: Thursday, May 01, 2014 2:07 PM
To: evergreen-reports at list.evergreen-ils.org
Subject: Re: [Evergreen-reports] Dewey Tens Call Number Report with Null Values
Hi Jessica,
This is probably obvious, but does the query require a right outer join to the asset.copy table rather than the existing inner join? If you enable nullability selection and choose "parent" when linking to the copy source would that work?
I don't seem to have access to the classic_current_circ source, so I cannot test this.
beth
Beth Willis
Digital & Catalog Librarian
NOBLE, Inc.
26 Cherry Hill Drive
Danvers, MA 01923
On 4/30/2014 10:11 AM, Jessica Venturo wrote:
> Hi Chris,
>
> Certainly!
>
> SELECT "3ab8a993e187c3fada2b60d37eeac5d3"."dewey_block_tens" AS "Dewey Block - Tens",
> COUNT(DISTINCT "3ab8a993e187c3fada2b60d37eeac5d3"."id") AS "# of Circulations"
> FROM reporter.classic_current_circ AS "3ab8a993e187c3fada2b60d37eeac5d3"
> INNER JOIN asset.copy AS "e057a6cb37b890359195eefb1bcb9827" ON ("3ab8a993e187c3fada2b60d37eeac5d3"."copy_id" = "e057a6cb37b890359195eefb1bcb9827"."id")
> WHERE "3ab8a993e187c3fada2b60d37eeac5d3"."circ_lib_id" IN ('25')
> AND date("3ab8a993e187c3fada2b60d37eeac5d3"."xact_start") BETWEEN '2013-07-01' AND '2013-07-31'
> AND "e057a6cb37b890359195eefb1bcb9827"."circ_lib" IN ('25')
> GROUP BY 1
> ORDER BY "3ab8a993e187c3fada2b60d37eeac5d3"."dewey_block_tens" ASC,
> COUNT(DISTINCT "3ab8a993e187c3fada2b60d37eeac5d3"."id") ASC
>
> Thanks!
>
> Jessica
>
>
>
> On Wed, Apr 30, 2014 at 7:39 AM, Sharp, Chris
> <csharp at georgialibraries.org <mailto:csharp at georgialibraries.org>> wrote:
>
> Jessica,
>
> Would you mind sharing the Generated SQL from the Debugging Info
> screen? That would help me know how the template is currently
> joining tables.
>
> Thanks,
>
> Chris
>
> ----- Original Message -----
> > From: "Jessica Venturo" <jventuro at biblio.org
> <mailto:jventuro at biblio.org>>
> > To: "Evergreen Community Reports Email List"
> <evergreen-reports at list.evergreen-ils.org
> <mailto:evergreen-reports at list.evergreen-ils.org>>
> > Sent: Tuesday, April 29, 2014 5:24:13 PM
> > Subject: [Evergreen-reports] Dewey Tens Call Number Report with
> Null Values
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > Hi all,
> >
> > A librarian in our consortium has requested a report that shows
> > circulation by Dewey ten blocks, filtered by check out date range,
> > the library where the item was checked out, and owning library of
> > the item. The template was easy to assemble using the Classic
> > Circulation source. However, they have recently requested that the
> > report list ALL of the Dewey ten blocks - not just the ones that had
> > circulations within the given time period. In other words, they
> > would like those Dewey blocks from which no items circulated to
> > display in the report with a value of "0." I had originally thought
> > to use the Classic Item Source and rebuild the template, but alas,
> > it does not seem that there is a link to the circulation table from
> > that source. I am hoping that someone with more experience with
> > nullabilty than me can give me some pointers on how to alter my
> > original template so that it displays the information this way (if
> > it's possible).
> >
> > The display field that I'm using are:
> > Classic Circulation View > Dewey Block - Tens (Transform - Raw Data)
> > Classic Circulation View > Circulation (ID, Transform - Count)
> >
> > The filters I'm using are:
> > Classic Circulation View > Library Circulation Link (Transform - Raw
> > Data, Operator - In List)
> > Classic Circulation View > Copy Link > Circulating Library (Transfom
> > - Raw Data, Operator - In List)
> > Classic Circulation View > Circulation Date/Time (Transform - Date,
> > Operator - Between)
> >
> > Any help you can give would be appreciated!
> >
> > Thanks,
> > Jessica
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > --
> >
> > Jessica Venturo
> > Applications Support Specialist
> > Bibliomation, Inc.
> > 24 Wooster Ave.
> > Waterbury, CT 06708
> > 203-577-4070 x105 <tel:203-577-4070%20x105>
> > _______________________________________________
> > Evergreen-reports mailing list
> > Evergreen-reports at list.evergreen-ils.org
> <mailto:Evergreen-reports at list.evergreen-ils.org>
> >
> http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports
> >
>
> --
> Chris Sharp
> PINES System Administrator
> Georgia Public Library Service
> 1800 Century Place, Suite 150
> Atlanta, Georgia 30345
> (404) 235-7147 <tel:%28404%29%20235-7147>
> csharp at georgialibraries.org <mailto:csharp at georgialibraries.org>
> http://pines.georgialibraries.org/
> _______________________________________________
> Evergreen-reports mailing list
> Evergreen-reports at list.evergreen-ils.org
> <mailto:Evergreen-reports at list.evergreen-ils.org>
>
> http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-repor
> ts
>
>
>
>
> --
> Jessica Venturo
> Applications Support Specialist
> Bibliomation, Inc.
> 24 Wooster Ave.
> Waterbury, CT 06708
> 203-577-4070 x105
>
>
> _______________________________________________
> Evergreen-reports mailing list
> Evergreen-reports at list.evergreen-ils.org
> http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-repor
> ts
>
_______________________________________________
Evergreen-reports mailing list
Evergreen-reports at list.evergreen-ils.org
http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports
More information about the Evergreen-reports
mailing list