Getting Started with Oracle BI Publisher 11.1.1.6.0

Download the Salary Report Datamodel. zip file to your local machine and unzip it. This contains a predefined data model Salary Report Datamodel.xdmz. You will use this file in the tutorial practice to upload a resource object to the catalog folder.

Have access to an Oracle Database11g with the sample schema OE installed and unlocked. This tutorial uses the OE schema included in the Oracle Database.

a. Enter the URL for BI Publisher in the browser window in the following format:
http://<hostname>: <port >/xmlpserver/. The examples covered in this tutorial use http:/localhost:7001/xmlpserver/

This opens the Oracle BI Publisher Enterprise login page.

b. Enter your credentials. Click Sign In. The Oracle BI Publisher Home page is displayed.

The Oracle BI Publisher Home page provides a task-based entry into Oracle BI Publisher's functionalities (greatly enhanced in the 11g release to enable the users to get started quickly with BI Publisher). It is divided into various sections enabling you to quickly begin a specific task, locate an object, or access help documentation.

You can observe:

• A global header containing links and options that enable you to quickly begin a task or locate a specific object within BI Publisher. It is always displayed when you are working in BI Publisher. While you are viewing a report or working within one of the task editors, you can use the global header to quickly begin a new task, search the catalog, access product documentation, or view a different object. On the global header, you can see the links to the Catalog, drop-down lists New, Open, Administration, Help, and Sign In details.
• A Create section on the left that has options to help you create reports, data models, and other objects
• A Browse/Manage section on the left that helps you to browse and manage the catalog, jobs, and job history
• A Get Started section on the left that helps you to get started with BI Publisher, with links to various BI Publisher tools, Online Help, and Oracle Technology Network
• A Recent section on the right, personalized to each user so that the users can open/view the reports that have been accessed recently (when you log in initially, you may not see any objects listed in the Recent section). This section contains the reports and other objects that you have recently viewed, created, or updated. You can take actions on these objects directly from the Recent region. You can open, edit, schedule, and view jobs or job history for a report.
• A Favorites section on the right enabling you to create your own list of objects for quick access. From the Favorites region, you can Schedule, Configure, Remove, and See Job History or Edit the objects that are selected. This is a new feature in the current release.
  

The Catalog page is displayed as a tree structure on the left side of the page with the details on the right. The catalog stores the BI Publisher objects, such as reports, data models, and style templates.

The catalog page has a tool bar with various task icons such as New, Upload, Copy, Cut, Paste, and other options that can be used when creating or managing the catalog objects such as data models and reports.

Use the Folders pane of the Catalog page to display and navigate the content.

To open the report Salary Report - No Parameters, select the report under the Shared Folders/Sample Lite/Published Reporting/Reports folder, and then click Open.

This will open the selected report in the Report Viewer as shown below.

There are several ways to add objects to the Favorites region:

• Use the More menu in the catalog to add the report or objects to the Favorites section.

1. Locate the report or the object in the catalog, click the More link, and then, click Add To Favorites in the More Menu.
   
   
2. A confirmation message is displayed.
   
   
   
3. The report is added to the Favorites section now.
   
   

• You can also add a report to the Favorites section from the Report Viewer. Use the Actions menu, and then click Add to My Favorite.

• You can also use the Manage link in the Favorites section of the Home page for adding and managing the favorites.

Click Catalog from the global header.

This will display the Catalog page.

Navigate to My Folders folder.

Click Folder from the New drop-down list.

Create dialog box is displayed.

In the Create dialog box, provide a folder name - Learn, and provide the description if necessary. Then, click Create.

The created folder is displayed in the catalog now. You will use this folder to save the files, reports, and other objects that you create for the tutorial practices.

On the Catalog page, navigate to the Learn folder, and then click Upload Resource icon.

In the File Upload dialog box, browse and select the Salary Report Datamodel.xdmz file from your local machine.

Click Upload after selecting the relevant resource.

.

