Question Re: suppressing null value fields between formulas in crystal reports in Technology Q&A

suppressing null value fields between formulas in crystal reports

Former Member — Thu, 15 Aug 2013 22:23:04 GMT

6 formulas that retrieve the correct data from the data base, however it places spaces between the differing values as well a 6 values aren't printing on the same line. As you can see in the image that is attached there are blank lines that wont allow the all the formulas to be printed on the same line. The formulas are located within the details sections. The more formulas I have the more spaces are added. Any way to have all formulas functioning on the same line?

Re: suppressing null value fields between formulas in crystal reports

abhilash_kumar — Fri, 16 Aug 2013 08:05:23 GMT

Hi Travis,

Are the formulae placed in separate details section?

I can't really make out from that screenshot if there are 6 formulae or not.

-Abhilash

Re: suppressing null value fields between formulas in crystal reports

Former Member — Fri, 16 Aug 2013 12:03:52 GMT

Hi Abhilash,

The formulas are all within the same details field, I have them all together because that's how I would like the report to be formatted. It the screen shot that I have attached are only showing 3 formulas.

In my designed it is set up like:

F1 F2 F3 F4 F5 and so forth

However when the report generates its like:

F1 blank blank blank blank blank

blank F2

blank blank F3

It never prints two formulas on the same line. I tried putt suppression on blank fields and a field that might not be null but has a space within it. That did not work.

Re: suppressing null value fields between formulas in crystal reports

abhilash_kumar — Fri, 16 Aug 2013 12:45:04 GMT

Why do they have multiple rows anyway? Do the values in each column repeat on some rows or can there be non-duplicates for a column too?

For. eg. can it ever be:

100 blank blank blank blank

blank 600 200 blank blank

200 blank blank 100 200

Look at the 1st column.

If these are just duplicates and will never be the way I've mentioned above, try this:

1) Create a formula with this code:

whilereadingrecords;

"";

2) Go to Group Expert > Move this formula field to the Group list

3) Suppress Group header and Details Section.

4) Go to Insert Summary > Choose the 1st formula field > Choose 'Max' as the Summary operation > Place the Summary on the Group Footer 1. Do the same for all the formula fields.

This way you should have one single row with the values.

-Abhilash

Re: suppressing null value fields between formulas in crystal reports

Former Member — Mon, 19 Aug 2013 14:37:19 GMT

Hi there Abhilash,

I tried doing like what you mentioned above however I did not see any differences.

Here is what my design looks like :

Here is example of my report, you can see where four of the calculations are inserting blank spaces. I have suppression on those fields if they are null.

Re: suppressing null value fields between formulas in crystal reports

abhilash_kumar — Mon, 19 Aug 2013 14:48:25 GMT

Hi Travis,

I see that the Details Section is still un-suppressed and I also don't see Summary fields (with 'Max' as the operation) anywhere in the Group Footers.

-Abhilash

Re: suppressing null value fields between formulas in crystal reports

Former Member — Mon, 19 Aug 2013 18:08:07 GMT

Thanks I got it to work. However I know have another issue. I am trying to subtract to formulas from one another. What is the best approach to doing so?

I have this within a formula, and want to subtract final-empty. However every combination I try it seems to throw an error. Currently, this is stating The string is non-numeric. Then I remove the tonumber statement and it says a numeric value was expected.

numbervar final;
numbervar empty;

final:=tonumber(Maximum({@final gross wt.}));
empty:=tonumber(Maximum({@empty wt.}));

final-empty;

Re: suppressing null value fields between formulas in crystal reports

abhilash_kumar — Mon, 19 Aug 2013 18:19:31 GMT

What do the {@final gross wt.} and {@empty wt.} formulae return?

Do the values they return contain anything other than numbers?

-Abhilash

Re: suppressing null value fields between formulas in crystal reports

Former Member — Mon, 19 Aug 2013 19:42:54 GMT

both of those formulas return a numerical value. they only return numbers.

Re: suppressing null value fields between formulas in crystal reports

abhilash_kumar — Tue, 20 Aug 2013 05:22:47 GMT

If they return a number you don't need the tonumber() function. Try this:

numbervar final;
numbervar empty;

