Issues with Dashboard

ERPNext Reports
#1

I have created a dashboard based on a reasonably complex sub-query.

Chart used was:
Custom,
Chart Source: I created a chart source and the dashboard is showing the chart.

I used datasets = frappe.db.sql(s_sql, as_dict=1) to extract data.

The query works perfectly in mysql and returns 10 rows.

However, when I look at the chart, there is only one row.
a. It takes the first project (as label(
b. It sums all the numeric values

So I get a dashboard with a single bar chart.

I have whitelisted the function. I’m not sure why I’m getting a single row.

#2

def get_flow_measurement(projects, filters):
# frappe.msgprint(projects)
filter_doc_status = “”" (0, 1) “”" if filters[‘show_drafts’] == 1 else “”" (1)“”"
filter_projects = “('” + “', '”.join([row[‘name’] for row in projects]) + “')”

# frappe.msgprint(filters['to_date'])
# frappe.msgprint(filters['from_date'])
# frappe.msgprint(filter_projects)

s_sql = f"""
            select  
            twm.customer
            , twm.project  as project
            , format(sum(twm.calculated_daily_volume),1)  as monthly_volume
            , sum(twm.num_readings) as num_readings
            from ( 
                    select  
                        customer
                        , 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}
                    and tw1.project in {filter_projects}
                    and tw1.measurement_date__time between \'{filters['from_date']}\' and \'{filters['to_date']}\'
                    group by date_format(measurement_date__time,"%%Y-%%m-%%d")
                    order by project, measurement_date__time 
                    
                )
            as twm
            group by customer, project
            order by customer, project
            """

# frappe.msgprint(s_sql);
datasets = frappe.db.sql(s_sql, as_dict=1)
# print(datasets)
s1= ", '".join(row['project'] for row in datasets)
frappe.msgprint(s1)
# frappe.msgprint(frappe.parse_json(datasets[0]['project']))

# axis_labels_dict=dict()
axis_labels = flow_volume_list = num_readings_list = list()
i=0
# list of projects and volume for the period
for row in datasets:
    axis_labels.append("'"+ row['project'] + "'")
    flow_volume_list.append(row['monthly_volume'])
    num_readings_list.append(row['num_readings'])

return flow_volume_list, num_readings_list, axis_labels

THIS is the Actual output
image

This is the expected Output
This was created ‘based on a query report’. Only problem here is that I can’t create filters.
image

#4

Error in SQL all along.

s_sql = f"“”
select
twm.customer
, twm.project as project
, format(sum(twm.calculated_daily_volume),1) as monthly_volume
, sum(twm.num_readings) as num_readings
from (
select
customer
, 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}
and tw1.project in {filter_projects}
and tw1.measurement_date__time between '{filters[‘from_date’]}' and '{filters[‘to_date’]}'
group by customer, project, date_format(measurement_date__time,“%%Y-%%m-%%d”)
order by customer, project, measurement_date__time

                )
            as twm
            group by customer, project
            order by customer, project
            """