Custom report against report builder


i made that report using the report builder

as we see Patient PID-00001 visit 4 time

i need to get another filed or another colume which analysis that report and get the number of the visit per patient

so i will get

Pation ID no

PID-00001 4
PID-00002 1
PID-00007 1
PID-000013 1

thanku for help

Hi @sheno,

as far as a I know this is more than what the Report Builder will do. But for cases like this, why not use a SQL report? This provides all features that SQL has. Maybe something like this:

SELECT `patient` AS `Patient`, COUNT(`name`) AS `Count` FROM `tabConsultation` GROUP BY `patient`; 

should give you a report as you wanted…

Hope this helps.

1 Like

Thanks Alot Dear for Your Guide
actually i got this error.

Traceback (most recent call last):
File “/home/frappe/frappe-bench/apps/frappe/frappe/”, line 62, in application
response = frappe.handler.handle()
File “/home/frappe/frappe-bench/apps/frappe/frappe/”, line 22, in handle
data = execute_cmd(cmd)
File “/home/frappe/frappe-bench/apps/frappe/frappe/”, line 53, in execute_cmd
return, **frappe.form_dict)
File “/home/frappe/frappe-bench/apps/frappe/frappe/”, line 939, in call
return fn(*args, **newargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/desk/”, line 90, in run
result = [list(t) for t in frappe.db.sql(report.query, filters)]
File “/home/frappe/frappe-bench/apps/frappe/frappe/”, line 166, in sql
self._cursor.execute(query, values)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/”, line 165, in execute
result = self._query(query)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/”, line 321, in _query
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/”, line 860, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/”, line 1061, in _read_query_result
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/”, line 1349, in read
first_packet = self.connection._read_packet()
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/”, line 1018, in _read_packet
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/”, line 384, in check_error
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/”, line 107, in raise_mysql_exception
raise errorclass(errno, errval)
ProgrammingError: (1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘tabConsultationGROUP BYpatient`’ at line 1"

waiting your view dear .

The error message looks like there are some ` missing (sorry, the ending ` of Count was missing in my post :see_no_evil:). Make sure you have exactly this (note that ` and ’ are not the same in SQL)

SELECT `patient` AS `Patient`, COUNT(`name`) AS `Count` 
FROM `tabConsultation` 
GROUP BY `patient`;

To troubleshoot, you can also enter the MariaDB console using

$ cd /home/frappe/frappe-bench && bench mysql

Or, if you prefer UI tools, have a look into MySQL Workbench.

1 Like

many thanks dear for your guide and help . it works well.

if there is a guide for using the erpnext report query
it will be awsome
i found this but its not exist

many thanks dear

I would recommend this as a useful reference manual, as MySQL queries are a wast world of options :wink:

1 Like

many thanks dear

can we make the output query clickable

like the normal report we can click the result and it lead me to the document

Dear @sheno,

sure, have a look at this:

SELECT `customer` AS `Customer:Link/Customer:200` FROM `tabSales Order`;

or in your case

SELECT `patient` AS `Patient:Link/Patient:200`, COUNT(`name`) AS `Count`
FROM `tabConsultation`
GROUP BY `patient`;
1 Like

many thx dear , for your guide and help


SELECT patientAS ID:Link/Patient:200 ,patient_nameAS Name,email,city_ AS city, COUNT(city_) AS Location, COUNT(patient) AS Visits
FROM tabPatient Appointment
GROUP BY city_ , patient
ORDER BY patient ASC;

can we add a total row where can sum the visit under visits and Locations under locations

best regards .

i searched a bout SUM function but i found its sum fileds of colume that is already exist in the table
but the
COUNT colume that we got from the query its not a colume as i think so the SUM function cant deal with

am i right ?

even i attepted to make some thing like this


But it didnt work because it cant consider COUNT as a colume as i guess.

There are two options. Frappe has a built-in function “add total row”, which should do exactly this. However, sometimes it might not work. In that case, use something like this:

(SELECT product, 
 FROM   tablename) 
(SELECT "all"          AS product, 
        SUM(quantity)  AS quantity, 
        SUM(something) AS something 
 FROM   tablename) 

so in your case

(SELECT `patient` AS `Patient:Link/Patient:200`, COUNT(`name`) AS `Visits`
FROM `tabConsultation`
GROUP BY `patient`)
(SELECT "all" AS `Patient:Link/Patient:200`, SUM(`Visits`) AS `Visits`
FROM `tabConsultation`);
1 Like

SELECT patient AS Patient:Link/Patient:200, COUNT(name) AS Count
FROM tabConsultation
GROUP BY patient
SELECT “all” AS Patient:Link/Patient:200, SUM(name) AS Count
FROM tabConsultation;

Count will count the number of occurences, sum will add up numbers. You will want to sum the “Visits”


(1054, u"Unknown column ‘Visits’ in ‘field list’")

SELECT patient AS Patient:Link/Patient:200, COUNT(name) AS Visits
FROM tabConsultation
GROUP BY patient
SELECT “all” AS Patient:Link/Patient:200, SUM(Visits) AS Visits
FROM tabConsultation


Try this

SELECT `patient` AS `Patient:Link/Patient:200`, COUNT(`name`) AS `Visits`
FROM `tabConsultation`
GROUP BY `patient`
SELECT "all" AS `Patient:Link/Patient:200`, COUNT(`name`) AS `Visits`
FROM `tabConsultation`;

Many thanks dear. It works awesome.

By the way dear

How can we use filter in query report
For example.
It’s like the filter fields in the report where we can for example filter based months as a time
Or filter based on location.
Because I noticed that the query report give us Template which has only one result
But if we add to the query the ability of filtration. So the report will be dynamically result change🔀 based on user filter need.

Hi @sheno,

you are welcome! :wink:

For filtering refer to

Simply add a (your report name).js file in the same folder and add the filter, then extend the SQL query to match. Something like

frappe.query_reports["Accounts Receivable"] = {
    "filters": [
            "label": __("Item Code"),
            "fieldtype": "Link",
            "options": "Item"


SELECT ... FROM ... WHERE item_code = %(item_code)s ORDER BY ...

Many :pray::two_hearts: dear. I will test it and feed u back.