How to get crystal to show nulls
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22811
Printed Date: 30 Apr 2024 at 11:51am
Topic: How to get crystal to show nulls
Posted By: Michele
Subject: How to get crystal to show nulls
Date 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
|
Replies:
Posted By: DBlank
Date 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.
|
|