Optimizing The Crystal Reports Side

This chapter describes methods that can be used to optimize report performance with Oracle using Crystal Reports’ internal features. Reducing the number of records returned from the database server is emphasized, both by using grouping on the server and by using SQL Expressions in the selection formula. Reducing the number of queries sent to the server by eliminating subreports is described, and moving processing to the server by replacing Crystal Reports formulas with SQL Expressions is covered in detail. An Oracle SQL Expression equivalent for almost every available Crystal Reports operator or function is listed, and methods of calculating running totals on the server are explained. The use of SQL Commands to implement queries that would otherwise be overly complex or impossible is also covered.

Reducing the Number of Records

Reducing the number of records returned to Crystal Reports is the single most important method for optimizing report processing because fewer records means less network travel time and less report processing time.

Grouping on the Server

Crystal Reports has an option called Perform Grouping On Server. If this option is turned on and only group level data is displayed in the report, Crystal will request only summary level data from the server, thereby reducing the number of rows that are returned. Perform Grouping On Server can be set individually for a report or as a default for the environment. To set it for a report, choose File | Report Options and check Perform Grouping On Server, as shown in Figure 6-1; another method is to go to the Database menu item and check Perform Grouping On Server.

Figure 6-1: Report options

To set the default value for the environment, which will affect all new reports, choose File | Options, go to the Database tab, and check Perform Grouping On Server, as shown in Figure 6-2.

Figure 6-2: Database options

To demonstrate grouping on the server, say that a report showing the total order amount for each employee is needed. Create a report using the ORDERS table, and put EMPLOYEE_ID, ORDER_AMOUNT, and ORDER_ID in the detail section. A query similar to the one shown in the following illustration will be generated, and the report will display the EMPLOYEE_ID, ORDER_ID, and ORDER_AMOUNT for each order.

Add a group on EMPLOYEE_ID and then a subtotal on ORDER_AMOUNT by EMPLOYEE_ID to get the total for each employee. Running this report will cause 2192 records to be returned from the server. This is report Chapter 6Order Amount by Employee with Detail.rpt.

Now suppose that the detail by ORDER_ID is not needed, so the detail section can be suppressed, which causes the query to change to one similar to the following illustration. The number of records returned is now reduced to six. This is report Chapter 6Order Amount by Employee with detail suppressed.rpt. To see the difference between this query and one with no grouping, go to the Database menu item, uncheck Perform Grouping On Server, and rerun the report. You will see that 2192 records are returned and the database query has lost its GROUP BY clause.

To continue, recheck Database | Perform Grouping On Server which is necessary for future report runs.

The user will be thrilled that the report runs so much faster now but might want the option to see the detail for each employee by order via drill-down. To implement drill-down, the section must be hidden rather than suppressed. Hiding the detail produces the summary query, and returns only six records as before, but drilling down on an EMPLOYEE_ID generates another query that returns the detail records for that employee. For example, drilling down on EMPLOYEE_ID 1 will return 360 records. This is report Chapter 6Order Amount by Employee with detail hidden.rpt. In total, with one drill-down, 366 records are returned, instead of 2192.

Grouping on the Server versus Returning All Detail

When to use grouping on the server rather than returning all detail rows will depend on the specific environment. When grouping on the server, drilling down on only a few of the grouped values is more efficient than returning all of the detail records, but creating a report that uses grouping on the server when users are likely to drill down on a large portion of the summary records is not as speedy or resource efficient as returning all of the detail records in one query. Using drill-down with a server- grouped report generates many queries that return few records. Each network trip and each query parsed on the server involves overhead. If users are likely to drill- down on a large number of the summary records, you should turn off grouping on the server and let all of the detail records be returned in the main report query.

Using SQL Expressions in Selection Formulas

Reducing the number of records returned is accomplished primarily through the use of a selection formula. The selection formula determines the criteria that a row must meet in order to be returned by the query. Some record selection criteria defined in Crystal Reports selection formulas are passed through to Oracle, resulting in few records being returned; some are not. Any record selection criteria defined using the Select Expert is passed through, but group selection criteria defined using the Select Expert is not. Manually created criteria may be passed through, depending on their content. Any selection formula that contains references to Crystal Reports formulas, uses Crystal Reports functions other than IsNull (and a few others), or otherwise performs computations, cannot be passed through.

