Skip to content

Using Crystal Reports as Quick Reports

Crystal Reports can be used to develop richly formatted layouts that can be included in and accessed from IFS Cloud as Quick Reports. See the Overview for more details.

In order to develop Crystal Reports a Crystal Reports license and client installation is required for each developer work station. The current version of Crystal Reports is 2020, even if this is the recommended version at the time of writing this, thing may change in the future as new versions are released. Usually reports developed using older version of Crystal Reports work fine using a newer runtime. To run a report developed using a newer version than the runtime is generally not supported, though. So before acquiring a copy of Crystal Reports, make sure this version works with IFS Cloud (i.e. the bundled runtime is updated). Examples used in this section all use Crystal Report 2011.

  • The user will not need to re-enter logon information for reports that only access data from the same IFS Cloud database as your currently logged on to.
  • Database table/view locations will be set automatically, making it possible to develop a report in one database and run it in any other IFS Cloud database/instance without modification.

Crystal Reports included in IFS Cloud as Quick Reports may be ordered and viewed from Aurena client. The reports can also be printed or exported to a number of supported file formats (including PDF, RTF, Excel to name a few). In order to view the reports from an Aurena client on the other hand, additional software is required. A separate component (CRYSTL) is also required. This will include any runtime license required.

Developing Crystal Reports using the Crystal Reports Designer

To develop Crystal Reports you need a client installation of Crystal Reports and a license. IFS support the integration to be able to run Crystal Reports within IFS Cloud, we do not provide any support when it comes to report development. Refer to the included documentation in Crystal Reports and/or make sure to acquire other types of support from 3rd party vendors/suppliers if needed. This applies to both customers and for IFS internally (i.e. country organizations developing reports for customers of for internal use). Also make sure to review the considerations and design lines section below.
For help on using Crystal Reports refer to your Crystal Reports User's Guide and the Special platform Considerations section as well as the Security Considerations section.

Deploying Crystal Reports for IFS Aurena

In order to preview Quick Reports of type Crystal, the reports to be previewed should be deployed in to a folder in the machine where the IFS Crystal Web Service is running. Create a folder in the machine where the IFS Crystal Web Service is running and copy the Crystal Report files (*.rpt file) that are going to be previewed from IFS Aurena. This folder is referred to as the OpenReportPath. In the installation process this folder is configured in the Web.Config file found in the IIS virtual directory for the IFS Crystal Web Service.

Considerations and design guide lines

If a Crystal Report is designed according to a couple of set principles, platform provides enhanced support that makes life easier for the end user. The benefits are.

  • The user will not need to re-enter logon information for reports that only access data the same IFS Cloud database as your currently logged on to.
  • Database table/view locations will be set automatically, making it possible to develop a report in one database and run it in any other IFS Cloud database/instance without modification.
  • Users can use the IFS Query dialog with saved queries functionality.

In order for a report to take full advantage of the platform support, the following guidelines need to be followed.

  • Always use server type "Oracle Server" when reporting of IFS Cloud data
    Data source type "Oracle Server" should be used for all report databases (i.e. database is what Crystal Reports calls a view, table or procedure added to the report). Do not use ODBC or any other connection type when accessing IFS Cloud data. If accessing data outside IFS Cloud other connection types may be used, but these will not benefit from automatic logon or any of the other benefits supplied by platform.
  • Add \ or \ to the end of alias for IFS Cloud data sources
    The alias for report databases that are connected to a view owned by the APPOWNER user should be changed to have a backslash () at the end of the alias name. Report databases connected to views owned by the <IFSINFO> user should have two backslashes (\) at the end of their alias name.
    This alias-naming convention is important because it is used by platform to identify which report databases can be automatically logged on.

Example alias names: "Fnd_User\", "Sales_Statistics\"

