MS Access for the Business Environment: Reporting in MS Access: Grouped Transactional Report Part II

Monday Oct 6th 2003 by William Pearson
Share:

Create a transactional report that groups at multiple levels. In the second half of a two-part article, author Bill Pearson leads the step-by-step design and creation of a report that groups customer transactions at multiple levels with corresponding totals.

About the Series ...

This article continues the series, MS Access for the Business Environment. The primary focus of this series is an examination of business uses for the MS Access relational database management system. The series is designed to provide guidance in the practical application of data and database concepts to meet specific needs in the business world. While the majority of the procedures I demonstrate will be undertaken with MS Access 2002, many of the concepts that we expose in the series will apply to other versions of MS Access.

For more information on the series, as well as the hardware / software requirements to prepare for the tutorials we will undertake, please see Tutorial 1: Create a Calculated Field with the Expression Builder.

Introduction to this Tutorial

This is the second half of a two-part tutorial that explores the creation of a basic transactional report that groups the information it presents at multiple levels, at most of which a corresponding total will be displayed. This article continues with the preparation we performed in Reporting in MS Access: Grouped Transactional Report Pt.I, where we established our data source as a query we constructed in the Northwind sample database.

In Part I, we discussed the requirements that the report will need to address, then listed the general steps involved in professional report design and creation. We then began to design and create our illustrative report in a hands-on manner, following many key steps that are common in a successful report writing project within a collaborative business environment. Within each step, we discussed the details involved and the results that we sought to obtain within our design.

The report that we began to create in our lesson focuses on orders placed by the customers of Northwind. It's objective is to present summary information about orders placed by customers over year-to-date and other time intervals - intervals that can be controlled by a given information consumer at report run time. We discussed some of the specific business needs that the report will need to address.

In this article, we will pick up where we left off with the common steps for successful reporting efforts, focusing initially on "pre-setting" the sorting and grouping of data in the report. We will then select data, from the data source we created in Part I, for inclusion in our report. After entraining the data, we will focus upon the arrangement of labels and text in the report, the establishment of settings based upon grouping, and the handling of other attributes specified by the intended audience. Finally, we will briefly discuss the review and refinement of the report based upon feedback that we receive from information consumers, who ideally review the report at various evolutionary stages.

NOTE: To perform the steps of this lesson from the starting point that follows, you will need to have completed the steps we undertake in our last lesson, Reporting in MS Access: Grouped Transactional Report Pt.I.

A Review of the Common Steps for Successful Reporting

In Part I, we discussed that, as diverse as they may be in scope and purpose, and as different as the industries and sizes of the organizations for whom we are consulting to complete them, the majority of reporting projects are composed of several common steps that bring about a successful conclusion. Those steps, again, include:

  • Gather the business requirements.
  • Design the report.
  • Locate and access the data.
  • Create the new report.
  • Establish report characteristics.
  • Set up sorting and grouping of data in the report.
  • Select data and include it in the report.
  • Arrange labels and text in the report.
  • Establish display and other settings based upon grouping and other attributes.
  • Review and refine the report based upon the input of its intended audience.

Due to the differences in each unique reporting project, the order of the above steps may differ. In addition, there may be different iterations of many of the steps, as the feedback we receive from the intended audience, procured throughout the report development cycle, as opposed to at the end only, will often drive stepping back through formerly completed steps to make the adjustments required to meet more refined requirements. The sooner we can identify these modifications, which often come to light only after the information consumers are shown our evolving designs, and after they get a feel for potential capabilities they may not have expected in the initial requirements gathering discussions, the less rework we are compelled to perform. Again, the key is recurring communication and synchronization of our efforts with the needs of the intended audience.

We will rejoin the report design we began in Part I at the "Set up sorting and grouping of data in the report" step in our procedures above.

Review and Rejoin the Project from Part I

In the present case specifically, and in a large percentage of report drafting efforts in general, we wish to organize data for presentation by a particular group or groups. The present reporting objective, as we defined in Part I, is to present summary information about the cumulative orders placed by customers. In our example report, as an illustration, we wish to group transactions primarily by customer, based upon the draft we completed in our early business requirements gathering stage (and depicted, again, in Illustration 1).


Illustration 1: Draft of the Proposed Customer Orders Report

