DBA > Articles

Reporting with the Familiar

By: Mark Rittman
To read more DBA articles, visit http://dba.fyicenter.com/article/

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:
* Datasource name: oe_ds
* URL: jdbc.oracle.thin:@<hostname>:<port>:<sid>
* Username: oe
* Password: password
* Database driver class: oracle.jdbc.driver.OracleDriver

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).

Full article...

Other Related Articles

... to read more DBA articles, visit http://dba.fyicenter.com/article/