Script Report - Done!

After a lot of research, trial and error, I got the script reports working. .

    • Create a custom module (see the creating a custom app/fixtures process)
    • Create a report [New Report]. Set the Module as the custom module. Set ‘Standard’=Yes.
    • Go over to the custom module folder /frappe-bench/apps/cspl/cspl/cspl/report/monthly_fab_report - you might need to create the last folder
    • After this it is a question of tinkering with the various functions to get the result you need.

One thing I learnt is that I prefer to pass F-strings to frappe.db.sql rather than using the method used in client_statement.py

so a typical function would look like:

def get_data(filters):

filter_doc_status = """ (0, 1) """ if filters.show_drafts == 1 else """ (1)""" ;
print(filter_doc_status);
filter_cust = """ and tw1.customer = '""" + filters.customer + "'" if filters.customer else ""

s_sql = f"""
		select  
		twm.project , date_format(twm.measurement_date__time, '%%Y-%%m') as month , 
		format(sum(twm.calculated_daily_volume),4)  calculated_daily_volume, 
		sum(twm.num_readings) as num_readings 
		from ( 
				select  
					docstatus, project 
					, date_format(measurement_date__time,"%%Y-%%m-%%d") as measurement_date__time 
					, format(avg(calculated_daily_volume),4) as calculated_daily_volume 
					, count(calculated_daily_volume) as num_readings  
				from `tabWater Height Measurement`  tw1
				where 
				tw1.docstatus in {filter_doc_status}
				{filter_cust}
				and tw1.measurement_date__time between \'{filters['from_date']}\' and \'{filters['to_date']}\'
				group by project, date_format(measurement_date__time,"%%Y-%%m-%%d")
				order by project, measurement_date__time 
			)
		as twm 
		group by twm.project, month
		order by twm.project, month """;

print(s_sql);
return frappe.db.sql(s_sql,filters, as_dict=1)
1 Like

An odd artifact that I observed was that filters.show_drafts works in the report, but does not work in the bench console.

You have to use *filters[‘show_drafts’]