As you recall, we created the rough initial draft based upon our understanding of the business requirement. We might then have presented the draft, listening closely to the feedback of the report's intended audience, to confirm our understanding of the need. After making a few adjustments, in our example, to the draft, we produced the rough design shown in Illustration 1 above. The initial draft includes grouping by customer, then by order dates of the customer, which is the focus of this section of our current lesson.

As we will see, we can create both single and multiple group levels within an MS Access report. Groups drive many other facets of report layout and behavior, as we will also see, including subtotals, grand totals, percentages of totals, summary calculations, sorting considerations, and other attributes of the report in which they are created. The order of the steps we undertake here can mean efficient report design and creation - or a tedious, time consuming process that is fraught with reversals and rework. As in many other areas of report creation, thinking ahead usually pays large dividends.

Let's return to the report at the state in which we left it in the first half of the lesson. We have done the majority of the preparation for populating our report with the data that has been requested by our information consumers. Having accomplished the preliminary layout for our report, we will begin, in Part II of this tutorial, to bring in and arrange the data for presentation.

1.  Open MS Access once again, and return to the Database window.

The steps for entering MS Access, as well as for navigating to the Database window, are outlined in Part I. Once at the Database window, our view should resemble that depicted in Illustration 2.


Illustration 2: Typical Database Window

2.  Click Reports, under Objects in the Database window.

The Report window appears, similar to that shown in Illustration 3.


Illustration 3: Typical Reports Window

3.  Select the Customer Orders Report that we created in Part I of this lesson, highlighting it.

4.  Click the Design button in the toolbar, as shown in Illustration 4.


Illustration 4: Select the Report and Click Design View

The new report appears in Design View, with little in place except the designated Page Header, Detail, and Page Footer sections, as partially shown in Illustration 5.


Illustration 5: The Blank Report, Design View

Having accomplished the preliminary creation, and basic layout, for our report, we will now "pre-set" sorting and grouping for our report, and create the sections thereof as part of the process.

Set up Sorting and Grouping of Data in the Report

We are ready to sort our data. MS Access requires that we do so before we can define groupings. Keep in mind that any sorting in the source query will be irrelevant.

1.  Select View --> Sorting and Grouping from the main menu in the Design View.

The Sorting and Grouping dialog appears.

2.  Click the top row within the Field/Expression column.

3.  Click the selector arrow that becomes visible.

A list of fields in the Customer_Orders_Source query appears.

4.  Select the CustomerID field in the list.

5.  Leave the Sort Order column setting at the default of Ascending.

6.  Click the Group Header field below the Field/Expression and Sort Order grid in the dialog (in the Group Properties section of the dialog).

7.  Using the down arrow in the Group Header field, select Yes to group on CustomerID.

8.  In the same manner, select Yes in the Group Footer field immediately underneath the Group Header field.

9.  Leave the remaining Group Properties at default.

10.  Select the OrderDate field for the Field/Expression column of the next row.

11.  Leave the Sort Order column setting, again, at the default of Ascending.

12.  Click the Group Header field below the Field/Expression and Sort Order grid in the dialog.

13.  Using the down arrow, select Yes to group on OrderDate.

14.  In the same manner, select Yes in the Group Footer field immediately below.

15.  Leave the remaining group properties at default.

The Sorting and Grouping dialog appears as shown in Illustration 6.


Illustration 6: Sorting and Grouping Dialog, with Settings

16.  Close the Sorting and Grouping dialog.

The report again appears in an empty state, although our recent settings have been applied. The telling difference, however, is the appearance, as seen in Illustration 7 (partial view), of the Header / Footer sections for the CustomerID and ShippedDate fields.


Illustration 7: The Report with Header / Footer Additions (Partial View)

Having accomplished the preliminary group and sort criteria, we will begin the process of bringing data into our currently blank report.

Select Data and Include It in the Report

Recall that, in Part I, Create the New Report section, we bound our new report object to the source query via the Choose The Table or Query where the Object's Data comes from selector within the New Report dialog, Design View (our selection of the query initialized the Report Designer, from which we first saw the Header / Footer layout in its basic, default form.) Our objective, at this step in our general report-building procedure, is to select the desired data controls, objects that represent the fields of the data source, and to include them by dragging the selected controls into our report via the Report Design window.

Populating the Detail Section