The uploaded resource Salary Report Datamodel is available under the Learn folder now.

This concludes the topic of uploading a predefined data model to the catalog folder.

This feature is useful if you have to use any predefined object such as data model, template, or report.

To set and view the account preferences for your user credentials, use the My Account option for your login. In this example the user name is weblogic.

Click My Account under the Signed In As user account to:

• Set general preferences
• Change your password
• View assigned roles

By default, the General tab is selected.

A locale is a language and territory combination. BI Publisher uses the Report Locale selection to determine translation and currency, date formatting that are to be applied on a report. In this example, the locale is set as English (United States).
Note: If a particular report does not have a translation for the selected locale, BI Publisher applies a locale fallback logic to select the most appropriate layout available. BI Publisher applies localized number, date, and currency formatting independently of the layout translation.

The UI language is the language in which the user interface displays. The language that you selected at login is selected as the default. Select from the languages that are available for your installation. In this example, it is set as English(United States).

Reports display the time according to the time zone preference selected here. You can override this setting when creating a scheduled report job, which is discussed in the later topic. In this example, the Time zone is set to [GMT+00:00] Casablanca.

Note: The time displayed on the user interface and reflected in report processing times is governed by the BI Publisher server time zone.

When BI Publisher is running in an integrated mode (installed with OBI EE), the Report Local, UI Language, and Time Zone drop-downs are grayed out. If you need to change these preferences for the BI Publisher running in the integrated mode, you should log in from the BI Analytics page.

Click the Password tab to reset the password.
Note: If your account password settings are inherited from another application such as Oracle BIEE, then you cannot update your password here.

Click the My Groups tab to view a list of the application roles to which you are assigned. You cannot modify this list. Click OK to close the My Account window.

To define a JDBC connection, click the Administration link on the global header.

In the Administration page, click the JDBC Connection link found under the Data Sources section.

This displays the Data Sources page. Observe the available Data Source list. Oracle BI EE is configured out of the box, if Oracle BI Publisher is not a stand-alone install, but part of the OBI EE install.

On the JDBC tab, click Add Data Source to create a JDBC connection to connect to Oracle Database.

On the Add Data Source page that is displayed, enter the details as given below:

• Data Source Name – Demo
• Driver Type –Oracle 11g
• Database Driver Class – oracle.jdbc.OracleDriver
• Connection String – Provide the database connection details.
  For example, db:oracle:thin:@hostname:port:SID
• Enter the username and password as OE.

Do not click Apply or Cancel after defining the above details. Click Test Connection.

If the connection to the database is established, a confirmation message is displayed indicating the success.

Then, click Apply.

You can see the newly defined connection, Demo, in the list.

BI Publisher ships with a repository of various sample files and reports. You can view these file data sources by clicking the File tab on the BI Publisher Administration page. The screenshot shows a predefined File data source with the name demofiles.

From the global header, click New and then select Data Model. You can also use the Create section and Data Model link.

The Data Model Editor window is opened.

In Data Model Editor, observe the Data Model Properties section that is displayed on the right.
Select a Default Data Source (that points to the database that you have been earlier connected to). In this example, you will use Demo. Optionally, you can add a description to the data model.

Note: Ensure that in the XML Output Options, the Include Parameter Tags is checked, and the Include Empty Tags for Null Elements and Include Group List Tag check boxes are not checked.

In this example, you will create a data set by using a SQL query for the report.

a. Click Data Sets on the Left side pane. This will display the Data Set Editor section.

b. In the Data Set Editor section under the Diagram tab, click New Data Set Menu and select SQL Query as the type of data set.

Create Data Set -SQL dialog box is displayed.

a. Enter an appropriate name for the data set (in this example, it is employees).

b. Accept the default data set option (as you have already defined it in the Data Model), and click Query Builder to create a query.

The Query Builder window displays the OE schema objects on the left side.

Click DEPARTMENTS and EMPLOYEES tables to add them to the Model canvas on the right.

