Crystal Reports techniques for ERP reports

SAP Crystal Reports is the report writer behind many ERP forms and custom reports, including in Prophet 21 software. It is also easy to make slow: a report that looks simple can pull every row in a table to the workstation, or run a separate query for every line it prints. We use the techniques below to keep ERP reports in Crystal Reports fast and correct, and they apply to any ERP on a SQL database.

General techniques, not shipped designs

Crystal Reports is a product of SAP. Everything here is general Crystal Reports practice, written from our own experience and SAP public documentation. Nothing here copies or describes any report or form shipped with an ERP product. For the pattern we use to customize ERP transactional forms safely, see Custom transactional forms with Crystal Reports. Menu names below follow recent Crystal Reports versions and can differ slightly in yours.

Techniques at a glance

Technique	Why it matters
Record selection that reaches the SQL	Filters on the server instead of in the report, so fewer rows cross the network
SQL Command or linked tables	Decides who writes the SQL and how much control you have
Fewer subreports, on-demand where possible	Each subreport is a separate query, and in a details section it runs per row
Shared variables	Bring a subreport total back into the main report
Grouping on the server and running totals	Summaries computed where the data is, and totals that respect suppression
Suppression formulas	Hide sections without changing the data, with a known effect on totals
Parameters and cascading prompts	Filter at the source and guide users to valid values
Runtime and version checks	The report has to work in the runtime the ERP uses, as well as in the designer

Record selection that reaches the SQL

The record selection formula decides which rows the report uses. When Crystal Reports can translate the formula into SQL, it adds a WHERE clause and the database does the filtering. When it cannot, it reads every row and filters them itself. SAP calls this pushing down record selection.

SAP gives a clear example in its user guide. A formula that wraps the date field in the Year function cannot be pushed down, so the generated SQL has no WHERE clause and every order comes back. The same filter written as a comparison against a date constant is pushed down, and only the matching orders come back. The report output is identical, but only the second version filters on the server.

This formula is not pushed down:

Year ({Orders.Order Date}) = 2026

This one is:

{Orders.Order Date} >= #Jan 1, 2026# and
{Orders.Order Date} < #Jan 1, 2027#

The same idea with a date range parameter, which is how most ERP reports should filter:

{Orders.Order Date} >= Minimum ({?Order Date Range}) and
{Orders.Order Date} <= Maximum ({?Order Date Range})

Habits that keep record selection on the server:

• Check the SQL every time. Use Show SQL Query on the Database menu and confirm your filter appears in the WHERE clause. This is the single most useful check in Crystal Reports.
• Turn on Use Indexes or Server for Speed in the report options. SAP documents it as required for push-down.
• Compare fields to constants or parameters. Avoid functions and type conversions on database fields in the formula, such as converting a number to text.
• Replace formula fields in the selection with SQL Expression fields. A selection on a Crystal formula field usually cannot be pushed down. The equivalent SQL Expression can.
• Filter on indexed columns such as dates, company, branch and customer keys where the database has indexes on them.

SQL Command or linked tables

You can build a report on tables linked in the Database Expert, and let Crystal Reports write the SQL, or on a SQL Command, which is your own SELECT statement that Crystal Reports treats as one table.

	Linked tables	SQL Command
Who writes the SQL	Crystal Reports	You
Control over joins and pre-aggregation	Limited to what the links and selection allow	Full: CTEs, subqueries, window functions
Filtering	Record selection, pushed down when possible	Command parameters inside the SQL
Changing it later	Visual, easy for the next report writer	Needs someone who reads SQL
Common failure	A selection that stops being pushed down without warning	Parameters concatenated into SQL, and filters placed on top of the command instead of inside it

We use linked tables for simple reports on a few tables, and a SQL Command when the report needs a pre-aggregated result, such as line totals per invoice before joining to the header (see avoiding fan-out). Put the filters inside the command as command parameters rather than in a record selection formula on top of it, so the database applies them.

Commands are passed through as written

SAP documents that Crystal Reports does not alter the SQL of a command, including its parameters, and that the command feature is open to SQL injection. Keep parameters to typed values such as dates and numbers where you can, validate text parameters with a list of values, and run the report with a read-only database login.

Subreports and what they cost

A subreport is a report inside a report. Each one runs as a separate report with its own query. That is fine in a report header or footer, where it runs once. It is expensive in a details section, where SAP notes it runs a separate query for each record in the main report. A 5,000-line report with a linked subreport in the details section runs about 5,000 extra queries.

Alternatives, in the order we reach for them:

1. Link the table instead. If the subreport only adds a field or two from a related table, link that table in the Database Expert. SAP recommends linking tables over linking subreports because linked tables usually perform better.
2. Pre-aggregate in SQL. If the subreport exists to total a child table (open orders per customer, receipts per purchase order), a SQL Command or view that returns one row per parent key can be linked like a table, with no fan-out.
3. Make it on-demand. An on-demand subreport shows as a link, and its data is not retrieved until someone clicks it. Use it for detail that few readers need.
4. Move it to a group section so it runs once per group rather than once per record.

Shared variables for subreport totals

Sometimes a subreport is the right tool and you need its total in the main report, for example to subtract a credits subtotal from a sales subtotal. Shared variables carry a value between the main report and its subreports. The value must be declared and assigned before it is read, and the main report can only read it after the subreport has run, which means in a section printed after the one that holds the subreport.

A reliable layout for a per-group total:

Section	Contains
Group header	A reset formula that sets the shared variable to zero
Group footer a	The subreport, which assigns its total to the shared variable
Group footer b	A display formula that reads the shared variable

