Auto Email Report - Sales Register - What am I doing wrong?

[bkm]

(using ERPNext v10 Production)

I have configured an auto email report to send me the Sales Register report every night.

The report filters are configures with the specific “Owner” and “Company” that I wish to have data sent. I left the “From Date” and “To Date” empty so they could be auto-populated by the report with the current date. (This is how the report works when accessing it directly in ERPNext)

The result is that I get NO report email. No email is even entered into the email queue.

So, after 2 nights of no emails, I went back to the report filter for the Auto Email Report. When I access the filter settings, the “From Date” and the “To Date” automatically populated themselves to the current date. This made me think that I had been over-thinking the original settings. So, I allowed the dates to default to the current date and saved the settings. That was on Tuesday.

The result is that on Wednesday morning I had an email with Tuesdays Sales Register report. This was a promising result.

However, on Thursday, I received another Sales Register auto email report, but is was again with the data for Tuesday.

Now on Friday, I have also received another Sales Register report containing only Tuesday data.

This tells me that the act of allowing the report filter to auto assign the dates on Tuesday forced the report to ALWAYS and ONLY report on Tuesdays data.

So, What am I missing here? What is it that I am doing wrong?

I want the Sales Register report to be sent to me for a particular owner and company every night containing the data for the day. However, I am unable to find a way to allow this to happen in the Auto Email Report function.

I must be missing something… am I right?!?

This is the intended purpose of this Auto Email report function… is this correct?

Please advise.

BKM

[bkm]

Really?!?

Certainly someone here has had success with this report where I am failing. Just point me to where I am going wrong.

BKM

[hereabdulla]

It may help you

@frappe.whitelist()
def send_daily_report():
    custom_filter = {'date': add_days(today(), -1)}
    report = frappe.get_doc('Report', "Employee Day Attendance Report")
    columns, data = report.get_data(
        limit=500 or 500, filters=custom_filter, as_dict=True)
    html = frappe.render_template(
        'frappe/templates/includes/print_table.html', {'columns': columns, 'data': data})
    frappe.sendmail(
        recipients=['xxx@xx.com'],
        subject='Employee Attendance Report - ' +
        formatdate(add_days(today(), -1)),
        message=html
    )

[bkm]

@hereabdulla

It may help you

I am sorry. I do not completely understand your reply.

It appears that your code snippet defines a custom filter for a report, calls out the report name, and then sends it by email.

Is this some sort of script to send the report for me without using the GUI in Auto Email Reports?

If this is a custom script for sending my specially configured report, then how might I add (what syntax) additional filter details to the “custom_filter” line? For instance I need to report to generate with the start and end date of the current day, and specify a particular owner and company.

If I send this report with several different combinations of filters does anything in the script need to change (aside from the “custom_filter” line)? Can I have multiple editions of this script with different filter data?

And finally, is this the entire script? If so, then where do I save it in the system and what naming convention do I use to save it do that it gets processed every night?

Sorry for so many questions, but I have never created anything like this except simple bash scripts that I run as cron jobs, so I just want to make sure I know where everything goes, how it is supposed to be named, and what permissions it would need.

BKM

[bkm]

I think the most scary part about this, is the fact that nobody seems to think it unusual that this cannot be done from the Auto Email Report function in the user interface.

The silence there is deafening.

If this really cannot be done in the Auto Email Report interface, why is it not an active developer fix for this?

This is one of the basic functions of an ERP system. It should be able to handle a simple repeating report with a defaulted date range.

Or am I over reacting here? Is there really a way to do this in the system without having to create custom scripts, and I am just not seeing it yet?

BKM

[charlie-cook]

I have several email reports setup - daily, weekly, monthly. I would need to see a screenshot of how you did this in order to possibly assist further.

[olamide_shodunke]

I had the same need some months ago and faced the same problem.

I think we may need to look at a fix for it, Like you said it is a basic requirement.

Regards

[bkm]

@charlie-cook

I would need to see a screenshot of how you did this in order to possibly assist further.

Ok… Here it is.

First let us see how the report looks when selected for immediate printing.
If I were to select the Sales Register Report from the list of standard ERPNext reports it would look like this:

You can see that the report defaults a one month range for the start and end dates. When I use this report normally, I set both the start and end dates to the sames date to get only yesterdays data. I also populate the “Owner” field with the user that I am interested in tracking their sales for the day.

