TECHNOLOGY: Business Intelligence
Reporting with the FamiliarBy Mark Rittman
Create reports with familiar desktop tools and Oracle enterprise datasources.
People have a thirst for information, whether it's the weekly sales figures, a list of customers who have outstanding service requests, or just a list of customers who placed orders in the last week. Combine this with the need to produce invoices, customer letters, settlement checks, or even data for business-to-business interchange, and you can see that a typical organization's need for reports is only going to increase as time goes by.
As the internet and service-oriented architectures permeate organizations, sources for our reports seem to multiply, and these days reports can be based on technologies such as XML and Web services as much as on relational databases such as Oracle Database 10g. End users typically want to receive their report output in a variety of formats, with Adobe PDF typically used for pixel-perfect presentation reports, HTML for reports delivered via the Web, and formats such as CSV and XML for exporting and loading into other systems.
To address these varied reporting needs and to add industry-leading reporting and publishing features to the Oracle Fusion Middleware family of products, Oracle Business Intelligence Publisher 10.1.3 includes Oracle Business Intelligence Publisher Enterprise, a complete set of reporting, authoring, and publishing tools that you can either install standalone or as an integrated part of Oracle Business Intelligence Suite Enterprise Edition. Oracle Business Intelligence Publisher Enterprise can be installed and run on a variety of J2EE-compliant application servers, including Oracle Application Server 10g.
Oracle Business Intelligence Publisher makes report publishing easy, by separating the data query from the report template, giving you the ability, for example, to create a single invoice retrieval query that is then associated, at runtime, with a variety of invoice templates to suit different categories of customers. Oracle Business Intelligence Publisher also handles multiple language translations and fonts, removing the need to purchase expensive single-use applications to print in particular languages or particular formats. Best of all, Oracle Business Intelligence Publisher uses familiar desktop tools such as Microsoft Word and Adobe Acrobat to lay out report templates, keeping the report building process within a familiar environment and reducing the requirement for training and additional software.
So once you've installed Oracle Business Intelligence Publisher, either standalone or as part of Oracle Business Intelligence Suite Enterprise Edition, how do you go about building some reports? In the following examples, you'll first create a warehouse inventory report and populate it by using data from the order entry (OE) sample schema. Then you'll see the integration between Oracle Business Intelligence Publisher and Oracle Business Intelligence Suite Enterprise Edition, using Oracle Business Intelligence Publisher to create a pixel-perfect report with an Oracle Business Intelligence Answers request.
Building a Database Report with a Parameter
Log in to the Oracle Business Intelligence Publisher Enterprise home page (the default username and password are Administrator/Administrator) to bring up a portal-style interface that shows the reports you can manage and run (see Figure 1).
The left side of the page provides a list of common tasks, and the main part of the page displays reports organized into folders and subfolders. To edit a folder, click the folder icon next to the folder name; to run a report, click the report name; to drill down further into folders and subfolders, click the folder name.
Before you can start building some reports, though, you need to configure Oracle Business Intelligence Publisher Enterprise to add a JDBC datasource, which will point to the database used for the first report. To do this, click the Admin tab (see Figure 1), locate the datasources area of the page, click JDBC Connection , and then click Add Data Source . Next, enter the details for your installation. The following are examples and placeholders for the datasource, URL, username, and password:
The reports in this article are designed to query the OE sample schema. For your system, provide the correct datasource name, URL, username, and password; click Test Connection to check whether the details you entered are correct; and click Apply to save the details. You are now ready to create your first report.
The first report request is to create a warehouse inventory report in which the user can select a warehouse and see all the products and stock on hand. The data for this report will come from the OE.WAREHOUSES, OE.PRODUCTS, and OE.INVENTORIES tables. To start creating this report, first click the Reports tab to return to the Oracle Business Intelligence Publisher Enterprise home page, click the My Folders link, and then click Create a new report in the Folder and Report Tasks area. In the Enter Report Name field, enter the report name warehouse_inventories and click Create . The new report appears on the right side of the page; click the Edit link under the report name to start the data set report definition.
Oracle Business Intelligence Publisher Enterprise now displays the Data Set page, which includes a dynamic HTML interface that enables you to define the data model, lists of values, parameters, and templates associated with a report (see Figure 2).
Click Data Model in the Report navigator (on the left side of the page), and click New to create a new data set. Name the query inventory_query , and select SQL Query in the Type field. Select the datasource you previously created from the Data Source list ( oe_ds in our example), and check the Cache Result check box if you would like Oracle Business Intelligence Publisher to save the data requested by the report to speed up future requests.
The SQL query used to provide data for the report can be provided in one of two ways. You can either type it directly into the SQL Query text box, using the following SQL script:
select w.warehouse_name , p.product_name , i.quantity_on_hand from oe.inventories i , oe.products p , oe.warehouses w where w.warehouse_id = i.warehouse_id and p.product_id = i.product_id and w.warehouse_name in (:warehouses)
(Note that the :warehouses bind variable will be used later when you add a parameter to the report.)
Or you can use the Query Builder feature to create your SQL statement graphically (see Figure 3).
To use Query Builder, click Query Builder on the Report Definition page. On the Query Builder page, select OE from the Schema list, and then drag the WAREHOUSES, PRODUCTS, and INVENTORIES tables to the graphical ( Model ) view. (Note that you may need to enter the first letters of each table name in the Search field to locate them in Query Builder.) To join the WAREHOUSES table to the PRODUCTS table, first click the column selector to the right of the WAREHOUSE_ID column and then click the corresponding column selector in the INVENTORIES table to create the join. Do the same join with the WAREHOUSE_ID column in the PRODUCTS and INVENTORIES tables. Then click Conditions to add in the ( :warehouses ) condition required for the report parameter. Click SQL to see the generated SQL and Results to see a subset of the query output. Finally, click Save (at the top of the page) to close Query Builder, and copy the resulting SQL onto the Data Set page.
The next step is to define the report parameter and the associated list of values. Create the list of values by selecting the List of Values node in Report Explorer, clicking New , entering a name, and selecting the same datasource as before ( oe_ds in our example). You can either manually enter the SQL that retrieves the list of values as follows:
select warehouse_name from oe.warehouses
To create a report parameter to go with the bind variable added to the report SQL, select the Parameters node and again click New . When you complete the parameter options (see Figure 4), the Identifier value should be the same as the bind variable ( :warehouses ) specified in the query used to populate the report. Select Menu for Parameter Type and leave Multiple Selections unchecked, which tells Oracle Business Intelligence Publisher to generate a list from which users can make their parameter selections. Click Save to save the parameter values.
You have now completed the initial stage of your report definition. Save your work, and return to the Oracle Business Intelligence Publisher Enterprise page that lists your reports and the folder and report tasks. Do not view your report yet.
Create a Report Template
Now that the report definition is complete, it is time to create an accompanying template that Oracle Business Intelligence Publisher will use to determine how to display the report results. A particular report definition can have many templates associated with it, but for now you will lay out a simple tabular report template by using the Oracle Business Intelligence Publisher add-in to Microsoft Word. To install Template Builder , click the Template Builder link in Folder and Report Tasks (or locate it on the Oracle Business Intelligence Publisher installation disk).
After installing Template Builder and then starting Microsoft Word, connect to the Oracle Business Intelligence Publisher server via the Oracle Business Intelligence Publisher toolbar. Select Oracle BI Publisher -> Log On (see Figure 5).
Using the login screen, enter the username and password you use to connect to Oracle Business Intelligence Publisher, and if this is the first time you have connected with Template Builder, enter the URL of the Oracle Business Intelligence Publisher Enterprise server.
Before you can lay out the report template, you need to retrieve a sample set of data from the report data model. The Template Builder Open Template dialog box presents you with a list of all the reports you have access to; locate and select the report you just defined, and click Open Report to retrieve the sample data set (see Figure 6).
You can now start laying out your report template.
For this report, create a table of inventory items and stock levels, grouped by the warehouses in which they are held. To do this, select Oracle BI Publisher -> Insert -> Table Wizard in the Word document and then use the wizard to select data items for the table, and optionally the items on which the table is grouped and sorted (see Figure 7). In the case of this table, group the data by warehouse and sort on inventory item.
Add a total to the report to show the total stock on hand for all products in the selected warehouse. To do this, select Oracle BI Publisher -> Insert -> Field , and then in the Field dialog box, select the Quantity on Hand field and the sum calculation.
Now, when you view the report template in Microsoft Word, you can see the report layout and use the built-in formatting features in Microsoft Word to add images, change fonts, add highlighting, and so on.
To upload this template back to the Oracle Business Intelligence Publisher Enterprise server, first save the template as an .rtf file to your file system and then select Oracle BI Publisher -> Publish Template As . Name the template, and click OK to upload it to the server. You can now return to Oracle Business Intelligence Publisher Enterprise and view your new report.
Now that you have defined a report that uses relational data from an Oracle database, you can create a report that uses Oracle Business Intelligence Suite Enterprise Edition as the datasource. When working with Oracle Business Intelligence Suite, you can either source your data directly from the Common Enterprise Information Model, the Oracle Business Intelligence Suite Enterprise Edition equivalent of Oracle Business Intelligence Discoverer's End User Layer, or you can change the report datasource type to Oracle BI Answers and select a request to use as the report datasource.
Now that the report datasource is specified, as shown in Figure 8, you can add parameters, lists of values, and then a report template in the same way you did before (for the report based on a SQL query).
Finally, with your report definitions complete, you can view the reports with a direct URL or through the Oracle Business Intelligence Publisher Enterprise Web interface, or you can even embed them in Oracle Business Intelligence Interactive Dashboards dashboard pages, along with requests created with Oracle BI Answers (as shown in Figure 9).
This article has introduced features of Oracle Business Intelligence Publisher Enterprise and Template Builder. It has showed how to create both SQL and Oracle Business Intelligence Answers-based reports and defined templates using the familiar Microsoft Word environment. You can define reports against your own application data; add features such as graphs, images, and even bar codes; and start to take advantage of Oracle Business Intelligence Publisher with Oracle E-Business Suite.
Mark Rittman (email@example.com) is cofounder of Rittman Mead Consulting and specializes in Oracle business intelligence, data warehousing, and reporting. He is chair of the UK Oracle User Group Business Intelligence & Reporting Tools special interest group (SIG), cochair of the Oracle Development Tools User Group BI/DW SIG, an Oracle ACE, and one of the authors of the forthcoming Oracle Press book Oracle Business Intelligence Suite Developers Guide . In addition, he publishes a blog .