The reset formula in the main report group header:

WhilePrintingRecords;
Shared CurrencyVar CreditTotal := 0;

The assign formula in the subreport, placed in its report footer:

WhilePrintingRecords;
Shared CurrencyVar CreditTotal := Sum ({Credits.Amount});

The display formula in main report group footer b:

WhilePrintingRecords;
Shared CurrencyVar CreditTotal;
CreditTotal

Use the Section Expert to split the group footer into a and b. If the display section shows the previous group's value, the subreport and the display formula are in the same section, or the reset is missing.

Grouping and running totals

Perform Grouping on Server hands grouping and summarizing to the database for grouped reports on SQL data sources, and fetches detail rows only when a reader drills down. It needs Use Indexes or Server for Speed turned on, and SAP advises against grouping, sorting or totaling on Crystal formula fields in such reports. Use SQL Expression fields instead.

Running Total Fields can reset on a group change and evaluate on a condition, so they are the tool for "total only the lines that meet a condition".

Summaries include suppressed records. A summary or running total field counts every record the report read, whether or not its section is suppressed. When the report suppresses records and the totals must match what prints, SAP recommends a manual running total built from three formulas: a reset in the group header, an accumulator in the details and a display in the group footer.

The manual running total, with a condition that mirrors the suppression:

WhilePrintingRecords;
CurrencyVar ShownTotal := 0;

WhilePrintingRecords;
CurrencyVar ShownTotal;
If Not ({Invoice_Line.Qty Shipped} = 0) Then
    ShownTotal := ShownTotal + {Invoice_Line.Extended Price};
ShownTotal

WhilePrintingRecords;
CurrencyVar ShownTotal;

The field names above are invented. The better fix, where possible, is to remove the unwanted rows in record selection so that there is nothing to suppress.

Suppression formulas

Conditional suppression hides a section or field when a formula returns true, from the Section Expert or the Format Editor. Typical uses on ERP reports:

• Hide a details section for zero-quantity lines, as in the running total example above.
• Hide a group footer when the group has one record, so a subtotal does not repeat the line.
• Print a remit-to block only on the last page, or a "continued" note on every page but the last.

{Invoice_Line.Qty Shipped} = 0

Count ({Invoice_Line.Line No}, {Invoice_Header.Invoice No}) = 1

Suppression changes what prints, not what the report reads. It does not make a report faster, and it does not remove records from summaries. Use record selection to exclude data and suppression to control layout.

Parameters and cascading prompts

Parameters are how users filter a report, and a parameter used in record selection is pushed down like a constant.

Choice	Use it for
Static list of values	Short, stable lists: status codes, report options
Dynamic list of values	Lists that come from the database, such as branches or salespeople
Cascading list of values	Dependent choices, such as region then customer, where the second list is filtered by the first
Range parameter	Date ranges, read with Minimum and Maximum in record selection
Multiple values	"Any of these branches" (confirm in Show SQL Query that the list reaches the server)

For an optional filter with an "all" choice, write the selection so the parameter test comes first, then check Show SQL Query to confirm what reached the server:

({?Branch} = "ALL" or {Orders.Branch} = {?Branch}) and
{Orders.Order Date} >= Minimum ({?Order Date Range}) and
{Orders.Order Date} <= Maximum ({?Order Date Range})

Dynamic and cascading prompts query the database when the prompt opens. Keep their lists small and sourced from indexed master tables. A list read from transaction tables makes the prompt the slowest part of the report. In SAP BusinessObjects managed environments, dynamic prompts are stored in the repository and have their own deployment steps. See the SAP user guide.

Performance checklist

• Show SQL Query shows every filter in the WHERE clause.
• Use Indexes or Server for Speed is on.
• No functions or type conversions on database fields in record selection.
• Filters use indexed columns, with dates as ranges.
• No linked subreports in details sections, or they are on-demand.
• Child tables that are summed are pre-aggregated, so nothing fans out.
• Only the columns the report uses are in the command or field list.
• Prompts with dynamic lists read small master tables.
• The report was timed against a production-sized copy, not a test database with a few hundred rows.
• The report runs under a read-only login.

Versions and runtime deployment

A report is designed in the Crystal Reports designer but usually runs inside something else: the ERP application, a report server or an application built with the Crystal Reports runtime for Visual Studio or Java. Our deployment notes:

• Design in a version the target runtime can open. Before you upgrade the designer, confirm which Crystal Reports runtime your ERP uses and test a saved report in it, not only in the designer.
• Match 64-bit and 32-bit components. SAP lists Crystal Reports 2020 and 2025 as 64-bit releases, and its 2025 user guide states that 32-bit user function libraries do not work in the 64-bit designer. The same applies to database drivers: a 64-bit designer needs a 64-bit ODBC data source or driver, and a 32-bit runtime needs a 32-bit one.
• Keep connection details out of the report. Point reports at a DSN or connection the application supplies at run time, so the same report works against test and production.
• Runtime licensing comes with the application. SAP notes that third-party applications can include a Crystal Reports runtime license, typically sold with that application. Check what your ERP license covers before deploying reports to other servers or tools.
• Keep reports in version control with a short change note. A binary report file is hard to compare, so the note is what the next report writer reads.

Next steps

Open your slowest report, choose Show SQL Query, and compare the WHERE clause with the record selection formula. If the filter is missing from the SQL, fix that first. Then look for subreports in details sections. Those two checks resolve most of the slow reports in Crystal Reports that we see. For the SQL patterns to put inside a command, see T-SQL patterns for ERP reporting.