rows with null fields being excluded from report

[RepRider]

CRXI using SQL database

This report shows certain types of appointments for the day with the
Provider, appt type, client name, time, anc various associate fields related to authorizations (auth ID number, name of auth cluster, start date, end date, units authorized, units left.

Currently all tables are left outer joined with the schedule table being first.

My problem is that if there is not an auth ID for a client then the appointment doesn't show on the report.

I need for all appointments of the designated type to show and also if there is no auth then the auth fields should be blank. I have checked the "null values to default" for this report.

I created a formula @Noauth that says...
If ISNULL ({Authorizations.authorizations_ID}) then 0 else ({Authorizations.authorizations_ID})

but still the nulls do not show up. For the current report there are showing 69 appts but I know that there are 6 others not displaying that do not have auth numbers.
I am sure it is something simple I am missing but I am brain dead for the day.

The select expert formula is..
not ({Program.program_name} in ["DD ALZHEIMERS RESPITE", "DD COMMUNITY
INPATIENT", "DD EMERGENCY SERVICES", "DD FAMILY SUPPORT", "DD STATE
INPATIENT", "MI ADULT COMMUNITY INPATIENT", "MI ADULT CRISIS RESIDENTIAL",
"MI ADULT EMERGENCY SERVICES", "MI ADULT GERIATRIC ASSESSMENT", "MI
ALZHEIMERS RESPITE", "MI CHILD COMMUNITY INPATIENT", "MI CHILD CRISIS
RESIDENTIAL", "MI CHILD EMERGENCY SERVICES", "MI CHILD STATE INPATIENT"])
and
{Authorizations.archived} = 0 and
{Authorized_Service_View.archived} = 0 and
{Authorizations.authorization_end_date} >= DataDate - 90 and
{Service_Group.service_group_name} in ["ACT", "ACT TEAM", "HOME BASED",
"HOME BASED CLUSTER", "PSYCHIATRIC", "PSYCHIATRIC CLUSTER", "PSYCHOL
CLUSTER"] and
{Sch_Entry.start_date} in DateTime (2008, 09, 07, 00, 00, 00) to DateTime
(2008, 09, 13, 00, 00, 00) and
{Sch_Entry.appointment_type_option} in ["Med Evaluation", "Med Review",
"Psych Evaluation"] and
not ({Sch_Entry.name} like ["*CANCEL*", "*RESCH*"])

I hope this makes sense to someone. I appreciate any help you can offer!!

 

[Madawc]

Probably one of the values tested in the formula field is also null. This will stop the formula.

I'd advise you to split up the formula, do as a set of 'boolians', So you could have a formula field called ServiceGroup,

{Service_Group.service_group_name} in ["ACT", "ACT TEAM", "HOME BASED", "HOME BASED CLUSTER", "PSYCHIATRIC", "PSYCHIATRIC CLUSTER", "PSYCHOL 
CLUSTER"]

If you display it in a test report without selection, it would return True or False, but spaces if it hits a null. UNless you first test not IsNull etc.

In the selection you could then just say ServiceGroup to get the formula.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10