The selected tables are displayed in the Model section. Now, define a join between these two tables as follows:
a. Click the box beside DEPARTMENT_ID column in the DEPARTMENTS table.
b. Similarly, click the box beside DEPARTMENT_ID column in the EMPLOYEES table. (These boxes, when marked for joins turn light gray.) Also note that a fine line joining the tables appears in the Model canvas.

c. Select the following columns from the tables (by selecting the check boxes beside the column names):

• Select DEPARTMENT_NAME from the DEPARTMENTS table.
• Select FIRST_NAME, LAST_NAME, HIRE_DATE, and SALARY columns from the EMPLOYEES table.
  
  

Click the Conditions link. Change the column names and remove "_" to have more appropriate aliases. Change the aliases for the columns as below:

• DEPARTMENT_NAME to DepartmentName
• FIRST_NAME to FirstName
• LAST_NAME to LastName
• HIRE_DATE to HireDate
• SALARY to Salary

Note: The aliases and display names can be changed in the Data Model page, either in the Diagram or in the Structure.

a. Enter "IN (:Dept)" in the Condition field for the department name column, as shown in the screen below. This will define a Dept parameter on the department name column.

Note: The IN condition is being used here for the parameter to accept All or Multiple values for the department name column. You will edit this parameter later to add list of values.

b. Click Results to see how the query results appear in Query Builder.

This will prompt you to enter a department name for the parameter, enter Sales and click OK.

Results are displayed.

View results. Click Save to save the query.

a. This takes you back to the Create Data Set -SQL dialog box. Observe that the query that you created is reflected in the SQL Query area. Click OK to add this data set to your data model.

b. A confirmation message asks whether you want to create a bind variable. Click OK to create the parameter.

c. An informative alert confirms parameter listing. Click OK.

This newly created data set is displayed under the Data Sets node in the left side pane of the Data Model Editor. Also, the Data Set diagram is displayed in the Data Set Editor pane. Observe that the Dept parameter is also listed under the Parameters node.

You will now add List of Values and add/edit parameters for this data set.

In the Data Model pane, click List of Values node. In the List of Values pane, click the + sign to create new List of Values.

Select the following Options:

• Enter DepName as the Name of the LOV.
• Select SQL Query as the Type from the drop-down list.
• Ensure that Demo is selected as the Data Source.
• Click Query Builder to define the query that returns department names for the LOV
  
  

In the Query Builder page, select DEPARTMENT_NAME from the DEPARTMENTS table. Click Save.

The query for the LOV should look like this:

a. Add another LOV (list of values) EmpName, following similar steps.
You will use the Employees table from the data, in the query builder.

b. After creating the LOV, reorder the list by using the Reorder buttons.

Now, edit the existing parameter, and add another parameter to use the list of values that you have created.

In the Data Model pane, click Dept under the Parameters node. In the Parameters pane, edit the properties by providing the following information:

Leave the Name as Dept and the Data Type as String.

Provide Default Value as * , and select Menu as the Parameter Type.

Ensure that DepName LOV is selected as the List of Values for the parameter.
Ensure that Multiple Selection, Can select all, and All Values Passed options are selected.
(These options allow you to select all or multiple values for the department.)

Also, change the Display Label as appropriate, such as Department Name.

Add another parameter Emp. Use the Add icon to create a new parameter.

Follow the previous steps to define the parameter properties.

You will need to use the EmpName LOV created previously for this parameter.

Note that the newly added parameter always displayed first. Use the Reorder buttons to list the parameters as shown in the data model pane.

Click the Save Icon to save the Data Model.

Save the Data Model as Salary Report Parameters under the Learn folder.

Click the XML icon in the Data Model Editor window to run the data as XML output.

In the XML Output window, observe that the Department and the Employee Parameters are available with the menu options. Click Run.

The XML output is displayed.

To save this as sample data, click the Open Menu drop-down list icon, and select Save As Sample Data.

You can see that the sample.xml is listed in the Sample Data section of the Data Model (as shown below):

