summarize totals crystal reports

8/8/2019 Summarize Totals Crystal Reports

1/5

TechRepu blic : A ZDNet Tech Community

by Leon Tribe | Oct 17, 2003 7:00:00 AM

Tags: Leon Tribe, Crystal Reports

Takeaway: Tip for creating Crystal Reports more efficiently

10 signs that you aren't cut out to be a developerCreating reports with Visual Studio .NET has never been easier

Crystal Reports Xi - Parameter PrintingCrystal Report Label printing FormatCrystal Report Continous PrintingLike any software package, Crystal Reports has its own set of pitfalls to avoid. I'll show you one of thehandiest and most versatile tools you can have in your Crystal toolkit: the three-formula trick. Ifyou've created a report but then had to filter at the group level, rather than at the record level, youmay have noticed that your summary fields get all out of whack.

To show you what I mean, I've taken the top 20 highest-grossing movies of all time frommovieweb.com, as shown in Figure A, and used them as the data source for my report.

Figure A

k Tip: Crystal Reports' three-formula trick http://articles.techrepublic.com.com/5100-10878_11-507

10/22/2010


2/5

If I filter out all movies that made less than $300 million in the group selection, I get the results inFigur e B .

Figure B

Some of the movies are gone, but the Total Takings haven't changed. Crystals automatic summarythinks the other movies are still there. You can fix this and many other summary problems by usingthree formulas.

Formula one: takings_initThe first of the formulas, shown in Figure C, defines the variable you'll use to add up the earnings.

Figure C

The first line, which should be default in all of your formulas, specifies that the formula will be runwhen the report is printing. This means it will be set after all filters and groupings have been applied

to the data. The second line defines a new variable called takings and sets its value to zero.

Place this formula where you want the summary to reset. If you want your summary to be for eachgroup, put takings_init in the group header. If you want it to be a summary for the entire report, put itin the report header. Remember to also suppress the field so the 0 does not appear in the finalreport.

Formula two: takings_calcThe second formula is the engine of the three. This sets the rules of the summary you want toperform. In this case, I'm simply adding up the earnings as I move down through the report so the


10/22/2010


3/5


4/5

With these three formulas replacing the work of one automated summary field, the result is nowaccurate. Also, to add other summaries, you only need to add one extra display formula. The

initialization and rule for the new summary can go in the existing formulas with a different variableused to store the value.

Crystal Reports is powerful but also has its eccentricities. If you find yourself filtering with the groupselection but then discover that your summaries are going crazy, use formulas to restore the sanity.Even if you're filtering at the section level, by building the same filtering rules into your calculationformula, you can still keep your results consistent.

Another powerful way to use this trick is in subreports. By declaring the takings variable as a SharedNumberVar in all three formulas, you can have the initialization and display formulas in your main

report while the calculation formula does the work in a subreport.

10 signs that you aren't cut out to be a developerCreating reports with Visual Studio .NET has never been easierCrystal Reports Xi - Parameter PrintingCrystal Report Label printing FormatCrystal Report Continous Printing

Great article Dr Dij | 10/17/03

... ___._ | 10/23/03


10/22/2010


5/5

Alternate Solution w/o writing formulae Rod Oatis | 11/05/03

Problems with the Running Total Expert Leon Tribe | 02/11/04

Different Datasources. deetee2000 | 10/22/03

Xtal: Same report, Diff't DBs? John Galt's Smarter Broth | 01/06/04

Great to see Crystal Tips! mmadison@... | 10/22/03

GR8 for newbies....any other articles? innit_green | 10/28/03

RE: Crystal Reports' 'three-formula trick' snunn@... | 10/22/08

RE: Crystal Reports' 'three-formula trick' islappy@... | 12/04/08

RE: Crystal Reports' 'three-formula trick' softcoda | 09/09/09

RE: Crystal Reports' 'three-formula trick' sofyand@... | 08/23/10

My UpdatesMy Contacts

Would you like your own dynamic Workspace on TechRepublic?

Take two minutes and set up a TechRepublic member profile.

Would you like your own dynamic Workspace on TechRepublic?

Take two minutes and set up a TechRepublic member profile.

Popular on CBS sites: US Open | PGA Championship | iPad | Video Game Reviews | Cell Phones

About CBS Interactive | Jobs | Advertise | Mobile | Site Map

2010 CBS Interactive Inc. All rights reserved. | Privacy Policy (updated) | Terms of Use


summarize totals crystal reports

Documents