Print Page | Close Window

CR Skipping Over Records With Unrelated Null Value

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22609
Printed Date: 03 May 2024 at 2:48am


Topic: CR Skipping Over Records With Unrelated Null Value
Posted By: DBABaracus
Subject: CR Skipping Over Records With Unrelated Null Value
Date Posted: 23 May 2018 at 9:52am
I'm trying to build a report that shows credit loads for future semesters for students. The data is arranged something like this: 

Record ID|Semester |Grade|Attempted Credits

(id)     |2018 Fall|     |3.0


Some students have manually changed credit loads, so they have grades "VC" (Variable Credit). The formula field I'm summing in the report is just:

If [Semester] = "2018 Fall" Then [Attempted Credits] Else 0

But then in the report, it only sums records where the Grade is not blank. I've tried things to isolate the blank grades and add them in ([Grade]="", IsNull{[Grade]}), but CR still doesn't seem to consider them. I've also checked the MDB file that it's based on to validate that the data is indeed there. It's just striking me as weird, because the [Grade] field isn't a key field, linked field, or referenced in any of the formulas or filters. It feels like if it disregarded records of people because their middle initial is blank.


Replies:
Posted By: DBlank
Date Posted: 24 May 2018 at 8:08am
Is all of this data stored in one table or are you joining tables?

Posted By: DBABaracus
Date Posted: 24 May 2018 at 8:50am
The credit/grade data is all stored in one data. There are separate linked tables for student info, etc., but that's not involved in this particular calculation.

I just double-checked the database and verified that this table does include all grade records, regardless of whether or not the grade is blank/null, "VC", or a normal letter grade. So there's something in Crystal Report that's causing it to skip over the records with blank grades. Again, the grade field is not linked to any other tables, is not referenced in any formula (or group or selection formula), and is not marked as a key field. It's so weird!

Posted By: DBlank
Date Posted: 24 May 2018 at 9:02am
Look closely at your tables and joins.
Joins are not automatically enforced.
They get enforced when you use any field from both ends (or daisy chained) of the table. Sometimes the first (or only) time you use a field from a table is in a formula and all the sudden your entire data set changes. This is because the join was just enforced and your data set just altered.

Posted By: DBlank
Date Posted: 25 May 2018 at 2:39am
Also in the formula field set the option to use 'default values for null'

Posted By: DBABaracus
Date Posted: 25 May 2018 at 5:23am
Originally posted by DBlank

Also in the formula field set the option to use 'default values for null'


This did it!! I feel like such a dunce. I've spent a week or more trying to figure out every conceivable way of writing it into a formula, not realizing the formula itself was ignoring the values. Ughghgh.

Thanks a ton!!!


Print Page | Close Window