/
Crystal Reports Integration

Crystal Reports Integration

 

 

What is Crystal Reports?

Crystal Reports is a reporting product produced by SAP to facilitate the representation of data in an understandable & timely fashion. Crystal Reports can extract data from many sources, including Microsoft SQL Server, which is what Jiwa uses as its data store.

The advantages of using Crystal Reports as the reporting engine in Jiwa is that the Crystal Reports product is relatively easy to use, is extremely popular, and is a skill that can not only be applied to Jiwa, but to many other software packages, or even used stand-alone.

To develop your own report for use in Jiwa, or to modify an existing Jiwa report, a licensed copy of Crystal Reports is required in addition to the Jiwa software. An add-in for Microsoft Visual Studio is also available from here - NOTE: This add-in does not work with Visual Studio Express Edition.

To familiarise one’s self with a typical Jiwa report, it is suggested that a standard shipped-with-Jiwa report be opened up in the Crystal Reports Designer and examined by the reader. Often, business specific requirements can be met by making minor alterations to an existing standard shipped-with-Jiwa report.

 

How to Install the Crystal Reports Editor

Crystal reports can be purchased as a stand-alone product, or a free edition can be used with a free edition of Microsoft Visual Studio.

You don't need Jiwa installed to edit Crystal Reports, but if you do have Jiwa installed, the version of Microsoft Visual Studio and SAP Crystal Reports For Visual Studio, Developer Version you should install without affecting Jiwa will differ depending on the version of Jiwa that is installed.  Installing the wrong version will cause Jiwa to longer be able to generate reports.

If Jiwa 7.00.175.00 or earlier is installed, Visual Studio 2015 must be used - the Community Edition is a free edition, and the paid Professional or Enterprise editions will also work.

If Jiwa 7.1 or later is installed, Visual Studio 2017 must be used - the Community Edition is a free edition, and the paid Professional or Enterprise editions will also work.

If Jiwa is not installed, then either Visual Studio 2017 or Visual Studio 2015 can be used, but note that when using Visual Studio 2017, you must use SAP Crystal Reports For Visual Studio, Developer Version SP 22 or later - earlier versions are not compatible with Visual Studio 2017 - follow the instructions for Installing When Jiwa 07.01.00.00 or Later is Installed.

 

Installing When Jiwa 07.01.00.00 - 07.02.01.24 is Installed

  1. 8ee993b5-e9d2-4112-9a41-dfb04d7491aa.png

  2. http://downloads.businessobjects.com/akdlm/cr4vs2010/CRforVS_13_0_22.exe

  3. c4b810cb-2dcd-4cfc-a95e-f73b957598fb.png

Installing When Jiwa 07.02.01.25 or Later is Installed

  1. VSCommunity2022.png
  2. c4b810cb-2dcd-4cfc-a95e-f73b957598fb.png

Installing When Jiwa 08.00.56.00 or Later is Installed

  1. VSCommunity2022.png
  2. c4b810cb-2dcd-4cfc-a95e-f73b957598fb.png

Editing Reports

Reports can be edited with the appropriate version of Visual Studio - double-clicking a .rpt file will either open Visual Studio, or if you have not yet associated the .rpt file type with Visual Studio, Windows will prompt you to select how to open the file:

Select the Microsoft Visual Studio Version Selector application, and check the "Always use this app to open .rpt files" and press OK

You can also open files within Visual Studio using the File → Open → File menu option.

The Visual Studio Crystal Reports editor then appears as follows (Note colour schemes are user customisable and may appear different):

Where Jiwa Reports are Located

Jiwa reports are all stored in the Jiwa database and deployed to the local filesystem on demand.  The location they are deployed to is in the C:\ProgramData\Jiwa Financials\Jiwa 7\%Jiwa Version%\%Windows User%\%SQL Server Name%\%Jiwa Database Name%\Reports folder.

Reports will not appear in this location until attempted to be run within Jiwa.

Best Practices

Rename Report File Names

If wishing to modify an existing standard Jiwa report, you should rename the file.  Try to name the file such that it is clear that this is a custom report.

Change Report Title

The Report Title in the Summary Info should also be changed, as Jiwa uses this when importing / updating a report.  Change the Report Title by select the Crystal Reports → Report → Summary Info menu option

The Summary Info Dialog will then open - change the Report Title field and press OK

Importing a New or Updated Report Into Jiwa

Once you have finished making changes to a Crystal Report, and want to add or update the report within Jiwa, make sure you have saved the changes in Visual Studio first.

Then, open the Reports form within Jiwa from the System Settings → Report Configuration menu, and press the Refresh From Files tool on the ribbon.

The Report selection dialog will then open - select the report file or files you wish to add or update

You will then be prompted with a confirmation dialog:

Then press the Save tool on the ribbon to commit the changes to the database.

 

Table Based vs. Stored Procedure Based Reports

This section talks about the two different techniques used in the various Jiwa reports, and why one should be used over the other.

Table Based Reports

The simplest of the report writing approaches is table based reports. Table based reports are those reports where only tables (or views) are added to the Crystal report, and linked together using the Crystal "Database Expert". Calculations and groupings are done by the report engine, and minimal programming skill is required by the creator. The Crystal "Record Selection Formula" is used to limit the data that is returned by the report.

Table based reports are quick and easy to construct, but can become awkward to work with when the reporting requirements are complex, involving lots of logic for selecting the data. Table based reports tend to be slower than their stored procedure based counterparts. For simple reports, a table based report should be used as the overhead in getting something up and working is minimal.

An example of a table based report in Jiwa is "CRINF003 - Creditors Adjustment Notes.rpt".

Stored Procedure Based Reports

Stored procedure based reports are those reports where all intelligence, including grouping information and calculations, is done by a stored procedure. Crystal Reports becomes just a dumb display engine that simply calls a stored procedure (often passing some parameter values), and then rendering the data returned. All new standard Jiwa reports are written in this manner. The advantages of stored procedure based reports are that they are fast (SQL already knows the query plan), and that the logic is separated from the display aspects. Some further advantages:

  • Changes/fixes often only require a modification to the stored procedure in the database, therefore we do not need to redeploy any report files.

  • Code re-use - the same stored procedure can be used by multiple reports, or even by the client software (i.e. Inventory Maintenance grids).

  • Complex report requirements are easy to design for, implement, and maintain through the use of the logic structures available in stored procedure syntax - T-SQL.

The disadvantages:

  • Must know T-SQL syntax.

  • Need to deploy the report AND a stored procedure initially.

An example of a stored procedure based report in Jiwa is "ININF000 - Inventory Maintenance Listing.rpt"

 

How Crystal Reports Integrates With Jiwa