Using Sub-Report Summary Value in Main Report Calculations

How to bring sub-report summary value to the main report and use it in calculations?

But, first we need a ready made report for our project, without the calculations part on it.

Sample database download links are given below. You may download the database version you need.  Microsoft Access2007 version database can be used for later versions of Access too.

First, take a look at the following images:

  1. Print Preview of the finished report.

    Summary Final Preview

  2. Original Report Preview. 

    Summary Original Preview

  3. Changes made on the Report in Design View, to get the result shown on the first image above..

    Summary Design view

  4. Sample Database Download Links, for Microsoft Access 2003 and 2007 Versions, are given below.

    Download Access2007 or later versions Database

    Download Access2002-2003 Database

  5. Download the sample database.

    In short, our task is to add the sub-report category total at the Footer Section of the Sales by Category sub-Report. Get that summary value of each category to the main report header.  Calculate percentage of each category of products sales value on Grand-Total Sales Value (or Pcercentage = Category Sales Value / Total Sales value of all Categories * 100).  We can do this by adding few text boxes on both reports and writing few expressions in them.

    Report Changes

    Let us start with the Sales by Category Sub-report first.

  1. Open the downloaded database.
  2. Open Sales by Category Sub-report in design view.

    Sales Category Design

  3. Right-click on the Report Footer bar and select Properties to display the Property Sheet.
  4. Select the Height Property and change the property value to 0.33” or 0.838 cm.
  5. Select the Text Tool from the Toolbar above and draw a text box on the Report Footer area below ProductSale column.
  6. Write the expression =Sum([ProductSales]) in the Control Source property of the text box. Change the Name property value to SubTotal.
  7. Modify the Caption property value of child label to read as Sub-Total.
  8. Save and close the Sales by Category Subreport.
  9. Open Sales by Category main report in Design View.
  10. Create a Text Box, on the Header Section of the Report, to the right of the report heading.
  11. While the text box is in selected state display the property sheet (F4).
  12. Write the expression =Sum([ProductSales]) in the Control Source property. Change the Name property value to TotalSales. Change the Caption property value of the child label to Total Sales.

    Note: The above expression will calculate the Total Product Sales value of all Categories on the main report. The same expression on the sub-report will calculate sales value of current category of Product (example: Beverages). Now we have the product sales value of a particular category as SubTotal on the Sub-Report and the Total Sales values of all categories of products as TotalSales. In the next step we can find the percentage of a particular category of product’s sales on total sales of all categories. The simple formula can be Percentage=SubTotal/TotalSales*100.  But, SubTotal is on the Sub-Report and we cannot directly address it as SubTotal on it's parent report or Main report (without specifying where it can be located in the main report having one or more child reports or sub-reports).

  13. Create a text box below the Category Header bar to the right of the Category Name heading, move the text box  to the right and position it below the Total Sales calculation control on the Report Header.
  14. Right-click on the Control Source Property of the text box and select Build option, from the displayed list, to open the expression builder control.

    • click on the = symbol to insert it into the expression editor window.
    • Double-click on the + symbol at the left side of the Reports option to expand and show other options.
    • Double-click on Loaded Reports.
    • Double-click on Sales by Category main report to display the Sub-Report’s name.
    • Click on Sales by Category Subreport to display it's control names in the next column.
    • Find the Subtotal control in the list and double-click on it.

      The Subtotal control reference of the sub-report: [Sales by Category Subreport].[Report]![SubTotal], is inserted into the expression builder. If we know how to write this reference correctly in to the Control Source property manually then we can do that without going through this route. But, once you go through this procedure a few times and study Microsoft Access’s addressing method you will know how to write them on Reports or Forms manually.

    • Click OK to come back to the Control Source property of the text box with the reference of the subtotal control and to write the rest of the expression to calculate the percentage.
    • Type /[TotalSales] to the end of the subtotal reference. Have you noticed the slash at the left side of the expression snippet?
    • Select Percent from the drop-down list of the Format property. With this settings we don’t have to write *100 part in the expression.
    • Change the Caption of the child label to Category %. Change the Font-size of the Text box and child label to 12 points and make Font-weight Bold.

    Our Report is almost finished, but we need little more change and that comes next after we preview the progress of our work so far.

  15. Save the changes we have made on the Report and open it in Print Preview, to see the result of the changes we have done on the report so far.

    The Report should look like the image given below.

    Sales Category Preview

  16. Move the Report to the next page.

    The Total Sales value on the Report Header Section is not appearing in the second page.

    This will not appear on the report from second page onwards along with the Report Heading. Report Header section values will only print on the first page of the report and Report Footer will print only on the last page of the report.  But, we want the Heading and the expression written on the Header Section to appear on every page of our Report. Whatever we insert on Page Header Section will appear on every page. We can transfer the Report Heading to Page Header Section but not the Calculation Text box. The SUM(), Count() and other aggregate functions will not work on the Page Header Section, but we want the value from the Total Sales control to appear on the Page Header Section as well. We will do the following changes to keep the report heading and Total Sales values on all pages of the report.

    • Open the report in design view.
    • Drag the Category Name header bar down to get enough space on the Page Header Section, to cut and paste the Report Heading there.
    • High-light report heading and the report date controls (leave alone the Total Sales textbox), cut and paste them into the Page Header Section.
    • Select the Total Sales text box, copy and paste it on the Page Header Section,  move and position it below the text control on the Report Header Section.
    • Write the expression =[TotalSales] (the Name of the total sales calculation text box on the header section) in the Control Source property (over-writing the existing expression) of the copied text box.

      This will display the value from Header Section text box, where the Total Sales value is calculated.  

    • Select the Header Section text control along with the child label and set their Visible Property value to No, to keep it hidden when the report is Previewed or printed.
    • Save the report with the changes.
    • Open Sales by Category report in Print Preview, move the pages forward and check the headings and category percentage values.
  17. I am sure next time you want to do something like this you can do it in few minutes.