28.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:

Launch Reports Builder (or, if already open, choose File > New > Report).
In the Welcome or New Report dialog box, select Use the Report Wizard, then click OK.
If the Welcome page displays, click Next.
On the Report Type page, select Create both Web and Paper Layout, then click Next.
On the Style page, type a Title for your report, select Group Left, then click Next.
On the Data Source page, click SQL Query, then click Next.
On the Data page, enter the following SELECT statement in the Data Source definition field:
```
SELECT O.CUSTID, O.SHIPDATE, O.TOTAL,
AVG(A.TOTAL) MAVG
FROM ORD O, ORD A
WHERE A.CUSTID = O.CUSTID
AND A.SHIPDATE BETWEEN O.SHIPDATE -123 AND O.SHIPDATE
GROUP BY O.CUSTID, O.SHIPDATE, O.TOTAL
```
Note:
You can enter this query in any of the following ways:
- Copy and paste the code from the provided text file called timeseries_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.
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 28.1, "Prerequisites for This Example" describes the sample schema requirements for this example.
On the Groups page, click CUSTID and click the right arrow (>) to move this field to the Group Fields list, then click Next.
On the Fields page, click the double right arrows (>>) to move all of the fields to the Displayed Fields list, then click Next.
On the Totals page, click Next.
On the Labels page, change the label for MAVG to 4-Month Moving Average, then click Next
On the Template page, select Predefined Template and click Beige, then click Finish to display your report output in the Paper Design view.
In the Paper Design view, double-click field F_MAVG to display the Property Inspector, and set the following properties:
- Under Field, set the Format Mask property to LNNNGNN0D00.
Do the same for field F_TOTAL.
Select the first number value underneath the Total label. Notice that all of the values are immediately selected.Shift-click the first number value underneath the 4-Month Moving Average label.Click the Align Right button. All of the values are immediately right aligned.
The report in the Paper Design view should now look something like this:

Figure 28-2 Paper Design view of the Time Series Calculations report

Description of "Figure 28-2 Paper Design view of the Time Series Calculations report"
To view the data model you just created using the Report Wizard, click the Data Model button in the toolbar. The same data model can be used for both your paper and your JSP-based Web reports.

Your data model should look something like this:

Figure 28-3 Data Model view of the Time series Calculations report

Description of "Figure 28-3 Data Model view of the Time series Calculations report"
Save your report as timeseries_your_initials.rdf.