28
Building a Time Series Calculations Report

Figure 28-1 Time series calculations report output

Text description of the illustration timeseries_web.gif

In this example, you will build a report that calculates and displays the four-month average of purchases for each customer.

Concepts

• Reports with time series calculations calculate values over a specified period of time. The techniques described for this report can be used to produce other formats of time series calculations, as well.

Data Relationships

• This time series calculations report uses a query that will compute four-month moving averages of customer purchases. The SELECT statement will sum the current purchase (TOTAL) with purchases made by that customer in the previous four months, then average that sum through use of a self-join. For example, if the data queried is 30-JUL-00, Oracle Reports will average all purchases the customer made between 30 MAR-00 and 30-JUL-00.

Example Scenario

This chapter will show you how to use the Report Wizard to create a simple time series calculations report for both paper and the Web. For the JSP-based Web report, you will modify the Web source to change labels and add format masks.

To see a sample time series calculations report, open the examples folder named timeseries, then open the Oracle Reports example called timeseries.rdf. For details on how to open it, see "Accessing the example reports" in the Preface.

Table 28-1 Features demonstrated in this example

Feature	Location
Use the Report Wizard to create a data model and layout for both the paper and Web reports.	Section 28.2, "Create a query and the layout"
Modify the JSP to generate the JSP-based Web report.	Section 28.3, "Modify the Web source of your JSP report"

28.1 Prerequisites for this example

To build the example in this chapter, you must have access to the SUMMIT schema, which you can download from the Oracle Reports Documentation page on the Oracle Technology Network (http://otn.oracle.com/products/reports/).

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:

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

9. On the Groups page, click CUSTID and click the right arrow (>) to move this field to the Group Fields list, then click Next.

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, change the label for MAVG to 4-Month Moving Average, then 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.

14. In the Paper Design view, double-click field F_MAVG to display the Property Inspector, and set properties:
    • Under Field, set the Format Mask property to LNNNGNN0D00.

15. Do the same for field F_TOTAL.

16. 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

    

    Text description of the illustration timeseries_pdv.gif

    17. 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

    

    Text description of the illustration timeseries_dm.gif

    18. Save your report as timeseries_<your initials>.rdf.

    28.3 Modify the Web source of your JSP report

    Now that you've created your paper report layout, you can take the same report and generate a JSP-based Web report that looks the same as your paper report.

    To modify your JSP-based Web report:

    1. Save your report, timeseries_<your initials>.rdf as a JSP under the same name (timeseries_<your initials>.jsp).

    2. Click the Web Source button in the toolbar to display the Web Source view.

       In the Web Source, you need to change the format mask to match that of the paper report.

    3. In the Web Source view, find the text:

       <td <rw:headers id="HFtotal" src="HBtotal, HBcustid, HFcustid"/> 
       class="OraCellNumber"><rw:field id="F_total" src="total" nullValue="&nbsp;"> 
       F_total </rw:field></td>
       

    4. Add the tag formatMask="LNNNGNN0D00" to the line, so that the line looks like this:

       <td <rw:headers id="HFtotal" src="HBtotal, HBcustid, HFcustid"/> 
       class="OraCellNumber"><rw:field id="F_total" src="total" 
       formatMask="LNNNGNN0D00" nullValue="&nbsp;"> F_total </rw:field></td>
       

       Note: The bold text is the new format mask tag that we've added to the Total field.

    5. Change the format mask for the MAVG field. The resulting line in the Web Source should look like this:

       <td <rw:headers id="HFmavg" src="HBmavg, HBcustid, HFcustid"/> 
       class="OraCellNumber"><rw:field id="F_mavg" src="mavg" 
       formatMask="LNNNGNN0D00" nullValue="&nbsp;"> F_mavg </rw:field></td>
       

    6. Save your report as a JSP.

    7. Click the Run Web Layout button in the toolbar to display your new JSP-based Web report in your browser. The report should look something like this:

       Note: If Netscape 7.0 is your default browser, the browser may not display. You can work around this bug by making a copy of the Netscape 7.0 executable, naming it netscape.exe; with this name, the browser will display as expected.

    Figure 28-4 Time Series Calculations JSP-based Web Report

    

    Text description of the illustration timeseries_web.gif

    The report displays the total for each customer, as well as the average over the past four months.

    Note: For information on creating a Parameter Form for a JSP-based Web report, refer to the documented example in Getting Started with Oracle Reports, located on the Oracle Technology Network (http://otn.oracle.com/products/reports/).

    28.4 Summary

    Congratulations! You have successfully created a time series calculations report for both paper and Web. You now know how to:

    • create a time series calculations report definition.

    • modify your report for the Web.

    For more information on any of the wizards, views, or properties used in this example, refer to the Reports Builder Online Help, which you can access in two ways:

    • From the Oracle Technology Network (http://otn.oracle.com/products/reports/), click Documentation and navigate to the Reports Builder Online Help for the most recent, hosted online help.

    • From Reports Builder, choose Help > Help Contents.

      Note: You can replace your Reports Builder online help with the most recent update by downloading the latest online help set available on the Oracle Technology Network (http://otn.oracle.com/docs/products/reports/). Instructions for replacing your help file are included in the readme.txt in the download file.