Creating Useful Report Templates

If a Crystal Report already exists that has been regularly copied in the past or a report that is viewed as the perfect report, consider it to be the beginning of an effective template. Because any report can be the basis for a template, the report designer might just need to refine a few functional or formatting characteristics to make the existing report more robust for use as a formal template.

If the report designer doesn't have any reports to use for creating templates, you don't need to be concerned. Everything you've learned so far (and in the upcoming hours) will help with effective template design. By creating a nice presentation-quality report, you have also created a likely candidate that can then be used as a useful report.

We are going to make an assumption here that you have some report creation and design skills already under your belt based on your work in the previous hours.

If you don't feel comfortable creating reports and maybe have skipped a few hours to get to this one, it might be a good idea to take a look at Hour 5.

Keep in mind a few key things when using an existing report for a template.

As previously mentioned, templates can be used to accomplish formatting tasks at lightning pace after data collection is done. Because any report can be used as a template, a Crystal Reports designer might already have a library full of ideas.

Applying one report layout as a template to another could cause some minor issues if the databases that are connected to each report are completely different in terms of schema, structure, or content. However, with some minor adjustments, the template report can be applied more effectively.

Formulas, for instance, can be problematic when applying a report as a template to another report. Because most formulas require database fields to function, they are closely tied to the actual database and structure of the data that is coming in to the report. Because formulas are used to act on database fields, using them in templates is not very effective as errors might occur when applying a template report to another report that accesses a different database. However, some tools are available that can minimize this effect. Using Custom Functions instead of prewritten formulas can alleviate some of the data dependencies, as can using the CurrentFieldValue evaluator for formatting formulas.

Also, even relatively small things can make a significant difference. Sometimes just focusing on the page headers or footers can go a long way in effective report template design. By reducing the repetitive nature of general page formatting, you will increase your report design productivity.

Using Custom Functions as Replacements for Data-Dependent Business Logic

Because we've already introduced Custom Functions in a previous hour, the focus of this section is how to use custom functions to avoid formula errors when applying templates. For more information on Custom Functions, see Hour 17, "Using Formulas and Custom Functions to Implement Complex Business Logic."

The reason Custom Functions are more useful in templates than straight formulas is that they are stateless, which means that they have no direct dependency on the database fields to get their data. Custom Functions see the data only as parameters that are passed in to the report. Instead of searching through an entire formula to find all uses of a given field, by passing it in to a Custom Function once, Crystal Reports will effectively do the search and replace repeatedly on the report designer's behalf.

Another advantage to using Custom Functions in a report template is that within the one report the template report it might be possible to use one Custom Function more than once because the logic might be used over and over with the only difference being the data that's used.

If a report that contains many formulas is applied as a template to a report that contains a different table name-for example, template.field and target.field the formulas would not change over correctly. Therefore, all the formulas will result in compiler errors upon the first run of the report to the preview. Because the report designer would have to go through all the lines of business logic and replace each and every database field occurrence, it could be a very tedious process. If the search-and-replace time could be limited to one line per formula, the report designer would be far more productive.

Of course, current formulas in a pre-existing report are already working and we would not want to break them. Or would we? What if there were some compelling reasons? (Like the advantages previously mentioned?) What if the formulas could be taken logic and all and converted into a Custom Function structure in one step? Sounds pretty painless, doesn't it?

By using the Formula Extractor, which is new to Crystal Reports 9, the Crystal Reports designer will actually review the existing formula, break it down, find the data-specific pieces, convert them into parameters, and reformulate the formula to accept those parameters and save it as a Custom Function. It will even rebuild the initial formula that created the Custom Function to apply the new Custom Function so that the report designer doesn't have to go back and perform that step manually.

For more information on the Formula Extractor, see Hour 10, "Understanding and Implementing Formulas."

Even after formulas are converted, there will still be the need to make adjustments for data-specific fields that would need to be passed in as parameters to those functions. However, because most Custom Functions reduce the lines of code, and pass in the data only once, the search-and-replace tasks are greatly reduced.

In general, to make formulas even easier to work with, use the new Formula Workshop as much as possible. This virtual all-in-one workspace for formulas means that navigation between formulas is quick and easy without having to open each one up separately or guess at their names.

Another good idea to keep in mind if there is concern about losing old formula logic when converting the formula, just comment out the old formula code and put the Custom Function in. Of course, commenting each line by hand can be cumbersome. By using the Comment/Uncomment (//) button on the toolbar, you can highlight all the contiguous lines of code you want to comment out, and then click this button. It will comment them all out in one quick step.

Using the CurrentFieldValue Function

When using formulas to create conditional formatting, they are usually designed to be data dependent so much so that the database field name is used at every opportunity. However, to make formatting formulas more portable (and reusable), use the CurrentFieldValue formatting function instead of the actual field name that would always change depending on where the formula is located.

CurrentFieldValue is a special signifier in the formula language that tells the formatting formula to look at the value of the field it is associated with, without actually having to know the name of the field. This is advantageous in two ways:

• For general formatting, this allows for copying of formatting formulas and reusing the formatting formulas within a single report or within multiple reports without having to replace data-specific field names.

• For template formatting, this is especially useful because we can't be sure that the database field is going to be of the same name, let alone of the same data type.

By keeping the reuse factor in mind when creating and maintaining formulas from now on, creating effective templates will become much easier over time.