Appropriate use of SQL Expression fields can force record selection to occur on the server. Using a selection formula that contains functions that are not converted to their native Oracle equivalents causes all records to be returned (not excluded by other portions of the selection formula) and local filtering to be performed. For example, say you have a selection formula that returns employee records only if the employee’s birthday is in January:

Month ({EMPLOYEE.BIRTH_DATE})=1

Open the report named Chapter 6Employee Birthdays with Crystal function.rpt and preview it. Look at the SQL query, shown in the next illustration, and notice that nothing was added to the query to accomplish the birth date filtering. Therefore, all detail records will be returned from the server, and the filtering will be done locally.

Look at the Report | Performance Information, then at the Processing section. You will see that Record Selection Done on the Database Server is listed as Partial, as shown in Figure 6-3.

Figure 6-3: Employee birthdays with Crystal function, performance information

If you modify the selection formula to use a SQL Expression, you can force the record selection to take place on the server. Create a SQL Expression named BirthMonth for computing the birth month using the following formula:

EXTRACT (MONTH FROM "EMPLOYEE"."BIRTH_DATE")

Modify the record selection formula to be as follows:

{%BirthMonth} = 1.00

Rerun the report and examine the SQL query.

It now shows that the selection is being performed on the server. This is confirmed by the Report | Performance Information, as shown in Figure 6-4.

Figure 6-4: Employee birthdays with SQL Expression, performance information

Using SQL Expressions as replacements for Crystal Reports functions is discussed in more detail in the “SQL Expressions” section later in this chapter. Modifying your selection formulas to remove Crystal functions and replace them with SQL Expressions is a simple and highly effective method to ensure that record selection occurs on the server.

Parameters

Parameters can be used in the selection formula to limit the number of records returned. This is a commonly used method because it is effective and intuitive. To specify a parameter value so that only the data the user is concerned with is returned, insert a parameter field into the selection formula as shown:

{Orders.SHIPPED}= {?Shipped}

The use of parameters is covered in more detail in the Crystal Reports User’ Guide and in other third-party references. Optimizing how Oracle parses parameterized report queries is covered in Chapter 8.

Miscellaneous Hints

In addition to limiting the number of records returned to Crystal, there are other methods to help optimize reporting.

Limiting Subreport Usage

The use of subreports can be very costly. If a subreport is placed in a detail section, it will be executed once for every detail row, that is, the subreport query will be sent to the database once for every detail row in the main report. A subreport placed in a group section will execute once for each different group value. A subreport placed in the report header or footer will execute only once for the entire report.

Many reports with subreports can be modified easily to eliminate the subreports and hence the multiple queries. Open Chapter 6Order Detail with subreport.rpt. Run the report and scroll to the last page. You will notice that it takes a very long time to scroll to the end of the report. The lengthy time is due to the subreport being executed once for each detail record in the main report. For the sample order data, that means one query was run for the main report, and 2192 queries were run for the subreport; as shown in Figure 6-5, the subreport’s Performance Timing has a large value for Time to Format All Subreport Instances. (Your actual values may differ from the figure due to differences in your environment.)

Figure 6-5: Order detail with subreport, performance information

Open Chapter 6Order Detail without subreport.rpt. Run the report and scroll to the last page. Notice that the data displayed is the same as in the Order Detail with subreport report, but that there was a substantial decrease in the time required to scroll to the last record. This is achieved because the Order Detail without subreport report joins the ORDERS table to the ORDERS_DETAIL table so that the detail information is available in the main report, cutting the number of queries required to one and resulting in a substantial improvement in performance. However, the join results in a record set with multiple copies of a master row where there are multiple detail rows for that particular ORDER_ID. For example, ORDER_ID 1002 appears twice in the dataset, along with its ORDER_AMOUNT of $5,060.28. Special care must be taken when summarizing the master-level detail because of the effect of the join.

