Building Reports Using Developer 6.0 or 6i
Reports are easy to create using the Oracle Developer Report Builder. Figure 6.1 shows the first selection box, which is very similar to that of the Form Builder. Run the Quick Tour and Explore the Cue Cards are an excellent way to become familiar with the terminology and capabilities of Oracle Reports.
Figure 6.1. The Welcome to Report Builder window.
Selecting the Use the Report Wizard radio button opens the Report Builder Object Navigator (which is quite similar to the Form Builder Object Navigator), as shown in Figure 6.2.
Figure 6.2. The Report Builder Object Navigator window.
The Report Wizard supports eight different report styles, as illustrated in Figure 6.3. The Tabular report is the typical style with rows and columns, whereas Form-like can print a row of data per page. This style is useful in the Sales Tracking application to build a sales receipt or a sales document that includes the picture of the vehicle. Mailing Labels are useful for quickly assembling addresses from the database into the correct format (for example, thirty labels per page, three labels in a row, with ten rows per page). Form Letters, on the other hand, incorporate data from a table (such as name, amount, and so on) and embed this information around text in the form of a letter. A form letter is generated for each row returned from the associated SQL query. Group Left and Group Above are useful for situations in which rows will appear on the report. Finally, a Matrix report is a summation-type report in which two related types of data are totaled together in the form of a graph. A relationship in our Sales Tracking application would be sales by month by types of vehicles.
Figure 6.3. Report Builder Report Wizard Style tab.
Enter the title Inventory Status, ensuring that the Tabular radio button is selected, and click Next. Select the default SQL statement on the Type tab and click Next (see Figure 6.4).
Figure 6.4. Report Builder Report Wizard Type tab.
The Report Wizard Data tab prompts you for a connection to the database (see Figure 6.5). Figure 6.6 illustrates the SQL statement used for this report. Notice that you could have used the Query Builder (as discussed in the previous section) to build the query, or you could have accessed a previously built SQL statement stored on the computer's file system. Click the Next button.
Figure 6.5. Report Builder Report Wizard Data tab with a database connection.
Figure 6.6. Report Builder Report Wizard Data tab with a SQL statement.
Click the >> button to select all the fields from the Data tab for display in the report (see Figure 6.7). The Totals tab shows how to select fields for report totals (see Figure 6.8). Select the INV_PURCHASE_AMT and the INV_SALE_AMT fields for totals. Notice that the INV_SALE_AMT has a null value assignment. Also, remember that the INV_PURCHASE_AMT is a mandatory field, whereas the INV_SALE_AMT is not. Therefore, that field could contain a null, making it unsuitable for display. Calculations without the NVL clause set the column to 0 if null. Finally, click the Next button when you are finished selecting fields for totals.
Figure 6.7. Report Builder Report Wizard Fields tab.
Figure 6.8. Report Builder Report Wizard Totals tab.
The Labels tab, as shown in Figure 6.9, enables you to easily change the column labels as they will appear on the report. This is also accomplished from the report layout (similar to the canvas layout of the Form Builder). Make any desired changes in the labels displayed and then click Next. The Template tab displays some default report templates. The Report Builder Online Documentation covers how to build these templates. Select the Confidential Background for this report, as you see in Figure 6.10, and click Finish.
Figure 6.9. Report Builder Report Wizard Labels tab.
Figure 6.10. Report Builder Report Wizard Template tab.
Figure 6.11 shows what the newly developed ST_INVENTORY_STATUS report looks like.
Figure 6.11. The Report Builder Live Previewer.
Notice that the items in the Object Navagator appear in the same order in the Live Previewer. (see Figure 6.12).
Figure 6.12. Object Navigator with the Live Previewer.
