Avatar of Carlo
CarloFlag for United States of America

asked on 

How to Add a Formula to Totals at the end of report

Scenario.
I have a report which shows two orders going out and a netamount of 531.58.
Problem here is that the order did not go out twice. It was backorder so when the other
items came we shipped those out.
In the report if we were to add this netamount field to the end of the report, it would be inacurate.

So I created a formula, {NetAmount}, to show me just the first order and if second order shows to show a zero for netamount.

Here is the {NetAmount} code:

if OnFirstRecord or {SYSOENT.ORDER_NO} <> Previous({SYSOENT.ORDER_NO}) then
   {SYSOENT.NET_AMT} else
0

This works fine. Problem is when I go to create a running totals field in the field explorer, the {NetAmount} Formula does not show. Where did I go wrong?
i just want to be able to add the formula and show this at the end of the report.

Thanks.
Crystal Reports

Avatar of undefined
Last Comment
Carlo
Avatar of Mike McCracken
Mike McCracken

Running totals handle this themselves.

Create the running total based on the  {SYSOENT.NET_AMT} field
Set it to Evaluate using a formula
   OnFirstRecord or {SYSOENT.ORDER_NO} <> Previous({SYSOENT.ORDER_NO})

mlmcc
Avatar of James0628
James0628

FWIW, the reason that you couldn't create a running total on that formula is because CR won't do a summary (summary function or running total) on a formula that uses OnFirstRecord or Previous/Next.  It probably has to do with the reports being built in multiple passes and things like Previous are done in a different pass from the summaries.  IAC, CR won't do it.  But you can probably get your total by incorporating the tests into the running total, as mlmcc described.

 James
Avatar of Carlo
Carlo
Flag of United States of America image

ASKER

I tried this tactic before thinking that it might work but what it shows is blank information.

Previous and Next are PrintTime functions

Are any of the amount fields NULL?

mlmcc
Avatar of James0628
James0628

A running total using that kind of formula (with OnFirstRecord and Previous) in the Evaluate section does work.  I just tried it here.  Could {SYSOENT.ORDER_NO} or {SYSOENT.NET_AMT} ever be null?  If so, that could be a problem.  In particular, I think it could cause a running total to come out blank (basically saying "no information").

 If those fields could  by null, the simplest way to handle that would be to go to File > "Report Options" and check the "Convert Database NULL Values to Default" option.  That should affect most any formula in the report except the record selection formula (which will hopefully be passed to the server).  CR 2008 also has an option in the formula editor for how that specific formula should handle nulls.  You can also use IsNull to check for nulls, but using one of those options above is a lot simpler.

 James
Avatar of James0628
James0628

Ah, he beat me to it.  If I didn't write so much, I would have won that race.  :-)

 James
Avatar of Carlo
Carlo
Flag of United States of America image

ASKER

If I just use this code:
SYSOENT.ORDER_NO} <> Previous({SYSOENT.ORDER_NO})

The first net_amount value is blank.

if I take the code entirely out and I just use sysoent.Net_Amount, I have a value of $100.48.
That is why to the code above I added 'if OnFirstRecord'

Something is wrong here and not sure what it could be.
We asked if a field could be NULL?

Crystal does some strange things when it tries to use a NULL value in a calculation.  The results are not predictable.

You definitely need the OnFirstRecord part to include the first record in the calculation.

Not( IsNull({SYSOENT.ORDER_NO})  AND Not (IsNull(Previous({SYSOENT.ORDER_NO}))
AND
(
OnFirstRecord or {SYSOENT.ORDER_NO} <> Previous({SYSOENT.ORDER_NO})
)

mlmcc
Avatar of Carlo
Carlo
Flag of United States of America image

ASKER

I am getting an error on the code above.
It says that I am missing a ')'
ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Carlo
Carlo
Flag of United States of America image

ASKER

I am glad I passed that.
Now it says: 'A field is required here', and highlights this area: Previous({SYSOENT.ORDER_NO})
Avatar of Carlo
Carlo
Flag of United States of America image

ASKER

fixed it I think. I took out 'Previous()' code and now it seems to work.
I let u know. Thanks.
You are correct.  But it should be

Not( IsNull({SYSOENT.ORDER_NO}) ) AND Not (PreviousIsNull({SYSOENT.ORDER_NO}))
AND
(
OnFirstRecord or {SYSOENT.ORDER_NO} <> Previous({SYSOENT.ORDER_NO})
)

mlmcc
Avatar of Carlo
Carlo
Flag of United States of America image

ASKER

The code worked perfectly when adding!
Thanks a lot!
Crystal Reports
Crystal Reports

Crystal Reports is a business intelligence application from SAP SE. It is used to graphically design data connections and report layouts from a wide range of data sources including Excel spreadsheets, Oracle, SQL Server databases and Access databases, BusinessObjects Enterprise business views, and local file-system information. Report designers can place fields from these sources on the report design surface, and can also deploy them in custom formulas (using either BASIC or Crystal's own syntax), which are then placed on the design surface. Crystal Reports also supports subreports, graphing, and a limited amount of GIS functionality.

36K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo