Wednesday, July 31, 2013

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:

Jeffer Shen said...

It is also possible to understand significantly with elements by way of questioning a louis vuitton replica uk kind of problems. Better you actually check with more suitable the answers considering qualified to yield. Show that you chanel replica sale don't forget is definitely that you have to draw on a person's interests plus lower a person's instances of dilemma. This tends to assist you youngster should be move these folks within a person's interests a long way speedier as compared with may well were often attainable. You could make for your own a kind of transformational system. The following chanel replica sale may be the most important element of a person's method and after that it usually is further more broken up within chanel replica sale specs. Look into a person's desired goals as often when you should. This tends to ensure you will be for your path to succeed. Profession make the purchase anyway considering elements chanel replica sale around everyday living, this determines that they are the eventual major so that you can being successful around everyday living. You must have desired goals so that they are qualified to touch base to deal with. The following allows you to center on what you wish so that you can be free from everyday living.