Hi Everyone,
Specially
@rmehta
@nabinhait
@anand
@saurabh6790
Is there any document which tells how to write a script report.
The below link is not sufficient as the Balance Sheet example document is missing.
https://frappe.github.io/frappe/user/en/guides/reports-and-printing/how-to-make-script-reports
Regards
Ruchin Sharma
Ruchin
Please don’t ask question pointed to specific users.
The mentioned link is sufficient for creating script reports on erpnext.
Either be specific what are you not able to understand since documents are always generic and cannot be specific to each script report in specific.
If you want to create a report like balance sheet then you should check the code for the report in the repository.
@adityaduggal thanks for your reply.
I am worried because the document is not self-explanatory.
A document should be designed in a way that anyone can easily understand what to do and where to do.
You are the exceptional case but its not easy for everyone to understand well.
My query is, I want to write a script report using SQL Queries, if you look into the balance sheet report there is lots of stuff which I am sure will not be understood by lots of people in the group.
It is just wasting of time with for understanding the things where you have no reference document and most of the people do this, they just copy and paste the code and do some minor changes. In my opinion the document should be self-explanatory by which anyone can easily understand what to do, where to do and how to do.
As far as pointing to someone specific is concern, I apologize for that.
@adityaduggal I appreciate if you can reply my query.
@ruchin78 Please note that frappe/erpnext documentation is mainly about features of frappe and erpnext and not about the coding languages like python or html or jinja and I guess you are asking for a documentation of those which is not going to be there as the document of frappe and erpnext assumes that you would be well aware about the structures in python, javascript etc.
Honestly, I am no exception as I had the same issues like you (infact I think you know a lot more coding than me) but I guess for concepts on python and js or some other things you would have to refer to the documentation of python.
You have still not defined what problem you have been facing in the 2 posts you have made, I would hence request you to kindly be specific as to what exact issue are you facing in creating a Script Report.
Edit: I just think you need a documentation on how to write script report then I guess you would need to google and learn python which I guess has its learning curve.
@adityaduggal
Debating on documentation part is wasting of time. We can discuss this topic sometime later or may be in one to one communication. My problem is I have a time logs defined and I want to use a row for “Total”.
As I have created the report using SQL Query therefore I am not able to add the “Total” in the report therefore I want to write a script report for the same.
Here is my query for the same.
SELECT
t.employee as "Employee:Link/Employee:120",
e.employee_name as "Employee Name::160",
t.status as "Document Status::120",
t.project as "Project:Link/Project:200",
t.task as "Task:Link/Task:120",
YEAR(t.from_time) as "Year::70",
MONTH(t.from_time) as "Month::70",
WEEK(t.from_time) as "Week::70",
sum(t.hours) as "Hours Spend:Float:120"
FROM `tabTime Log` t,`tabEmployee` e
WHERE t.status='Submitted'
AND t.employee=e.employee
AND e.status='Active'
AND DAYOFWEEK(t.from_time)>=1
AND DAYOFWEEK(t.to_time)<=7
GROUP BY t.employee,
e.employee_name,
t.project,
t.task,
WEEK(t.from_time),
MONTH(t.from_time),
YEAR(t.from_time)
I appreciate if you can help me in this regard.
I tried it but it is not working as well.
@ruchin78 I have tested your query in my local account and it shows Total row and values in the report. Obviously, I have checked “Add Total Row” in the Report setting.
Here are some other links about reports:
https://frappe.github.io/frappe/user/fr/guides/reports-and-printing/how-to-make-script-reports.html
Hi @nabinhait,
Thanks a lot, you are great man, I don’t know what magic you did but trust me earlier it was not coming can’t say what was the cause.
Also
Thanks @sagar and @adityaduggal
But I appreciate if anyone can explain me about script report.
Regards
Ruchin Sharma
Now, I got it, it does’t work when you filter the data for specific employee or for specific week/month etc. That is my worry and that is why I want to write a script report.
@ruchin78 You have entered 43 in the filter in the second screen shot in your week column that is why the total is missing. It’s quite self explanatory.
@adityaduggal
Yes, that is what I am asking for. I don’t want my total to hide at any point of time that is why I want to create a script report for the same. That means the provide solution doesn’t solve the purpose of total.
Regards
Ruchin Sharma
afaik this is not currently possible even in script reports…topic closed.
Hi Everyone,
I have posted a topic for writing a custom report in ERPNext.
Below is the link for the same.
It is my humble request to @adityaduggal that if you are not sure about anything then please do not close the topic let other also participate in it.
I added total by clicking “Add Total Row”
It is coming but when I filter the data it goes out, I have found the way that it is possible via Script Report but I have not idea how to do this via script report.
Below is the example where I saw it is possible
can anybody help me to achieve this with my query.
@ruchin78 First of all thanks for the comments.
Now if you need to filter out results then you need to write the script report which was easily answered in your previous post.
Understand one basic thing: Script Reports have 3 basic files, suppose we take the example of this mentioned report “Stock Balance”
stock_balance.js This is the Javascript file which would add the filters marked (Added Filter)
stock_balance.py This is the python file which would have your python code along with the mysql query.
stock_balance.html This is the html file where you can define the print format of the report.
Honestly I am still not getting where exactly are you stuck since the last post link posted by your for help file easily explains how to add filters.
Edit: I see that you have created now 3 topics for somewhat similar questions, I would request you once again to refrain from doing so as its not a good sight to see a lot posts which are of same nature again an again.
I don’t know where to write my query in the python file.
See the below code of stock_balance.py
from __future__ import unicode_literals
import frappe
from frappe import _
from frappe.utils import flt
from erpnext.selling.doctype.customer.customer import get_customer_outstanding, get_credit_limit
def execute(filters=None):
if not filters: filters = {}
#Check if customer id is according to naming series or customer name
customer_naming_type = frappe.db.get_value("Selling Settings", None, "cust_master_name")
columns = get_columns(customer_naming_type)
data = []dd
customer_list = get_details(filters)
for d in customer_list:
row = []
outstanding_amt = get_customer_outstanding(d.name, filters.get("company"))
credit_limit = get_credit_limit(d.name, filters.get("company"))
bal = flt(credit_limit) - flt(outstanding_amt)
if customer_naming_type == "Naming Series":
row = [d.name, d.customer_name, credit_limit, outstanding_amt, bal]
else:
row = [d.name, credit_limit, outstanding_amt, bal]
if credit_limit:
data.append(row)
return columns, data
def get_columns(customer_naming_type):
columns = [
_("Customer") + ":Link/Customer:120", _("Credit Limit") + ":Currency:120",
_("Outstanding Amt") + ":Currency:100", _("Credit Balance") + ":Currency:120"
]
if customer_naming_type == "Naming Series":
columns.insert(1, _("Customer Name") + ":Data:120")
return columns
def get_details(filters):
conditions = ""
if filters.get("customer"):
conditions += " where name = %(customer)s"
return frappe.db.sql("""select name, customer_name from `tabCustomer` %s"""
% conditions, filters, as_dict=1)
There is lots of stuff which I really don’t understand what is the use of these.
Hi,
Below is my screenshot of my report code as well as the output but I am not getting the total at the end.
Hi @ruchin78,
you have to write
t.hours as “Hours:Float:100” as like.
please use reference report in the report list i.e. “Item-wise Sales History”
Sagar Shiragawakar.
New Indictrans Technologies Pvt. Ltd, Pune
