Set a field to NULL
Printed From: Crystal Reports Book — Forum Name: Technical Questions
Posted By: lolly54 — 10 Oct 2013 at 1:08am
I have a tricky question... If the field {Product.Number} is 0, can I set it to NULL in crystal? Anyone? Thanks!
Posted By: DBlank — 10 Oct 2013 at 3:34am
Not really. There is a trick to do this with strings but I dont think it works for numbers.
However if it is for display you can conditionally suppress it, if it is for calculations you can use shared variable formula or Running Totals to exclude them from your calculations.
Posted By: lolly54 — 10 Oct 2013 at 9:53pm
I tried suppressing it if "currentfieldvalue = 0", however, the number is still showing. In the end, I set the font colour condition to display white if it is 0. This is not the perfect way to do, at least it is "working" now. Thanks! :)
Posted By: DBlank — 11 Oct 2013 at 4:11am
Is it possible it is not actually zero but displaying as zero because it is rounding down to display the integer. Others have had this issue and resoved it using a <1 condition. You could use a round(x,0) or for suppression
round(currentfieldvalue,0)=0
It does not make sense that your conditional color formula works but perhaps it is written just differently enough to account for something.
Also be aware that if you are summing the field it will sume the actual field value not the displayed (or hidden) 0 so it can alter your expected results.
Posted By: Traceyc — 15 Oct 2013 at 2:58pm
You can set a 0 number to null by creating a formula with nothing in it and saving it. Reference it as: if{Product.Number} = 0 then {@Null} else {Product.Number} (or something similar). This works perfectly when dealing with strings. If you get an error saying something like it wants a number or wants a string just use tonumber({@Null}). Also works with dates, just use date({@Null}).
Posted By: Shruthi — 21 Jul 2016 at 7:44pm
The other simplest way to set a 0 to NULL is, changing the font of 0 to white. This can be done by 1) Right click on the required field and select 'Format Field' 2) Select 'Font' 3) Then select 'Color' 4) Write the formula as if {@number}=0 THEN crWhite Note : @number is a formula field
|
