Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Getting a NULL value in a Formula Field Post Reply Post New Topic
Author Message
RiddleMeThis
Newbie
Newbie


Joined: 28 Sep 2011
Location: United States
Online Status: Offline
Posts: 6
Quote RiddleMeThis Replybullet Topic: Getting a NULL value in a Formula Field
     Posted: 11 Oct 2011 at 8:11am
I have a report that is using a recordset from a stored procedure. I want to assign a value to a formula field to use on a report. The formula looks like this:
 
if {usp_GenerateZLabelReport;1.Attribute_Name}="Lot ID" then stringVar x := {usp_GenerateZLabelReport;1.Attribute_Value}
 
The majority of the time it works great. A few times however I get a NULL value and then the value I'm looking for (browsing the formula field from within Crystal). Looking at the data being returned from the stored procedure this shouldn't be happening as there is only one record being returned that matches usp_GenerateZLabelReport;1.Attribute_Name}="Lot ID".
 
Suggestions?
 


Edited by RiddleMeThis - 11 Oct 2011 at 8:12am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 11 Oct 2011 at 8:26am
not quite sure what you are doing here, but...
your formula will evaluate for each row in your set. if, on that row, either field you are using (attribute_name or attribute_value) is null on that row then you will have a NULL returned. If you want it to return zero instead of a null change the way the formula handles nulls to use 'default values for nulls'.
HTH


Edited by DBlank - 11 Oct 2011 at 8:27am
IP IP Logged
RiddleMeThis
Newbie
Newbie


Joined: 28 Sep 2011
Location: United States
Online Status: Offline
Posts: 6
Quote RiddleMeThis Replybullet Posted: 11 Oct 2011 at 8:47am
Using default values for Nulls and the attribute_name field will never be null.
What is happening is when I browse the data I'll see two lines (this is not an array variable). The first line is blank, and the second line has the data I'm expecting to see. Kind of has me tossed for a loop as I can see no reason the first line should ever be inserted.
 
Thanks for your reply
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 11 Oct 2011 at 9:09am

oops. just notices this is text not numeric

the default will remain an empty string.
if you want to tease it out here is what I would do
add an ELSE to your statement ...
if {usp_GenerateZLabelReport;1.Attribute_Name}="Lot ID" then stringVar x := {usp_GenerateZLabelReport;1.Attribute_Value}  Else "? Why is this text here ?"
drop the formula field on your detail row
drop {usp_GenerateZLabelReport;1.Attribute_Name} on the detail row
drop Attribute_Value on the detail row
look for any row with your special text ("? Why is this text here ?") and see what is going on with the two values that make up your if-then.
Note you can filter the data to formula = ("? Why is this text here ?") if you have too many rows to sift through it.
IP IP Logged
RiddleMeThis
Newbie
Newbie


Joined: 28 Sep 2011
Location: United States
Online Status: Offline
Posts: 6
Quote RiddleMeThis Replybullet Posted: 11 Oct 2011 at 9:17am

Thanks for the suggestion, I did this and the only time I see the special text is when attribute_name <> "Lot ID", which is what I would expect. I have less than 10 rows on the report so easy to shift through the data.



Edited by RiddleMeThis - 11 Oct 2011 at 9:35am
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.