First, let's summon the Field List that MS Access provides as a "control palette" (my metaphor, not Microsoft's), so that we can see the fields represented.

1.  Select View --> Field List from the main menu in the Report View.

2.  Select Field List from the menu that appears.

The Field List appears, as depicted in Illustration 8.


Illustration 8: The Field List Appears

We can now begin dragging controls, and thus the data fields they represent, into our report. We will populate the Detail section, which, in our report, represents the transactional level, first, and then handle the group headers and footers next. (Our focus in this lesson is the inclusion and presentation of the data. We will not spend a great deal of time on formatting, report titles, and other such cosmetics, so as to allow maximum coverage of actual reporting concepts.)

3.  In the Field List, click the ProductID field.

4.  Hold down the CTRL key, to allow the selections of non-contiguous fields.

5.  Click each of the following to select them simultaneously with the ProductID field.

  • ProductName
  • UnitPrice
  • Quantity
  • Discount
  • NetOrder

The Field List with selected fields appears as shown in Illustration 9.


Illustration 9: The Field List with Our Selections

6.  Click some point within the fields highlighted (left-mouse).

7.  Drag the fields to the Detail section.

8.  Drop the fields / controls at a point in the upper left corner of the Detail section, at approximately zero (0) inches on the horizontal (across the top of the Design View) ruler. Make the drop point as close to flush to the top of the Detail section as possible

The controls appear, still selected as a group, as shown in Illustration 10.


Illustration 10: Dropping the Data Fields / Controls

At this juncture, we will discuss working with the controls we have just put in place. Actually, a pair of controls appears for each field we dragged off the field list. The control to the left labels the control with which it is paired (hence we refer to the control on the left as a label control). The control to the right is known as a text control; it is the actual representative of the underlying data field to which it is attached, or "bound," in the data source.

To move a control pair (actually termed a compound control), we select the pair by clicking either control. Next, glide the mouse cursor over either of the controls, until the cursor becomes a hand, as shown in Illustration 11. Once the hand appears, simply click and drag to move the compound controls together.


Illustration 11: Preparing to Move the Compound Control

To move only one of the two controls within the pair, drag it by its Move handle, the large black box in the upper left corner of the control selected. A hand with a pointing finger appears when the cursor is in position to move the single control (as depicted in Illustration 12), at which point we simply click and drag, to move the single control as required.


Illustration 12: Preparing to Move the Single Control

The smaller squares are Sizing handles for the selected control. Selection itself is indicated by the appearance of Sizing handles: they are visible only for the selected control. A summary diagram appears in Illustration 13.


Illustration 13: Summary Diagram - Moving, Sizing and Selection for Controls

Let's practice the selection, moving and sizing techniques in the Details section of our report, where we dropped the six fields we dragged from the Field List above.

9.  Click the ProductID compound control at any point.

The Sizing and Move handles appear for the ProductID control pair.

10.  Hover the cursor over the area to the immediate right of the second Move handle.

The cursor becomes a hand image.

11.  Click and drag the ProductID compound control to a position approximately 1/4-inch on the horizontal (across the top of the Design View) ruler; keep the control as close to flush to the top of the Detail section as possible.

12.  Click the ProductName compound control at any point.

The Sizing and Move handles appear for the ProductName control pair.

13.  Hover the cursor over the area to the immediate right of the second Move handle.

The cursor becomes a hand image.

14.  Click and drag the ProductName compound control to the immediate right of, and aligned with, the ProductID compound control.

15.  Drop the ProductName compound control, as shown in partial view of the Detail section in Illustration 14.


Illustration 14: New Placement of the ProductName Compound Control

16.  Click the label control (the left control in the pair) for ProductID.

17.  Press the Delete key.

The ProductID label disappears. We will likewise remove the label controls from each of the fields we dragged into the Detail section, as we do not want to show these in the report.

18.  Delete the label controls for each of the remaining control pairs in the Detail section of the report:

  • ProductName
  • UnitPrice
  • Quantity
  • Discount
  • NetOrder

The Detail section of the report appears as shown in Illustration 15.


Illustration 15: The Detail Section Controls - sans Labels

19.  Resize the ProductID text control to have the right border touch the 1-1/4 inch horizontal marker line (as we proceed, ensure that all Detail section controls are placed snug against the top of the Detail section).