There are several options for proper handling of the duplicate master level data, depending on your needs. In the report shown, the master level fields have been moved into the group header. When a detail field is placed into a group header, Crystal Reports displays the value from the first record in the group. This option is adequate in this case because you are only displaying the master level data and not using it in computations. To use the master level data in computations, however, you would need to create a formula field that returned the maximum or minimum of the master level field and use that in any other aggregations. Because the values of the master table fields will be the same for every record in the group, any function that returns the unique value will work. You could also use the Oracle equivalent for maximum or minimum to ensure that the processing occurs on the server. A final option is to re-create the summary level data from the detail data. In this case, multiplying the UNIT_PRICE by the QUANTITY and then summing across the group will return the group’s ORDER_AMOUNT.

Connection Type

The available connection types are discussed in the Chapter 1. In most situations, the native driver will be the fastest possible connection type. If an ODBC connection is required, the Wire Protocol driver may deliver the best performance. The Wire Protocol ODBC driver may be even faster than the native driver. You must experiment in your environment, given any restrictions that may exist, to discover the best connection type for your situation. As the various drivers are updated, they may leapfrog each other in performance, so it is important to stay current.

Use Union All Instead of Union

Use Union All instead of Union where possible. Union requires extra processing to filter out duplicate rows. If you know that there will never be duplicate rows, using Union All will speed up query processing.

Avoid Sort in Specified Order

Avoid use of the Crystal option to sort in a specified order because it causes the sort to occur locally, and it cannot be done on the server.

Indexed Fields

Using fields that are indexed is important in two cases. Any fields that are used for linking tables should be indexed. The Database Expert will link By Key if you so choose, as shown in Figure 6-6. Note that the By Key option does not work in the originally released version of Crystal Reports 9. You must apply the most recent hot fix to correct this problem.

Figure 6-6: Database Expert, Links tab

The other case where indexing is important is in the selection formula. Fields used often for filtering should be indexed to prevent full table scans and speed up the query. See Chapter 7 for more information about appropriate database indexing.

Avoid Linking Stored Procedure Datasets to Other Objects

When using stored procedures as Crystal datasets, avoid linking those datasets to other tables, views, or stored procedures. This type of operation cannot be done on the backend and forces the linking operation to occur locally. Instead, return all required fields from the stored procedure itself.

Large Character Fields

Oracle supports several string field datatypes: CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB, and LONG. However, LONG will be phased out in the future and should be replaced with CLOB or another appropriate type. Crystal treats each field as either a String or a Memo. The Oracle character types CHAR, VARCHAR2, NCHAR, and NVARCHAR2 are treated as Strings of the defined length. The Oracle LOB types, CLOB, NCLOB, and LONG, are treated as Memo fields.

Field types that Crystal considers to be String (CHAR, VARCHAR2, NCHAR, NVARCHAR2) can be used for grouping and sorting. All of the Oracle character types can be used in formulas, although Crystal reads only the first 64K of any LOB field. Some field types are not usable by some drivers; see Table 1-1 in Chapter 1 for details.

Because some of these field types can hold a very large amount of data, it is important to reduce traffic by returning only what is truly required for any given report. Though computations can now be done on most of these field types, you should make an effort to perform any required computations with SQL Expression fields rather than formulas, especially if the computations reduce the size of the string that is returned. Likewise, although sorting and grouping on large string fields is supported, it should be avoided. If necessary, a substring of the total should be created and then grouped or sorted on, instead of the entire field.

SQL Expressions

Moving processing from the Crystal Reports side to the server side can have a minimal to substantial impact on report processing time, depending on the particular type of processing involved. In some instances, it could actually adversely affect total processing time, so each environment should be individually evaluated. If, for instance, in a client/server environment, the database server is slow and has little memory, the client machines are fast, and the network is speedy, then allowing a large amount of processing to happen on the client side is advantageous. In the more common situation of a powerful server, so-so network, and mediocre clients, forcing processing to take place on the server would clearly be advantageous. In a web environment, the comparison must be between the power of the database server, the power of the report/web server, and the capabilities of the network.

Crystal Reports allows you to create selection formulas. Selection formulas are equivalent to the WHERE clause in a SELECT query. Crystal selection formulas have no SQL counterpart, as SQL Expressions are the Crystal regular formula counterparts. Selection formulas are always written in Crystal or VB syntax like regular formulas. Crystal Reports converts some operators and functions to their Oracle equivalent when they are used in selection formulas, but it does not convert all, or even most, operators or functions. Whether an operator or function is automatically converted when used in a selection formula is documented in the “Operators” and “Functions” sections, later in this chapter. Replacing expressions in a selection formula with SQL Expressions when Crystal does not perform the conversion automatically guarantees that the filtering is done on the server.

