[Evergreen-reports] Dewey Tens Call Number Report with Null Values

[Beth Willis]

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-reports
>
>
>
>
> --
> 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-reports
>