20.  Move the ProductName control to align with ProductID vertically, with the ProductName left border at the 1-1/4 inch horizontal marker line.

21.  Resize the ProductName control, if required, so that its right border touches the 2-1/2 inch horizontal marker line.

22.  Move the UnitPrice control to touch ProductName's right border at the 2-1/2 inch horizontal marker line.

23.  Resize the UnitPrice control, as necessary, so that its right border touches the 3-inch horizontal marker line.

24.  Position the Quantity control so that its left edge touches the 3-1/4 inch horizontal marker line.

25.  Resize the Quantity control, as necessary, so that its right border touches the 3-3/4 inch horizontal marker line.

26.  Position the Discount control so that its left edge touches the 4-inch horizontal marker line.

27.  Resize the Discount control, as necessary, so that its right border touches the 4-1/2 inch horizontal marker line.

28.  Position the Net Order control so that its left edge touches the 4-3/4 inch horizontal marker line. (The page will automatically widen as required.)

29.  Resize the Net Order control, as necessary, so that its right border touches the 5-1/2 inch horizontal marker line.

Now let's size the Detail section to ensure that our detail transactions are presented in compact fashion.

30.  Move the pointer to the bottom of the Detail section, placing it between the Detail section and the OrderDate footer section.

The pointer becomes a "double-headed arrow," indicating that it is in position for dragging the Detail section border to the desired position.

31.  Hold down the left button on the mouse, as soon as the arrow image appears.

32.  Drag the Detail section border up, until a tight fit is achieved for the controls we have included.

33.  Release the button to fix the border in place.

The Detail section of the report now appears as depicted in Illustration 16.


Illustration 16: Detail Section (Partial View) with Our Adjustments to the Border

We can perform an ongoing preview of our evolving report through the use of the Preview feature. Let's do so now, to get a feel for the effectiveness of our design work thus far.

34.  Click the Print Preview button (shown in Illustration 17) on the main toolbar.


Illustration 17: The Print Preview Button

We are prompted for an "As Of" date, via an Enter Parameter Value dialog that appears, and which is initialized because of the logic we built into our underlying data source, a query that we constructed in the first half of this lesson. Recall that our intent with this query is to enable an information consumer to generate the customer order information found in this report "as of" any given date in time (or at least "as of" any date that is included within the data itself). For more information regarding the setup or reasoning behind the prompt, see Part I.

35.  Type the following (a date which we know to be preceded in time with transactions, within our database) into the AsOfDate box:

16-Oct-1996

The Enter Parameter Value dialog appears, with our input, as shown in Illustration 18.


Illustration 18: The Enter Parameter Value Dialog

36.  Click OK to apply the date.

The report executes, returning data that appears similar to that partially shown in Illustration 19.

Illustration 19: The Report Preview - Partial Detail Section

We can see that the Detail section of the report is shaping up nicely and that the data is appearing in accordance with our alignment and placement intentions. The spaces that appear between the detail sections are due to the placement of our header / footer sections, which are currently empty. We will see in the next section how to populate, as appropriate, these sections.

37.  Click the Close button to close the Preview window.

38.  Save your work as desired, as a safety measure.

Working with the OrderDate Header and Footer Sections

 

In our report, we will use the group header sections to present the order date and customer information that is contained within the Detail section. The Detail section of this report is "home" to the customer order information; the OrderDate header and footer sections will serve as "containers" from which we can present the OrderDate groups to which each set of details belongs.

1.  Re-enter the Design View for the Customer Orders Report, if necessary.

2.  Select OrderDate from the Field List.

3.  Drag the OrderDate compound control to the OrderDate header section, dropping it into the upper left corner of the section.

4.  Reposition the compound control to be flush to the top of the OrderDate header section, and at approximately the 1/4-inch point on the horizontal ruler.

5.  Click the text control (the right control in the pair) for OrderDate, to select it.

6.  Drag the text control to align to the right of the OrderDate label, at approximately the 7/8-inch point on the horizontal ruler, flush to the top of the OrderDate header section.

7.  Ensuring that the OrderDate text control is still selected, right-mouse click.

8.  Select Properties from the context menu that appears, as shown in Illustration 20.


Illustration 20: Context Menu