Using native Oracle functionality rather than Crystal Reports formula functionality can affect the speed of report processing even when not used in the selection formula. The tables in the “Operators” and “Functions” sections list an alternative Oracle method that can be implemented in a SQL Expression for each type of Crystal Reports formula function and operator. Substitutions in a selection formula always have a positive impact, but other substitutions should be used with care, after comparing processing times. SQL Expressions created in Crystal Reports become expressions in the Select list of the resulting database query. They are usually similar to Crystal formulas and compute some value. However, an entire SELECT statement can be entered as a SQL Expression if it returns a single value and is not correlated to any field in the main query.

Within Crystal Reports formulas, the user can create straightforward formulas or entire blocks of code. Blocks of code cannot be directly translated into valid Oracle equivalents that can be passed in the SELECT clause of a query. Whenever block- type processing is required, you must create an Oracle function or procedure; you cannot create a PL/SQL block within a Crystal Reports SQL Expression. The Oracle equivalents listed in the tables in the next section assume that you are creating a SQL Expression.

Converting a Crystal formula to a SQL Expression changes the time at which the computation is evaluated as well as where it is evaluated. When using a Crystal formula, the earliest the computation is performed is while Crystal is reading the records, and the computation is performed on the client (or web server). When using a SQL Expression, the computation is evaluated on the Oracle server before Crystal begins to read any records.

Included with the XTREME Oracle sample database, as modified for this book, are PL/SQL functions that can be used to duplicate Crystal Reports functions in Oracle that cannot be duplicated easily in a SQL Expression. The code listing is shown in Appendix B, along with instructions for running it.

Operators

The possible Crystal Reports operators are listed in the Operators box of the Formula Editor, which is docked in the upper-right of the Formula Editor by default, as is the Operators box for the SQL Expression Editor. Each Crystal operator is listed in the tables in this section in the order that it appears in the Operators box as shown in the next illustration.

Arithmetic

Note that arithmetic operators, when used in a Selection formula, will never be converted to their Oracle equivalents. For example, if your Selection formula was the following:

({fieldA}+{fieldB})>100

the Oracle equivalent would be as shown:

WHERE (fieldA+fieldB)>100

Nevertheless, using the selection formula shown, Crystal Reports will not add this to the WHERE clause, and the record selection for this condition would be performed locally. If an equivalent SQL Expression field is built and used in the Selection formula, then the condition (and the processing) will be passed to the server.

Table 6-1 lists all available Crystal arithmetic operators as shown in the illustration and their SQL Expression equivalents.

Conversion

The currency conversion function applies Crystal Currency Formatting, so no Oracle equivalent is possible.

Comparisons

Note that Crystal Reports Comparison functions return the Boolean value of True or False when used independently of an IF statement or other programming construct. Oracle does not have a native datatype equivalent to Boolean, so the example functions return the string ‘True’ or ‘False’. They could easily be modified to return 1 or 0, if a numeric result was desired, or any other reasonable values. Also, note that all of the Crystal Comparison functions are automatically converted to their Oracle equivalents when used in selection formulas, unlike most other Crystal operators and functions.

The Crystal comparison operators are equal (x=y), not equal (x<>y), less than (xy), less or equal (x<=y), and greater or equal (x>=y). For each operator used in a Crystal formula or selection formula, an Oracle CASE statement can be substituted. For example, a Crystal formula such as the one shown here

{EMPLOYEE.SUPERVISOR_ID} = {EMPLOYEE.REPORTS_TO}

could be converted to the following SQL Expression:

CASE WHEN "EMPLOYEE"."REPORTS_TO" IS NULL THEN NULL WHEN "EMPLOYEE"."REPORTS_TO" = "EMPLOYEE"."SUPERVISOR_ID" THEN 'True' ELSE 'False' END

