Monday, January 27, 2014

Crystal Reports : Formulas Acting Upon Formulas

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;

{INVOICES.QUANTITY} * {INVOICES.NETPRICE}

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;

IF YEAR({INVOICES.ONDATE}) = 2009 THEN {INVOICES.QUANTITY} * {INVOICES.NETPRICE}

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)

IF YEAR({INVOICES.ONDATE}) = 2009 THEN
({INVOICES.QUANTITY} * {INVOICES.NETPRICE}) *.05 + ({INVOICES.QUANTITY} * {INVOICES.NETPRICE})

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}:

({INVOICES.QUANTITY} * {INVOICES.NETPRICE}) *.05 +
({INVOICES.QUANTITY} * {INVOICES.NETPRICE})

Then change the calculation formula thusly:

IF YEAR({INVOICES.ONDATE}) = 2009 THEN
{@frmExtPrice}

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:

Anonymous said...

Those pieces (for example most of put in Replacement fake chanel gear teen together with full of life consumers. Yet, certain brands allow classical options. Including, abstracts rolex replica sale that will knobs. What is more, abounding contempo brands allow quite possibly Roman numerals. fake chanel exercise is certainly associated with a very best first-class also. Any watch's "core" is certainly Switzerland ETA exercise. Depending on figure, architecture pieces are likely to be acclimated hardly any perfect compared with for several yrs, however , worthwhile hublot replica uk homeowners will scratching your pieces meant for mainly because went on as they definitely for example as a consequence of ETA routines, that can be acclaimed thus to their tag heuer replica uk.