The Properties sheet for the OrderDate text control appears.

9.  Select the Format tab of the Properties sheet, if necessary.

10.  Select Left in the Text Align setting of the Format tab.

The Properties sheet - Format tab for the OrderDate text control appears as depicted in Illustration 21.


Illustration 21: Properties sheet - Format tab for the OrderDate Text Control

11.  Close the Properties sheet.

12.  Select the OrderDate compound control by clicking the label control, holding down the SHIFT key, and then clicking the text control.

The compound control appears as selected.

13.  Click the Bold button (Illustration 22) in the main toolbar.


Illustration 22: The Bold Button

14.  Move the pointer to the bottom of the OrderDate header section, placing it between the OrderDate header section and the Detail section.

15.  Hold down the left button on the mouse, as soon as the "double-arrow" image appears.

16.  Drag the Detail section border up, until a tight fit is achieved for the controls we have included.

17.  Release the button to fix the border in place.

The relevant portion of the Design View should appear similar to Illustration 23.


Illustration 23: The OrderDate Header

The relatively simple setup for the header is complete. Next, we can consider the OrderDate footer section. The fact that it should be essentially the same as the header setup will allow us a welcome shortcut, as we shall see in the steps that follow.

18.  With the OrderDate compound control selected, press the CTRL-C key combination.

This copies the compound control into the notepad area.

19.  Click inside the OrderDate footer section.

20.  Press the CTRL-V.

This pastes the compound control, together with the adjustments and formatting that we have already performed in the OrderDate header, into the footer. We are thus spared at least the tasks of resizing, formatting and re-setting the alignment property for the OrderDate compound control. We have only to arrange it as we need it in the footer section, as well as to handle any differences in its use.

21.  Using the methods we have outlined previously, move the OrderDate compound control so that its left border is aligned with the 1/4-inch point on the horizontal ruler.

This should align the OrderDate compound control in the footer with the compound control from which it was cloned in the OrderDate header. We want to make one alteration, however; a modification to the existing text in the label control to make it more meaningful in its context within the footer.

22.  Right-click the OrderDate label control in the OrderDate footer.

23.  Select Properties from the context menu that appears.

24.  Select the Format tab of the Properties sheet, as necessary.

25.  Modify the Caption field at the top of the Format tab to read as follows:

Totals for:

The Properties sheet - Format tab for the OrderDate label control appears as depicted in Illustration 24.


Illustration 24: Properties sheet - Format tab for the OrderDate Label Control

26.  Close the Properties sheet.

27.  Move the pointer to the bottom of the OrderDate footer section, placing it between the OrderDate footer section and the CustomerID footer section.

28.  Hold down the left button on the mouse, as soon as the "double-arrow" image appears.

29.  Drag the CustomerID footer section border up, until a tight fit is achieved for the controls we have included.

30.  Release the button to fix the border in place.

The OrderDate header and footer sections now appear in the report as shown in Illustration 25.


Illustration 25: OrderDate Header and Footer Sections (Partial View) with Our Adjustments

Let's perform another preview of our evolving report.

31.  Click the Print Preview button.

32.  Type the following into the AsOfDate box that appears:

16-Oct-1996

33.  Click OK to apply the date.

The report executes, returning data that appears similar to that partially shown in Illustration 26.


Illustration 26: The Report Preview - Partial Sections

Let's take a look at the overall page layout from a somewhat "higher" level; while it might make the specific data harder to see, the zoom option for Preview is certainly useful in getting a feel for the overall page layout.

34.  Pass the mouse cursor over the report until a magnifying glass icon (with a " - " sign inside it) appears.

35.  Click the report once.

The report appears in reduced magnification mode, actually "zooming out," affording us a general "page" view of overall layout of the report, as shown in Illustration 27.


Illustration 27: The Report Preview - Zoomed Layout View

Again, we see that the report is developing according to our objectives.

36.  Click the Close button to close the Preview window.

37.  Save your work as desired, as a safety measure.

Next, we will move into the CustomerID header and footer sections.

Working with the Customer Header and Footer Sections

The next header and footer sections with which we will deal in the Customer Orders Report will act to group order information by customer. The Customer header and footers will enclose the OrderDate group, within which lies the Detail information. The steps involved in setting up this part of our report will be quite similar to those of the OrderDate sections that we assembled above, once again occurring within the Design View for the report.

