Adding Calculated Controls to a Report

Calculated controls are useful in reports. You use calculated controls to determine extended values, such as quantity times unit price or quantity times cost. Now you have enough space at the right of the report to add two columns: one for the UnitPrice field and one for the extended inventory value, which is UnitPrice multiplied by UnitsInStock. The following subsections explain how to provide the data for and add these controls.

Changing the Report's Record Source

You created the Inventory by Category report by selecting fields directly from the Products and Suppliers tables in the Report Wizard. Therefore, the Record Source property for the report as a whole is an SQL statement that selects only the fields that you chose initially in the Report Wizard. Although you can add fields to the report by creating unbound text box controls and using the Expression Builder to create an expression to retrieve the desired value, it's a more straightforward process to create a query to select the desired fields and then substitute the new query as the report's data source. You also can specify record-selection criteria in a query.

Jet SQL

Following is the Jet SQL statement generated by the Report Wizard:

     SELECT Products.CategoryID, Products.ProductID,       Products.ProductName, Products.SupplierID,       Suppliers.CompanyName, Products.UnitsInStock     FROM Suppliers INNER JOIN Products       ON Suppliers.SupplierID=Products.SupplierID; 

Northwind.mdb's Products table includes some products that have been discontinued. Inventory reports shouldn't include counts and valuations of products that no longer are available for sale.

Tip

Alternatively, you can use the Filter and Filter On property values on the Data page of the report's Properties window to prevent discontinued products from inclusion in the report. Another approach would be to add a WHERE NOT Discontinued clause to the Record Source SQL statement. As a rule, however, it's easier to troubleshoot report problems if you use a query as the Record Source property of the report. The query lets you quickly preview the result set on which your report is based.

To create a query that eliminates discontinued products from the result set, follow these steps:

1. Open a new query in Design view by clicking the Queries shortcut in the Database window and then double-clicking the Create Query in Design View shortcut.

2. Double-click the Products table in the Show Table dialog and then close the dialog.

3. Drag * from the field list to the first column of the query.

4. Drag the Discontinued field to the query grid's second column.

5. Clear the Show check box for the Discontinued field and then type False in the Discontinued field's first Criteria row.

6. If you want to list products alphabetically by ProductName, add the ProductName field and select an ascending sort (see Figure 16.19). Alternatively, you can specify ProductName in the Order By list and set the Order By On property value to Yes.

   Figure 16.19. This query prevents the Inventory by Category report from including discontinued products.

   

7. Run the query to test your work, close the Query window, and save your changes using the name qryInventory.

Tip

You don't need to add the Suppliers table to the query because the SupplierID field of the Products table supplies the CompanyName lookup value to the table.

To change the report's Record Source property value to the new query, follow these steps:

1. Open the Inventory by Category report in Report Design view, and press Ctrl+R or click the Select Report button to select the report.

2. Click the toolbar's Properties button to open the report's Properties window. Then click the Data tab to display the report's data properties.

3. Click the Record Source text box and then use the drop-down list to select the qryInventory query as the report's new Record Source property (see Figure 16.20).

   Figure 16.20. Replace the Wizard-generated SQL statement with the new query to serve as the Data Source property value for the report.

   

4. Check the report in Print Preview mode, and then save the changes to the report.

Adding the Calculated Controls

Now that you've changed the report's record source, you have easy access to the UnitPrice field that you need for adding the calculated Cost and Value fields to the report. UnitPrice is the selling price of the product, not its cost to Northwind Traders. For this example, assume that Northwind Traders sells its goods at a uniform markup of 50%. In retailing terminology, this means that a product costing $1.00 sells for $1.50, and the inventory value is 66.7% of the UnitPrice value. Thus the text box expression for the cost of the product is =[UnitPrice]*0.667 and the value is =[UnitsInStock]*[UnitPrice]*0.667.

To add the Cost and Value calculated fields to the report, follow these steps:

1. Return to Design view, and click the Toolbox button on the toolbar to display the Access toolbox if it isn't already displayed.

2. Click the Label tool in the toolbox and place the label to the right of the Units label in the Page Header section. Type Cost as the caption.

3. Add another label to the right of Cost and type Value as the caption.

4. If necessary, change the font and size to match the other labels in the Page Header. (Access automatically sets the font name, but not the size, bold, or other font attributes.)

5. Click the Text Box tool, and add two unbound text boxes in the Detail section under the new labels. Delete the attached labels, and align the right edge of the text boxes under the right edge of the Page Header labels.

   Tip

   A faster method of adding text boxes and labels is to select both the label and the text box, and then press Ctrl+C and Ctrl+V to superimpose a copy that has an associated label over the existing controls. Drag the copy to its new location.

6. Select the new Cost text box, open the Properties window, click the Data tab, and type =[UnitPrice]*0.667 in the Control Source text box (see Figure 16.21).

   Figure 16.21. Type as the Data Source property value the expression for the value to print in the calculated field text box.

   

7. Select the Value text box, click the Data tab, and type =[UnitsInStock]*[UnitPrice]*0.667 as the expression. Change to Print Preview to check your work.

   Tip

   

   A good way to enter long, complex expressions is to click the Builder button to open the Expression Builder, which provides a larger text box in which to type the expression.

8. Return to Design view, drag the Percent label and text box to the left, and change the label caption to % Cat. Units.

9. Repeat steps 5 and 6 to add a calculated text box in the CategoryID Footer section under the Value label, but type =Sum([UnitsInStock]*[UnitPrice]*0.667) as the subtotal expression and don't delete the label. Click the Bold button on the toolbar to set the Font Weight property to Bold. In the Properties window, click the Other tab and then set this text box's Name property as txtCatValue. Type Value: as the name of the label and change the font to 8-point Arial bold.

10. Repeat step 9 to create the grand total value text box with the =Sum([UnitsInStock]*[UnitPrice]*0.667) expression in the Report Footer section. In the Other page of the Properties window, set this text box's Name property as txtTotalValue. Change the Font Size to 8 points, set the Font Weight to Bold, and delete the associated label. Also apply the Bold attribute to the Grand Total label.

11. Add another unbound text box to the right of the % Cat. Units text box in the CategoryID Footer section. Type =[txtCatValue]/[txtTotalValue] as the value of the Control Source property and set the Format property's value to Percent and the Font Weight to Bold. Change the label caption to Value and conform the font. The report design at this point appears as shown in Figure 16.22. Press Ctrl+S to save your report.

    Figure 16.22. The enhanced report design with added Cost, Value, Cat(egory) Value, and Grand Total Value calculated fields.

    

    

    If a Parameters dialog appears when you test your report in Preview mode, see the "Unexpected Parameters Dialogs" topic of the "Troubleshooting" section near the end of the chapter.

    
    

12. Click Print Preview to check the result of your additions. Use the vertical scroll bar, if necessary, to display the category subtotal. The next section describes how you can correct any values that are not aligned properly and the spacing of the Detail section's rows.

13. Click the Bottom of Report page selector button to display the grand totals for the report (see Figure 16.23). The record selector buttons become page selector buttons when you display reports in Print Preview mode.

    Figure 16.23. The last page of the report displays grand totals for units and inventory values.