Saving the Inventory by Category Report to a Page

The Inventory by Category report (rptInventoryByCategory) that you created in Chapter 16, "Working with Simple Reports and Mailing Labels," is a candidate for conversion to a page. This report illustrates problems that you encounter with aggregate values in group footer sections the mysterious error message shown in Figure 25.24 appears during the transformation process. It's a common practice to use text boxes with aggregate functions in report group footers, so you're likely to see this message often. Loosely translated, the error message means that RPT2DAP.xsl doesn't translate =Sum([ FieldName]) expressions into the required GroupOfFieldName function for section aggregates.

Figure 25.24. This error message appears when the report that you save as a page has an aggregate expression in a group footer.

When you click OK to acknowledge the message, the page opens with #Name? as values of aggregate text boxes. Clicking the Expand button to display rows of the detail section opens an "Operation is not allowed when the object is closed" message (see Figure 25.25). The Category name is missing because pages don't display lookup field values. You can't put aggregates in the outermost footer, so there's no Grand Total value. To create a page that you can modify to comply with page design rules, you must delete the text box expressions and then save the modified report as a page.

Figure 25.25. After acknowledging the error shown in Figure 25.24, the page opens with errors and missing elements. Another error message appears when you click the Expand control.

Modifying the Source Query to Supply Required Field Values

For this example, substituting CategoryName and supplier CompanyName values for lookup fields requires redesigning the source query qryInventory, for this example. You must also supply fields to replace the calculated Cost and Value columns because the page section Value aggregates depend on field values. The once-simple qryInventory query requires a total makeover. Fortunately, the required changes to the query don't affect the design of the original rptInventoryByCategory report.

Jet SQL

The SQL statement for the modified qryInventory query is:

[View full width]
 SELECT Products.ProductID, Products.ProductName, Products. QuantityPerUnit,    Products.UnitPrice, CCur(0.667*[UnitPrice]) AS Cost, Products. UnitsInStock,    CCur([UnitsInStock]*[Cost]) AS [Value], Categories.CategoryID,    Categories.CategoryName, Suppliers.SupplierID, Suppliers. CompanyName FROM Suppliers INNER JOIN (Categories    INNER JOIN Products       ON Categories.CategoryID = Products.CategoryID)       ON Suppliers.SupplierID = Products.SupplierID WHERE (((Products.Discontinued)=False)); 

The modified qryInventory query is in the \Seua11\Chaptr25\Data25.mdb file.

Creating and Modifying the Source Report for the Page

To permit saving the sample report as a page and to simplify the page redesign process, do the following:

1. Save a copy of rptInventoryByCategory as rptInventory.

2. Open rptInventory in Report Design view. In the Detail Section, right-click the SupplierID drop-down list and choose Change To, Text Box. Replace SupplierID with CompanyName as the Control Source value.

3. Delete the expression in the Cost field, and type Cost as the field name. Do the same for the Value text box, but type Value.

4. In the CategoryID footer section, change the CategoryID list to a text box, and delete the CategoryID field name. Delete the expressions in the remaining four text boxes in the CategoryID footer. All text boxes display Unbound as their value.

5. Delete the two text boxes in the Page Footer section, and move the Report Footer section up. Your redesigned source report appears as shown in Figure 25.26.

   Figure 25.26. The changes shown here are required to eliminate errors when transforming the report to a page and to specify the columns to display from the modified source query.

   

6. Change to Print Preview to check your work. Close rptInventory and save your changes.

7. Right-click rptInventory in the Database window, choose Save As, and save the report as the Inventory page with Inventory.htm as the file name.

8. Click an Expand control to display the initial version of the Inventory.htm page (see Figure 25.27). Click OK to acknowledge "The DefaultSort property is invalid" message.

   Figure 25.27. Deleting the expressions in the source report's text box and changing the data source of the text boxes results in this initial page design.

   

Removing the Navigation Controls and Adding a Category Caption

