Introduction.
How to bring the sub-report summary value, to the main report and use it in calculations?
But, first, we need a ready-made report for our project.
Sample database download links are given below. You may download the database version you need. The demo database is in the Microsoft Access2007 version and can be used in later versions too.
First, take a look at the following images:
- Print Preview of the finished report.
- Original Report Preview.
- Changes were made to the Report in Design View, to get the result shown in the first image above.
Download Links.
- Sample Database Download Links, for Microsoft Access 2007 and 2003 Versions, are given below.
- Download the suitable sample database.
In short, our task is to add the sub-report category total to the Footer Section of the Sales by Category sub-Report. Get that summary value of each category to the main report header. Calculate the percentage of each category of product sales value on Grand-Total Sales Value (or Percentage = Category Sales Value / Total Sales value of all Categories * 100). We can do this by adding a few text boxes on both reports and writing a few expressions in them.
The Sub-Report Changes.
Let us start with the Sales by Category, Sub-report first.
Download NWaccdb.zip
Download NWmdb.zipOpen the downloaded database.
Open Sales by Category, Sub-report in design view.
Right-click on the Report Footer bar and select Properties to display the Property Sheet.
Select the Height Property and change the property value to 0.33” or 0.838 cm.
Select the Text Tool from the Toolbar above and draw a text box on the Report Footer area below the Product Sale column.
Write the expression =Sum([ProductSales]) in the Control Source property of the text box. Change the Name property value to SubTotal.
Modify the Caption property value of the child label to read as Sub-Total.
Save and close the Sales by Category Subreport.
The Main-Report Changes.
Open Sales by Category main report in Design View.
Create a Text Box, on the Header Section of the Report, to the right of the report heading.
While the text box is in the selected state, display the property sheet (F4).
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 in the sub-report will calculate the sales value of the 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 sales on the total sales value 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 its 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).
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 under the Total Sales calculation control on the Report Header.
Right-click on the Control Source Property of the text box and select the 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 on the left side of the Reports option to expand and show other options.
Double-click on Loaded Reports.
Double-click on the Sales by Category main report to display the Sub-Report's name.
Click on Sales by Category Subreport to display its 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 into 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] at the end of the subtotal reference. Have you noticed the slash on the left side of the expression snippet?
Select Percent from the drop-down list of the Format property. With these 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 of 12 points and make Font-weight Bold.
Report Sample Print Preview
Our Report is almost finished, but we need a little more change and that comes next after we preview the progress of our work so far
Save the changes we have made in 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.
Move the Report to the next page.
The Total Sales value of the Report Header Section is not appearing on the second page.
This will not appear on the report from the 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 in the Page Header Section as well. We will make the following changes to keep the report heading and Total Sales values on all pages of the report.
The Final Changes.
Open the report in design view.
Drag the Category Name header bar down to get enough space to the Page Header Section, to cut and paste the Report Heading there.
The highlight report heading and the report date controls (leave alone the Total Sales textbox), cut, and paste into the Page Header Section.
Select the Total Sales text box, copy and paste it on the Page Header Section, and move the position 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 (overwriting the existing expression) of the copied text box.
This will display the value of the 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.
Print Preview the Report.
Open the Sales by Category report in Print Preview, move the pages forward, and check the headings and category percentage values.
I am sure next time you want to do something like this you can do it in a few minutes.
No comments:
Post a Comment
Comments subject to moderation before publishing.