Oracle report creation, modification and functions

Oracle Reports have some strong features that allow you to easily browse data, but you need to do some additional work on the form. In particular, reports are good at displaying subtotal-or even at multiple layers. Even if the organization chooses to browse data online, the report is still a useful tool for creating data details and aggregate data.

1. Create and modify an Oracle report

1. Introduction to the report developer

The original purpose of a report is to create a print list of data and provide information to the decision maker. Currently, decision makers prefer to process data directly on a website without printing paper. In many cases, you can use forms to provide an interactive environment for decision makers to retrieve data. Oracle Developer Suite includes a report Developer tool with a wizard to help you create basic reports.

2. Oracle Report Type

Before creating a report, you must understand the main layout of the report. The main types of reports include tabular report in table format, simple list, interrupt control, or group report), matrix report, or cross table and mail label mailing label ). Figure 10-1 shows an example of a table report. A table-based report is a simple list of data. If a table contains numerical data, there is usually a subtotal at the bottom. This type of report is easy to create and read. However, such reports are not particularly useful for decent online access. A few years ago, managers needed to print a huge data list for reference at hand. Now it's as simple as publishing a query to get the required data or a brief list of total data. However, you still need to be able to make these reports. Remember, they should always be arranged.

3. Create and modify an Oracle report
Oracle provides a Report Wizard to help you create most common reports. In general, it does a good thing; but you still have to modify the report and organize it multiple times until they are more professional. One of the first challenges is to avoid placing everything on one line. If you try to enter too much content into a page, the Report Wizard will be troublesome. First, try to reduce the font and compress more data into the report. But the report is hardly working. You must return it to manually modify it. It is best to stop and allow the Wizard to create a report with as few items as possible. If you want to add more items later, you can add them one by one and check whether the addition is appropriate.

Ii. Enhanced Oracle report functions

Previously, we have made some changes to the basic reports created by the wizard. However, some reports require more work than this. In particular, it is difficult to create templates for Matrix reports. Therefore, more functions are usually needed. The main problem is that the standard template is designed for reports with vertical portrait layout and only displays a limited number of columns. A matrix report has multiple columns and can be extended to multiple pages. To illustrate this process, you will use the Wizard to create a matrix report that sums sales by month and by agent.

To make the query simpler, the report calculates the total price-it is not the final sales price. If you want the final sales price, you can write a query to extract the final BidPrice from the CustAgentList table and add the query to the report by concatenating the ListID. However, this chapter focuses on the report writer, which is a little complicated and distracting. Therefore, use AskingPrice to simplify this problem so that you only start the Wizard:

(1) Start Report Builder and use the Wizard to create a matrix Report entitled Sales by Month and Agent. Select only simple Matrix reports. You do not need to add a group to the report. A matrix group creates different matrices for each group. Only one matrix is required for this Oracle report.

(2) create a query from the Agents, Listings, and SaleStatus tables. Set the condition to the list of Sold properties, that is, SaleStatus. SaleStatus = 'sold '). Sort by proxy's surname and name. Select and extract with caution: LastName, FirstName, and AskingPrice.

Now go to the tricky part. The manager wants the report to display the total by month, and the month is displayed at the top of the report, and the proxy is displayed at the bottom left corner. The Listing table contains two dates. For simplicity, you can use EndListDate. If you take the time to create a query to get the actual sales price, you will also get the actual sales date, but here is not the key ). The problem is that the date is a real day, and all you need is a month. The answer is to create a new column and use the TO_CHAR (date, 'mm') function to convert the date to the corresponding month.

(3) There is a Define Column button when the query is created. You can use it to add a new Column. However, it cannot actually save the description of the column. Therefore, it is easier to simply edit the SQL statement and add the column with the SaleMonth alias.

(4) to define a matrix, you must tell the wizard the fields displayed in the row, column, and matrix unit. First, select the row field. Select the LastName and FirstName fields as shown in Figure 10-13 of this example. Because the report does not use a group, both fields belong to Level 1.

(5) Similarly, select the new SaleMonth field as the column. On the next wizard screen, select the AskingPrice column and click the Sum button to tell Reports Builder to calculate the total monthly price for each sales agent. Similarly, on the aggregate screen, select SumAskingPrice and calculate its Sum to obtain the total of rows and columns.

(6) Add a space and set it to uppercase letters on the tag screen to organize tags. However, you need to narrow the column so that you can set the Month label instead of Sale Month. The default width should be narrow at the moment. On the template screen, you must select the No template option.

The initial Paper Design view of the matrix report. Although it contains all the desired data, it is a little simpler and hard to read. Note: This report is sparse because there are only eight sales activities in this period. If you want to see Oracle Reports with more data, you can change the query condition and change Sold to For Sale. However, we still use this small report.

To make a report look similar to a group report, you should add a logo in the upper left corner. As before, use the Insert | Image menu option and adjust the logo size.

1. Function Description of exporting a database from an Oracle database
2. Common types of Oracle Database locks
3. Implementation of Oracle Database File Movement
4. Four highly practical startup methods for Oracle databases
5. Introduction to solutions to ensure performance of Oracle database tables and Indexes