Statistical values on a report based on numerical fields

Statistical values on a report based on numerical fields

Requirement

Display the common statistical results on the reports for the numerical fields in a form, to help in identifying trends and decision-making process.

Use Case

In any sales-based business, crunching numbers is a big task. An order management application has two forms, Employee and Order. The Employee form holds the details of the employees and sales agents working for the business, and the Order form holds the order details. An administrator wants to find the total, average, minimum and maximum sales performance of an agent, which will help to decide on sales, marketing and restocking.
See how it works

Steps to follow

1. Create the forms with the following details:
Form
Form Link Name
Field Type
Field Name
Field Link Name
Employee
Employee
Name
Employee Name
Employee_Name
Email
Email
Email
Order
Order
Date
Order Date
Order_Date
Lookup (Employee)
Sales Owner
Employee
Currency
Total Amount
Total_Amount
Subform
  • Single Line
  • Number
Order Details
  • Product Name
  • Quantity
Order_Details
  • Product_Name
  • Quantity

2. Create a list report on the Order form and name it Monthly Orders.

3. Open Report Properties. Under the Filter section, add a filter to Monthly Orders to show only the orders of this month:
 
4. Group the records in the Monthly Sales based on the Sales Owner lookup field:
 
5. Under the Design pane for the Monthly Sales report, under Quick View, select the first layout.
 
6. Under Configure Fields, click on the Total Amount field. Select the Total, Average, Min, and Max toggle boxes.
 

This will display the sales done every month by the agents and display their total, average, minimum, and maximum sales.

See how it works

Points to note

  • Grouping and Sorting can be done directly on the report. Mouse over the column header in the report and click the drop - down menu next to each field name for the list of options. Select the Group or Sort option as per the requirement, and also specify if the records must be in an ascending or descending order.