Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2016 : Technical Questions  
Message Icon Topic: How to get crystal to show nulls Post Reply Post New Topic
Author Message
Michele
Newbie
Newbie
Avatar

Joined: 21 Jun 2019
Location: United States
Online Status: Offline
Posts: 3
Quote Michele Replybullet Topic: How to get crystal to show nulls
    Posted: 09 Aug 2019 at 5:34am
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
Accept that which is good
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 8991
Quote DBlank Replybullet Posted: 09 Aug 2019 at 7:44am
a few things to consider.
Your SQL is placing the conditions on the join. I think you would have to use a crystal command or that sql query as a view or stored procedure to replicate that sort of result.
Crystal does not enforce joins unless you use a field in some capacity from both tables in the join or the end nodes of a joined chain. Or you can set the joins to enforced inside the join itself.
In your select formula you can set the formula to 'use default values' for nulls which will get you some of your results. However this would be like changing your sql join conditions to sql where clause which might lose some records you wanted.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.045 seconds.