Tuesday, July 27, 2010

Crystal Reports : Show or Suppress Report Sections

While designing a report, you may find it helpful to Suppress (hide) sections, easily Revealing them later.
Within Crystal, right-click on the Section name on the left hand section of the designer.
Then select Hide or Suppress, depending on your need.
Hide will still allow you to "Drill Down" and see the section in question.
Suppress will not allow you to reveal the section at all until you right-click on it and select Show.

Crystal Reports : Changing the decimal precision on a field

In the case of "calculated" fields, especially where division is involved, the result can sometimes be a number like "10.25" or "13.53".
Crystal will default the formatting of such a result to include the decimal places as the calculation requires.
If you only wish to see the "integer" portion of the field in question, you need only to change the field formatting.

1. Right-click on the field in question
2. Select "Format Field"
3. On the "Common" tab, click "Customize"
4. Drop down the "Decimals" list to select your desired precision.
5. "Ok" your way back out.

Note : These Field Formatting options are only available on numeric fields. If your formula is returning a string, try using the ToNumber() Crystal function to convert it.

Crystal Reports : Processing Formulas Based on Parameters

Back to our {INVOICES.TOTAL} example. Let us take this one step further and relate a parameter to what's going on inside {@frmTotal}.

We will create a parameter that will control whether or not taxes are added to each Invoice.

Create a parameter called {?Tax} and give the users two options, "Tax" or "No Tax".

So,. based upon what user chooses, the formula will calculate itself differently. Here is how it might look;

{@frmTotal} :

IF {?Tax} = "Tax" THEN ({INVOICES.TOTAL} * .08) + {INVOICES.TOTAL}
ELSE {INVOICES.TOTAL}

So, you can see that if "Tax" is selected, the Invoice total adds eight percent to itself. We need not test for "No Tax" explicitly as there are only two choices.

You could also do it this way:

IF {?Tax} = "No Tax" THEN {INVOICES.TOTAL}
ELSE ({INVOICES.TOTAL} * .08) + {INVOICES.TOTAL}

Giving the end user the ability to change the way the report is calculating (rather that just modifying the Selection Criteria) is an extremely powerful way to create some longevity for your reports.

Crystal Reports : Subtotal Formulas, not Database Fields

Let us suppose we have an Invoices report. This report is subtotaling a value called {INVOICES.TOTAL}, which is simply a "raw" dollar amount for each invoice. You could simply use the "Insert Summary" menu item under "Insert" in Crystal. However, this limits you if you ever need to modify the way Invoices are subtotaled.

It would be better to create a formula field called {@frmTotal} and then Summarize that.

The code for {@frmTotal} is simply {INVOICES.TOTAL}. Depending on your back-end database value, you may need to convert it with TONUMBER{INVOICES.TOTAL}.

The reason for this is simple; now that we have control over how an Invoice total is expressed, we can do anything we want, including multiplying each invoice by a "markup", like this:

(TONUMBER{INVOICES.TOTAL} * .03) + {INVOICES.TOTAL}

Which returns each Invoice Total plus three percent of itself. Because you are still Summarizing {@frmTotal}, you need only change this in one place.

This is good practice as any Crystal Reporter knows that report usage has a way of changing. Leaving yourself "wiggle room" is always a good idea.