ACC2002: Reports Saved as Data Access Pages Are Missing Grand Totals (282381)

RESOLUTION

To work around this behavior, use one of the following methods.

Method 1

If the report is not based on any group, it will not create group headers/footers when you save it as a data access page. Therefore, to create a grand total, use the following method:

1. In the sample database Northwind.mdb, click Reports in the Database window, and then click New.
2. In the New Report dialog box, click Report Wizard, click the Products table in the Choose the table or query where the object's data comes from box, and then click OK.
3. In the Report Wizard, select the following fields from the Available Fields list, and then move them to the Selected Fields list:

   ProductName
   CategoryID
   UnitPrice

4. Click Next, remove any group levels, and then click Next two times.
5. For Layout, select Tabular, and then click Finish.
6. On the View menu, click Design View.
7. On the File menu, click Save As.
8. In the Save As dialog box, click Data Access Page in the As list, and then click OK.
9. In Page view of the newly created data access page, click Design View on the View menu.
10. In the Header: Products section of the data access page, click in the UnitPrice text box.
11. On the toolbar, click the arrow on the AutoSum toolbar button. Note that there are options for Sum, Average, Min, Max, Count, StDev, and Any.
12. Click Sum in the list. This creates a Footer: Products-UnitPrice section with a Bound Span control named SumOfUnitPrice.
13. On the View menu, click Page View to view the sum of UnitPrice column in the data access page.

Method 2

If the report is based on groups and each group has one or more totals followed by a grand total, the grand total will appear blank when you save the report as a data access page. The following steps show you how to workaround this behavior:

1. In the sample database Northwind.mdb, click Reports in the Database window, and then click New.
2. In the New Report dialog box, click Report Wizard, click the Products table in the Choose the table or query where the object's data comes from box, and then click OK.
3. In the Report Wizard, select the following fields from the Available Fields list, and then move them to the Selected Fields list:

   ProductName
   CategoryID
   UnitPrice

4. Click Next two times, and then click Summary Options.
5. In the Summary Options dialog box, click to select the Sum check box, click OK, and then click Finish.
6. On the View menu, click Design View.
7. On the File menu, click Save As.
8. In the Save As dialog box, click Data Access Page in the As list, and then click OK.
9. In Page view of the newly created data access page, scroll to the bottom of the page and note that the Grand Total box is blank.
10. On the View menu, click Design View.
11. On the Tools menu, point to Macro, and then click Microsoft Script Editor.
12. In the Object list, click MSODSC.
13. In the Events list, click DataPageComplete. The following script is inserted:

    <SCRIPT language=vbscript event=DataPageComplete for=MSODSC>
     <!--
    
     -->
    </SCRIPT>
    						

    IMPORTANT: When you create VBScript blocks for MSODSC events, you must add a parameter to the event name, as follows:

    <SCRIPT language=vbscript event=DataPageComplete(obj) for=MSODSC>
     <!--
    
     -->
    </SCRIPT>
    						

    The obj parameter added above is used to return specific information about the event to the script. You must add this parameter, regardless of whether it will be used or not, because the script will not work without it.
14. Enter the following script within these script tags:

      Const FooterLevel = 3
      
      Dim curGrandTot
      Dim sectionFooter
    
      '[ProductSales Grand Total Sum] is the name of a text box that
      'should already reside in the Navigation: GroupLevel0
      'section of this data access page.
      If [ProductSales Grand Total Sum].value = "" Then
          Set sectionFooter = obj.DataPage.FirstSection
    
          While Not (sectionFooter Is Nothing)
              'Test to make sure that focus is in
              'the Footer: GroupLevel0 section.
              If sectionFooter.type = FooterLevel Then
                  'The following line accumulates each of the totals per group
                  'to create a grand total.
                  curGrandTot = curGrandTot + CCur(sectionFooter.HTMLContainer.children("Sum Of ProductSales").value)
              End If
              Set sectionFooter = sectionFooter.NextSection
          Wend
     
          'Fill the control with the frand total.
          [ProductSales Grand Total Sum].value = curGrandTot
      End If