[Evergreen-reports] Reports and nullability

[Jessica Venturo - Bibliomation]

Hi Beth,

I have a similar template that I built starting in the Open Billable Transactions source and linking in ILS User.  The template is filter by Billing Location, Permission Group from the ILS User source, and  "Balance owed > 0."  I just tested it by removing that filter and checking "enable nullability selection," and I got back several users with a zero balance.  I wonder if nullability is not working in this case because there is actually a "0" in the table, and therefore the field is not technically null?  It does, however, only return patrons in the selected permission group, even without nullability enabled.

Thanks,
Jessica

Jessica Venturo
Applications Support Specialist
Bibliomation, Inc.
24 Wooster Ave.
Waterbury, CT 06708 
203-577-4070 ext. 105

----- Original Message -----
From: "Beth Willis" <willis at noblenet.org>
To: "Evergreen Community Reports Email List" <evergreen-reports at list.evergreen-ils.org>
Sent: Friday, May 24, 2013 11:31:33 AM
Subject: [Evergreen-reports] Reports and nullability


When creating report templates, I often find that the generated SQL that 
includes null values in the filter parameters, even if I attempt to 
exclude null values.

As an example, I want to create a report of users (students) with 
balances owed that exceed a specified amount.

I have followed these steps in creating a new template:

1) I selected "Enable nullability selection"

2) I chose the ILS User source

3) I selected several variables from this source for inclusion in the 
display (eg. last name, first name, email, secondary identification)

NOTE: we use the secondary identification field to designate a student's 
grade/class; for this report, there is a filter on this field.

4) Next, I selected the source I want to link to: Open Billable 
Transactions.  I want to exclude null values, so I selected Nullable = 
Default.

5) I selected one field (balance owed) from this source for inclusion in 
the report output.

6) I set my filters using fields from each source


The resulting output correctly includes only users in the selected 
grade/class (i.e. Senior), but includes a number of cases where the user 
did not have an outstanding balance.  My assumption was  that by 
selecting "Default" when linking to the Open Billable Transactions 
source I would create an inner join and get a one-to-one match between 
the two sources.  Is this incorrect?

The SQL is below.  You can see that there is an outer, not an inner, 
join between the actor.usr table and the 
money.open_billable_xact_summary table.  Not what I expected.

Can anyone point out what I am doing wrong?


SELECT	"fe21ca9ecd516f33ec379ecc11aa7051"."family_name" AS "Last Name",
	"fe21ca9ecd516f33ec379ecc11aa7051"."first_given_name" AS "First Name",
	"fe21ca9ecd516f33ec379ecc11aa7051"."ident_value2" AS "Secondary 
Identification",
	"fe21ca9ecd516f33ec379ecc11aa7051"."email" AS "Email Address",
	SUM("b2950253a32b916e8653bdb542da7670"."balance_owed") AS "balance_owed"
   FROM	actor.usr AS "fe21ca9ecd516f33ec379ecc11aa7051"
	LEFT OUTER JOIN money.open_billable_xact_summary AS 
"b2950253a32b916e8653bdb542da7670" ON 
("fe21ca9ecd516f33ec379ecc11aa7051"."id" = 
"b2950253a32b916e8653bdb542da7670"."usr")
   WHERE	"fe21ca9ecd516f33ec379ecc11aa7051"."home_ou" IN ('56')
	AND "fe21ca9ecd516f33ec379ecc11aa7051"."ident_value2" IN ('Senior')
   GROUP BY 1, 2, 3, 4
   HAVING ((SUM("b2950253a32b916e8653bdb542da7670"."balance_owed")) IS 
NULL OR SUM("b2950253a32b916e8653bdb542da7670"."balance_owed") > '0')
   ORDER BY "fe21ca9ecd516f33ec379ecc11aa7051"."family_name" ASC, 
"fe21ca9ecd516f33ec379ecc11aa7051"."first_given_name" ASC, 
"fe21ca9ecd516f33ec379ecc11aa7051"."ident_value2" ASC, 
"fe21ca9ecd516f33ec379ecc11aa7051"."email" ASC, 
SUM("b2950253a32b916e8653bdb542da7670"."balance_owed") ASC


Thank you.
beth
-- 
Beth Willis
Digital & Catalog Librarian
NOBLE, Inc.
26 Cherry Hill Drive
Danvers, MA 01923

_______________________________________________
Evergreen-reports mailing list
Evergreen-reports at list.evergreen-ils.org
http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports