OVERVIEW
In this tutorial we are going to build a report that answers some managerial questions by summariz-ing data from a database. The database contains sales information for an equipment wholesaler. The questions are:
‧ How many orders has each customer placed?
‧ How much product was moved with each order?
‧ What were the total sales per order and per cus-tomer?

We will answer these questions by selecting data at the most detailed level, and then using the report to summarize the data. The report will contain the following items:
‧ The customer number and name
‧ The orders for each customer
‧ The quantity and total amount for each order

ESTABLISH THE BASIC LAYOUT

Task 1 Create the Dataview
1 Create a new report.
2 Access the data workspace.
3 Select File | New.
4 Double-click on the Query Designer icon.
5 Select the Customer, Orders, Items, and Parts tables.
6 Click the Fields tab.
7 Click the All Fields check box.

Task 3 Calculate the Total for Each Line Item
1 Click the Calcs tab.
2 Maximize the Query Designer.
3 Double-click on the Company field.
4 Select Expression from the drop-down list box
in the Function column.
5 Enter the following calculation into the Expression edit box: Listprice * Qty
6 Change the name of the Field Alias to Total.
7 Click on the Sort tab.
8 Select Custno, then Orderno.
9 Click OK. It may take a second or two before you see the dataview.

Task 4 Generate the Report via the Report Wizard
1 Access the design workspace.
2 Hide the Data Tree if it is visible.
3 Select File | New.
4 Double-click the Report Wizard icon.
5 Select the following fields in order:

Custno
Company
Orderno
Itemno
Description
Listprice
Qty
Total

6 Click Next until you reach the screen with the checkered racing flag.
7 Select 'Modify the report's design'.
8 Click Finish. The layout should look like this:

9 Press Ctrl + S and save the report under the name Sales Statistics.
10 Preview the report. This report contains data in its raw form. The data doesn't really answer the proposed questions, but it does give us something we can transform into answers. Notice the data in the Total column. We can use this total to compute the total per order and per company.

MODIFY THE BASIC REPORT LAYOUT

Task 1 Calculate Totals for Each Company
1 Return to the design workspace.
2 Select Report | Groups.
3 Click Add.
4 Assign the group to the CustNo field by expanding the drop-down list box and selecting 'CustNo'.
5 Click OK. A group header and group footer band will appear on the canvas. Notice the descriptions on these bands. The number shows
which group the band belongs to and the word 'Custno' shows the field to which the group is assigned.
6 Extend the group footer band to the second tick on the vertical ruler:

7 Select a DBText component in the band. This establishes the font size and style for the DBCalc we are about to create.
8 Place a DBCalc component in the group footer band.
9 Assign it to the 'Total' field.
10 Right-align the DBCalc component with the Total DBText component in the detail band.
11 Set the top of the DBCalc component to 0.09.

Task 2 Calculate the Total Quantity Per Company
1 Place another DBCalc component in the group footer band.
2 Assign it to the 'Qty' field.
3 Right-align the DBCalc with the Qty DBText component in the detail band.
4 Align the top of the Qty DBCalc with the Total DBCalc.
5 Right justify the text of the DBCalcs.
6 Place a shape in the group footer band and set it to ParentWidth and ParentHeight.
7 Send the shape to the back.
8 Set the shape's color to yellow. The layout should look like this:

9 Preview. The yellow shapes show where the group footer is printing. The group footer band contains the grand total for both the quantity of
items sold and the sales revenue. Now let's calculate the total for each order.

Task 3 Calculate Totals for Each Order
1 Return to the design workspace.
2 Select Report | Groups
3 Click the Add button, then select Customer. Orderno from the drop-down list box.
4 Click OK. New group header and group footer bands are created for the Orderno group. The layout should look like this:

5 Extend the Orderno group footer to the second tick on the vertical ruler.
6 Copy and paste the DBCalc components in the Custno group footer band.
7 Drag the selection into the Orderno group footer band.
8 Set the top of the components to 0.09
9 Right-align the DBCalc components with the corresponding DBText components in the detail band.

Task 4 Create a Shape for the Orderno Group Footer Band
1 Place a shape in the band and set it to Paren-tHeight and ParentWidth.
2 Send the shape to the back.
3 Set the shape's color to aqua. The layout should look like this:

4 Preview. The aqua shapes show where the group footer is printing for each order. Notice that the customer number, company name, and order number in the detail band contain repeating values.

Task 5 Fix the Repeating Fields
1 Return to the design workspace.
2 Extend the Custno group header band to the second tick on the vertical ruler.
3 Select the Custno and Company DBText components in the detail band and move them into the Custno group header band.
4 Extend the Orderno group header to the second tick on the vertical ruler.
5 Move the Orderno DBText from the detail band into the group header band.
6 Preview the report. Now the customer and order information appears only once at the top of the corresponding group.

FIT AND FINISH

Task 1 Modify the Bands
The data in the detail band is not needed to answer the questions we posed at the beginning of the tutorial. In fact, it adds unnecessary 'noise' to the report and makes it difficult to see the answers calculated in the group footer bands. We can resolve this issue by hiding the detail band.

1 Return to the design workspace.
2 Right-click over the white space of the detail band.
3 Select Visible, which will set this option to False.
4 Preview. Now only the totals are shown. However, the Orderno appears on a separate blank line. It would be easier to read the report if we placed the Orderno with the total.
5 Return to the design workspace.
6 Move the Orderno DBText from the Orderno group header band to the Orderno group footer band.
7 Right-align the DBText with the Orderno label in the header band.
8 Align the top of the DBText with the Qty

DBCalc.

9 Set the height of the Orderno group header band to 0.
10 Delete the shapes from the Orderno and Custno group footer bands. The layout should look like this:

Note: The shapes were used to help us quickly identify where the group footer bands were print-ing. Now that we have hidden the detail band, they are no longer necessary.

Task 2 Set the Format of the DBCalcs
1 Right-click over the Total DBCalc in the Custno group footer band and select Display Format.
2 Select the first menu option with a dollar sign.
3 Right-click over the Total DBCalc in the Orderno group footer band and select Display For-mat.
4 Select the first menu option with a dollar sign.

Task 3 Complete the Custno Group Footer Band
1 Select the DBCalc components in the Custno group footer band and set the font to bold.
2 Place a shape in the Custno group footer band.
3 Set the following positions for the shape:

Left 0.05
Top 0. 06
Width 7.95
Height 0.18
4 Send the shape to the back.
5 Set the color of the shape to light gray. The layout should look like this:

Task 4 Put the Finishing Touches on the Report
1 Select the Custno and Company DBTexts in the Custno group header band and the Orderno DBText in the Orderno group footer band.
2 Set the font color to navy.
3 Set the font to bold.
4 Set the Visible speed menu option to False for the following labels:

Itemno
Description
Listprice

5 Change the text for these labels accordingly:

New Report Sales Statistics
Custno Customer No.
Orderno Order No.

6 Autosize these three labels. The final layout should look like this:

7 Preview. The final report should look this:

8 Close the Report Designer, saving the changes.