Note: It is very important to save sample data for a data model. Otherwise, when creating Layouts, the previews do not appear correctly.

This concludes the topic of creating a new Data Model with Parameters and List of Values. You will use this data model in the next topic to create a new report.

A BI Publisher report is made of data model, layout, properties, and translation (optional). Creating a New Report involves selecting the source of the data for the report, selecting the data columns to be displayed in the report, and then adding a meaningful layout for this data, defining the properties for the report parameters, and scheduling the report.

• A Data Model defines data that is used by a report. Selecting a data model is the first step in creating a new report. You can select the data model with three options. They are:
  
  • Select an existing data model from the catalog. Locate the data model by navigating through the catalog or by using the Search option.
  • You can also upload a Microsoft Excel file (file type.xls). If the uploaded spreadsheet contains multiple sheets, select the sheet to use as the data source. You can include data from one sheet only.
  • You create a new data model. This option opens the data model editor for you to create a new data model.
    
• Next step is to design a layout for the report data. The layout defines how the data is presented in the report. A layout consists of a template file and a set of properties for rendering the template file. BI Publisher supports templates created from various sources including Microsoft Word, Adobe Acrobat, Microsoft Excel, Adobe Flash, and BI Publisher's own layout editor. A report can include multiple layouts.
  
  
• Next, configure properties for the report. The report properties enable you to control many aspects of the report generation, formatting, and display.
  
  
• Optionally, add translations for the report. BI Publisher's translation support enables you to include translations for individual layouts or for all translatable strings in the layout, data model, and the report metadata.
  
  
• Save the Report to view it in Report Viewer. After the report is generated you can schedule it for various destinations.

Click New>>Report in the global header.

You will be asked to select the data source for the new report. Select the Use Existing Data Model option.

You will use the Salary Report Parameters data model that you have created previously.

Select the previously created data model Salary Report Parameters from the Learn folder. Click Next.

Select the option to Use Report Editor, and then, click Finish. This will lead you to saving the report before proceeding further.

Selecting the Guide Me option guides you through selecting specific columns from your data source for this report and viewing a simple tabular layout. By using this wizard, you can quickly view the data, create a simple tabular report, and be guided to the layout editor.

Save the Report as Salary Report with Parameters under the Learn folder.

The Report Editor window is displayed with the saved report, where you can edit the layout, parameters, and other properties, and also a link to view the report.

BI Publisher Layout Templates are created by using the BI Publisher Layout Editor – a design tool that provides a WYSIWIG, drag and drop interface for creating pixel perfect reports in PDF, RTF, Excel, Power Point, and HTML.
It also provides dynamic HTML output that supports lightweight interaction through a browser.

BI Publisher layouts that are created online are best suited for reports of simple to medium complexity that do not require custom coding. Because the dynamic HTML view is only available for BI Publisher layouts, these layouts must be used when there is a requirement to enable a Report Consumer to interact with the report.

• To use the online layout editor, your account must be granted a role that includes the appropriate permissions for accessing report layout tools.
• For optimum viewing, set your display resolution to 1024 x 768 or higher.
• The layout editor does not support name spaces in the XML data.

Select a boilerplate or shared template to create a new layout for this report. Select Chart and Table under the Shared Templates.

Note: Boilerplates or shared templates make creating reports easy and efficient. You can use the standard shared templates or can create your own predefined templates based on your work requirements.

The report is opened with an untitled layout in the Layout Editor. Observe the various sections, Data Source, Components, and Properties, that enable you to create a pixel perfect layout.

The Data Source displays the data available. Because this is a predefined layout with a chart and a table structure in place, you will see the grid sections to guide you to add text items, charts, and data tables. You will start adding the data from the data source to make this a meaningful report.

Change the text items in the layout to suit the Report, Chart, and Table that you are going to create. Double-click the default text item to continue with the same format. You can format the text according to your requirement with the Text properties displayed on top of the editor. In this example, the selected Salary by Departments section title is left aligned.