1.  In the Field List, click the CustomerID field.

2.  Hold down the SHIFT key, to allow the simultaneous selection of contiguous fields.

3.  Click the CompanyName field.

4.  Drag the fields together to the CustomerID section.

5.  Drop the fields / controls at a point in the upper left hand corner of the CustomerID header.

6.  Align the compound control approximately with the 1/8-inch tick on the horizontal ruler, as flush to the top of the CustomerID section as possible.

7.  Right-click the CustomerID label control.

The Properties page for the CustomerID label control appears, with the Format tab as the default view.

8.  Modify the Caption to read as follows:

Customer:

9.  Change Font Weight to Bold.

The Properties sheet - Format tab for the CustomerID label control appears as depicted in Illustration 28.


Illustration 28: Properties sheet - Format tab for the CustomerID Label Control

10.  Close the Properties sheet.

The Properties sheet disappears.

11.  Click the CustomerID text control to select it alone.

12.  Pass the cursor over the Move handle for the control CustomerID text control, until the pointing finger image appears.

13.  Click and drag the control to the immediate right of the CustomerID label control.

14.  Click the CustomerID label control to select it.

15.  Resize the CustomerID label control to fit the new caption (the right border of the control will align approximately with the 3/4-inch point on the horizontal ruler).

16.  Move the CustomerID text control again to adjust its position to the immediate right of the CustomerID label control.

17.  Resize the CustomerID Text control so that its right border aligns approximately with the 1-1/4 inch point on the horizontal ruler).

18.  Click the label control (once again, the left control in the pair) for CompanyName.

19.  Press the Delete key.

The CompanyName label disappears, leaving only the associated text control.

20.  Click the CompanyName text control to select it.

21.  Move the CompanyName text control to the immediate right of the CustomerID text control (aligning it approximately with the 1-1/4 inch point on the horizontal ruler, and to the top of the CustomerID section).

We need, once again, to size the section to ensure that our CustomerID header is presented in compact fashion.

22.  Move the pointer to the bottom of the CustomerID header section, placing it between the CustomerID header section and the OrderDate header section.

The pointer becomes a "double-headed arrow," indicating that it is in position for dragging the CustomerID header section border to the desired position.

23.  Hold down the left button on the mouse, as soon as the "double-arrow" image appears.

24.  Drag the CustomerID header section border up, until a tight fit is achieved for the controls we have included.

25.  Release the button to fix the border in place.

The straightforward setup for the CustomerID header section is complete. Next, we will consider the CustomerID footer section. We will take advantage of the shortcut we used earlier in "cloning" a large part of the text we need, as we did earlier, to save time and effort.

26.  In the CustomerID header section, select the CustomerID label control (now captioned "Customer:"), and, pressing the SHIFT key, select the other two controls (all three controls in the header should be selected).

27.  Press the CTRL-C key combination.

28.  Click inside the CustomerID footer section.

29.  Press the CTRL-V key combination.

This pastes the controls, together with the adjustments and formatting that we have already performed in the CustomerID header, into the footer. We will next arrange it as needed in the footer section, as well as to handle any required modifications.

30.  Using the methods we have outlined earlier, move the compound controls so that they align with the controls from which they were cloned in the CustomerID header. (This can most easily be accomplished by selecting all simultaneously, then dragging and dropping so that the left border of the CustomerID label control approximately aligns with the 1/8 - inch mark on the horizontal border.)

We have an alteration here, similar to the one we made in the OrderDate footer earlier: We again want to modify the text in the footer to indicate it is a "totals" line.

31.  Right-click the CustomerID label control in the CustomerID footer.

32.  Select Properties from the context menu that appears.

33.  Select the Format tab of the Properties sheet.

34.  Modify the Caption field at the top of the Format tab to read as follows:

Totals for:

The Properties sheet - Format tab for the CustomerID label control appears as depicted in Illustration 29.


Illustration 29: Properties sheet - Format tab for the CustomerID Label Control

35.  Close the Properties sheet.

36.  Move the pointer to the bottom of the CustomerID footer section, placing it between the CustomerID footer section and the Page footer section.

37.  Hold down the left button on the mouse, as soon as the "double-arrow" image appears.

