Custom report against report builder

ERPNext Reports
1

Dears

i made that report using the report builder

consultation no
consultation no1219×586 52.6 KB

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

2

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…

grafik
grafik1171×638 29.2 KB

Hope this helps.

1 Like
3

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

Traceback (most recent call last):
File “/home/frappe/frappe-bench/apps/frappe/frappe/app.py”, line 62, in application
response = frappe.handler.handle()
File “/home/frappe/frappe-bench/apps/frappe/frappe/handler.py”, line 22, in handle
data = execute_cmd(cmd)
File “/home/frappe/frappe-bench/apps/frappe/frappe/handler.py”, line 53, in execute_cmd
return frappe.call(method, **frappe.form_dict)
File “/home/frappe/frappe-bench/apps/frappe/frappe/init.py”, line 939, in call
return fn(*args, **newargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/desk/query_report.py”, line 90, in run
result = [list(t) for t in frappe.db.sql(report.query, filters)]
File “/home/frappe/frappe-bench/apps/frappe/frappe/database.py”, line 166, in sql
self._cursor.execute(query, values)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/cursors.py”, line 165, in execute
result = self._query(query)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/cursors.py”, line 321, in _query
conn.query(q)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py”, 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/connections.py”, line 1061, in _read_query_result
result.read()
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py”, line 1349, in read
first_packet = self.connection._read_packet()
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py”, line 1018, in _read_packet
packet.check_error()
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py”, line 384, in check_error
err.raise_mysql_exception(self._data)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/err.py”, 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 .

4

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
5

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

6

I would recommend this as a useful reference manual, as MySQL queries are a wast world of options :wink: https://dev.mysql.com/doc/refman/5.7/en/select.html

1 Like
7

many thanks dear

8

can we make the output query clickable

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

9

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
10

many thx dear , for your guide and help

visits
visits1292×565 44 KB

=========================================

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 .

11

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

SUM(COUNT(patient))
or
SUM(Count)

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

12

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, 
        quantity, 
        something 
 FROM   tablename) 
UNION 
(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`)
UNION
(SELECT "all" AS `Patient:Link/Patient:200`, SUM(`Visits`) AS `Visits`
FROM `tabConsultation`);
1 Like
13

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

all
all888×467 16.5 KB

14

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

sum(`Visits`)
15

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

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

====================================

16

Try this

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

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.

18

Hi @sheno,

you are welcome! :wink:

For filtering refer to https://frappe.io/docs/user/en/guides/reports-and-printing/how-to-make-query-report.html

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": [
        {
            "fieldname":"item_code",
            "label": __("Item Code"),
            "fieldtype": "Link",
            "options": "Item"
        },
    ]
}

and

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

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