Select the default chart object in the layout grid, and choose appropriate type and style for the chart. This example has used Bar Chart and Earth Style. Cancel the 3D effect and keep the chart simple.

Drag and drop the Salary and DepartmentName elements to the chart as shown below:

Change the aggregation property of Salary to Average.

A Bar chart is generated for the selected data.

Now you will add the data table for Salary by Departments. You will use "Repeating Sections" and "Data table" elements.

a. Under the Salary by Departments grid cell, click Insert>Repeating Section.

b. In the Insert a Repeating Section editor window, select Element, Row, and DepartmentName. Click OK.

The Repeating section is inserted.

Select the repeating section area and click Insert>Data Table.

Drag the data elements from the Data Source to the Layout pane. Observe that DepartmentName is outside the data table.

Adjust the column width by editing the column property as shown in the image. You will have to select the column to be edited, and then change the properties.

You can also edit the columns by adjusting the column directly in the table.

a. Format the Hire Date column by the Data Formatting option. Select an appropriate date format.

b. Format the Salary column by using the Data Formatting option. Select the Currency format.

c. Format the total row for the salary column with currency formatting. You will have to select this cell separately for the formatting.

The formatted table is displayed as shown below.

d. Check for any empty data elements that are not defined in the template. In this newly created layout there is an empty data table. Because you have inserted a new data table under the repeating section, the default data table element that was part of the chosen boilerplate template remains empty.

Select and delete it from the layout. Empty data elements will result in errors in the report views.

Click the Save icon to save the layout.

Save the layout with the name New Formatted.

Observe that the saved Layout is displayed in the Layout Editor with the name New Formatted.

Click the Viewer menu and select the Interactive view to view the report in a browser window.

The Report is previewed in Interactive Viewer in a browser window. Close the browser window to get back to the Layout Editor.

Click Save and then click Return to close the Layout Editor.

You can create Report templates online as described in this topic.

You can also create various other templates such as RTF, EXCEL, PDF, Flash, and eText.

Additional information:

• RTF templates – by using Template Builder for MS Word ( MS Word add in)
• Excel templates – by using Excel Analyzer ( MS Excel Add in)
• PDF templates – by using any application that supports PDF conversion, or by scanning a paper document to be used as a template, or, by downloading a PDF document from a third-party source.
• Flash Templates – by using the FlexBuilder IDE from Adobe
• eText Templates – by using either EFT or EDI types based on commands.

The Report Editor window displays the newly created New Formatted Layout.

Click the Save icon, and then click View Report.

The report is displayed in the Report Viewer window.

Observe that the parameters are displayed horizontally, and with the display type as menus. You will learn to change this display in the next few steps. This is a recently added enhancement.

Click Edit Report from the actions menu.

This will bring you back to the Report Editor. Parameters are defined in the data model. The report editor enables you to configure the parameter settings. Click the Parameters link in the report editor window. The Parameters dialog is displayed as shown below.

Observe that the parameter display is set to Horizontal Region, label location is Place label on side, and the Show Apply Button is set to Default. Also, the type of the option for parameters are Menu.

Now, change the display options as shown below and click OK.

Save the report and view it in the Report Viewer.

The Report is displayed in the Report Viewer window with the newly set display options for the parameters.

You can select the various departments, and employee name combinations to view the respective report data. This adds greater flexibility and interactivity to the BI Publisher reports.

You can also change the display for the Show Apply Button option to false in the parameter settings for the same report, and observe the change in the display. Use the Report Editor to edit the parameters as explained previously.

Now, Return to the Report Editor from the Actions menu, and change the parameter display back to menu. You will use this display and the layout in the next topic of Scheduling Reports.

You can schedule a Report Job in any of the following ways:

• From the Home page: From the Create region select Report Job

• From the global header: Click New> Report Job

• From the Catalog: Navigate to the report you want to schedule, then select the Schedule link