The = operator can be replaced with any of the other operators. Note that the complexity in the Oracle solutions is due to the differing default treatment of nulls between Crystal Reports and Oracle, and that other possible solutions exist. For instance, for the equality operator, here is an alternative SQL Expression:

NVL2("EMPLOYEE"."REPORTS_TO", DECODE("EMPLOYEE"."SUPERVISOR_ID", NULL, NULL, "EMPLOYEE"."REPORTS_TO", 'True', 'False'), NULL)

If you are using comparisons in other programming constructs where a Boolean is expected, such as an IF statement, the Oracle comparison operators you can use are the same as the Crystal Comparison operators.

Strings

Note that using any of the Crystal Reports String operators in a selection formula will cause that portion of the formula to be evaluated locally.

Table 6-2 lists all available Crystal string operators, as illustrated next, and their SQL Expression equivalents.

Ranges

Range expressions used in selection formulas are converted to Oracle equivalents.

Table 6-3 lists all available Crystal range operators, as shown in the illustration, and their SQL Expression equivalents.

Boolean

Note that, as previously discussed, Crystal Reports Boolean operators return the Boolean value of True or False, but Oracle does not have a native datatype equivalent to Boolean. Additionally, null conditions are not explicitly checked in these examples, so results can vary from the Crystal formula result if either operand is null.

Of the Crystal Reports Boolean operators, NOT, AND, and OR are converted to their Oracle equivalents when used in selection formulas.

Table 6-4 lists all available Crystal Boolean operators, as shown in the next illustration, and their SQL Expression equivalents.

Arrays

Array manipulation using native Oracle functionality is not possible inside a Crystal Reports SQL Expression.

Patterns

Note that both of the Crystal Reports Pattern operators convert to their Oracle equivalent automatically when used in selection formulas. The Crystal wildcard characters ? and * are equivalent to the Oracle wildcard characters _ and %.

Table 6-5 lists all available Crystal string pattern operators, as shown in the illustration, and their SQL Expression equivalents.

Control Structures

Null conditions are not specifically checked in the following examples, so differences between the results could exist if any of the operands are null.

Table 6-6 lists two of the available Crystal control structures shown in the illustration and their SQL Expression equivalents.

The remaining Control Structure operators are related to looping. It is not possible to use a looping structure in an Oracle SQL Expression.

Other

Parentheses can be used as expected inside SQL Expressions. The remaining Other operators are not usable inside an Oracle SQL Expression.

Scope

The Scope operators are not usable inside an Oracle SQL Expression.

Variable Declarations

You cannot declare variables inside an Oracle SQL Expression.

Functions

Available Crystal Reports functions are listed in the Functions box of the Formula Editor as shown in the illustation. The Functions box is docked in the upper middle of the Formula Editor by default.

Math

Note that none of the Crystal Reports Math functions convert to its Oracle equivalent automatically when used in selection formulas.

Table 6-7 lists all available Crystal math functions, shown in the following illustration, plus the exponentiation operator, and gives SQL Expression substitutes.

Summary

Note that for each Crystal summary function that has a conditional (group by) field in it, there must be a corresponding group in the report. No such restriction exists for the Oracle equivalents. The required grouping is done transparently on the server, and summaries are returned for each detail row.

The Crystal Reports summary functions are used when an aggregate value is needed in a detail row for some computation. The Oracle implementation of this type of function is called Reporting Aggregate Functions and it returns the same value for each row in a given partition or group. Oracle also defines Windowing Aggregate Functions that can be used to create moving aggregates, such as moving averages; cumulative aggregates, such as running totals; Ranking Functions that can be used to create rankings; cumulative distributions and percentiles; and Linear Regression Functions that can be used to compute regression statistics. See the “Analysis” section in Chapter 4 for details on the Oracle Analysis functions.

Note that the Oracle equivalents for the Crystal Summary functions cannot be used in Group Selection formulas. They return values for each row in the result set, even though the value may be the same for a particular set of rows. Because they are per row, they can be used in SQL Expressions, although they cannot be used in Record Selection formulas. Oracle will not allow analytical functions to be used in the WHERE clause. To enable group selection on the server, see the “HAVING Clause” section in Chapter 4.

