Crystal Reports: Formulas Showing Blanks


Attached is my Crystal 8.5 report, and below is the SQL view that most of the fields are based on.  One of those fields is "Sales".  The name of this VIEW is "CARBON".

Also attached is a simple Word document that I call "Crystal Problem".  As you can see, from the last two columns of the data that I pasted from the report, Total Expenses and Net Profit are showing blank instead of $0.00.  The expenses in the columns (other than the "Sales" column of course) are showing at $0.00.  That's the problem.  The total for Total Expenses should be $0.00 instead of blank.  Likewise, Net Profit should be showing the same amount as Sales, since Sales - $0.00 (for expenses) = Sales.

Underneath the data in this "Crystal Problem" document are the Crystal formulas for Totals Expenses and Net Profit.  How can I modify those formulas to show $0.00, instead of blanks?

Thank you!


SELECT     ME97705.ME_Job_ID AS [Project], ME97705.ME_User_Defined AS [Editor], ME97705.DOCDATE AS [DocumentDate], ME97708.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company],
                      CASE WHEN ME97705.ME_Breakdown_Code = 'SALES' THEN ME97705.TRXAMNT ELSE 0 END AS [Sales], GL00105.ACTNUMBR_1 AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001',
                       '9150') THEN ME97705.TRXAMNT END AS [TransAmt],
                      /*COALESCE(ME97708.ME_Job_Close_Date, '') AS [Close Date],*/ CASE WHEN ME97708.ME_Job_Close_Date <> '1900-01-01 00:00:00.000' THEN ME97708.ME_Job_Close_Date ELSE NULL
                      END AS [CloseDate], CASE WHEN ME97708.ME_Job_Close_Date = '1900-01-01 00:00:00.000' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97705 INNER JOIN
                      ME97708 ON ME97705.ME_Job_ID = ME97708.ME_Job_ID INNER JOIN
                      GL00105 ON ME97705.ACTINDX = GL00105.ACTINDX INNER JOIN
                      ME97702 ON ME97708.CUSTNMBR = ME97702.MEuserdefined3
/*where ME97708.CUSTNMBR = 'BCFB'*/ UNION ALL
SELECT     ME97704.ME_Job_ID AS [Project], ME97704.ME_User_Defined AS [Editor], ME97704.DOCDATE AS [DocumentDate], ME97707.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company],
                      CASE WHEN ME97704.ME_Breakdown_Code = 'SALES' THEN ME97704.TRXAMNT ELSE 0 END AS [Sales], GL00105.ACTNUMBR_1 AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001',
                       '9150') THEN ME97704.TRXAMNT END AS [TransAmt],
                      /*COALESCE(ME97707.ME_Job_Close_Date, '') As [CloseDate],*/ CASE WHEN ME97707.ME_Job_Close_Date <> '1900-01-01 00:00:00.000' THEN ME97707.ME_Job_Close_Date ELSE NULL
                      END AS [Close Date], CASE WHEN ME97707.ME_Job_Close_Date = '1900-01-01 00:00:00.000' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97704 INNER JOIN
                      ME97707 ON ME97704.ME_Job_ID = ME97707.ME_Job_ID INNER JOIN
                      GL00105 ON ME97704.ACTINDX = GL00105.ACTINDX INNER JOIN
                      ME97702 ON ME97707.CUSTNMBR = ME97702.MEuserdefined3
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Have you tried formatting the field to show 0 instead of NULL?

Perhaps the first choice would be to "fix" your view.  Add ELSE 0 to the Case that fills the TransAmt column, in each Select.  You have an ELSE 0 for the Sales column, but not TransAmt.  So, I suspect that you're getting null in TransAmt when the account is 4001 or 9150, and if there are no other values, the summary is null, so the field comes up blank.

 If I'm right, adding ELSE 0 to the TransAmt Case will get rid of the nulls, and you should get 0 for the summary.  Make sure you change the TransAmt Case in each Select.

 If that doesn't work, there are other options, like checking for nulls in your "Total Expenses" and "Net Profit" formulas, but I'm pretty sure that changing those Case statements will take care of it.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.