Oracle BI Publisher is the reporting immovable to author, rule, and take in hand every your reports and documents easier and faster than stated reporting tools. Use your web browser or going on to date desktop tools to make every one of from pixel-do customer-facing documents to interactive handing out reports adjoining nearly any data source. View reports online or schedule them and tackle tens of thousands of documents per hour following minimal impact on transactional systems.
This tutorial covers the following topics:
- Signing In
- Viewing a Report
- Creating Your First Data Model
- Creating Your First Report and Layout
- Connecting to a Data Source
- Connecting to an Email Server
Signing In
To sign into the Trial Edition of BI Publisher:
1. Open the sign-in window in one of the following ways:
- In the welcome page, click Sign In to BI Publisher.
- From Windows Start menu, select Programs, Oracle BI Publisher Trial Edition, then Sign In to BI Publisher.
2. In the sign-in the window, enter the credentials:
- Username: The username you specified during installation
- Password: The password you specified during installation
After you sign in to BI Publisher, the Home page displays. The Home page is the task-oriented, centralized workspace combined with a global header, allowing access to BI Publisher objects, editors, and help documentation.
The Recent section enables quick access to the objects you have recently interacted with. Use Favorites region to personalize Home page with links to the items you use the most.
Viewing a Report
BI Publisher Trial Edition comes with the many predefined sample reports which demonstrates BI Publisher’s robust reporting capabilities.
To view the report:
1. Click Catalog link in the Browse/ Manage section or click Catalog link in the global header.
The Catalog page is displayed as the tree structure on left side of the page with the details on the right. The catalog objects such as reports and the data models are organized into folders.
2. Expand Shared Folders and expand the Samples folder.
- Overview folder and click the Open link for the SFO Passenger Count Report.
3. Explore the features of this interactive report on your own or follow these guided steps:
- This report includes a Description tab that describes the insights provided by each of the report layouts. Each of the other tabs is a different layout of same data. Click on the Description tab to read about highlights of SFO Passenger Count Report.
- Click the Insight tab.
– Click the specific airline to filter the report components by just that airline. To filter by multiple choices, click additional airlines while pressing Shift+Ctrl. Notice that the selections move to the top of the list.
– The horizontal lists enable you to filter data by Domestic or International flights and by the specific years.
– Click any particular data point on the chart to filter the rest of the report components by that measure.
– Note the scrollable table. You can filter table by any value in any column and also sort based on any column.
- Click Airline by Year tab. This layout highlights the trends of passenger volume from one year to the next year. Again, you can select a specific airline to focus on or a specific region. Also, you can select the specific years or months.
The following example shows Airline by Year layout filtered by Region: Asia; Year: 2008, 2009; Month: 1,2,3.
- Note that these layouts by default display in the interactive output format. To change output format, click the View Report toolbar button to select another format.
4. To continue exploring the features of BI Publisher sample reports, navigate to Shared Folders, then Samples, and then Index. The Index provides descriptions and links to each of the sample reports. The Index can help you find examples of reporting features that your enterprise requires.
Creating Your First Data
Modeling your first report is based on the employee data in sample schema provided in the BI Publisher Trial Edition. In this procedure, you can create the data model to extract employee information. This data model will include a parameter to enable users to make a selection for the employee department at run time. This section includes the following procedures for creating your data model:
- Opening the Data Model Editor
- Setting the Data Model Properties
- Creating a SQL Data Set
- Defining a List of Values for the Parameter
Viewing and Saving Sample Data
1. Opening Data Model Editor to open data model editor:
- On the global header, click New and select Data Model from the menu.
2. Setting Data Model Properties set data model properties as follows:
- In the task pane on the left, select Data Model to display the Properties pane.
- From the Default Data Source list, verify that the demo is selected. Optionally, add a description to the data model.
3. Creating a SQL Data Set
The employee data for this report is retrieved by the SQL query against demo database.
To create a SQL data set:
- From the task pane on the left, select Data Sets.
- From the New Data Set menu, select the SQL query.
- In the Create Data Set – SQL dialog box, enter a name for the data set (for example employees) and click Query Builder.
- The Query Builder launches displaying the schema objects on the left side. Click Departments and Employees to add them to the work area.
- Define a join between these two tables as follows: Click join column beside DEPARTMENT_ID in the DEPARTMENTS table. Similarly, click join column for DEPARTMENT_ID in EMPLOYEES table.
- Select the following columns from the tables (by selecting the checkboxes 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 tab.
- Add parameter condition “IN (: P_DEPT)” in Condition field for DEPARTMENT_NAME column. Then this defines parameter P_DEPT on department name column. The IN condition is used to enable the parameter to accept multiple values for the DEPARTMENT_NAME column.
- Click Save. Then the SQL query displays in the New Data Set dialog.
- Select the parameter checkbox, and then click OK. The data model editor prompts you to allow creation of the P_DEPT parameter. The data model editor confirms creation of the parameter. Click OK. Note that parameter has been created and the SQL data set now displays as an object on the Diagram tab called G_1.
- Click the Structure tab. The Structure View enables you to edit characteristics of your data set, including Display Name of the data elements. The display names can also be customized in report layout, but it’s often more efficient to edit the data set. Rename Display Name for the column names as you would like them to appear in report.
- Click Save.
- In the Save dialog, select My Folders. Click the New Folder icon to create a new folder for your data model. Enter a name for your folder and click on Create.
- Enter name for your data model and click Save to save it to your new folder.
4. Defining a List of Values for the Parameter
The data model editor creates the parameter, P_DEPT, for you. In your final report, you can select the value to pass to the report from a list of values (or menu).
To add the list of values and associate it with the parameter:
1. In the Data Model pane, click List of Values. In the List of Values, pane click to create then new list of values.
Enter the following:
- Enter the Department as the Name of the List of Values.
- Select SQL Query as the Type.
- Ensure that the demo is selected as the Data Source.
- Click Query Builder to define the query to return department names for the list of values.
2. In Query Builder, add DEPARTMENTS table and select DEPARTMENT_ NAME. Click On Save. The list of values which pane displays the SQL query. Click Save to save your data model.
3. To associate P_DEPT parameter with this list of values, under the Parameters node of Data Model pane, click P_DEPT.
4. In the Parameters pane, in the Default Value field, enter * (asterisk), and then select Menu as the Parameter Type.
5. Set the Menu properties as follows:
- In the Display Label field, enter Department.
- Ensure that Department is selected as the List of Values.
- Select the Multiple Selection checkbox.
- Select the Can select all checkbox.
- Select the All Values Passed option.
6. Click Save.
5 Viewing and Saving Sample Data
To use the BI Publisher’s report layout tools, your data model must includes sample data. To run data model and save sample data:
- On data model editor toolbar, click View Data.
- On Data page, the Department parameter is set to default value, all. Select the number of rows to return (50) and click View. The data displays in the viewer pane by default in Tree View.
- Click on Save as Sample Data to save this sample to the data model.
- Click on OK to return to the data model.
- Click Save. You are now ready to create a report using your data model.
Creating Your First Report and Layout
To create a report:
- On the global header, click the new menu and then select Report.
- In the Report Wizard, the first step is to select the data. Select the Use Data Model, and then to the right of the Data Model field, click the browse button
- In the Select Data Model dialog, select Employee DM, and then click Open to return to the Create Report Wizard.
- Click Next.
- Select the Chart and Table option, and then click next.
The templates provided here contain report components already inserted in a predefined layout. Predefined templates make building reports easier and faster and the report output more consistent.
- Click the Department element and drag to the Drop Series Here position on the chart. Click the Salary element and drag it to Drop Value Here position on the chart. The chart populates with data.
- Click Next.
- The table layout automatically includes columns you selected for the chart. To complete the table, drag any additional columns from data source, and then click Next.
- Select Customize Report Layout option to navigate to layout editor, and then click Finish.
- Enter a name and optional description for the report, and then click Save.
The report is displayed in layout editor with title automatically centered on the page. By default, the measured field (Salary) is summed. To change the function to average, first, click in chart area.
- Click the Salary field to select it.
- In the Chart Measure Field tab, from the Formula menu, select Average.
- Double click on Title, change it to “Average Salary by Department”.
In the table, the date displays in the raw format from the data. To format the date for the report:
a. Click inside Hire Date column to select it. When column is selected, then Column tab displays.
b. On the Column tab, in the Data Formatting group, select the MM/DD/YY format.
- Next, format the Salary data field as follows:
a. Click inside the Salary column to select.
b. On the Column tab, in the Data Formatting group, select -$1,234.56.
- Click to save the layout.
- Click Return to display the report in the report viewer. Note that your report is interactive. The chart values displays as you mouse over them and table is enabled for filtering and sorting. Note also that the table automatically filters to display the selections made on the chart. When you click on the series item (such as Marketing) the table automatically filters to display only Marketing employees.
- Finally, from the View Report menu, select PDF to launch the PDF viewer inside the BI Publisher report viewer. BI Publisher displays same report components, in pixel-perfect positioning, now in the printable report format.
Connecting to a Data Source
To fully experience BI Publisher Trial Edition you will need to connect BI Publisher to your own data sources. BI Publisher can leverage the data from many types of sources including relational databases, multidimensional databases, Microsoft Excel spreadsheets, Web services, and XML files. Before you can create a data model from your own database, you need to configure a connection.
In this topic, you will learn how to configure the JDBC connection to Oracle Database. To configure the JDBC connection:
1. Click Administration.
2. On Administration page, under Data Sources, click on JDBC Connection.
3. On the Data Sources page, on the JDBC tab, click Add Data Source.
4. On the Add Data Source page, enter the details for your database as follows:
- Data Source Name – Assign one name to this data source. The name you specify appears in list of data sources shown in data model editor.
- Driver Type – Select one driver type to suit your database (for example, Oracle 11g).
- Database Driver Class – This field automatically populate according to your choice of the Driver Type. For example, oracle.jdbc.driver.OracleDriver.
- Connection String – Provide database connection details in the form jdbc:oracle:thin:@[host]:[port]:[sid, substituting all the details for your database for [host], [port] and [sid].
- Username and Password – Enter the username and password for the database.
5. Click Test Connection. When you see confirmation message, click on Apply. This data source is now available for building data models.
Connecting to an Email Server
To deliver reports, connect BI Publisher to your email server. Once configured you can use the BI Publisher scheduled jobs to send reports to your colleagues. To connect to an email server:
1. Click Administration.
2. On the Administration page, under Delivery, click on Email.
3. This displays the Delivery page with the Email tab selected. Click on Add Server.
4. On the Add Server page, enter the details for your email server as follows:
- Enter a unique Server Name.
- Enter the email server Host (for example mail.example.com) and Port.
5. Click Apply. Email is now available as a Destination Type for BI Publisher scheduled jobs.