38.  Drag the Page footer section border up, until a tight fit is achieved for the controls we have included.

39.  Release the button to fix the border in place.

The CustomerID header and footer sections now appear in the report as shown in Illustration 30.


Illustration 30: CustomerID Header and Footer Sections (Partial View) with Our Adjustments

Let's perform another preview of our evolving report.

40.  Click the Print Preview button.

41.  Type the following into the AsOfDate box that appears:

16-Oct-1996

42.  Click OK to apply the date.

The report executes, returning data that appears similar to that partially shown in Illustration 31.


Illustration 31: The Report Preview - Partial Sections

43.  Click the Close button to close the Preview window.

44.  Save your work as desired, as a safety measure.

The basic ingredients of the report are now in place. We next need to add totals to the footers to round out the basic functionality of the report.

Working with Totals

Let's revisit the footers we have created long enough to add totals. We have aligned our various levels of grouping adequately to make this an efficient exercise. The totals that we insert will be group summaries, and, as such, are contextually sensitive to the section into which we insert them. The expression in a field that yields a summary at a given group level will also yield the correct summary when placed in another group level (respective of the level in which it is placed); it "knows" its location and, as we shall see, behaves accordingly.

We will start with the OrderDate footer section then work our way outward in summary creation, just as we did in the creation of the groups themselves.

1.  Click the ToolBox button to cause the ToolBox to appear.

The ToolBox button is depicted in Illustration 32.


Illustration 32: The ToolBox Button

The Controls ToolBox appears, as shown in Illustration 33.


Illustration 33: The Controls ToolBox

2.  Click the Text Box control (shown circled in red in Illustration 33 above).

3.  Place the mouse cursor within the OrderDate footer.

The cursor becomes a "+" ("crosshairs") sign, to assist us in placing the new control.

4.  Click at the point of insertion of the top left corner of the control.

5.  Try to place the control under the NetOrder field just above the OrderDate footer, by drawing a rectangular outline approximately its size under the NetOrder field.

6.  Click the Label control that has appeared to the left of the new text control.

7.  Press the Delete key.

The Label control disappears.

8.  Adjust the alignment of the new Text control as closely as possible with the NetOrder field by resizing and dragging as appropriate.

9.  Right-click the new Text control.

10.  Select Properties.

The Properties sheet appears, defaulted to the Format tab.

11.  Select Currency in the Format field.

The Properties sheet - Format tab for the new Text control appears as depicted in Illustration 34.


Illustration 34: Properties sheet - Format Tab for the New Text Control

NOTE: The new Text control, like all new controls, is assigned an identifying name by MS Access upon creation ("Text21" in Illustration 34). In general, we would assign logical names to the controls based upon corporate or project standards that made sense to us at the point of creation. We will leave these identifiers at default for purposes of this lesson, in the interest of brevity, but proper naming is, without doubt, an important attribute in organizing and maintaining the object set in any real world project.

12.  Click the Data tab.

13.  Type the following into the Control Source field:

=Sum([NetOrder])

The Properties sheet - Data tab appears as shown in Illustration 35.


Illustration 35: Properties sheet - Data Tab for the New Text Control

14.  Close the Properties sheet for the new Text control.

15.  On the Controls Toolbox, click the Line button, shown in Illustration 36.


Illustration 36: The Line Button in the Controls ToolBox

16.  Place the mouse cursor within the OrderDate footer.

As we saw with the Text control in the immediately preceding steps, the cursor becomes a "+" sign, to assist us in placing our control.

17.  Click at the point of insertion, this time at the top left corner of the recently placed Text control (our summary control).

18.  Place the Line control above the new summary Text control field, by dragging the line to a length approximately the size of the NetOrder field.

The approximate desired effect is depicted in Illustration 37.


Illustration 37: Line Placement above the new Summary Text Control

19.  Click the Line control to select it.

20.  Hold the SHIFT Key.

21.  Click the new summary Text control to select it simultaneously with the Line control.

22.  Use the "up," and other arrow keys, as shown in Illustration 38, to adjust the positioning of the controls to align them with the NetOrder column to which they relate.


Illustration 38: The Directional Keys Act as Excellent Placement Tools for Selected Controls

NOTE: Multiple visits to the report Preview might be in order to allow us to perfect the alignment of the manually placed controls to the NetOrder controls. (If you accidentally push the control pair into the section above, "back out" with the UNDO (CTRL + Z) key combination.)

