Rep 0110 Unable To Open File Reports

Have access to or have installed Oracle Reports Developer.

Have access to or installed the sample schema. This tutorial uses the HR schema included in the Oracle10g database.

If it is not already open, start Oracle Reports by clicking the Reports Builder icon on your desktop. The welcome screen displays. Select the option Build a new report manually. Click OK.

If Reports Builder is already open, create a new report by selecting File > New > Report from the menu.

Select the option Build a new report manually. Click OK.

In both cases, the Data Model view of the Report Editor displays.

The Data Model view is a work area in which you create, define, and modify data model objects to be used in your report. Data model objects include queries, groups, columns, parameters, and links. Reports Builder uses the data model to determine what data to retrieve for the report.

Add a query to the data model by selecting Insert > Query from the menu. This action invokes the Data Wizard.

Click Next.

Reports Builder assigns a default name to each query you build with the Data Wizard. Give your query a meaningful name by entering Departments_Query in the Query name field.

Be sure that the Matrix query check box is cleared. Click Next.

For the data source, accept SQL Query as the data source type by clicking Next.

On the data source definition page, click Connect to connect to the database.

In the Connect dialog box, enter HR for the User Name, HR for the Password, and the name of your database for the Database field.

Note: If you were previously working in this Reports Builder session using a different schema, you will have to connect to the HR schema in order to complete this tutorial.

The Data Source definition dialog box displays. You can now type in a SQL statement, define a query visually using Query Builder, or import an existing file.

For this tutorial, you will use Query Builder to define your query. Query Builder is an easy-to-use data access tool that provides a logical and intuitive means to access information from your organization's databases for analysis and reporting. You can use Query Builder to define almost any query that you would build using a SQL SELECT statement. Query Builder automatically generates the appropriate SELECT FROM [table.column] clause based on columns displayed in the Query Builder workspace.

Click the Query Builder button to define your query. The Query dialog box displays, as does a window displaying the data tables, views, snapshots, and synonyms to which you have access.

Select the EMPLOYEES table from the list of tables. Click Include. Click Close. A graphical representation of the Employees table displays in the right panel of the Query dialog box. This panel is the Data Source panel.

The Departments_Query will contain several tables. Click the Select Related Tables icon on the Query Builder icon bar to display tables related to the Employees table.

Select the HR.DEPARTMENTS table.

Click Include and then click Close. A graphical representation of the Departments table is added to the Data Source panel, and the primary key/foreign key relationships between the two tables are shown.

Now display the tables in the database that are related to the DEPARTMENTS table. With the focus on the DEPARTMENTS table, click the Select Related Tables icon .

Select the HR.LOCATIONS table.

Click Include and then click Close. A graphical representation of the LOCATIONS table is added to the Data Source panel and the primary key/foreign key relationship between the DEPARTMENTS and LOCATIONS tables is shown.

Include one more table in this query. With the focus on the LOCATIONS table, click the Select Related Tables icon .

Select the HR.COUNTRIES table.

Click Include and then click Close. A graphical representation of the COUNTRIES table is added to the Data Source panel and the primary key/foreign key relationship between the LOCATIONS and COUNTRIES tables is shown.

Now that you have selected the tables for the query, choose and order the columns that will appear in the report.

Select the Column Sequence icon from the icon bar in Query Builder.

The Column Sequence dialog box displays all the available columns in the left panel, and the displayed columns in the right panel. Notice that the columns are prefixed with their respective table names.

Select DEPARTMENTS.DEPARTMENT_NAME from the Available Columns list and click the >>Copy>> button to move the column to the Displayed Columns list. As an alternative, you can double-click the column name to move the column to the Displayed Columns list.

Repeat this process for EMPLOYEES.FIRST_NAME, EMPLOYEES>LAST_NAME, EMPLOYEES.SALARY, LOCATIONS.CITY, COUNTRIES.COUNTRY_NAME, and EMPLOYEES.HIRE_DATE.

Click OK.

Next, you want to ensure that the data returned from the query is sorted by the column DEPARTMENT_NAME.

Select the Sort icon in the icon bar in Query Builder. The Sort dialog box displays.

Select DEPARTMENTS.DEPARTMENT_NAME from the Available Columns list and click the >>Copy>> button to move the column to the Sorted Columns list. As an alternative, you can double-click on the column name to move the column to the Sorted Columns list.

Now highlight DEPARTMENT_NAME in the Sorted Columns list and ensure that the Ascending option, the default value for Sorting Order, is selected. Notice the arrow to the left of DEPARTMENT_NAME. An upwards arrow indicates Ascending sort order, while a downwards arrow indicates Descending sort order.

Click OK.

For this report, you want to display data for those employees hired after January 1, 1990. To restrict the data retrieved from the database, you can use Query Builder to add a condition to the query.

Thus far in the tutorial, you have worked in the Data Source panel on the right side of the Query dialog box. Now you will work in the Conditions panel on the left.

In the Query window, click the blank conditions field in the Conditions panel.

Next, click HIRE_DATE in the EMPLOYEES table. This action causes the column EMPLOYEES.HIRE_DATE to be entered into the condition field.

Complete the expression by entering the criteria: EMPLOYEES.HIRE_DATE > '01-JAN-90'. Be sure to include the single quotation marks.

Finally, click the Accept icon when you are satisfied with the condition expression.

You've completed the definition of the query for this report. Click OK. The resulting SQL statement is displayed for you.

Click Next.

For this report, you will group department and employee information by country.

Select COUNTRY_NAME in the Available Fields list and click [>] to move it to the Groups Fields list.

Click Next

You do not have to calculate any totals for this report. Click Next.

You've completed the definition of your data model for this report. Click Finish. A graphical representation of your data model displays in the Data Model view of the Report Editor. Notice the hierarchical structure of the data model: the master group is G_COUNTRY_NAME and the detail group, containing department and employee information, is G_DEPARTMENT_NAME.

Save your report as DepartmentsReport_< your_initials >.jsp .

To save a report definition, click the Save icon in the toolbar, or select File > Save from the menu. The first time you save your report definition, the Save dialog box displays, giving you the opportunity to enter a meaningful name for your report.

Click Save.

Select the Paper Layout icon on the Report Editor toolbar to invoke the Paper Layout view.

Select Insert > Report Block from the menu. The Report Block Wizard displays.

Note: The Report Block Wizard is very similar to the Report Wizard. You can specify a title and report style, select the group or groups as desired, select the fields you want to display in the report, modify the labels, and choose a template. However, the Report Block Wizard enables you to retain the existing paper and Web layouts defined for the particular data model, while, at the same time, enhance both. If you invoke the Report Wizard from the Paper Layout view, however, you will overwrite all paper and Web layout specifications previously defined for the report. Another feature of the Report Block Wizard is that it enables you to include multiple data blocks in a single report.

Enter a report title of Departments Report and choose Group Above as the report style.

Click Next.

You now select the group(s) you want to display in your report and the direction in which the records should repeat. Your data model contains two groups, one containing country information and the other containing department and employee information.

Select G_COUNTRY_NAME from the Available Groups list and click the Down > button. This moves the master group to the Displayed Groups list.

Repeat the same action for the detail group, G_DEPARTMENT_NAME.

Click Next.

You now need to decide on the fields you want to display in the report. From the Available Fields list, select COUNTRY_NAME and click the [>] button. Repeat this process for CITY, FIRST_NAME, LAST_NAME, SALARY, HIRE_DATE, and DEPARTMENT_NAME fields.

Ensure that the displayed fields are in the order shown below. You can drag and drop a field to modify the order.

Click Next.

On the next panel of the wizard, you can modify the default labels and widths. For this tutorial, keep the defaults as they are.

Click Next.

As the final step in the Report Block Wizard, you can include a template in the report definition. Templates allow you to create a standard look and feel, and also promote reusability. In this tutorial, you will use one of the predefined templates for your report.

Select Beige from the Predefined template list.

Click Finish. You've successfully created the paper layout for your data model.

Save your report definition by clicking Save in the toolbar.

To preview the paper layout, click the Run Paper Layout icon on the toolbar, or select Program > Run Paper Layout from the menu.

The paper layout for your Departments Report displays in the Paper Design view.

Select the report in the Object Navigator.

Select File > Generate to File > PDF from the menu. Save your report output as DepartmentsReport_<your initials>.pdf, such as DepartmentsReport_sk.pdf.