Monday, June 4, 2012

Crystal Reports : Modular Formulas Refresher

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;

{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?

2 comments:

Lisa said...

I have a column in a database that consists of a formatted string containing titles and values associated to those titles. The problem is I want crystal to see this as a record so I can apply formatting to the section having the values.Invoice Templates

Justin Hill said...

Sounds like you need to cut apart the concatenated values in this column with a few Crystal Formulas. Then, you should be able to place the formulas where you want them within the report.
I would suggest starting with MID(), perhaps a combination of MID() and INSTR() to get the values out. Please, let me know if I can be of further assistance, thanks!