Note: Due to changes from Crystal Report version 7.0.x to version 8.0 the alias character has been changed. The alias character '*' and '**' which was used in platform versions prior to 2.2.3.B is still supported for old layouts, but are not recommended to use. In newer versions of the Crystal tool "*" can not be entered as a part of the alias name - Special parameters
If you use the parameters IFS_WEB_USER_, IFS_DUAL_REC_, IFS_AO and IFS_IAL in your report they will be substituted automatically by the platform framework. The user will not be required to specify any value for those. In version 8 and above of Crystal Reports any Oracle stored procedure that should be called from Crystal Reports need to have a reference cursor parameter. If you name this parameter IFS_DUAL_REC_ the user will not be prompted for this parameter. - Parameters with "IFS_" prefix
Except the special parameters mention above, it is not recommend to use “IFS_” prefix for other user defined parameters in crystal reports. The “IFS_” prefix is used by the framework. So if you use “IFS_” prefix for your normal parameters, system will not consider it as a normal report parameter. Then it will not list them in IFS parameter dialog. Then the users would not be able to enter parameter values in runtime. And also, since it is not considered as a normal report parameter, the parameter values sent in custom query strings will not automatically set in crystal reports too. - Only use data types String, Number, Date, Currency and Boolean for Report Parameters.
Only data types String, Number, Date, Currency and Boolean are supported by the platform Query Dialogs and are the only ones that should be used for report parameters. Report fields may of course use any data type. Also only single value parameters are supported. There's no support for range parameters or multi-value parameters. Nor is there support for the list of values (LOV) functionality for parameters called "dynamic parameters" introduced in Crystal Reports XI.

  • Limited support for Crystal Commands
    In general custom SQL query commands are not supported by the framework. Yet, some simple and less complex Crystal Report commands might work if they follow the following guidelines. If you can’t get the report to work even after changing it as below, then the alternative is to redesign the layout to use actual view(s). 1. The sql query should use the fully qualified name of the table(s)/view(s) with the schema owner of the runtime database. e.g. select * from IFSPROD.Fnd_User
    1. If you have designed the layout as above and still you can’t get the report to run, then include a “_C” to the end of the Crystal Command alias name.
  • PLSQL Package procedures are not supported.
    PLSQL Package procedures are not supported by the IFS reporting framework. Instead one can use Oracle Stored Procedures. If an Oracle stored procedure is used in the report, the necessary permission should be granted.
  • Custom Field Support.
    You could use CFV views in your Crystal Quick Reports provided that the end-user also have the necessary access rights and you have inserted the CR_WEB_INIT stored procedure in the Crystal layouts for row based security.

Security Considerations

In order to guaranty security for reports executed on web or through IFS Aurena some considerations have to be made.

Note: This only applies when running reports from the web or through IFS Aurena. If a customer is not using the web interface or IFS Aurena, or does not wish to run the reports from the web or through IFS Aurena, the following is not necessary.

When a Crystal Report is executed through IFS Aurena, the Crystal Reports runtime creates it's own connection to Oracle. In order for this connection to use the same kind of security mechanisms as used when IFS Aurena accesses the database, the report needs to be created in a certain way. Creating the report this way will ensure that the end user does not get access to data he or she is not allowed to access. One typical example would be that different users running the same query against a view in IFS Cloud gets different result sets based on their privileges (i.e the view has some row based security).

Constructing a report according to these guidelines involve adding a stored procedure to the report, manually at design time, and also linking this procedure to the main entry point in the report. For a detailed information on how to develop a report according to these guidelines, please refer to this step by step instruction.

In order to ensure that only reports that comply with these guidelines are executed in the environment, for IFS Aurena you need to set these information in the system parameter section, please refer to the IFS Aurena configuration guide for more details.