23.  Readjust the footer borders as necessary to maintain the "tight fit" we had previously established.

24.  Preview the report, using 16-Oct-1996 as the "as of" date again, to review the outcome.

We are now ready to insert a summary control in the CustomerID footer. Let's take a "shortcut" similar to one we took with titles in this footer above. This time, we will leverage the operation to demonstrate another feature about our summary control.

25.  Select both the line and the summary control we created in the OrderDate footer above. (Hint: Use the SHIFT key).

26.  Press the CTRL + C key combination to copy the selection.

27.  Place the cursor in the CustomerID footer.

28.  Press the CTRL + V key combination to paste the selection.

Our newly pasted control pair will typically not appear conveniently below the last summary control we created. It will appear "pre-selected," however, wherever it "lands," meaning that we can easily align it with the precision of the directional keys we used above.

29.  Position the new control pair within the NetOrder "column" (underneath the last summary control we created).

30.  Select the new summary control alone.

31.  Right-click the control, to display the context menu.

32.  Select Properties from the context menu.

The Properties sheet appears, defaulted to the Format tab.

33.  In the Font Weight field, select Bold.

34.  Close the Properties sheet.

We are returned to the Design View.

Now, let's preview the report again (same "as of" date at the prompt, for consistency's sake), to observe the behavior of the new control.

35.  Click the Print Preview button.

36.  Type the following into the AsOfDate box that appears:

16-Oct-1996

37.  Click OK to apply the date.

The report executes, returning data that appears similar to that partially shown in Illustration 39.


Illustration 39: The Report Preview - Partial Sections

Taking CustomerID BERGS as an example, as shown above, we note that the new total appears (in bold, as we intended), but not only do we note it's appearance; we notice that it is context sensitive to its position in the CustomerID footer of the report. The identical expression that we input into the OrderDate footer takes on a new meaning based upon its location in the CustomerID footer. It is because of this fact that we can benefit by the use of the "shortcut" we have illustrated.

38.  Click the Close button to close the Preview window.

39.  Save your work as desired, as a safety measure.

We now have all the control functionality in place to deliver the requirements of the intended audience of the report. Moreover, the prompt feature of the report means our totals are the totals for each customer "as of" the date we plug into the mechanism at runtime. I typically prefer to get all data elements of the report in general order before beginning formatting; to begin earlier than that is almost certain to mean rework, if virtually any element or elements change; data changes can ripple through the entire report, causing the need to realign and myriad other such tweaks.

We will not go further with formatting, as that could fill a lesson in itself. Feel free to experiment with borders, colors and other formatting functionality to make the report attractive and easy for the information consumers to read. In addition to formatting, we would no doubt want to create page headers and footers appropriate to the report. Particularly attractive features might include display of the "as of" date in the header, as part of the report title; page numbers and a host of other options can be added, as you can see through experimentation on your own.

40.  Close MS Access, when desired.

Review and Refine the Report Based Upon the Input of Its Intended Audience

At this stage in our report construction, we might present our work to members of the intended audience to obtain their confirmation of its meeting their expectations. We might also obtain feedback as to further adjustments to make, based not only upon deficiencies, but, as is often the case, upon ideas generated by the review process for further enhancements and added functionality. Everyone wins in this environment of continuous improvement, and we meet the challenges with ready confidence.

Conclusion...

With this lesson, we continued a two-part tutorial surrounding the creation of a transactional report that groups and summarizes the information it presents at multiple levels. We resumed where we left off in Part I, reviewing the common steps for successful reporting efforts, as well as an illustrative business requirement that we had been given by our information consumers in the previous half of the lesson. We then focused initially on "pre-setting" the sorting and grouping of data in the report. After establishing our grouping and sorting criteria, we selected data from the query data source we created in Part I, for inclusion in our report. Moreover, with the introduction of each data element, we focused upon the arrangement of labels and text in the report, the establishment of settings based upon grouping, and the handling of other attributes expected by the intended audience. Finally, we briefly discussed the need for review and refinement of the report, based upon feedback that we receive from information consumers, who ideally scrutinize the report design at various evolutionary stages.

» See All Articles by Columnist William E. Pearson, III

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved