Crystal Reports in SAP Business One: Best Practices & Examples

Crystal Reports in SAP B1: An Overview

SAP Business One ships with SAP Crystal Reports as its built-in reporting engine. Crystal Reports allows you to design custom print layouts and analytical reports — invoice templates, delivery note layouts, inventory aging reports, sales performance summaries — using a drag-and-drop designer connected directly to the SAP B1 SQL Server database.

Crystal Reports integrates with SAP B1 at two levels:

• Print Layout Designer (PLD): SAP B1's simpler built-in layout tool for standard document printing. Good for basic customizations.
• Crystal Reports (RPT files): The full Crystal Reports engine, attached to specific SAP B1 document types as "linked reports" or accessible via the Report and Layout Manager. This is what you use for complex, custom reports.

Connecting Crystal Reports to the SAP B1 Database

SAP B1 stores all its data in a SQL Server database. Crystal Reports connects to it using a direct ODBC or OLE DB connection string pointing to your company database.

When creating a new Crystal Report for SAP B1, use the following connection approach:

1. In Crystal Reports Designer, choose OLE DB (ADO) as the data source type.
2. Select Microsoft OLE DB Provider for SQL Server.
3. Enter your SAP B1 SQL Server hostname, company database name, and a read-only SQL login. Never use the SAP B1 system user (sa) for report connections.
4. Select the company database from the dropdown.

Key SAP B1 Database Tables for Reporting

Understanding the SAP B1 database schema is essential for writing good report queries. Here are the most frequently used tables:

• ORDR / RDR1: Sales Order headers (ORDR) and line items (RDR1)
• OINV / INV1: AR Invoice headers and lines
• OPCH / PCH1: AP Invoice headers and lines
• OITM / OITW: Item master data (OITM) and warehouse inventory (OITW)
• OCRD: Business Partner master data (customers and vendors)
• JDT1 / OJDT: Journal entry lines and headers for financial reporting
• ODLN / DLN1: Delivery Note headers and lines

SQL Query Best Practices

Crystal Reports allows you to write custom SQL queries (via "Add Command" in the data source) instead of relying on the visual table-join builder. For SAP B1 reports, always use custom SQL — it gives you full control over query optimization and prevents Crystal from generating inefficient cross joins.

Example: Sales Order Report Query

SELECT
  T0.DocNum          AS [Order Number],
  T0.DocDate         AS [Order Date],
  T0.CardName        AS [Customer Name],
  T1.ItemCode        AS [Item Code],
  T1.Dscription      AS [Item Description],
  T1.Quantity        AS [Qty],
  T1.Price           AS [Unit Price],
  T1.LineTotal       AS [Line Total],
  T0.DocTotal        AS [Order Total],
  T0.DocStatus       AS [Status]
FROM
  ORDR T0
  INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
WHERE
  T0.DocDate BETWEEN {?StartDate} AND {?EndDate}
  AND T0.DocStatus = 'O'
ORDER BY
  T0.DocDate DESC

The {?StartDate} and {?EndDate} placeholders are Crystal Reports parameter fields that prompt the user for values when the report runs — giving every report date-range filtering for free.

Using Parameter Fields

Parameter fields make your Crystal Reports interactive. Instead of hard-coding filter values in your SQL, you pass them in from the user at runtime. Common parameters for SAP B1 reports:

• {?StartDate} / {?EndDate} — Date range filter
• {?CardCode} — Filter by specific Business Partner
• {?WhsCode} — Filter by warehouse
• {?DocStatus} — Filter by document status (O=Open, C=Closed)

When a Crystal Report is linked to a SAP B1 document type (e.g., linked to Sales Orders), SAP B1 automatically passes the current document's DocEntry as a parameter. This is how "print this invoice" layouts work — SAP B1 opens the report and passes the current DocEntry, and Crystal retrieves that specific document's data.

Performance Best Practices

Poorly optimized Crystal Reports can bring SAP B1 to its knees for all users. Follow these rules on every report:

• Always filter at the SQL level, not the Crystal level. Use SQL WHERE clauses, not Crystal's "Select Expert" for filtering. Crystal-level filters retrieve all records first, then filter — SQL-level filters let the database engine do the work.
• Avoid SELECT *. Only select the columns your report actually uses. This reduces data transfer and memory usage significantly on large tables.
• Use date range parameters. Never let a report default to querying all history. Force a date range parameter to limit the result set.
• Avoid subreport abuse. Subreports inside detail sections run once per row — a subreport in a report returning 1000 rows makes 1001 database round-trips. Where possible, use JOINs in your main SQL query instead.
• Test with production data volumes. A report that runs in 2 seconds with 100 records may take 90 seconds with 50,000. Always test with realistic data.

Linking Reports to SAP B1 Documents

To attach a Crystal Report as a print layout for a specific SAP B1 document type (e.g., replace the standard Invoice printout with your custom branded template):

1. In SAP B1, go to Administration → Setup → General → Report and Layout Manager.
2. Navigate to the relevant document type (e.g., Sales → Sales Invoice).
3. Right-click and choose Add → Crystal Report.
4. Browse to your .rpt file and assign it a display name.
5. Set it as the default layout if you want it to replace the standard printout.