[Evergreen-reports] Reports and nullability

[Beth Willis]

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