In BI Publisher, you can add a new data set under data model and select data source
type as SQL Query. Copy the SQL query from Crystal Reports ‘Show SQL Query’
dialog window and paste it in the text area of BI Publisher data set.
Crystal reports supports database function as a data source, where the data logic is
defined in a function and the report simply calls the function. In such a case, you
should examine the function to determine what the function does. A Function
returning a single value can be handled by a SQL Query Data Set or a Data Template
but a complex function is best handled by a Data Template. If the Crystal report uses
REF CURSOR to retrieve a recordset, then you will need to use a Data Template. REF
CURSOR as a return value of a function is supported by BI Publisher but if the
function has a REF CURSOR as OUT or IN OUT parameters then you will need a
wrapper function with a return type as REF CURSOR or you can modify the original
function to return a REF CURSOR.
If the function contains a DML statement for data insert, update, or delete then you
should split the function into two parts; one part to handle the DML statements and
the other to handle the select query of the function. Any calculation or any DML
statement that needs to be executed before the select query is executed should be done
using beforeReport trigger. Similarly, any calculation or DML statement, which needs
to be executed after the query is executed and XML data is received on the Server,
should be done using an afterReport trigger.
1.3: Convert Parameters
Note: This is an optional step and is required only for reports that have parameters.
To find the parameters in Crystal Report, expand the Parameter Fields in the Field
Explorer. There can be several parameters defined in a report, but only those with a
check symbol are attached to the report. The details of each parameter can be viewed
by right clicking on the parameter and selecting edit option. The Formula Workshop
shows the role of these parameters in record selection.
Parameters in BI Publisher are defined in the Report Editor along with the Data
Model. These parameters can be passed to the database query or can be passed to the
layout for dynamic layout formatting.
Crystal Reports support parameters as input box, List of Values (LOV), range bound
parameters, cascading parameters, date parameter using a date picker.
In BI Publisher, input box is defined in the report editor as parameters with parameter
type as text. List of Values can be defined in the Report Editor as well; both static and
dynamic list can be created. Dynamic LOV is defined by writing an SQL query.
For range bound parameters, you can define two separate parameters; one input box to
enter the lower range and the other input box to enter the upper range. In case this
range has to be displayed in the report output, these two parameters can be
concatenated and displayed on the RTF template. Cascading prompt behavior can be
achieved in BI Publisher by defining parameters and associating each of them with a
List of Value. Since the List of Value gets data from a query the cascading relationship
should be built at the SQL query level where one LOV binds with another LOV. On
Converting reports from Business Objects Crystal Reports to Oracle BI Publisher Page 6