Percentage on Report Summary
We have worked with a Query to solve this problem earlier in the Blog Post: Percentage on Total Query. This time let us see how it is done on a Report. Our task is to show detail-line-wise value’s percentage on Report Summary Total.
The solution is simple. Create a Report with some values in it with Report level summary. Add a Text Box in the detail section and write an expression to divide the Report Summary Value into the detail level value.
Let us try it out.
- Import the Order Detail Table from Microsoft Access Sample Database: C:\Program Files\Microsoft Office\Office11\Sample\Northwind.mdb
- Open a new Query in SQL View; without selecting a Table from the displayed list.
- Copy and Paste the following SQL String into the SQL editing window of the new Query:
SELECT [Order Details].[Order ID], Sum([Order Details].Quantity) AS TQuantity, Sum([Order Details].[Unit Price]) AS UnitPrice, Sum([Unit Price]*[Quantity]) AS TotalPrice FROM [Order Details] GROUP BY [Order Details].[Order ID];
- Save the Query with the name OrderSummary.
- Design a Report (as shown in the image given below) with the Detail Section and Report Footer summary controls using OrderSummary as Source.
- Click on the Text Box at the Footer of the Report to select it.
- Display the Property Sheet (F4 or ALT+Enter) of the Text Box.
- Change the Name Property Value to GTPrice (stands for Grand Total Price).
- Write the expression =Sum([TotalPrice]) in the Control Source Property.
- Select the Text Box at the right end in the Detail Section and display its Property Sheet.
- Write the expression =[TotalPrice]/[GTPrice] in the Control Source Property.
- In the Format Property select the Percent format from the drop-down list.
- Type 2 in the Decimal Places Property.
- Save and Close the Report.
- Open the OrderSummary in Print Preview and check the detail line percentage value calculated on Report Footer Grand Total Price.
- Scaling the Chart Object
- User and Group Check
- User-Defined Data Type
- Budgeting and Control
- Change Secure DB to Unsecured