Each summary function is shown with two forms: the most basic form, which is a summary over the entire report, and the more complex form, where the summary is for a particular group. Summary functions using the additional DateTime condition options are shown at the end of the table for the SUM summary function only.

Table 6-8 lists all available Crystal summary functions, shown in the illustration, and gives SQL Expression substitutes.

Nth Largest

To compute the Nth largest value and return it in every detail row, you can use a SQL Command with a query similar to the following. Note that this case shows a rank by a grouping. Because ties are possible and only one Nth value per grouping is needed, you must add a tiebreaker field to the Rank() function. In the following example, EMPLOYEE_ID was used. The choice for N could be converted to a parameter to allow the user to select N at runtime. Note that this query returns the second largest salary for a position. If a position has only one employee, null is returned.

SELECT e.EMPLOYEE_ID, e.FIRST_NAME, e.SALARY, POSITION, e.REPORTS_TO, s.NthSalary FROM XTREME.EMPLOYEE e LEFT OUTER JOIN (SELECT Position, NthSalary FROM (SELECT Position, SALARY NthSalary, RANK() OVER (PARTITION BY Position ORDER BY Salary DESC, Employee_Id) N FROM Employee) WHERE N=2) s USING (position) ORDER BY POSITION, e.REPORTS_TO

Nth Smallest

To get the Nth smallest value, just change the sort order to ascending:

SELECT e.EMPLOYEE_ID, e.FIRST_NAME, e.SALARY, POSITION, e.REPORTS_TO, s.NthSalary FROM XTREME.EMPLOYEE e LEFT OUTER JOIN (SELECT Position, NthSalary FROM (SELECT Position, Salary NthSalary, RANK() OVER (PARTITION BY Position ORDER BY Salary ASC, Employee_Id) N FROM Employee) WHERE N=2) s USING (Position) ORDER BY POSITION, e.REPORTS_TO

Mode

To get the mode, you can create a SQL Command with a query like the following. Because there may be multiple modal values, the minimum is chosen. This is consistent with the Crystal Reports Mode function.

Note that this query uses the subquery factoring clause of the Oracle SELECT command.

SELECT e.EMPLOYEE_ID, e.FIRST_NAME, e.SALARY, POSITION, e.REPORTS_TO, s.Min_Modal_Salary FROM XTREME.EMPLOYEE e LEFT OUTER JOIN (WITH Counts AS (SELECT Position, Salary, COUNT(Salary) M FROM Employee GROUP BY Position, Salary), MaxCounts AS (SELECT Position, MAX(M) MaxM FROM Counts GROUP BY Position) SELECT Counts.Position, MIN(Counts.Salary) Min_Modal_Salary FROM Counts, MaxCounts WHERE Counts.Position=MaxCounts.Position AND Counts.M=MaxCounts.MaxM GROUP BY Counts.Position) s USING (Position)

Nth Most Frequent

To get the Nth most frequent value, you can create a SQL Command with a query like the following. Again, because there may be multiple values that occur with Nth frequency, the minimum is chosen. This is consistent with the Crystal Reports NthMostFrequent function.

SELECT e.EMPLOYEE_ID, e.FIRST_NAME, e.SALARY, POSITION, e.REPORTS_TO, f.NthMostFrequent FROM XTREME.EMPLOYEE e LEFT OUTER JOIN (WITH Ranks AS (SELECT Position, Salary, RANK() OVER (PARTITION BY Position ORDER BY Salary) R FROM Employee), RankCounts AS (SELECT Position, R, COUNT(R) N FROM Ranks GROUP BY Position, R) SELECT DISTINCT Position, MIN(Ranks.Salary) NthMostFrequent FROM Ranks JOIN RankCounts USING (Position, R) WHERE N=2 GROUP BY Position) f USING (Position)

Financial

Equivalents of the Crystal Financial functions created as Oracle functions can be created, but they are too complex for inclusion here.

Strings

Note that none of the Crystal Reports String Functions converts to its Oracle equivalent automatically when used in record selection formulas.

Table 6-9 lists all available Crystal string functions shown in the illustration and gives SQL Expression substitutes.

Date and Time

None of the Crystal Reports Date and Time functions converts to its Oracle equivalent automatically when used in record selection formulas.

Table 6-10 lists all available Crystal date and time functions, shown in the illustration, and gives SQL Expression substitutes.