final:= Maximum({@final gross wt.});
empty:=Maximum({@empty wt.});

final-empty;

-Abhilash

Re: suppressing null value fields between formulas in crystal reports

Former Member — Tue, 20 Aug 2013 20:16:11 GMT

Hi,

When I do that it says a number is required. Within the two calculations that returns a number they return the maximum number, by using that function does it covert the number to anything?

Re: suppressing null value fields between formulas in crystal reports

abhilash_kumar — Wed, 21 Aug 2013 04:40:11 GMT

Are you sure {@final gross wt.} and /or {@empty wt.} return a number?

Could you paste the code for both these formulas here?

-Abhilash

Re: suppressing null value fields between formulas in crystal reports

Former Member — Wed, 21 Aug 2013 12:39:52 GMT

@ final

if ({RESULTS.TESTNO}='Liquid Reconciliation' or {RESULTS.TESTNO}='Reconciliation') and {RESULTS.ANALYTE}='Final Gross Weight'

then tonumber({RESULTS.FINAL})

@empty

if ({RESULTS.TESTNO}='Liquid Reconciliation' or {RESULTS.TESTNO}='Reconciliation') and {RESULTS.ANALYTE} like 'Empty*'

then {RESULTS.FINAL}

The empty calculation doesn't have tonumber bc it throws an error stating non-numerical

Re: suppressing null value fields between formulas in crystal reports

abhilash_kumar — Wed, 21 Aug 2013 12:59:50 GMT

Hi Travis,

Looks like the {RESULTS.FINAL} field contains characters as well. Modify the @empty formula to:

if ({RESULTS.TESTNO}='Liquid Reconciliation' or {RESULTS.TESTNO}='Reconciliation') and {RESULTS.ANALYTE} like 'Empty*'

then

(

local stringvar temp := {RESULTS.FINAL};

local stringvar Empty;

local numbervar i;

for i := 1 to len(temp) do

(

if temp[i] IN ["0" to "9"] then

Empty := Empty + temp[i];

);

ToNumber(Empty);

)

You should now be able to do final-empty as suggested in my previous reply.

-Abhilash

Re: suppressing null value fields between formulas in crystal reports

Former Member — Wed, 21 Aug 2013 16:37:10 GMT

Doing what you mentioned above to empty, throws an error saying non-numerical, and highlights

tonumber(Empty) the last statement in the calculation

Re: suppressing null value fields between formulas in crystal reports

abhilash_kumar — Wed, 21 Aug 2013 16:51:43 GMT

OK. Could you create another formula with the code below and place it beside the original formula:

IF IsNumeric({RESULTS.FINAL}) then

"Numbers"

else

"Strings"

What does the formula return?

P.S: Does the {RESULTS.FINAL} contain NULL values by any chance? In which case you can also select 'Default Values for Null' from the Drop-down on the top in the Formula Editor Window for the @Empty formula.

-Abhilash

Re: suppressing null value fields between formulas in crystal reports

Former Member — Wed, 21 Aug 2013 17:59:15 GMT

It returns NUMBERS. No the final should never have a null value.

so the formaula that is have an issue is: It says the empty needs to be a number, then I say tonumber then it says non numerical.

numbervar final;
numbervar empty;

final:= Maximum({@final gross wt.});
empty:=Maximum({@empty wt.});

final-empty;

Re: suppressing null value fields between formulas in crystal reports

abhilash_kumar — Thu, 22 Aug 2013 05:32:51 GMT

Could you attach the report with saved data please? To attach the report change its extension from .rpt to .txt and click the 'use advanced editor' link in the report window.

Make sure you commend the piece of the code in the formula that throws the error before saving the report.

-Abhilash

Re: suppressing null value fields between formulas in crystal reports

Former Member — Tue, 27 Aug 2013 14:29:59 GMT

Sorry, for the delay. I was out of town. Where is the Use Advance Edit at?

Re: suppressing null value fields between formulas in crystal reports

abhilash_kumar — Tue, 27 Aug 2013 15:14:26 GMT

Sorry for the confusion. I should've said:

"To attach the report change its extension from .rpt to .txt and click the 'use advanced editor' link in the report reply window (Here in SCN)."

-Abhilash