Schedule a monthly email with report as pdf

Schedule a monthly email with report as pdf

Requirement

Schedule a monthly sales report to the sales manger in a PDF with stats on achieved sales versus target for the month.

Use Case

A sales management app records sales activities and numbers to track the performance of its sales team and the overall business. The sales manager needs a monthly report that graphs the achieved sales as compared to the target, in a PDF.

Steps to follow

1. Create two forms with the following details:
Form
Form Link Name
Field Type
Field Name
Field Link Name
Sales
Sales
Single Line
Sales Rep
Supplier_Name
Name
Customer Name
Customer_Name
Subform
  • Single Line
  • Number
Order
  • Product
  • Quantity
Order
  • Product
  • Quantity
 
Currency
Total Cost
Total_Cost
Target vs Achieved
Target_vs_Achieved
Date
Date
Supplier_Name
Currency
Target Sales
Target_Sales
Currency
Achieved Sales
Achieved_Sales


The Target vs Achieved form is used for the monthly reporting. The Date and Target Sales field values are provided manually every month. The Achieved Sales field's value is calculated by adding up all the values in the Total Cost field for each month.

2. Create a Pivot Chart report named Forecast vs Actual based on the form Target vs Achieved, with the settings as displayed below. Plot the Date field on the X-Axis, and Target Sales and Achieved Sales fields on the Y-Axis.


3. Configure a schedule with the following details: 

4. Click Add New Action and select Deluge Script.

5. Save the following Deluge snippet in the Deluge editor:
  1. // Fetch all records from Sales form for the current month

  3. sales_records = Sales[ID != 0 && Date_field in this month];

  5. // Calculate the total sales
  6. total_sales = sales_records.sum(Total_Cost);

  8. // Fetch records from Target_vs_Achieved form and sort in descending order to have the latest record (current month) on top
  9. forecast_records = Target_vs_Achieved[ID != 0] sort by Added_Time desc;

  11. // Assign the total sales value to the Achieved Sales field in the first record
  12. forecast_records.Achieved_Sales=total_sales;

  14. // Send mail with the attachment to the sales maneger 
  15.  sendmail
  16. [
  17. from: zoho.loginuserid
  19. subject: "Monthly Report"
  20. message: "Please find the attached monthly sales report"
  21. attachments:report:Forecast_vs_Actual as PDF
  22. ]

The script calculates the total sales for the month, and assigns the value to the Achieved Sales field. The report containing this value and the forecast value is then sent as an attachment.

See how it works      

Points to Note 

  • It is recommended to place the send mail snippet in a separate scheduler with some time gap. This ensures there is sufficient time for fresh data to be generated with new calculations in the Pivot Chart before the attachment is sent.
  • Pivot Charts can only be sent in a PDF. Learn more about the send mail task and the supported formats for various report types.