Date Ranges

The Date Range functions return ranges. Returning ranges is not possible in a SQL Expression.

Arrays

The Array functions return arrays. Returning arrays is not possible in a SQL Expression.

Ranges

The Range functions return Boolean values. Returning Boolean values is not possible in a SQL Expression.

Type Conversion

Crystal formula syntax includes several type conversion functions as shown in the following illustration. Table 6-11 lists them and gives SQL Expression substitutes.

Programming Shortcuts

Note that none of the Crystal Reports Programming Shortcuts functions converts to its Oracle equivalent automatically when used in record selection formulas unless no database fields are included.

Table 6-12 lists all available Crystal programming shortcuts, shown in the illustration, and gives Oracle SQL Expression substitutes.

Evaluation Time

The evaluation time functions deal with the Crystal Reports processing passes. All Oracle processing will happen in Pass 1, during the WhileReadingRecords phase.

Print State

Crystal Reports contains many Print State functions as shown in the next illustration. Some Print State functions are applicable to the report itself and have no Oracle equivalents. These functions are PageNumber, TotalPageCount, PageNofM, GroupNumber, RecordSelection, GroupSelection, InRepeatedGroupHeader, and DrillDownGroupLevel. They are not shown in the following table. RecordNumber, OnFirstRecord, and OnLastRecord have Oracle equivalents that are applicable in some circumstances, but not all circumstances. Print State functions that have Oracle equivalents are shown in Table 6-13.

Document Properties

The Document Properties functions concern report specific values and have no Oracle equivalents.

Alerts

The Alert functions concern report specific values and have no Oracle equivalents.

Note that Oracle contains a Publish/Subscribe feature that allows messages to be sent to users when defined conditions are met in the database.

Additional Functions

Oracle equivalents for Crystal’s Additional functions can be created as Oracle functions if desired.

Running Totals

Crystal Running Totals can be moved to the server for processing by using Oracle’s Windowing Aggregate Functions. For a full discussion of this capability, see the section “Reporting Aggregate Functions” in Chapter 4.

For an example, create a Crystal Running Total, as shown in Figure 6-7, for a report that uses the EMPLOYEE table and is grouped by EMPLOYEE.POSITION and sorted by EMPLOYEE.REPORTS_TO.

Figure 6-7: Crystal Running Total 1

Put the running total field on the report, and then create a SQL Expression using the following code:

SUM("EMPLOYEE"."SALARY") OVER (ORDER BY "EMPLOYEE"."POSITION","EMPLOYEE"."REPORTS_TO" ROWS UNBOUNDED PRECEDING)

Put the SQL Expression field on the report and compare its values to the Running Total. They should match.

Next, modify the Running Total so that it resets for each EMPLOYEE.POSITION group, as shown in Figure 6-8.

Figure 6-8: Crystal Running Total 2

Modify the SQL Expression as shown:

SUM("EMPLOYEE"."SALARY") OVER (PARTITION BY "EMPLOYEE"."POSITION" ORDER BY "EMPLOYEE"."POSITION","EMPLOYEE"."REPORTS_TO" ROWS UNBOUNDED PRECEDING)

Again, the values should match.

For a conditional running total, modify the running total field to evaluate only if the salary is greater than $35,000. The formula in the Evaluate section should be as follows:

{EMPLOYEE.SALARY}>=35000

Modify the SQL Expression as follows:

SUM(CASE WHEN "EMPLOYEE"."SALARY">=35000 THEN "EMPLOYEE"."SALARY" ELSE 0 END) OVER (PARTITION BY "EMPLOYEE"."POSITION" ORDER BY "EMPLOYEE"."POSITION","EMPLOYEE"."REPORTS_TO" ROWS UNBOUNDED PRECEDING)

The results should match.

SQL Commands

Crystal Reports constructs a SQL query for the developer from choices that are made in wizards or elsewhere in the development environment. Crystal Reports 9 allows the user to create a query by simply typing in a command that is valid for the particular database in the connection. This feature is called a SQL Command.

SQL Allowed in SQL Commands

Crystal documentation states only that any SQL acceptable to the database can be executed in a SQL Command object. This is not very detailed information. So what exactly can be done in a Crystal SQL Command?

