Ask Your Question

Report problem

asked 2019-10-25 00:49:33 +0100

jodybingo gravatar image

One of the fields on my report should print the number of Theme packs the customer is taking. Everything works fine except for the pepperoni pizza package. The field on the report is blank unless I throw a 0 in the cheese pizza field. What am I missing?
Here is the formula that is boggling me:
IF(OR([ch. pizza]>0;[pep. pizza]>0;[burgers]>0;[ch. strips]>0);SUM(IF(OR(ISBLANK([ch. pizza]);[ch. pizza]=0);0;[ch. pizza]);IF(OR(ISBLANK([pep. pizza]);[pep. pizza]=0);0;[pep. pizza]);IF(OR(ISBLANK([hotdogs]);[hotdogs]=0);0;[hotdogs]);IF(OR(ISBLANK([burgers]);[burgers]=0);0;[burgers]))&" Theme Packs";"")
The first part of the formula just checks of the customer has taken any one of our packs. If yes then it adds them all (if the cell is null it is counted as zero).
I'm on LO 6.3.2 with a MySQL8 backend on a W10 machine.
Thank you to anyone who can assist

edit retag flag offensive close merge delete



Gut feeling says you have a NULL field. NULL + anything = NULL. ISBLANK is not ISNULL. You should initialize all data coming into the report.

Ratslinger gravatar imageRatslinger ( 2019-10-25 01:35:59 +0100 )edit

Hi Ratslinger, what is involved in "initializing all data"?
Thanks, Jody

jodybingo gravatar imagejodybingo ( 2019-10-26 15:18:45 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-10-26 17:11:45 +0100

Ratslinger gravatar image


Initializing data is simply insuring there is a value in a field where needed. In you situation there is a problem with numeric data fields. When entering data, you can set the default value of 0 for the field in the table or in the control on a form. When using SQL you can also replace a NULL value:

COALESCE(`myField`,0) as `value1`

COALESCE uses the first non NULL value (can be more than just the two values depicted here).

Using various methods you can/will avoid much of this checking (ie: for blanks and NULL) in creating a report.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-10-25 00:49:33 +0100

Seen: 34 times

Last updated: Oct 26