Since this is a daily requirement for me to monitor the daily sales effectiveness of each of the sales users, I wanted to set this up in the Auto Email Report function. That way I can still get the data sent to me even when I am traveling and not at the office to manually pull the reports.

To that end, I created a NEW Auto Email Report and it starts out looking like this for the initial configuration:

Notice the upper left corner it says “Not Saved” next to the report name. That is because after the initial save it requires the report filters to be specified. As soon as you click anywhere on the filter section you get the following pop-up:

Here again you can see the date defaulted to a one month range. If I set the from and to dates to be yesterdays date (09-29-2018) and the owner to be Tom@myemail.com, then I will get a Sales Register report every night for Tom and it will ALWAYS have the data from 09-29-2018 forever!

This is the problem. This is a daily reporting requirement and yet it seems impossible to automate from the Auto Email Report function (as apparent in the screen shots).

Oh yeah… I guess that I should also include that if you force the dates to be blank (in hopes of letting it use default values) then the report is never generated. I tried that as well.

BKM

[bkm]

@hereabdulla Thank you for your code snippet. Please let me know if you can provide any further details about how to get it in place.

Thanks - BKM

[bkm]

@olamide_shodunke

I had the same need some months ago and faced the same problem.

Did you even find a work-around for this? Or did you do something similar to the suggestion above about creating a custom script?

BKM

[charlie-cook]

Interesting. So… it looks like you need to duplicate the Sales Register report and give it a name like “Sales Register - Tom” or something. NOTE: it must be a CUSTOM (not standard) report.

Then, go to Setup Auto Email Report and since you are using a CUSTOM report (not a standard), you’ll see a field appear like this:

Choose “send only if there is any data” and put 24 in the next field. This way, you’ll only get data that has been updated in the last 24 hours. You’ll get it at midnight every night. I have several daily reports setup like this and it works great. Make sure you don’t set any date filters on your custom report - just leave it blank and then you’ll get the email with only data created from the last day.

Hope that helps!

[bkm]

@charlie-cook

Interesting. So… it looks like you need to duplicate the Sales Register report and give it a name like “Sales Register - Tom” or something. NOTE: it must be a CUSTOM (not standard) report.

Yeah… I figured it would be that way, but I cannot get a custom report to work.

When I call up the standard “Sales Register Report” as an Administrator, and then select “Duplicate” from the menu, It gives me the chance to apply a new name for the report and to change it change “Standard Report” from YES to NO and then save it.

Once it is saved and I go back to the report list and call up the new “Tom Sales Register” there are no columns in the report!!!

Here are the steps I followed in screenshots:

1. Open a Sales Register report and Select “Duplicate”
   
   
   
   .
   .
2. Change the report name to “Tom Sales Register”
   
   
   
   .
   .
3. Change “Is Standard” from Yes to “No”
   
   
   
   .
   .
   As soon as you actually click on the “No” the screen changes.

• the “Roles Permissions” section disappears
• a new filed shows up for “Letter Head”
  
  
  
  .
  .

4. Click on the blue “Save” button in upper right and you get this:
   
   
   
   .
   .
5. Click on the grey “Show Report” button in upper right and you get a report with no columns!!!
   
   
   
   .
   .
   So, at this point I am at a loss for what to do. I do not see a path to generating a functional custom report that is a copy of the Sales Register report.

What have I missed?

BKM

[charlie-cook]

Hmm… did you try just adding the columns you need to that report? There’s a selection under Menu to Select Columns.

[bkm]

@charlie-cook

There’s a selection under Menu to Select Columns.

Hmm… I do not remember that being an option under the “Menu” but I will look again when I get bak to the office. I will update here once I try it.

BKM

[charlie-cook]

in v10, it’s actually a button that says “pick columns”

In v11, it’s under Menu.

[bkm]

@charlie-cook

n v10, it’s actually a button that says “pick columns”

Well then, that settles it. There is no “Pick Columns” button as evidenced by the screenshots in the thread above. So there must be something else missing.

BKM

[suchita]

Hey did you find any solution for this. If yes please help

[Chinjuprotech]

@suchita @bkm
Any solution why auto email report is not sending?

[yashodhan]

are you sure you have scheduler enabled for site? check that first if you are self hosted.

[Chinjuprotech]

@yashodhan

How can i check that?

Other Notifications email & Daily Digest are getting. Only issue with Auto Email Report.

kindly advise