The SQL Command must result in a dataset or an opened REF Cursor. There are two types of statement structures that work from SQL Commands for Oracle. The first is any valid Oracle SELECT statement, no matter how complex, entered as the only command. Entering multiple commands one after the other causes errors. The second structure that works is an anonymous PL/SQL block that calls a REF Cursor stored procedure. Using the second structure, other commands may be executed after the call to the stored procedure. The syntax is shown next:

BEGIN Stored_Proc([Parameters]); Other statements END;

Experimentation determined that the call to the stored procedure must come first, otherwise an error results. The other statements must not open another REF Cursor, but they can do Inserts, Deletes, Updates, and so on.

I do not recommend making database changes via SQL Commands, but using SQL Commands to take advantage of Oracle Select options that are not available in Crystal wizards can be very powerful. Oracle SQL has many options that are not available or not translatable via normal Crystal operations, but these special features can be used in SQL Commands, views, or stored procedures and may be useful in certain situations. See Chapter 3 and Chapter 4 for details on Oracle SQL that may be used in SQL Commands.

Some drivers may require that you call a stored procedure from a SQL Command because they do not display the stored procedures in the Database Expert. In that case, the following syntax will call the procedure:

{CALL ProcSchema.ProcPackage.ProcName(Param1, Param2,…)}

Do not include the REF Cursor parameter in the list.

Optimizing SQL Commands

The ability to use SQL Commands in Crystal Reports gives you the power to fully utilize the query capabilities of Oracle. Because you can write any valid SELECT statement, you can customize the query to return only the data you require, which is always the most important optimization goal. However, as with any power, it can be easily abused.

You must never return more columns in a SQL Command than will be used in the report directly or that are needed for computations. Ideally, you should embed the computations in the query too. When Crystal constructs the query for you, it returns only those fields that are used in the report. If you write a SQL Command, every column in the Select list will be returned, whether or not you use it in the report.

For example, create a report based on this SQL Command:

SELECT * FROM Employee

Include the Employee_ID, Supervisor_ID, Last_Name, First_Name, Position, and Birth_Date on the report. Run the report and then look at Report | Performance Information and choose Performance Timing. You will see something like the results shown in Figure 6-9.

Figure 6-9: Performance Timing for selecting all fields

Now change the SQL Command as shown:

SELECT Employee_ID, Supervisor_Id, Last_Name, First_Name, Position, Birth_Date FROM Employee

Run the report and view the Performance Information again. You will see a much improved processing time, similar to that in Figure 6-10.

Figure 6-10: Performance Timing for selecting specific fields

The large performance gain this example shows is due to the large BLOB and CLOB fields in the Employee table, which are returned in the first query even though they are not used.

SQL Command Parameters

When using SQL Commands, you have the capability to create parameters. These parameters can be both less powerful and more powerful than regular Crystal report parameters. They must be simple types such as string, number, or date, but they cannot be multivalued in the typical sense. SQL Command parameters are converted to strings and added to the database query before it is sent to the server. Because of this, you can do things with these parameters that are similar to constructing the query programmatically.

For example, say that you want to allow your users to add their own filtering criteria. Create a SQL Command with a string parameter as shown:

SELECT Employee_ID, Supervisor_Id, Last_Name, First_Name, Position, Birth_Date FROM Employee WHERE {?Where_Clause}

As long as the user enters a valid WHERE clause, the query will execute and return only the rows that the user needs. Try entering ‘Employee_Id IN (1,2,3)’ in the prompt.

Schema Specific Queries

Imagine that you are in an environment where each user has a copy of an application’s tables. In this case, the schema needs to change for each user who runs the report. This is difficult to accomplish in Crystal because the schema qualifier is embedded in the report definition. Here is a solution using a SQL Command:

SELECT Employee_ID, Supervisor_Id, Last_Name, First_Name, Position, Birth_Date FROM Employee

Because no schema qualifier is listed in the query, Oracle will resolve the table reference as usual, first looking in the schema of the logged in user.

This chapter covered optimization techniques that can be used in the Crystal Reports designer. The next chapter will cover optimization techniques that can be employed on the Oracle database to enhance report efficiency.