I'm relatively new to crystal reports. For some reason the crystal report isn't showing any items where there is a null value. Is there somewhere I need to turn on showing nulls? I know that sql turns up 145 nulls in a similar query.

The report links are set up like this:


                                                      left outer join            ADD_ANSW
    Person inner join    Person             |-----------------------> Table    
    Table ------>        Facilities -------    not enforced
              not enforced Table      -------
                                                      | left outer join           ADD_ANSW_1
                                                      -----------------------> Table (clone)
                                                       not enforced

My Report Selection Formula looks like this:

    not({Person_Facilities} in ["ABC","CDE"])
    and
    {ADD_ANSW.QUES_ID}=3
    and
    {ADD_ANSW_1.QUES_ID}=17
    and
    {Person_Facilities.Current_status}="Active"

The report fields showing are:

    Person_ID, Last, First, Middle, ADD_ANSW.ANSW_TEXT, ADD_ANSW_1.ANSW_TEXT

But the report isn't showing any nulls. For example, I know Luke Skywalker does not have a value for ANS_TEXT, so he should show as null, but he's not in the crystal report resulting data set at all. Any ideas? Luke Skywalker is in the Person Table and also Person Facilities Table.

My SQL query to check it is this:

    select
    p.Person_ID
    ,p.Last
    ,p.First
    ,p.Middle
    ,q.ANS_Text as "Employed"
    ,qq.ANS_TEXT as "Exception"
    from Person p
    join Person_Facilities pf on pf.Person_id=p.Person_id
    left join add_answ q on p.person_id=q.person_id and q.ques_id=17
    left join add_answ qq on qq.person_id=q.person_id and qq.ques_id=3
    and
    pf.Current_Status like ('Active')
    and
    pf.FacCode not in ('ABC','CDE')

and this shows Luke Skywalker having nulls for his Employed and Exception values.

The only thing I found in my internet search [isnull or value][1] is where people suggest in crystal using a selection formula that says something like table.field="Employed" or isnull(table.field), but my selection formula should show both, shouldn't it? I am looking for ques_id=3 or ques_id=17. If the value isn't filled for that person, the left join should show null for the ans_text for that ques_id.

I also saw [display null][2]

Any suggestions? There are more questions than 3 or 17, so I can't just look at question 3 or just at question 17.

I realize it's a big question, but hopefully someone knows.


[1]: https://kenhamady.com/cru/archives/1489
[2]: http://www.forumtopics.com/busobj/viewtopic.php?t=112588