30.2 Create a Query and the Layout

The steps in this section will show you how to build a simple data model and report layout in the Report Wizard, which you can then use to generate either a JSP-based Web report or a paper report. In the next section, you will modify the JSP so that the appropriate information displays in your Web report.

When you are creating a single-query report, such as this one, you can use the Report Wizard to create the data model and layout simultaneously.

To create a data model and layout:

  1. Launch Reports Builder (or, if already open, choose File > New > Report).

  2. In the Welcome or New Report dialog box, select Use the Report Wizard, then click OK.

  3. If the Welcome page displays, click Next.

  4. On the Report Type page, select Create both Web and Paper Layout, then click Next.

  5. On the Style page, type a Title for your report, select Group Left, then click Next.

  6. On the Data Source page, click SQL Query, then click Next.

  7. On the Data page, enter the following SELECT statement in the Data Source definition field:

    SELECT (FLOOR(SAL/1000))*1000 BOTTOM,
    CEIL((SAL+1)/1000) * 1000 TOP,
    ENAME,
    DEPTNO
    FROM EMP
    ORDER BY 1,2, SAL
    

    Note:

    You can enter this query in any of the following ways:
    • Copy and paste the code from the provided text file called aggregatingdata_code.txt into the Data Source definition field.

    • Click Query Builder to build the query without entering any code manually.

    • Type the code in the Data Source definition field.

  8. Click Next.

    Note:

    If you are not already connected to a database, you will be prompted to connect to the database when you click Query Builder or Next. Ensure that you connect to a database that has the appropriate schema for this example. Section 30.1, "Prerequisites for This Example" describes the sample schema requirements for this example.
  9. On the Groups page, click the following fields in the Available Fields list and click the right arrow (>) to move them to the Group Fields list, then click Next:

    • BOTTOM

    • TOP

  10. On the Fields page, click the double right arrows (>>) to move all of the fields to the Displayed Fields list, then click Next.

  11. On the Totals page, click Next.

  12. On the Labels page, click Next.

  13. On the Template page, select Predefined Template and click Beige, then click Finish to display your report output in the Paper Design view. It should look something like this:

    Figure 30-2 Paper Design view for the aggregating data report

    Description of Figure 30-2 follows
    Description of "Figure 30-2 Paper Design view for the aggregating data report"

  14. In the Paper Design view, click the text Bottom and change the text to Salary Range.

  15. Delete the text Top.

    Note:

    You can also modify the other column headers to make the text more meaningful.
  16. Click the Paper Layout button in the toolbar to display the Paper Layout view. The layout currently looks like this:

    Figure 30-3 Paper Layout view of the Aggregating Data report

    Description of Figure 30-3 follows
    Description of "Figure 30-3 Paper Layout view of the Aggregating Data report"

  17. In the Paper Layout view, click the Flex Off button in the toolbar to set Flex mode off.

  18. Click the right edge of the f_bottom frame and drag it to the left about 0.5 inches (1.5 centimeters) to make room between the f_bottom and f_top fields.

    Tip:

    To find objects in the Paper Layout view, you can use the Object Navigator. When you click an item name in the Object Navigator, the corresponding object is selected in the Paper Layout view.
  19. Click the Text tool in the tool palette.

  20. Drag a boilerplate text object between f_bottom and f_top, then type "-" in the text box. The layout should now look like this:

    Figure 30-4 Paper Layout view with new boilerplate text

    Description of Figure 30-4 follows
    Description of "Figure 30-4 Paper Layout view with new boilerplate text"

  21. Now, click the Run Paper Layout button in the toolbar to display your report. It should look something like this.

    Figure 30-5 Paper Design view

    Description of Figure 30-5 follows
    Description of "Figure 30-5 Paper Design view "

    Note:

    Notice how the report displays the employee names per salary range, hence aggregating the data.
  22. Save your report as aggregatereport_your_initials.rdf.

  23. Take a look at the data model of your report. You can use this data model to generate either a paper report or a JSP-based Web report. To view the data model, click the Data Model button in the toolbar. Your data model should look something like this:

    Figure 30-6 Data Model for the aggregating data report

    Description of Figure 30-6 follows
    Description of "Figure 30-6 Data Model for the aggregating data report"