Add a BI Publisher Report to a Flow

This example demonstrates how you can create a BI Publisher report and run the report using a flow. Add single or multiple BI Publisher reports to your copied or user-defined flow pattern.

When you submit the flow, the report automatically generates an output file that you can view. The output file is based on the template used for the BI Publisher report, Use the Run BI Publisher Report task to include the task in the flow pattern. The Run BI Publisher Report task belongs to the Statutory activities in the flow pattern.

Complete these tasks to create a user-defined report and include it in a flow.

Create a Data Model
Create a report template
Create a user-defined flow to run the new report

Create a Data Model

The first step in creating a report is to define the source of data for the report. A data model contains a set of instructions for the BI Publisher to retrieve and structure data for a report. In this example, you create a data model that has a single data set that retrieves data from a single data source, the database tables in the HCM application.

You use an SQL query to reference the application database tables and retrieve the list of values for the report parameters. When you run the report, the report engine executes the query and uses the results to display and populate the parameter list of values.

You must have BI Publisher Developer or BI Publisher Administrator role to create or edit reports from the BI Publisher.

Follow these steps to create the data model.

Navigate to Reports and Analytics under Tools in My Client Groups on the Home page.
Click Browse Catalog to open BI analytics in a new tab.
Navigate to /Shared Folders/Custom folder.
Click New and select Data Model under Published Reporting.
Click Add under Diagram and select SQL Query.

Enter this information in the New Data Set - SQL Query window.

Field	Value
Name	Enter a name for the new data model.
Data Source	ApplicationDB_HCM
Type of SQL	Standard SQL

Enter this SQL Query in the Query Builder box.

Select pfi. Instance_Name, pfi.Fl_Task_Status, pfi.Status as Task_Status, pci.Status as 
Checklist_Status, pci.Owner_ID
from pay_flow_instances pfi, Pay_Flow_Instances pci
where pfi.flow_instance_id = pci.flow_instance_id
and   pfi.instance_name = instanceName
and   pfi.status = flowStatus
and  pci.status = checklistStatus

Click OK.
Click Parameters under Properties in the left pane.

Enter these values for each of the three parameters defined for this model, Flow Instance Name, Flow Status, and Checklist Status.

Field	Value
Name	Enter a name for the parameter.
Data Type	Select String from the list.
Default Value	This isn't a mandatory field.
Parameter Type	Select a value from the list. Select text for all three parameters in this example.
Row Placement	Enter a value to show in which row to place each parameter. By default, all parameters are placed in row one.

Click the Save As icon, enter a Name for the Data Model, and save the Data Model under the path Shared Folders/Custom/.
To test the Data Model, select the Data tab.
Click View to view the XML. Provide a value for the three parameters.
Click Table View to view the sample data in a tabular form.
Click Validate.
Click Export and save the XML file locally on your computer.
Click View Engine Log and view the Data Processor log file in XML form. You can save the file to your local directory.

Create the RTF Report Template

You can either use MS Word which already has the BI Publisher plug-in integrated to create an RTF template or use BI publisher's quick Create Report option from the Data Model editor page. In this example, we will use the Create Report option.

Follow these steps to create an RTF report template.

Click Create Report.
Under the Select Data tab, click Use Data Model option to create the report using the data model you have already created in the previous task.
Click Next and under the Select Layout tab, select the report layout to define the page orientation of the report. The available options are either Portrait or Landscape.
Click Next and under the Create Table tab drag fields from the Data Source and drop them in columns on the table in an appropriate sequence.

The Create Report process uses the sample data that is saved to the data model. If sample data is not attached to the data model, the selected column headings display without data.
Click Next and save the report in the Shared Folders/Custom folder.
Navigate to /SharedFolders/Custom and open the report, provide appropriate values for the Instance Name, Flow Status and Checklist Status as an input parameter and click Apply.
To add additional roles, for example, BI Consumer Role, click More and then Permissions, and select the appropriate Permissions against a role.
Click OK.

Create a User-Defined Flow to Run the BI Publisher Report

Follow these steps to add the BI Publisher report to a user-defined flow.

Use the Flow Patterns task under Payroll in My Client Groups.

Click Create and enter this information.

Field	Value
Flow Pattern	Enter a name for the flow pattern.
Description	Entering a description for the flow is optional.
LDG Required	Select an LDG to restrict this flow for a specific legislative data group.
Flow Status	Select Active.
Connector Status	Task Flow

Select Statutory in the Activities to Include field.
In the Tasks section, select Run BI Publisher Report.
Click Next.
On the Parameters page, click Select and Add, and add the three parameters.

The flow parameters map to the BI Publisher arguments. For example, if your report is based on a SQL query, the first argument is the first bind variable of a SQL query data model.

Tip: To easily determine the sequence of arguments, view the list of parameters for the generated report in BI Publisher.

For example the First Argument is the Instance Name, the Second Argument is the Flow Status and so on.
Optionally, rename the parameter to a more meaningful name.
On the Task parameters page, in the Parameter Details section, complete these steps:
1. Confirm that the Parameter Basis for the First Argument value is Bind to Flow.
2. Select the Report Path parameter and enter the parameter Basis as Constant and Basis Value as the report path. Enter the path of the XDO.
  
  For example, if the BI Publisher data model is saved to the Custom folder in Shared Folders you would specify /Custom/yourBIreport.xdo.
Verify that the first, second and third argument parameters are bound to the flow parameter correctly.
Click Next, review the flow and save it.
Use Submit a Flow task from Payroll in My Client Groups on the Home page and submit the flow.
Submit the flow and view Checklist. Keep refreshing until flow is completed. Once completed, click on the Run BI Publisher Report link and view the report output.