Known limitations

  • SQL Expressions
    Crystal Reports containing SQL expressions used within IFS Cloud will only work if the application owner name is the same in the development database as in the runtime database (the same goes for the <IFSINFO> owner name if any views in that schema are used). Due to this limitation Crystal Reports containing SQL expressions are not recommended for use within IFS Cloud. Still, as a service to customer implementations utilizing this functionality IFS Cloud is able to execute reports containing SQL expressions as long as the mentioned limitation considered. This limitation is a consequence of not being able to easily interpret all possible SQL expressions, find out what's references to an Oracle schema owner and replace that with the current values as we do for views, tables and procedures aliased using the "\" and "\" naming standard. What this means in practice is that if you develop your reports in a database with IFSAPP as the application owner name you can only run that report in other IFS Cloud instances using IFSAPP as the appowner name (if the report contains SQL expressions that is).

  • Defining data sources in sql.ini only and using Oracle instant client
    Crystal Reports makes it's own connection to Oracle, therefore a local Oracle Client (SQL*Net) installation is needed and the data source needs to be defined in tnsnames.ora. Further more the data source name defined in sql.ini and tnsnames.ora needs to be identical.

  • Manual fix for alias limitation in Crystal Reports
    The alias characters ("\" and "\") used by the IFS Crystal Report framework is not supported by Crystal Report Developer XI by default. Due to this reason when developing reports an error message is show when adding a backslash ("\") as an alias character to a table or view, informing the report developer that the backslash is not a supported alias character. In order to work with the alias characters used by IFS, these alias characters should be manually added to the system registry. Add the registry key given below manually to the system registry or follow the steps given below to add the alias characters to the system registry by creating and running a script file,

    1. Copy and paste the following text to any text editor or download the already done sample file

    For Crystal Report XI

Windows Registry Editor Version 5.00  
[HKEY_CURRENT_USER\Software\Business Objects\Suite 11.0\Crystal Reports\Database]   
"InvalidAliasCharList"="!(){}"

​ For Crystal Report XIR2

Windows Registry Editor Version 5.00  
[HKEY_CURRENT_USER\Software\Business Objects\Suite 11.5\Crystal Reports\Database]   
"InvalidAliasCharList"="!(){}"

​ For Crystal Report 2011

Windows Registry Editor Version 5.00  
[HKEY_LOCAL_MACHINE\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database]  
"InvalidAliasCharList"="!(){}"  
[HKEY_CURRENT_USER\Software\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database]  
"InvalidAliasCharList"="!(){}"

​ For Crystal Report 2013

Windows Registry Editor Version 5.00  
[HKEY_LOCAL_MACHINE\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database]  
"InvalidAliasCharList"="!(){}"  
[HKEY_CURRENT_USER\Software\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database]  
"InvalidAliasCharList"="!(){}"
  1. Save the file by giving a name but make sure the file extension is.reg (e.g InvalidAliasCharList.reg)
  2. RMB on the file and select "Merge" and run the script file.

By adding this registry key Crystal Report Developer XI, Crystal Report Developer XI R2 or Crystal Report Developer will only recognize characters entered in the key as invalid alias characters.

Also read the information about the "Alternative alias naming mechanism (characters) due to bug in Crystal Reports" further down in the list of limitations. - The new feature called dynamic parameters, introduced in Crystal Reports XI, is not supported by the IFS integration
In Crystal Reports XI, there's a new feature called "dynamic parameter". This is essentially a dynamic list of values (LOV) functionality which enables the user to define a SQL statement to be performed get an updated lost of value when specifying eth input values for a parameter. This feature is not supported by the current IFS Cloud integration with Crystal Reports. In the future there might be support for this or similar functionality, but for now this can't be used in Crystal Reports that are supposed to be executed/triggered from within IFS Cloud.

  • Range parameters are not supported
    Range parameters are not supported by the IFS Crystal Reports integration. Instead of using range parameters, the recommended workaround is to use two parameters instead (one start of range and one stop of range parameter).

  • Multi-value parameters are not supported
    The IFS Cloud integration with Crystal Reports offer no support for multi-value report parameters. Stick with single-value parameters if you intend to use the Crystal Report as a Quick Report in IFS Cloud.

  • Buffer overrun error in the Crystal Reports XI designer, when connecting to a Oracle 10g Release 2 database (i.e. 10.2.x.x.x) running IFS Cloud
    At the time of writing there was a bug in the Crystal Reports XI designer that makes the development tool crash with a "buffer overrun" error, when trying to design a report against an IFS Cloud database running on Oracle 10g release 2.
    Business Objects has solved this problem in SP3. We experienced problems when applying the patches on top of an old installation. Therefore we recommend you to start by removing any previous installation. The patch can be found from Business Objects patch/Service pack download page.

    • First download Crystal Reports XI Release 2 Service Pack 2 - Full build and install it.
    • Then download Service Pack 3 - Incremental Patch only and apply it on top of SP2.
  • Alternative alias naming mechanism (characters) due to bug in Crystal Reports
    Running a report with "\" or "\" in the alias will cause a runtime exception. In order to work around this problem an alternative alias naming mechanism have been added to the previous two already supported "\" + "\" and "*" + "**". The new naming standard that can be used as of IFS Apps 7 SP2 is "_A" for views deployed in the application owner schema and "_I" for views deployed in the <IFSINFO> user schema. These two new naming standards are supported by IFS Aurena. If you are only using Windows Clients there's no need to use this approach instead of the "\" approach, and as soon as Crystal corrects the bug the old "\" solution will work in the integration again. The support for the "_A" and "_I" approach will still be maintained (along with the support for "\") after Crystal corrects this bug, so there's no need to change any reports once the problem is fixed.
    There's one potential problem with this solution, even if it's very unlikely anyone will come across this issue in the real life scenario. If you would have a view name ending with _A or _I that you would not like to logon automatically to (i.e. it's not an IFS Cloud view) it's impossible to prevent the automatic logon from being done anyway.

  • Limitation in view name length in combination with alias and automatic logon

    In Oracle there's a limit of 30 characters for an identifier, such as a view name. Since our approach for doing automatic logon to IFS Cloud data sources in the Crystal Report is based on setting an alias by appending one or two characters to the view name in the Crystal Report, this means that we're only able to perform automatic logon for data sources (i.e. views) with a name shorter or equal to 28 (or 29 depending on the approach and schema owned) characters.

  • Paper format issue related to a problem in the Crystal Reports designer in Crystal Reports XI

A Report with a paper size set at the design time should preview according to the design time paper size regardless of the runtime client time machine's default printer's paper size. But the reports designed using Crystal Reports XI R1 versions, always use the client machines default printers paper size to preview the report.
The solution is to upgrade the Crystal Reports Designer to Crystal Reports XI R2 Service Pack 3.There’s a common misunderstanding that Crystal XI and XI Release 2 are two separate versions/tracks. IFS has concluded this is not the case. It could be considered the same version from an IFS integration perspective and the Release 2 is only a re-branding of a service release. The Crystal Report Vendor owns statement regarding release 2.

  • None of the above cases apply to the IFS integration towards Crystal Reports. The report format is identical for both versions. What we also concluded is that since the Crystal Report Vendor have stopped releasing corrections on XI Release 1, the only way to move forward when issues are discovered in the designer is to upgrade to Release 2 and apply a service pack that includes the fix for the specific issue. In this vase the problem is solved in SP3 for CR XI Release 2. We experienced problems when applying the patches on top of an old installation. Therefore we recommend you to start by removing any previous installation. The patched can be found from the Crystal Report Vendor patch/Service pack download page.

In order to upgrade the Crystal Reports Designer,
o      First download Crystal Reports XI Release 2 Service Pack 2 - Full build and install it.
o      Then download Service Pack 3 - Incremental Patch only and apply it on top of SP2.

  • Crystal Reports designer versions (service packs and support)

    The support on Crystal Reports provided by IFS R&D / platform is limited to the actual integration. This means that issues/problems that are diagnosed to be a problem in the Crystal Reports Designer are not supported by IFS. That type of support is preferably obtained by the Crystal Report Vendor directly or one of their support partners. IFS R&D / platform will not keep track of fixes in the Crystal Reports designer and certify or require upgrades to specific service packs or service releases. Once we’re made aware of a problem/limitation of this nature, the information about this will be added to LCS solutions and as a note in this known limitations sections. What we won’t do though, in such cases is to perform certification type activities and state in the documentation that a certain service pack level of the Crystal Report viewer is mandatory when using the Crystal Reports integration with IFS Cloud. We IFS R&D / platform consider this type of support / certification to be something that needs to be handled in customer projects or by regions possibly together with the Crystal Report Vendor.

  • On-demand Sub Reports in IFS Aurena. In IFS Aurena, when you preview a crystal report with on-demand sub report, it gives the error “Error finding JNDI name <JNDI Name>". This is a limitation in the Crystal Web Service approach and the issue was fixed from the patch 84744. It converts the On-demand sub report to an embedded sub report at the runtime. However, you can’t see the hyperlink to the sub report. So for that follow the instructions given below to add the label that as a link to the sub report.

    Add a label to the Sub-Report header with the Sub-Report name.

The label should place on the top most left corner of the sub report header. RMB on label and select "Size and Position..." menu option. Give the (X,Y) co-ordinates as (0,0)

Then RMB on label and select the menu "Format Text". Select font "arial" Size 12 and the font color as Blue. And also underline the label to view it as a link to your on-demand sub report.

Once you order the report, you can see the sub report link in main report. Double click on this to open your sub report.

  • Use of \ alias character for view with Crystal Report.net runtime version 13.0.13 and above.

Due to an Oracle sessions hanging issue in the Crystal.net runtime 13.0.1 version, it was seen that customers updated their Crystal Report .net runtime version to version 13.0.13 or above. After this update, the generally used \ alias character stopped working and an error was seen when you try to run a layout where the \ alias was used in the views inside the layout. For this Crystal suggested to manually add a registry entry to denote the invalid view alias characters. Add the registry key given below manually to the system registry or download this file and RMB on *.reg file and select "Merge" to add the entry to the system registry. This should be added in the machine where the Crystal Web Service is running. You will have to restart the Crystal Web Service after adding this registry entry.

Windows Registry Editor Version 5.00  
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\SAP BusinessObjects\Crystal Reports for .NET Framework 4.0\Crystal Reports\Database]  
"InvalidAliasCharList"="!(){}"
  • Exporting Crystal Quick Report to PDF is not supported.

Export to PDF option would fail with the following error message in Crystal HTML Viewer :

java.lang.NullPointerException

This is a known limitation in Crystal HTML Viewer according to Crystal Knowledge Base Article "1713669". Crystal Reports for Eclipse runtime libraries are not supported/compatible with Open JDK to run in Linux environments. Therefore, it's not possible to export crystal quick reports as ifsapp-reporting-cr container is a Linux based container which is used to handle crystal quick reports.

  • Conditional formatting on box objects are not supported.

Crystal quick reports with conditional formatting on box objects would fail with the following error message in Crystal HTML Viewer :

java.lang.NullPointerException

As a workaround, an empty textbox could be used instead of box object. Same formula used on the box object can be applied to the empty text box. When designing, empty text object should be at the bottom and other fields should be on top of the empty text field.

Positioning can be done using RMB on the text field --> Move --> To Front / To Back / Forward / Backward.

Example

See this example of creating a basic Crystal Quick Report to learn how to develop a simple Crystal Report that can be used from with IFS Cloud.