Business Process Server enables you to integrate externally defined reports. Use the Report Builder to create external reports that can access and execute reports using external report servers.
To create an external report:
1. In the Business Process Portal, select the Management module and then click Reports > My Reports.
The Report List page appears.
2. At the top of the page, select the report type as External Report from the drop-down list and then click Add Report.
The Add External Report page appears.
3. Select the type of the report you want to create–Crystal, HTML, or BPM SQL.
4. In the Report Name field, provide a name for the new report.
Note: Do not use the forward slash (/) character in a report name. Business Process Server cannot display a report that includes ‘/’ in its name.
5. In the Description field, provide a description for the report.
6. In the Report URL field, provide the URL of the corresponding external server.
When creating HTML reports, enter the URL of the HTML server in the Report URL field.
When creating Crystal Reports, enter the URL of the Crystal Report server in the Report URL field.
When creating BPM SQL reports, specify a file name instead of a URL in the Report URL field. This file contains the SQL statements for execution by Business Process Server. The BPM SQL files are in the <App_Server>\sbm.war\BizManage\reports directory. The file extension is *.BizSQL, which works for all databases. The path to the BPM SQL file depends on the application server you are using.
Note: If you want to include the Chart View in your BPM SQL report, then your query must contain these parameters from Business Process Server database: process_template_name, process_template_id, process_instance_name and process_instance_id
Note the following conditions when using BPM SQL syntax:
You must use the exact syntax when developing BPM SQL reports. The following parameterized SQL query is provided as an example:
select * from processinstance where processinstance.creator = '<%= who %>'
where the parameter value must be enclosed within the <%= %> tag, and you must enter a space before and after the query name. The tag must also be enclosed with single quotes.Optionally, you may also use non-parameterized SQL queries, the syntax for which is as shown in the following examples (in these examples, * = all):
Select * from processinstance where processinstance.creator IN '<%= who %>'
Select * from processinstance where processinstance.creator = '<%= who %>'
Select * from processinstance where processinstance.creator NOT '<%= who %>'
Please make sure that you enter the SQL query as is shown in the above examples with appropriate changes as per your requirements.
Do not put a semicolon at the end of the SQL statement; it causes an invalid character error.
Use the following syntax when you want to display a valid field under a customized label in Business Process Portal:
select field1 AS label1, field2 AS label2, field3 as label3,... from table name
or in a specific example:
Select PROCESSINSTANCE.PROCESSINSTANCENAME AS Task from PROCESSINSTANCE
where PROCESSINSTANCE.PROCESSINSTANCENAME is the field, Task is the label and PROCESSINSTANCE is the table name.
7. In the Define Parameters section, provide the name and value of the parameter and then click Add.
All defined parameters appear in a list and can be edited or deleted.
When using BPM SQL as the report generator, Business Process Server substitutes the parameters into the file containing SQL statements, enabling you to define parameterized SQL statements. When entering parameters and values, follow the SQL syntax. For example, in the Name field, use the following format to enter parameter names in the BPM SQL query file: parameter_name or ‘parameter_name’
As shown in the example above, the parameter value may be given without single quotes or within single quotes, depending on the SQL syntax. For example, if the parameter’s value is part of a Where clause, then it is in single quotes.
8. Click Create to create the report.
The Report List page appears and displays all created reports.
You must use the exact syntax when developing BPM SQL reports. The following parameterized SQL query is provided as an example: