Crystal Reports Help: Replace negative amounts with 0

Report Design

Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design

Topic: Help: Replace negative amounts with 0

Page of 2 Next >>

Author	Message
Nav522 Senior Member Joined: 25 Aug 2009 Location: United States Online Status: Offline Posts: 166	Topic: Help: Replace negative amounts with 0 Posted: 05 May 2010 at 8:16am
	Hello Folks, I have a report where i need to replace the negative numbers with Zero. Is there anyway that i can do this in the Format Field. Can anyone throw some light. Thanks
	IP Logged


Takesen Senior Member Joined: 29 Dec 2008 Location: United States Online Status: Offline Posts: 143	Posted: 05 May 2010 at 8:28am
	Have you tried using a case statement? or an if else statement should work. like if {table.field} < 0 then 0 Else {table.field}
	If IRL is an MMO... I guess we're still in beta...
	IP Logged

Nav522 Senior Member Joined: 25 Aug 2009 Location: United States Online Status: Offline Posts: 166	Posted: 05 May 2010 at 8:37am
	Hi Taksen thanks for getting back. I hav tried using If Else.It returns an error saying 'A Boolean is required'. Thanks
	IP Logged

Takesen Senior Member Joined: 29 Dec 2008 Location: United States Online Status: Offline Posts: 143	Posted: 05 May 2010 at 8:43am
	What's the data type of your field? the field you're using may not be a number? I just tried it with a few different sample pieces and it worked fine over here. do you have any sample data?
	If IRL is an MMO... I guess we're still in beta...
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 05 May 2010 at 8:55am
	Likely you are using the formula in the Select Expert. Use the Field Explorer and then right click on the FOrmula Fields and select New Formula then use Takesen's formula there.
	IP Logged

Takesen Senior Member Joined: 29 Dec 2008 Location: United States Online Status: Offline Posts: 143	Posted: 05 May 2010 at 9:03am
	Sorry about that. I should have addressed that part of it. My Bad! :(
	If IRL is an MMO... I guess we're still in beta...
	IP Logged

Nav522 Senior Member Joined: 25 Aug 2009 Location: United States Online Status: Offline Posts: 166	Posted: 05 May 2010 at 9:15am
	Thanks taksen and Dell for your thoughts. Yes the Field is a Number and if i use the formula inside Record Selection it throws an error saying it needs boolean. But however if i create a new Formula as Dell has mentioned it worked fine. Now i have to see if it displays zeros if the whole report has -ve amounts. Thanks
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 05 May 2010 at 9:24am
	Use the formula field to display the zero or above values in you report canvas. If it does not display on a row it is because the field was NULL. YOu can address that by flipping a setting in the formula field to 'Use default values for Nulls'
	IP Logged

Nav522 Senior Member Joined: 25 Aug 2009 Location: United States Online Status: Offline Posts: 166	Posted: 05 May 2010 at 9:33am
	Dell thanks for the thought. I was trying to figure out the setting in the formula field but unable to find. Is it in the report options? Thanks
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 05 May 2010 at 9:47am
	it is set per formula right click on the formula field and select Edit in the Formula workshop there is a pick list option usually in the upper-right hand corner of the tool bar (but it is part of the Expression Editor so it depends on where you have that moved to). It likely is currently set to 'Exceptions for Nulls" Flip it to 'Default Values for Nulls' Edited by DBlank - 05 May 2010 at 9:49am
	IP Logged

Page of 2 Next >>

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