Tuesday, April 30, 2013

Crystal Reports : Formulas Working on Formulas

This keeps coming up; I see clients struggling with the ongoing administration of report logic to satisfy the requirements of management. Which, simply put, you should be designing your reports with an eye towards future changes.

You will undoubtedly have noticed that there is no real right way to do anything in Crystal.
A Running Total could be substituted by a Summary on a Formula, and so forth.
Where technique really matters is in duplication of labor. Effective use of Formulas is one way to cut down on this:

Let us consider a calculation on an Invoice report;


But just to make it tricky, we're making this part of a formula, because we only want our calculation to happen for a certain year;


Now to make it worse, you've got dozens of these calculation fields, all specifying different criteria (i.e. Year, Month, etc). But that's fine, you create the fields normally and everything works.

Suddenly management decides they need to increase their prices by 5%, and this must be reflected in the Invoice report.

It is easy enough to change our formula code (you'll notice our Quantity and Netprice is now encapsulated in parenthesis)


And this works too, but now you have a full days work ahead of you adding that .05 to each and every calculation field.

A better way:

Create a formula called {@frmExtPrice}:


Then change the calculation formula thusly:


This way, any changes that need to be made to the way the Invoice calculates line items can be modified in frmExtPrice once and all the calculation fields automatically "have it".

It is easy to code all your logic directly into one big formula, but keep the bigger picture in mind.

While you're at it, why not make your .05 into a parameter?

1 comment:

best essays writing in australia said...

Now people has started giving concentration on the subjects of information technology, because they knowledge that is emerging subject and it is very important to have knowledge about it.