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

 

[rharness]

lbass,

Sorry, I think I mis-understood the request.

Ok...here's some sample data from the report, using the formula you provided.

person_style_view.P_Unique_Key
ADA 9775
ADA 9775
ADA 9775
ADK 3501
ADK 3501

person_style_vew.Course_Code
PTI 102
HSI005
TRAINING
HSI272PST
HSI003

person_style_view.End_Date(DateTime)
10/6/2005 4:30:00PM
5/11/2006 4:30:00PM
7/31/1999 4:30:00PM
4/8/2005 4:30:00PM
10/7/2004 5:00:00PM

person_style_view.Registration_Status
F
F
C
W
F

registration_style_view.P_Unique_Key
all blank lines on report (no data visable)

registration_style_view.Course_Code
all blank lines on report (no data visable)

registration_style_view.End_Date
all blank lines on report (no data visable)

registration_style_view.Registration_Status
all blank lines on report (no data visable)

@all_include_null (this is the formula, drug out to the details section of report)
all lines on report list "none"

Thanks lbass!

 

[lbass]

I am not asking you to use a formula anywhere. There should be no record selection and only the fields from both tables in the detail section. Please show a sample of the results in the detail section as they would appear in the report--horizontally.

-LB

 

[rharness]

lbass,

Thanks for hanging in there with me on this.

Ok...how's this? To get the following, I'm only using the registration_style_data table for these results.

Division P_Unique_Key Last_Name Course_Code Class_Name End_Date
37 LAM 1166 Lampinen HSI 552 Bloodborne Pathogens Training7/19/2006
60A RIC 2652 Richardson HSI 552 Bloodborne Pathogens 9/28/2007
60A PUC 7710 Puccinelli HSI 552 Bloodborne Pathogens 10/2/2007

 

[lbass]

The fields from BOTH tables.

-LB

 

[rharness]

Hmm...I think this may be part of the problem, as when I try to add fields from another table, without using any formulas or the outer-join stuff, *nothing* prints!

However, I just heard back from our LMS vendor and they said I should be using the registration_style_view, person, and learning_plan tables. I don't understand why the learning_plan, as I browsed the fields, and other than the unique id type fields, most of them are empty.

Anyways, I'm still struggling to get the report to print details, if I use more then just one table, if I don't use any formulas. Does that make sense??

 

[lbass]

How are you linking these new tables? Start with the table that contains all records (person?) and then do left joins from that table to the other tables.

-LB

 

[rharness]

lbass,

I was actually using the report wizard, so I'd select the tables, then let the wizard link them together. Then the only thing I'd change is the person ID by switching it from whatever default join it currently is, to the 'left outer join', per your suggestion.

Is this not the way that this part of the process should be done?

(and for the record, I've got some other reports (some created/tweaked with help from this board) that work great. I just can't believe I can't seem to make this work some how).

 

[CR85user]

Sadly, the wizard is a bit of a pain sometimes in terms of how it links tables.

I prefer always to remove the links and link the tables myself.

Which fields in the learning_plan table (aside from student id) are populated.

Is there some sample data?

Cheers

'J

 

[rharness]

CR85user,

I think I understand what you mean re: the wizard!

And actually I just spoke to the LMS vendor tech support, and he said that actually the learning_plan table isn't going to help me, as we're not even utilizing Learning Plans in our database.

He seems to think I may not even be able to do what I've been trying to do here (showing who has and who hasn't taken the class).

Maybe that's why I'm struggling so much?

But thank you so much for trying to help CR85user (and lbass)!

 

[lbass]

It's not that it can't be done in CR, because it can, but it seems like you are unsure of what tables to use in your database (and which ones are populated), and we can't really help you with that.

-LB

 

[rharness]

Understood lbass. Hopefully at some point I'll at least figure out exactly which tables to use (and populated) and I'll revisit the forums and see if anyone is still willing to help

Thanks again!

-Roger H.