CR 10 - Include records even if value is null?

[rharness]

In Crystal Reports 10, is there a way to include records in a report, even if it is 'null'? (Not even sure if I'm using that term correctly).

I'm trying to get CR to give me a report that lists everyone in a specific division, then tell me the dates that they took a certain class, AND/OR tell me if they haven't had the class.

Something like this:

NAME CLASS DATE
Jones,J. 10/1/07
Smith,S. 9/5/07
Hope,B. None
Perez,L. 9/14/06

Using the Select Expert, I'm asking CR to report on all people who've have an "F" (for 'finished') as their registration status. That works fine, but when I try to get CR to give me the folks who *haven't* taken the class, by asking the Select Expert to give me folks who's status doesn't equal "F", I get no records.

I've tried messing the null value stuff, but again I don't know if that's even what I need to be looking at.

Right now I don't care HOW the report might list the folks who've not taken the class, but I'd just like to get both of these statuses on the report. (date for folks who've taken it, "none" or blank for folks who haven't). Is this possible? I can't believe I can't figure this out.

Thank you!
-Roger

 

[lbass]

If you have a table with names and a table with class participation, you need a left join from the names table to the class table. Then you can write a formula like:

if isnull({class.studentID}) then
"None" else
totext({class.date},"MM/dd/yyyy")

Remove the record selection formula that limits the records first. Then replace your date field in the body of the report with this formula.

-LB

 

[rharness]

lbass,

Thank you for your answer/help!

Your suggestion makes sense, but can you point me to the spot where I can perform the left join? Can I do this somehow in the "Links" tab of the Database Expert?

-Roger H.

 

[lbass]

Yes, in the links screen, right click on the line connecting to the two tables and choose "link options" and change the join to a left outer join.

-LB

 

[rharness]

lbass,

I know you don't know the entire structure of our database/etc., but what field(s) should I used to make the left out join?

 

[CR85user]

Dependant on the datatype you may need to either reverse the logic of your initial query or account for a null or empty field.

If for example your initial criteria was:

{classes.regstatus} like 'F'

then you may instead need to try:

isnull({classes.regstatus})
or
{classes.regstatus} like ''

As lbass pointed out you ought really be using a left outer join where one table (the one on the left) has data on everyone and the other table (on the right and joined from the left on a common unique field with left outer join) has info on some.

Crystal is great at easing you into good practises as i'm sure you have already found.

'J

 

[lbass]

If you have a student ID field, I would use that.

-LB

 

[rharness]

Ok now I'm just trying to get folks that have not completed the class.

I'm using person_style_view on left side and registration_style_view on right.

I have P_Unique_key linked in both tables by a 'Left Outer Join'.

In my formula, I have this:
{registration_style_view.Course_Code} = "HSI 552" and
{registration_style_view.Registration_Status} like ""

tried different combinations of registration_sytle_view and person_style_view in the formula, but nothing prints with any combo.

But I know I still have the logic wrong, because obviously the Course.Code will never equal HSI 552.

(and for the record, changing Registration_Status to equal "F" DOES get me folks who've taken the class.

Am I just way off-base with this? Am I making it harder than it should be?

 

[lbass]

Remove ALL record selection criteria on the right hand table. Then use a formula in the body of the report as I suggested earlier.

Why are you looking for registration status = "". You didn't set the report options to convert null values to default, did you??? You shouldn't have. If you want to limit by the code, then use a formula in the detail section like this:

if isnull({registration_style_view.P_Unique_Key} or
{registration_style_view.Course_Code} <> "HSI 552" then
"None" else
totext(totext({registration_style_view.date},"MM/dd/yyyy")

-LB

 

[CR85user]

If you want to list everyone within a division then do as lbass has suggested and don't have a selection criteria in place or even just have a plain

{person_style_view.P_Unique_Key} > ''

as the selection criteria.

Then consider a parameter to select the division you require to run the report for.

Then as lbass pointed out, you can add a line per person detailing the information you are after in any format you require.

'J

 

[rharness]

lbass,

I know I'm asking for alot, but I'm getting syntax errors with (missing parentheses) when using this code:

if isnull({registration_style_view.P_Unique_Key} or
{registration_style_view.Course_Code} <> "HSI 552" then
"None" else
totext(totext({registration_style_view.date},"MM/dd/yyyy")

Once again I feel ridiculous I can't figure out the correct syntax, but every combination I've tried doesn't seem to run. I've tried googling examples and applying the results, but again I can't seem to make it work.

I apologize for the hand-holding request, but if you get time, can you take a look-see to determine if it looks ok?

Thanks again lbass,

-Roger H.

 

[lbass]

Where there is an open paren there has to be a close paren. There are two typos, sorry.

if isnull({registration_style_view.P_Unique_Key}) or
{registration_style_view.Course_Code} <> "HSI 552" then
"None" else
totext({registration_style_view.date},"MM/dd/yyyy")

-LB

 

[rharness]

lbass,

That fixed my syntax errors, thank you!!

Ok...i think I'm really close now, but still not quite there. It IS listing everyone now, but it's listing "None" for everyone! (I have about 50 people so far who should have dates listed for them).

I have nothing selected via the Select Expert, and I have the following fields on the report:
person_style_view.Division
person_style_view.P_Unique_Key
person_style_view.Name_Last_First_Middle
@all_plus_null (for my date column)

Here's the code:
if isnull({registration_style_view.P_Unique_Key}) or
{registration_style_view.Course_Code} <> "HSI 552" then
"None" else
totext({registration_style_view.End_Date},"MM/dd/yyyy")

I have 'person_style_view.P_Unique_Key' Left Outer Joined to 'registration_style_view.P_Unique_Key'

Shouldn't this be working? I think I've tried all combinations of changing the listed fields on the report and changing the formula to use person_style_view instead of registration_style_view. But again, nothing seems to work. Based on all this, can you see anything obvious that I'm doing wrong??

Wow.

 

[lbass]

This should show the dates only for HSI 552 and "none" for all others.

-LB

 

[rharness]

lbass,

So something like this, correct?

NAME CODE DATE
Smith HSI 552 10/5/07
Jones HSI 552 10/1/07
Johnson HSI 552 None

or something like that, right?

I'm getting this:
NAME CODE DATE
Smith HSI 552 None
Jones HSI 552 None
Johnson HSI 552 None

Do you have any ideas why it won't print the dates for folks that HAVE taken the course?

Thanks again!
-Roger H.

 

[CR85user]

I would have thought that you are pretty much there already in terms of finally selecting everyone.

The logic of your formula is basically saying that if the course code is mentioned then state 'None', what you are instead looking for is firstly those taking/finishing the course and THEN stating the date of those who finished? correct?

If I remember rightly you already have a registration status code which indicates where a date ought be entered, would that not be a better option for your formulae?

if {registration_style_view.Course_Code} = "HSI 552" and
(
isnull({registration_style_view.Registration_Status}) or
{registration_style_view.Registration_Status} <> "F"
)
then "Not Complete" else
'Finishing date:' + ' ' + totext({registration_style_view.End_Date},"MM/dd/yyyy")


Does that get you any closer?

I would also suggest a parameter for the course code to make things clearer in future and take course codesd out of the equation.

Create a parameter for a string value, call it say CCode, then in your selection criteria you can use:

//Only select people referencing the course in question

{registration_style_view.Course_Code} like {?CCode}

that way you can then make your date formula to show the date based on the status of completion only:

//Formula for date

(
isnull({registration_style_view.Registration_Status}) or
{registration_style_view.Registration_Status} <> "F"
)
then "Not Complete" else
'Finishing date:' + ' ' + totext({registration_style_view.End_Date},"MM/dd/yyyy")

That however is assuming that the date in this db field is specific to the course.

A BIG assumption, more data on your db structure would help.

'J

 

[rharness]

CR85user,

Thank you for your help!

>>>The logic of your formula is basically saying that if the course code is mentioned then state 'None', what you are instead looking for is firstly those taking/finishing the course and THEN stating the date of those who finished? correct?
>>>

Yes, what I'm trying to do is basically list everyone in the database (I'll narrow it down to specific divisions once I can get this part working), and if they've taken the course, list their name and the date they took it, and if they haven't taken it, list their name, and 'none/not complete/etc'.

There will be some folks that won't have any status at all for this class, (which would hopefully have 'not completed' by their name while most of the folks associated with the class should have a status of "F".

Does this seem like the formula should work??

My problem(s) may be more then folks are really willing to help me with, but when I run the report with the formula you've created, I do get all folks listed, but it also lists all classes they've taken, and the formula column is actually blank, whether they've taken the course or not. But I think part of my problem is in trying to get the report to work, I'm trying different combinations of the registration_style_view with the person_style_view, both in your formula, and what fields I'm dragging to the reports. I assume a big part of the problem is that I don't totally understand my database structure. (the data is all being taken from Pathlore, and LMS we're using).

And as you said, I should have already been able to figure this out by now. It seems so simple, yet I'm able to make it work. Arghhhh....

And your idea for the parameters sound good as well, as if i can ever get this working, I need to run this for other mandated training as well.

Thanks again CR85user! Really appreciate you and lbass's help with this.

-Roger H.

 

[lbass]

Please put the fields from both tables in the detail section, including those used in the formula, and then report back with some sample data.

-LB

 

[rharness]

lbass,

Ok...this is using the following code in the Formula Fields (which is brought over to the report as the last column titled "all_plus_null":
if {registration_style_view.Course_Code} = "HSI 552" and
(
isnull({registration_style_view.Registration_Status}) or
{registration_style_view.Registration_Status} <> "F"
)
then "Not Complete" else
'Finishing date:' + ' ' + totext({registration_style_view.End_Date},"MM/dd/yyyy")

I only used info from the person_style_view, as anything I'd try to use from the registration_style_view would just print nothing, (like the all_plus_null column is now printing).

I've copied a sample page, which has 4 columns,
P_Unique_Key, Course_Code, End_Date, and all_plus_null

(i didn't format any of the fields).

Notice the 12th row down...

AND 3587 HSI 340 10/27/2004 4:30:00PM
AND 3587 HSI 003 12/9/2004 5:00:00PM
AND 3587 HSI 241 6/2/2005 2:00:00PM
AND 3587 HSI 005 11/17/2005 4:30:00PM
AND 3587 MSI 001 3/10/2006 10:00:00AM
AND 3587 TRAINING 7/28/2006 4:30:00PM
AND 3587 HSI 553 11/14/2006 4:30:00PM
AND 3587 EDI 299 2/13/2007 4:00:00PM
AND 3587 HSI 005 2/21/2007 12:00:00AM
AND 3587 HSI 272 8/14/2007 3:30:00PM
AND 3587 PTI 700 8/21/2007 3:00:00PM
AND 3587 HSI 552 10/10/2007 12:00:00AM
AND 3775 TRAINING 6/12/1998 5:00:00PM
AND 3775 HS 105 5/9/2003 12:30:00PM

Does this help? I can still add info from the registation_style_view table, but again it won't print anything.

Thanks again!!

 

[lbass]

I don't agree with the use of that formula. Please just show a display of the fields from both tables, as I requested.

-LB