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 Applications 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 2011, even if this is the recommended version at the time of writing this 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 Applications (i.e. the bundled runtime is updated). Examples used in this section all use Crystal Report 2011.

Contents

• Developing Crystal Reports using the Crystal Reports Designer
• Deploying Crystal Reports for Web Client and IFS EE
• Considerations and design guide lines
• Security Considerations
• Known limitations
• Example

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 Applications, 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 Foundation1 Considerations section as well as the Security Considerations section.

Deploying Crystal Reports for Web Client and IFS EE

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 Web Client or IFS EE. 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, Foundation1 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 Applications 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 Applications 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 Foundation1 support, the following guidelines need to be followed.

• Always use server type "Oracle Server" when reporting of IFS Applications 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 Applications data. If accessing data outside IFS Applications other connection types may be used, but these will not benefit from automatic logon or any of the other benefits supplied by F1.
• Add \ or \\ to the end of alias for IFS Applications 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 IALOWNER user should have two backslashes (\\) at the end of their alias name.
  

This alias-naming convention is important because it is used by Foundation1 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 Foundation1 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 Foundation1 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 Foundation 1 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 Foundation1 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.
• Crystal Commands are not supported.

Custom SQL query commands are not supported by the framework.
• 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.

Security Considerations

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

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

When a Crystal Reports is executed from the IFS Web Client or through IFS EE, 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 the IFS Web Client or IFS EE 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 Applications 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, the administrator can enable a setting in the web client configuration (webclientconfig.xml) called SECURED_REPORTS_REQUIRED. If this settings is used, reports without the above mentioned stored procedure will not be possible to execute. Instead one would get an error message. For more information on how to configure this, please refer to the Web Client Administration & Configuration guide. For IFS EE you need to set these information in the system parameter section, please refer to the IFS EE configuration guide for more details.

Known limitations

• SQL Expressions
  Crystal Reports containing SQL expressions used within IFS Applications 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 IAL owner name if any IALs are used). Due to this limitation Crystal Reports containing SQL expressions are not recommended for use within IFS Applications. Still, as a service to customer implementations utilizing this functionality IFS Applications 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 Applications 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 XI, XI R2 and 2011
  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_CURRENT_USER\Software\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database]
  "InvalidAliasCharList"="!(){}"

  2. Save the file by giving a name but make sure the file extension is .reg (e.g InvalidAliasCharList.reg)
  3. 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 Apps 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 Apps.
• 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 Applications 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 Applications.
• 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 Applications
  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 Applications 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
  At the time of writing there was a bug in the Crystal Reports runtime files preventing you from using the "\" character in an alias for a report, that is supposed to be run by the new Web Client integration (i.e. the one that was reinstated in Apps 7 SP2). 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 IAL's or views deployed in the information owner schema. These two new naming standards are supported by IFS Web Client, IFS EE and the IFS Windows Client (i.e. you can use the same Crystal Quick Report from Web, IFS EE and Windows). If you're only using Windows Clients there's no need to use this approach instead of the "\" approach, and as soon as Business Objects 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 Business Objects  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 Applications 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 Applications 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 / F1 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 / F1 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 Apps. We IFS R&D / F1 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 Web Client and IFS EE.

  In Web Client and IFS EE, 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.

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 Applications.