cancel
Showing results for 
Search instead for 
Did you mean: 

How to inlcude all records in a report, with a formula result of 0 or >0?

Former Member
0 Kudos

I'm reporting on classes scheduled for a certain location, and calculating the number of available seats per class. A formula will give me the net seats available (Max seats-count of students). BUT if there are no students enrolled, class does not appear on the report. Formula is {SCHEDULES.qy_sch_max} - Count ({WAITING_LISTS.no_emp}, {SCHEDULES.cd_crs)

What am I missing? Currently using Crystal 8.5 and reporting against a SQL database.

Appreciate your help.

christi

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

thanks, Sanjay - been there done that.

basically, I need to show the net result for the open seats available,which will be the same as the max seats offered.

I tried the following:

If Isnull{WAITING_LISTS.no_emp}

then {@Open}={SCHEDULES.qy_sch_max}

else

{SCHEDULES.qy_sch_max} - Count ({WAITING_LISTS.no_emp}, {SCHEDULES.cd_crs})

and got a message "Missing the then". (@Open is the formula title)

Former Member
0 Kudos

If Isnull ( {WAITING_LISTS.no_emp} )

then {@Open}={SCHEDULES.qy_sch_max}

else

{SCHEDULES.qy_sch_max} - Count ({WAITING_LISTS.no_emp}, {SCHEDULES.cd_crs});

Edited by: Sanjay Kodidine on Jun 18, 2009 12:06 PM

Former Member
0 Kudos

We're getting closer , I think - I get an error message that says a formula cannot refer to itself, directly or indirectly.

Do Ineed to exprapolate the formula within itself?

Former Member
0 Kudos

What does the Open formula contain? Try this:

If Isnull ( {WAITING_LISTS.no_emp} )

then {SCHEDULES.qy_sch_max}

else

{SCHEDULES.qy_sch_max} - Count ({WAITING_LISTS.no_emp}, {SCHEDULES.cd_crs});

Former Member
0 Kudos

Sanjay - very helpful - no errors on that formula - and it makes more sense to me;however,I'm still not getting the class scheduled events on the report that have no enrollments. There must be a problem with my linked tables.

Former Member
0 Kudos

Make sure the links are "Left Outer Joins" so that you get the class even though there are no students signed up.

Former Member
0 Kudos

Sanjay - I checked allmy linkage and reversed some connections - finally got it to work - you put me on the right track - thanks so much!.

Edited by: Christine Kaesche on Jun 18, 2009 11:34 PM

Answers (1)

Answers (1)

Former Member
0 Kudos

If there are no students enrolled for a particular class, then check for nulls and see if that helps. Not sure about CR 8.5 but in the formula editor see if you can change Exceptions for Nulls to Default Values for Nulls, otherwise try under Report Options.