• From the Report Viewer: Click Actions and then click Schedule

In this example, you will schedule the previously created Salary Report with Parameters report.

Select the report from the catalog and click Schedule.

The Schedule Report Job window is displayed. It contains four tabs to define the options for your report job: General, Output, Schedule, and Notification.

The Schedule Report Job window displays the General tab by default. You can either leave the default selected parameters as it is, or change it to suit your requirement. In this example, you can see the selected report for scheduling in the general tab, and default parameters.

Click the Output tab.

Select the options as shown below.

• Select the Make Output Public, and Save Data for Republishing check boxes.
• Output name is Output1 by default. This refers to the delivery destinations that you have defined.
• Leave the default selected layout as is.
• The format selected is HTML. The various other formats supported are PDF, RTF, Excel, and Power Point 2007.

Note: Email, a typical destination type, is not configured. This feature is covered in the Advanced Features tutorial.

a. Click Schedule tab.

b. Select the options as given below:

• Frequency is set to Daily.
• Leave the Start and End Dates as default selection. You can change this based on your requirement.
  

Note: Do not run the report immediately. Maintain a delay with the submission and the actual schedule time.

:

You can use the Notification tab to send notification to the recipient via Email. This is an optional step.

c. Click Submit.

Provide an appropriate name to the Report Job in the Submit dialog box, and then click OK. In this example, the Report Job name is Sal Report Job 1.

You will get an alert message that the Report Job is successfully submitted. Click OK.

This concludes the topic of Creating a Report Job.

The Manage Report Jobs page displays information about future scheduled and recurring report jobs and enables you to take actions on these jobs. You can edit the running or future scheduled report jobs here. For this, you will need a running or future scheduled report job. You will now edit the previously scheduled job, which was set to run daily.

Click Open>Report Jobs from the global header.

The Manage Report Jobs page is displayed with the scheduled reports.

You can delete, pause, resume, or edit report jobs from this page using the icons as shown in the image.

Select and click the Report Job Name. In this example, click Sal Report Job 1 to view the Report Job details.

You can also edit the report job from the Report Jobs Detail page.

Click the Edit icon next to the job name as shown below:

This will take you to the Schedule Report Job Page.

a. Edit the Report Job to change the parameters displayed. Select Shipping, Sales, and Finance departments from the Department Name drop-down list. Keep the Employee Name as is wih the All option.

b. Under the Output tab, change the format to PDF.

c. Click Submit. This will submit the same job with the previously set frequency. Keep the name of the job same as before and click OK.

d. After you get the confirmation that the job is submitted successfully, click Return to return to the Manage Report Jobs page.

Observe the changes in the Manage Report Jobs window. The edited and submitted job is listed under the previously chosen Job name Sal Report Job1. Click the Job Name to view the details as explained at the beginning of this topic. In the displayed report Sal Report Job 1, observe that the values for the departments are reflected as defined by you during the editing. The output format change is reflected too. Scheduling frequency is left unchanged.

You will edit this report job again to change the frequency of the schedule and submit the job as a new one and see the scheduled report output.

Click the Edit icon to edit the report job. You will be taken to the Report Job/Scheduling page.

a. Now, change Schedule Frequency for the same job to Once, and select the Run Now option. Keep all other criteria unchanged from the previous schedule.

b. Click Submit As New. Name this Report Job as Sal Report Job 2 and click OK.

c. Click OK in the submission confirmation message.

Now, click Open>Report Job History from the global header. You will need to use the Job History to view the Jobs that are executed already.

The Report Job History page displays information about running and completed report jobs.

Observe that the newly scheduled report is successfully submitted. Click the report job - Sal Report Job 2 to view the details.

The Report Job History for Sal Report Job 2 is displayed as shown below. The parameters display and output options are as set by you.

Click Output 1 link in the list to see how the scheduled report is displayed.

The report is displayed in the browser window in the PDF format. Use the browser back button to return to the BI Publisher.

This concludes the topic Managing Report Jobs.