APPX Software Library

Chapter 6: Create Reports with Crystal Reports 2016

Create a COUNTRY Report through OLE DB

This recipe uses OLE DB (ADO) → Microsoft OLE DB Provider for ODBC Drivers → AppxODBC. The Microsoft provider bridges Crystal Reports to the supplied 32-bit APPX ODBC driver. It still queries the live APPX gateway; it does not read an exported spreadsheet or CSV. The direct ODBC (RDO) route failed in this configuration. See the RDO failure note.

  1. Start 32-bit SAP Crystal Reports 2016. Dismiss the trial status and informational notices if shown. Select File → New → Standard Report to open Standard Report Creation Wizard. Start at its Data page.
  2. Expand Create New Connection → OLE DB (ADO). Select Microsoft OLE DB Provider for ODBC Drivers, then Next.
Choose Microsoft's OLE DB provider for the existing ODBC driver.
Figure 19. Choose Microsoft's OLE DB provider for the existing ODBC driver.
  1. Select AppxODBC from ODBC DSN, then Next. Enter the authorized APPX user and password. Keep Database: appx, then select Finish.
  2. Under this new OLE DB (ADO) → AppxODBC connection, double-click Add Command. Use the command under the OLE DB connection, rather than the identically named command under ODBC (RDO). Enter the four-column COUNTRY SQL:
SELECT name, region_label, no_words, post_code_loc
FROM DMO_DMO_COUNTRY
ORDER BY name
  1. Select OK in the command editor. The wizard shows Command under Selected Tables. Select Next to open Fields.
The standard wizard selects the direct SQL command through the OLE DB bridge.
Figure 20. The standard wizard selects the direct SQL command through the OLE DB bridge.
  1. On Fields, select the double right arrow ≫ to add all four command columns to Fields to Display, in order: name, region_label, no_words, post_code_loc.
  2. Select Finish to create the report without grouping or selection filters. Check the seven COUNTRY records in Preview against the expected sample, including the blank region for Netherlands and blank post-code location for Ireland.
Crystal Reports returns all seven countries through the OLE DB-to-ODBC bridge.
Figure 21. Crystal Reports returns all seven countries through the OLE DB-to-ODBC bridge.
  1. Select File → Save As and save APPX-551-COUNTRY-OLEDB.rpt in a local working folder. Keep it separate from reports created through a different provider.
  2. Select File → Export → Export Report. Set Format: PDF and Destination: Disk file, then select OK. Keep All pages selected, select OK, and save APPX-551-COUNTRY-OLEDB.pdf locally.
  1. Close Crystal Reports, reopen the saved .rpt file, and press F5. Confirm Refresh report data?, enter the APPX password in OLE DB (ADO) → Connection Information, and select Finish. Wait for the query to finish and check Records: 7 in the status bar before saving again. The initially displayed saved records alone do not prove a new query ran.
The reopened OLE DB report requests the APPX password for a live refresh.
Figure 22. The reopened OLE DB report requests the APPX password for a live refresh.

Verified result: a newly created report retrieved 7 COUNTRY records through the OLE DB-to-ODBC bridge. Closing Crystal, reopening the saved report, entering the password and refreshing returned the same seven records. The exported PDF's four data fields agree for every record with the checked sample.