| Oracle® Business Intelligence Standard Edition One Tutorial Release 10g (10.1.3.2.1) E10312-01 |
|
 Previous |
 Next |
| Oracle® Business Intelligence Standard Edition One Tutorial Release 10g (10.1.3.2.1) E10312-01 |
|
Previous |
Next |
In this chapter, you create reports using Oracle BI Publisher to:
Create an Invoices report from the Oracle Database
Add parameters and lists of values to the report
Build the template, and publish the report
Schedule automated delivery of the report
Create a report from an Oracle BI Answers request and publish the report in Oracle BI Interactive Dashboards
Create a report from Oracle BI metadata
Prerequisites: There are no prerequisite steps for this chapter.
This section contains the following topics:
Section 7.1, "Logging in to Oracle BI Publisher and Setting Preferences"
Section 7.2, "Creating a Report Based on the Operational Data"
Section 7.3, "Automatically Delivering BI Publisher Reports"
Section 7.4, "Publishing a BI Publisher Report in BI Dashboards"
Section 7.5, "Creating an Oracle BI Publisher Report Using BI Server Metadata"
To log in to Oracle BI Publisher and set preferences:
In Windows, click Start > Programs > Oracle Business Intelligence > BI Publisher. This opens the Log In screen for Oracle BI Publisher. Enter Administrator for both the User ID and Password. Click Log In.
|
Note: Because you have logged in as the Administrator, the welcome page displays the Reports, Schedules, and Admin tabs. |
Click the Preferences link. The Preferences screen appears, with tabs for General Preferences and Account. Observe the options in the General Preferences tabbed page, and select appropriate options for UI Language, Reports Locale, and Reports Time Zone according to your requirements. You can also enable the accessibility option.
Click Apply, then close the window.
In this topic, you will create invoices from the BISE1_SALES operational data source.
This section contains the following topics:
You need to configure a JDBC connection from Oracle BI Publisher to the Oracle Database. To do this:
While logged into BI Publisher as Administrator, click the Admin tab.
Click the JDBC Connection link under Data Sources.
Click Add Data Source.
Enter bise1_sales for the Data Source Name.
Enter jdbc:oracle:thin:@localhost:1521:bise1db in the Connection String field.
Enter the Username and Password, as follows:
Username: bise1_sales
Password: welcome1 (or the password specified during installation)
Enter oracle.jdbc.driver.OracleDriver for the Database Driver Class.
Click Test Connection. If the test is successful, click Apply.
This section contains the following topics:
To define a new report:
While logged into Oracle BI Publisher as Administrator, click the Reports tab.
Click Home, then click the My Folders link.
Click Create a new report in the Folders and Report Tasks menu.
Enter Invoices as the report name, then click Create.
Click Edit under the newly created report.
Click Report.
Select bise1_sales from the Default Data Source drop-down list.
Deselect the Auto Run option.
Click Save.
To create a data model:
Click Data Model.
Click New.
Enter Customer Invoices in the Name field.
Select bise1_sales as the Data Source.
Click Query Builder.
Select the following tables and the specified columns for each table:
Customers: Name
Addresses: Cust_Street_Address, Cust_Postal_Code
Cities: Name, State_Province
Countries: Name
Orders: ID
Order_Items: Product_ID, Order_Date, Amount, Quantity
Products: Name
Join the tables by clicking the box to the right of the columns to be joined:
Customers.ID = Addresses.Customer_ID
Addresses.City_ID = Cities.ID
Cities.Country_ISO_Code = Countries.ISO_CODE
Customers.ID = Orders.Customer_ID
Orders.ID = Order_Items.Order_ID
Order_Items.Product_ID = Products.Identifier
These boxes, when marked for joins, turn to light gray. Also note that a fine line joining the tables appears in the Model canvas.
Click Conditions.
Specify Aliases for the following columns:
Customers.Name = CUST_NAME
Countries.Name = COUNTRY
Cities.Name = CITY
Products.Name = PRODUCT_NAME
Orders.ID = ORDER_ID
Accept the default Aliases for the other columns. Click Save.
Edit the SQL Query, and add the following conditions:
and ORDERS.PROMOTION_ID =999 and ORDERS.CHANNEL ='Direct Sales' and ORDER_ITEMS.ORDER_DATE between :start_date and :end_date
The last condition references parameters which will be created next.
Click Save.
Instead of generating all invoices at once, the invoices will be generated based on a date range. You will create two lists of values and two parameters to capture the start and end dates at report run time. To do this:
Create the first list of values. Click List of Values.
Click New.
Enter start_date_lov in the Name field.
Select bise1_sales as the Connection.
Enter the following statement in the SQL Query box:
select DISTINCT TO_CHAR (ORDERS.ORDER_FINISHED, 'DD-MON-YYYY') AS START_DATES from BISE1_SALES.ORDERS order by TO_DATE(START_DATES)
Click Save.
Create the second list of values. Click List of Values.
Click New.
Enter end_date_lov in the Name field.
Select bise1_sales as the Connection.
Enter the following statement in the SQL Query box:
select DISTINCT TO_CHAR (ORDERS.ORDER_FINISHED, 'DD-MON-YYYY') AS END_DATES from BISE1_SALES.ORDERS order by TO_DATE(END_DATES)
Click Save.
Create the first parameter. Click Parameters.
Click New.
Complete the Parameter Settings page as follows:
Identifier: start_date
Data Type: String
Default Value: 12-DEC-2006
Parameter Type: Menu
Display Label: Start Date
List of Values: start_date_lov
Options: deselect Can select all, then select Refresh other parameters on change
Click Save.
Create the second parameter. Click Parameters.
Click New.
Complete the Parameter Settings page as follows:
Identifier: end_date
Data Type: String
Default Value: 12-DEC-2006
Parameter Type: Menu
Display Label: End Date
List of Values: end_date_lov
Options: deselect Can select all, then select Refresh other parameters on change
Click Save.
You will upload an existing RTF template as the starting point for the report. After uploading the template and creating a layout based on the template, you will modify the template using Microsoft Word in the next section.
To create the layout:
Click Layouts.
Click Browse under the Manage Template Files section.
Locate the tutorial\bi_pub\invoices.rtf file, then click Upload.
Click New.
Complete the Layout Settings page as follows:
Name: Invoices
Template: invoices.rtf
Template Type: RTF Templates
Output Format: Limit Output to
Deselect all the format types, except for PDF.
Click Save.
Before you can create or modify any templates in Microsoft Word, you need to make sure that it has the BI Template Builder plug-in, formally known as Oracle BI Publisher Desktop.
This section contains the following topics:
To install Oracle BI Publisher Desktop:
Click the Reports tab in BI Publisher. Click the Home link, then click the Shared Folders link. Click Template Builder in the Folders and Reports Tasks section in the left menu box.
Click Save File to save the BIPublisherDesktop.exe file to a directory on the Windows client computer.
Locate the BIPublisherDesktop.exe file using Windows Explorer. Double-click the file to begin the installation. Choose the language, then click Next.
Click Next on the following two screens, then click Finish in the InstallShield Wizard Complete screen.
To open the report layout template:
Start the Microsoft Word application from the program menu. You will notice an additional menu item called Oracle BI Publisher at the top.
|
Note: The Oracle BI Publisher menu is displayed in Word only when you have successfully installed the Oracle BI Publisher Desktop. |
Also, there is a new Toolbar with the following menu items: Data, Insert, Preview, Tools, and Help. Oracle BI Publisher has similar items, and a few extra.
From the Oracle BI Publisher menu, select Log On.
In the Login screen that appears, enter Administrator as the Username and Password, then click Login. The first time you connect, you will need to specify the report server to which you want to connect. Enter the report server URL in this format: http://localhost:9704/xmlpserver.
|
Note: If you are not running Microsoft Word on the computer where Oracle BI Standard Edition One is installed, you must replacelocalhost with the actual hostname of the computer on which Oracle BI Standard Edition One is installed. |
In the Open Template dialog box, click My Folders.
Click the Invoices report. In the Template Layouts pane on the bottom, select the Invoices template. Click Open Layout Template.
In the template, notice the words and letters with the gray background. These are called form fields. Form fields are Word objects that allow you to reference other data (for example, a mail merge letter). BI Publisher uses form fields in two ways. The first use is to reference data fields from the report definition (like YEAR and MONTH). The second use is to embed instructions that control how the data fields will be laid out (like G, F, and E). If you want to know what these instructions are, double-click the form field and view the Help text. It is important to treat these form fields carefully and not accidentally delete or move them. Doing so will change the layout of your report. You may want to modify or add your own form fields with XSL commands to do more sophisticated things with your layout.
To insert report fields into the template:
Scroll down to the second page of the template.
Position the cursor in the large box beneath Invoice Address. From the Oracle BI Publisher toolbar, select Insert > Field.
Select Cust Name from the Field dialog box. Click Insert.
Continue to populate the invoice address box. For each field, position the cursor on the appropriate location in the template before you select the field and insert it. Keep the Field dialog box open.
When you are done, the invoice address box of the template should look like the one shown in Figure 7-5.
Next, you need to populate the Invoice Information part of the template. Insert the ORDER_ID field next as the Invoice Number. Insert the sum of the Amount field for Payment Due. Be sure to select the On Grouping option.
When you are done, the Invoice Information section of the template should look like the one shown in Figure 7-7.
Next, you need to populate the invoice line items table. Position the cursor under the Product ID column in the table, in front of the gray letter F. Select the Product ID field, making sure that the Calculation field is set to blank. Click Insert.
Continue to insert fields into the invoice line items table. When you are done, the invoice line items table should look like the one shown in Figure 7-9.
|
Note: The gray letter E at the end of the line item Amount field should remain. It is an Oracle BI Publisher formatting code. Also, note that the Total Amount Due is the sum of the Amount field, just like Payment Due. |
Close the Field dialog box.
To apply format masks:
Double-click the sum Amount field of Payment Due. In the Text Form Field Options dialog box, specify the following, then click OK:
Type: Number
Default number: 9999
Number format: $##,##0.00
Apply the same format mask as above for the two Amount fields in the invoice line item table.
Double-click the Quantity field. In the Text Form Field Options dialog box, specify the following, then click OK:
Type: Number
Default number: 1
Number format: 0
The Quantity and Amount columns of the invoice line item table should now look like the ones shown in Figure 7-11.
Upload the modified template to the Oracle BI Publisher server. In Microsoft Word, select Oracle BI Publisher > Update Layout Template.
Click OK in the confirmation dialog box.
To view the report:
Log in to Oracle BI Publisher as Administrator.
Click the Invoices report link.
Set the Start Date to 01-DEC-2006, and the End Date to 31-DEC-2006. Click View.
Scroll through the generated PDF file:
|
Note: Though the invoices are all in one single PDF file, the page count (Page x of y) is automatically reset for each invoice. This is achieved through the form field format codes mentioned earlier. |
This section contains the following topics:
To set up the Oracle BI Publisher Scheduler report delivery options, perform the following steps:
Log in to Oracle BI Publisher as Administrator, specifying Administrator as the password.
Click the Admin tab.
Click the Delivery Configuration link under the Delivery section.
Configure an Email report delivery server. Click the Email tab.
Click Add Server.
Fill in the fields as follows, then click Apply:
Server Name: The name for this Email report delivery server (can be the same as the Host value)
Host: The host name of your SMTP server
Port: The port number for your SMTP server (typically 25)
Next, configure an FTP report delivery server. Click the FTP tab.
Click Add Server.
Fill in the fields as follows, then click Apply:
Server Name: The name for this FTP report delivery server (can be the same as the Host value)
Host: The host name of your FTP server
Port: The port number for your FTP server (typically 21)
|
Note: The Host you specify must have an FTP service running. Typically, computers running Windows do not run FTP services, but computers running Linux or UNIX do. |
Click Reports to return to the home page.
To schedule FTP delivery:
Schedule the Invoices report for automatic delivery. Click Invoices.
Click the Schedule button.
Keep all the default field values.
Scroll down to the Delivery section. Select the FTP destination.
Notice that additional fields appear after the FTP destination is selected. Fill in the fields as follows, then click Submit:
FTP Server: The name of the FTP report delivery server
Username: Operating system user account name
Password: Operating system user account password
Remote Filename: The full path and file name for the report
You see a message that the report delivery job has been scheduled.
Click the Schedules tab to view the status of the report delivery job.
Click the Invoices job link.
Notice the delivery status is Success. Click the Reports tab to return to the home page.
You can also verify the existence of the report file by logging into the FTP server and viewing the directory where the report has been stored.
In this topic, you will create a new report with a template in BI Publisher from a BI Answers request in the GEC_DW subject area. You will publish this report in BI Interactive Dashboards.
This section contains the following topics:
Section 7.4.1, "Create an Oracle BI Publisher Report from an Oracle BI Answers Request"
Section 7.4.2, "Publish the Oracle BI Publisher Report on Oracle BI Interactive Dashboards"
To create an Oracle BI Publisher report from an Oracle BI Answers request:
Open the regional_sales.rtf file in Microsoft Word. This is a pre-created template file in Word, which has a header and footer, and an image inserted.
Log in to Oracle BI Publisher from the Word Oracle BI Publisher menu. This opens the Log In screen for Oracle BI Publisher. Enter Administrator as the Username and the Password, then click Log In.
After you log in, the Open Template Window opens. Select Oracle BI from the Workspace drop-down list.
Go to the users > Administrator > GEC_DW > Sales Trends folder. Double-click the Region Sales in View Selector report. This report has a dashboard page prompt associated with it.
The Save As Oracle BI Publisher Report Window opens. Select the My Folder folder to save the same report in Oracle BI Publisher.
Click Save. You will be able to see this report in Oracle BI Publisher now.
Notice the name of the document has changed in Word and includes the name of the Oracle BI Answers request.
In the Word document that loads data from this Answers request, place the cursor a few lines below the image. From the BI Toolbar, select Insert > Table Wizard to define a format for the table data in the report. This displays the Table Wizard. Do the following:
Select Table and click Next.
Ensure that ROWSET/ROW is selected as the Grouping Field, and click Next.
Select all the fields and click Next.
Select Region from the Group By drop-down list, accept the defaults for other options, and click Next.
Select the sort orders for the measures Quantity Sold. Accept the defaults in this step and click Next.
Edit the labels of the fields to Product Category and Quantity Sold, and click Finish to complete the creation of the table template.
The template you created looks like the one shown in Figure 7-17.
Double-click _Sales_._Quantity_Sold_ and format the field as shown in Figure 7-18. Click OK when you are done.
Use Word formatting features and format the other fields as shown. Create a title for the report called Regional Sales Report. Change the color to red and the font size to Header 1. Change the column headers color to blue and increase the font size to 14. Change the Region field font size to Header 2 and the color to green. Right-align the Amount Sold field and column header. The final format should look like Figure 7-19.
From the BI Toolbar menu, select Preview > RTF to preview the template. Scroll down to see the footer.
Switch back to Normal view. Publish the template by selecting Publish Template As from the Oracle BI Publisher menu. Enter Regional_Template as the name of the template.
Save the template as an RTF file for future use.
In this topic, you will publish the Oracle BI Publisher report that you created from the Oracle BI Answers request onto an Oracle BI Dashboard. To do this:
Return to Oracle BI Interactive Dashboards. Select the Sales Analysis dashboard and the Sales Trend page. This dashboard page has a prompt for the reports on the page. Alternately, if you have created the Sales Analysis1 dashboard, you can select the Regional Analysis page. The figures in this procedure are based on the Sales Trend page, in the Sales Analysis dashboard, but the steps are the same for any dashboard.
In the Sales Trend page, from the Page Options drop-down list (found on the right top corner of the page), select Edit Dashboard. The Dashboard Editor screen appears.
Drag and drop the BI Publisher Report from the Dashboard Objects list to the section as shown in Figure 7-21.
You can see that the BI Publisher Report object is added to the dashboard. Click the Properties link on this object.
The BI Publisher Report Properties screen appears. Click Browse to browse and specify the path for BI Publisher Report. Select the Region Sales in View Selector report from My Folders in BI Publisher, and click OK. Click OK again to close the BI Publisher Report Properties screen.
This will take you back to the Dashboard Editor page. Click Save to save the changes you made to the dashboard.
This dashboard page has a prompt on Region. Select Americas from the Region drop-down list. Click the Go button for the prompt. All reports on this page will display sales for Americas only. The dashboard is shown; scroll down and observe that the BI Publisher Report is displayed on the dashboard in the section that you have added.
In this topic, you will create an Oracle BI Publisher report from BI Server Metadata. You will also associate a pre-created template with the report to view the data.
This section contains the following topics:
Section 7.5.1, "Create an Oracle BI Publisher Report from BI Server Metadata"
Section 7.5.2, "Publish the Template to View the Report Data in Oracle BI Publisher"
To create an Oracle BI Publisher report from BI Server metadata:
Log in to Oracle BI Publisher as Administrator (password Administrator).
|
Note: You can log in to Oracle BI Publisher by logging in to Presentation Services and selecting the More Products > BI Publisher option, or you can log in to Oracle BI Publisher directly by selecting Start > Programs > Oracle Business Intelligence > BI Publisher. |
Navigate to My Folder and click Create a New Report. Enter From BI Server as the name of the report and click Create. The newly created report is displayed on the page.
Click the Edit link displayed below the report name to edit the properties. The report is displayed in the edit mode. Click Data Model and click New to define the data source for this report.
In the Data Set screen, select SQL Query from the Type drop-down list.
Select Oracle BI EE as the data source.
Click Query Builder to create a SQL Query. The Query Builder screen opens, displaying the GEC_DW subject area repository objects on the left.
Now, drag and drop the Time, Channels, Products, and Sales from the GEC_DW subject area one by one, to the Model canvas on the right. Select the following columns to be displayed in the query by selecting the checkboxes next to the column names:
Year from Times
Channel_name from Channels
Product Category from Products
Profit from Sales
Click Save. This will take you back to the BI Publisher Data Set screen. Observe that the query is displayed in the SQL Query field. Click the Save icon to save the report.
In this section, you will associate a pre-created template to the report you just created and then view the report in Oracle BI Publisher with this template. To do this:
In Microsoft Word, open the biserver.rtf file.
Through Word, log in to Oracle BI Publisher as Administrator (password Administrator). The Open Template screen appears.
Select the From BI Server report. Click Open Report at the bottom. This will bring you back to Word.
From the Oracle BI Publisher menu, select the Publish Template As option. This opens the Upload as New dialog box. Enter biserver as the name of the template, then click OK.
After the template is uploaded, it displays the message biserver was added to the report From BI Server. Click OK again
Connect to Oracle BI Publisher as Administrator. Open the From BI Server report from My Folders.
Click Layouts. Notice the biserver.rtf template is associated with the report.
Click View to display the report.
|
Note: You can also view the data in other formats, such as PDF, RTF, MS Excel, and so on. |
Scroll down and see the tabular report, as shown in Figure 7-31.
