How to modify a Crystal Report to use ADO instead of ODBC

Two report formats report directly to an ActiveX Data Objects (ADO) RecordSet object (producing a .xml file):

  • Crystal ADO Report
  • Microsoft ADO Report

Running these reports in EMu generates a .xml file with the data from the record set. The location of this file can vary, but typically it can be found in:

C:\Users\[your username]\AppData\Local\KESoftware\Reports\e[module name]

For example, a report run in the Parties module, will save the xmldata file to:

C:\Users\[your username]\AppData\Local\KESoftware\Reports\eparties

Note: It is important not to move the xmldata.xml file as this will cause problems when sharing the report with other users.

Crystal reports which currently connect to an ODBC data source can be modified to use an ADO RecordSet:

  1. In the EMu module from which the Crystal Report is run, click Reports on the Home tab of the Ribbon to open the Reports box:

    Reports

  2. Select the Crystal Report in the list and view the properties for the report by selecting .

    In this example we select the List (A4) report.

  3. In the Report Properties box, select Crystal ADO Report from the Type drop list:

    Report Type

    The fields for this report are:

    Two tables are generated in this report.

  4. Click OK and run the report.

    5. The ADO record set will be created and an error will display (the report is looking for a .csv data source).

    We now need to modify the Crystal Report.

  5. Click OK to close the error message and again display the Report Properties for the Crystal Report you are modifying (Step 2 above).
  6. Click Download to save the report locally and open it in the Crystal Report Designer.
  7. Select Database>Set Datasource Location from the Menu bar.

    8. The Set Datasource Location dialogue will display:

  8. In the Replace with pane, expand Create New Connection and then expand ADO.NET (XML).
  9. In the ADO.NET (XML) box, locate and select the xmldata.xml file created when the report was run by clicking the ellipsis beside File Path, e.g.:

    The location of this file can vary, but typically it can be found in:

    C:\Users\[your username]\AppData\Local\KESoftware\Reports\e[module name]

    For example, a report run in the Parties module, will save the xmldata file to:

    C:\Users\[your username]\AppData\Local\KESoftware\Reports\eparties

    Note: More details here.

  10. Click Finish to close the ADO.NET (XML) box and return to the Set Datasource Location box:

    11. Next we map fields from the old ODBC data source to the new ADO RecordSet. In this example there are two tables to map and one sub-report.

  11. To map the old ODBC Catalogue fields to the new Catalogue table, click ecatalog_csv in the Current Data Source pane and then click the row table in the Replace with pane.

    12. The Update button will be enabled:

  12. Click Update to display the Map Fields box:

    Fields with the same name are mapped automatically:

  13. Uncheck the Match type check box to reveal more fields in the Unmapped Fields pane:

  14. Complete mapping fields in the Unmapped Fields pane.

    15. In this example we map ecatalogue_key to ecatalogue_key and irn to irn by selecting both fields to map and clicking Map.

    As they are mapped, fields are moved to the Mapped Fields pane:

  15. When all fields are mapped click OK to return to the Set Datasource Location box.
  16. Repeat the mapping process for all fields (in this example, mapping fields in the Subreport CreatorD_csv to the ADO CreatorDetails table).
  17. When all fields have been mapped in all tables click Close to return to the Crystal design window.
  18. Refresh (F5) the report.

    19. If you have a sub-report object, you may receive an error regarding sub-report links, e.g.:

    If so:

    1. Click OK to open the Record Selection Formula Editor.
    2. Change the link key field used by the old ODBC table to the link key field referenced by the ADO RecordSet:

      Record Selection Formula Editor

    The report should now work correctly.

  19. Save the report.
  20.  The final step is to save the modified report back to EMu:
    1. In EMu, repeat Steps 1 and 2 above to display the Report Properties box for the report:

      Report Type

    2. Select Upload, locate the report file you modified and saved and save it back to EMu.

    The Crystal ADO Report can now be run.