The Inventory page has only eight categories and a few products per category, so you don't need navigation controls on the form. If you retain the Header: GroupLevel0 section and the Expand controls for the group, instead of expanding all groups by default, a CategoryName caption adjacent to the Expand control aids readability.

To make the initial design changes to the page, do this:

1. Change to Page Design view. Right-click Header: GroupLevel0, and clear the Record Navigation check box. Do the same for the Header: qryInventory section.

2. Double-click the Expand control, click the Other tab, and change the Src property value to Black Arrow.

3. Open the Toolbox, select the Bound Span control, and draw the control adjacent to the right of the Expand control. Delete the added label.

4. Double-click the control, click the Data tab, open the ControlSource list, and select CategoryName. The ControlSource property value changes to GroupOfCategoryName: CategoryName.

5. If you want to retain the Category label and CategoryID text box in the Footer: GroupLevel0 section, double-click the text box and set its ControlSource property to CategoryName. In this case, the property value becomes GroupOfCategoryName1: CategoryName.

6. If you change the ControlSource property value of the CategoryIDtext box or delete it, you must change the value of the DefaultSort property of the GroupLevel0 group properties. For this example, the original value is [GroupOfCategoryID98] ASC, but this field no longer exists in the Field List. Right-click the section, choose Group Level Properties, and set the DefaultSort property value to [GroupOfCategoryName] ASC.

   Tip

   Be sure to change the DefaultSort property when deleting or changing transformed text boxes on which group sorting depends. If you don't, you receive "The DefaultSort property is invalid" messages when changing to Page view. The DefaultSort property value must match a GroupOfFieldName item in the Field List.

7. The offending DefaultSort property value that raises the error on expanding a group is DESC with no field name for GroupLevel: Query Inventory. Change DESC to [Value] DESC to fix the problem.

8. If the font name for the new controls that you've added isn't the same as the existing controls, conform the fonts in other than the Caption: GroupLevel0 section. Tahoma is the font used for most of the examples in this book.

9. Change to Page view to check your work so far (see Figure 25.28).

   Figure 25.28. Removing navigation controls, adding a CategoryName caption, and making other minor changes improve the usability of the form.

   

Adding Category Subtotals and Grand Totals to the Page

Adding subtotals and grand totals to pages differs considerably from the method that you use for reports. The AutoSum feature makes replacing the missing group subtotals and grand totals easy.

To replace the report's missing subtotals and grand totals, do the following:

1. Select the UnitsInStock text box in the Header: qryInventory section, and click AutoSum to add a new label and SumOfUnitsInStock text box in the Footer: GroupLevel0 section. Delete the label and the original text box, and drag the SumOfUnitsInStock text box to the right of the Sum: label.

2. Repeat step 1 for the text box under the Value label. Move the SumOfValue text box to the right of the Cat. Value label.

3. Delete the % Cat. and Value label and text boxes in the Footer: GroupLevel0 section. Programming calculated text box values is beyond the scope of this chapter. Drag the Category label and text box to the right.

4. Select the SumOfUnitsInStock text box, and click AutoSum to add a Footer: qryInventory-SumOfUnitsInStock section and a SumOfSumOfUnitsInStock text box. Delete the associated label. Align the left and right edges of the Footer section with the sections above it.

5. Repeat step 3 for the SumOfValue text box, and then adjust the positions of the text boxes under the Units and Value text boxes.

6. Select the Grand Total: label, and press Ctrl+X to cut it to the Clipboard. Delete the unnamed footer section where the Grand Total: label was located.

7. Select the Footer: qryInventory-SumOfUnitsInStock section, and press Ctrl+V to paste the label. Figure 25.29 shows the final design in Page Design view.

   Figure 25.29. The final design of the Inventory.htm page includes units and value subtotals by category, as well as grand totals for all categories.

   

8. Open the report in Web Page Preview to display the summary version of the page (see Figure 25.30).

   Figure 25.30. Web Page Preview displays the eight subtotals and grand totals for units in stock and inventory value.

   

The Inventory link to the Inventory.htm page is in the Data25.mdb file located in the \Seua11\Chaptr25 folder of the accompanying CD-ROM.