[Evergreen-reports] Dewey Tens Call Number Report with Null Values
Jessica Venturo
jventuro at biblio.org
Thu May 1 16:01:01 EDT 2014
Thank you, Jason, and everyone else who chimed in. I had the feeling it
wouldn't be possible with the current source, but I figured it would be
worth checking.
I've been interested in learning how to create custom report views for a
while, but haven't tried it yet. I'll have to make a point to do that now
that I have something to work with. Thanks again!
Jessica
On Thu, May 1, 2014 at 2:59 PM, Boyer, Jason A <JBoyer1 at library.in.gov>wrote:
> 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
> _______________________________________________
> Evergreen-reports mailing list
> Evergreen-reports at list.evergreen-ils.org
> http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports
>
--
Jessica Venturo
Applications Support Specialist
Bibliomation, Inc.
24 Wooster Ave.
Waterbury, CT 06708
203-577-4070 x105
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://list.evergreen-ils.org/pipermail/evergreen-reports/attachments/20140501/e4e2ab7f/attachment-0001.html>
More information about the Evergreen-reports
mailing list