43
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
39
the UI of Parameter definition page, there is a check the box for ’Refresh other
parameters on change’ which enables the cascading behavior. The date parameter with
a date picker UI is also supported in BI Publisher.
Finally, to allow these parameters to associate with the report data, you will have to edit
the report SQL query which you copied from Crystal. The parameters passed will
become host variables in the report SQL query. In case of Data Template, the report
SQL query will be treated the same way and additionally you will have to include the
parameter names in the parameters section.
1.4: Convert Summary Fields, Running Total Fields, Built-in Functions, Custom Functions
and Formula Fields
Summary Fields, Running Total Fields, Built-in Functions, Custom Functions and
Formula Fields are very common in Crystal Reports. It is important to understand the
role of these features in every report before converting them. Summary Fields and
Running Total Fields are purely data calculation fields, but Built-in Functions, Custom
Functions and Formula Fields may apply to Report layout formatting as well.
Therefore, your first step should be to separate the Built-in functions, Custom
functions and Formula Fields into data calculation function and layout formatting
function categories. You need to evaluate whether these Formulae or Functions can be
handled at the time of Data Extraction, if so they would fall into data calculation
function category. If the formula field is a simple calculation then you can allow it to be
handled on RTF template as well, but most of the complex formula fields, custom and
Built-in functions should be converted into a PL SQL function. The formula fields and
Custom or Built-in Functions that contribute to the layout formatting of the report
would fall into layout formatting function category and they should be handled on the
RTF template.
Summary Fields:
A Summary Field in Crystal Report is used to create summary data within the group
footer or header. The summary Field allows you to create different types of calculation
– sum, average, count etc. – on any report field. You can check the details of
calculation by right clicking on the Summary Field and then selecting Edit Summary.
In BI Publisher, a Summary Field can be handled at the time of data extraction using
Data Template, where you can define an aggregate function (SUM, AVG, COUNT,
MIN, MAX) at group level in dataStructure section.
Also a Summary Field can be handled in the RTF template in BI Publisher by using
the Insert Fields from toolbar or Menu bar of the BI Publisher Template Builder by
selecting the calculation function from dropdown list and checking the ‘On Grouping’
checkbox. For the summary functions that are not available from the drop down list
you will have to modify the code beneath the inserted placeholder in the RTF template.
Converting reports from Business Objects Crystal Reports to Oracle BI Publisher Page 7
41
Running Total Fields: The Running Total Fields in Crystal Reports are an advanced
version of Summary Fields. It evaluates the running total at different levels – for each
record, on change of field, on change of group, or based on a formula. Also it allows
the user to reset the running total value at different levels – for each record, on change
of field, on change of group, or based on a formula. Running Total Fields can be
created on any database column or a formula field.
To handle Running Total Fields in BI Publisher you should use the concept described
under title “Page-Level Calculations”, subtitle “Running Totals” in the BI Publisher
Users Guide. Use the updateable variable. The Running Total Fields that are reset on
change of group can be treated as Summary Fields and converted as described for
Summary Fields on the RTF template. If the Running Total Fields is built over a
Formula Field, then you can use a PL SQL function to define the Formula field. The
PL SQL function, when included in SQL query, will return data as XML element that
can be treated like any other XML elements for Running Totals. Another way to handle
Formula Fields under Running Total Field is to write the formula using XSL or BI
Publisher syntax on the RTF template.
Built-in Functions and Operators: There are several built-in functions and operators
provided by Crystal Reports. Some of them already exist as an equivalent function or
operator in BI Publisher; however, you will not find an equivalent function for several
of them. You will need to identify the complex Built-in functions that are related to
data calculation and convert them into PL SQL function. The simple built-in functions
can be handled at RTF template using BI Publisher code syntax.
Custom Functions: Custom Functions can be found at three places within Crystal
Reports Formula Workshop
ɷ
Report Custom Functions
ɷ
Repository Custom Functions
ɷ
Under Built-in Functions - Additional Functions (Plug-in Functions)
First check if an equivalent function is already available in BI Publisher. For example,
Date, Number or Currency formatting etc. is already available in BI Publisher for
which you may have written a custom function in Crystal Reports. If the function does
not exist in BI Publisher then you should convert those functions that are related to
data calculation into a PL SQL function and should be called with in the SQL query
statement.
Formula Fields: The formula fields, as mentioned earlier, need to be evaluated and
separated out into the data calculation and layout formatting formulae categories. The
ones that fall into data calculation formulae category should be included as part of data
model in BI Publisher. You can create PL SQL function to handle such formulae and
Converting reports from Business Objects Crystal Reports to Oracle BI Publisher Page 8
39
they will be processed at database level. The PL SQL function can be called from the
SQL query and the corresponding calculated data will be part of the XML data.
It is a common practice to use custom Functions or Built-in Functions and Operators
in a Formula Field for code re-use. The same code reusability approach can be used
while converting them into PL SQL function.
2. Convert Layout
See the user guide of Oracle BI Publisher
10.1.3.3.2 for details on supported features
related to layout design.
2.1: Open a Blank RTF
To start layout design open a blank document in Microsoft Word.
Note: In general starting with blank RTF is easier but in some situations you might
want to start with the editable RTF output from Crystal Reports and use it as the initial
template for BI Publisher. This technique may be helpful when the editable RTF
output file has not lost any formatting feature and the report has complex layout
formatting. For dynamic column reports and cross-tab reports blank RTF is the best
approach.
2.2: Get Sample data
Log on to the BI Publisher server from the Template Builder Add-in to Word and
open the newly created report to load XML data for template design.
Alternatively, you can log in to the BI Publisher server, view the report output data and
export the XML data. This XML data can be loaded from the BI Publisher Template
Builder.
2.3: Create layout template
At a high level we can categorize a Crystal Report layout into three categories – layout
design, calculations and formatting.
2.3.1: Layout Design
Layout Design includes identification of report format – tabular, form or free form,
identification of data elements, hierarchy, grouping, sorting, and filtering. These
contents acts as the backbone of the report layout design.
To build the data organization in BI Publisher, you can select a wizard in Template
Builder. The ‘Table Wizard’ can handle most of the simple data organizations and the
Table/Form wizard can handle some complex formats involving nested grouping. You
can use the ‘Cross Tab’ wizard to build cross tab reports and ‘chart’ tool to insert
various types of charts. The Insert Field dialog allows you to simply insert one field at a
time. This can work along with the ‘Repeating Group’ feature in the Insert Menu of
toolbar to create complex formats.
2.3.2: Calculations
Converting reports from Business Objects Crystal Reports to Oracle BI Publisher Page 9
38
Calculations refer to all the indirect data values which are obtained from a built-in
function, Summary Field, expression, formula fields, custom functions or conditional
display logic, Parameters, etc.
In BI Publisher, first check if an equivalent built-in function exists. Next you can check
whether the layout formatting is available as Native Microsoft Word or RTF formatting
feature. If so, just apply the desired formatting using equivalent Word or RTF
formatting feature. If such a function does not exist then they need to be written as
code in the Text Form Fields or directly on RTF page using either BI Publisher syntax
or XSL syntax.
A sub-template can replace a complex formatting function or a re-usable formatting
function. Similar to functions parameters can be passed to these sub-templates and
variables can be shared between the main report and the sub-templates. These sub-
templates can even replace the sub-reports used for condition based formatting.
2.3.3: Formatting
Formatting refers to the visual display of data elements, table size, table borders, table
background, static text, images, background color, font size, font color, font style,
alignment, header and footer contents etc. Since BI Publisher can leverage Microsoft
Office Word for creating RTF templates, you have the access to all the formatting
functionality available in Word.
2.4: Preview and Upload Report
At anytime while creating the RTF layout you can preview what the final report will
look like in the desired output format. Navigate to ‘Preview Template’ under Oracle
BI Publisher menu or Toolbar to view the report in PDF, HTML, RTF or Excel
formats. Inspect the preview version of the report to check that you have replicated
the layout and formatting you want in the report. If not, continue to modify and then
preview until the report looks the way you want.
Once the template is complete, navigate to ‘Publish Template As’ under Oracle BI
Publisher menu. Enter a template Name and click OK on the next prompt to
complete the upload.
CRYSTAL REPORT CONVERSION EXAMPLE
EmployeeSalaryReport
This is a very simple report covering some of the basic features to outline the
conversion steps. At a glance the output shows that the report data has been grouped
by Department and then by Manager. Total salary is the sum of annual salary of
employees reporting to a Manager and this is displayed as the last row of data under
each Manager. The report uses a banded format with alternate rows shaded in grey
background.
Converting reports from Business Objects Crystal Reports to Oracle BI Publisher Page 10
Documents you may be interested
Documents you may be interested
