suppressing null value fields between formulas in crystal reports

[Former Member]

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?

[Former Member]

@ 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

[abhilash_kumar]

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

[Former Member]

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

tonumber(Empty) the last statement in the calculation

[abhilash_kumar]

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

[Former Member]

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;

[abhilash_kumar]

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

[Former Member]

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

[abhilash_kumar]

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

[Former Member]

I do not have a "use advance editor" link in reply window. I can only attach a link or photo.

[Former Member]

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.

[abhilash_kumar]

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

[Former Member]

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.

[abhilash_kumar]

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

[Former Member]

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;

[abhilash_kumar]

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

Do the values they return contain anything other than numbers?

-Abhilash

[Former Member]

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

[abhilash_kumar]

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

[Former Member]

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?

[abhilash_kumar]

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

Could you paste the code for both these formulas here?

-Abhilash

[abhilash_kumar]

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