Script Report with filters that get data using SQL

ERPNext Reports
1

Has anyone tried the above? I am able to create a Script Report by writing code in the “Query/Script” section and “Client Code” section of the Edit report UI. Please note I am not writing any code in python or JS files. All the code is written in the edit report UI.

Now, the filters are defined in the “Client Code” section using Javascript like below:

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
posting_months = [“April 2024”, “May 2024”]

frappe.query_reports[“Cost Center wise Breakup”] = {
“filters”: [
{
fieldname: “Vertical”,
label: “Vertical”,
fieldtype: “Data”
},
{
fieldname: “Project”,
label: “Project”,
fieldtype: “Data”
},
{
fieldname: “Cost Center”,
label: “Cost Center”,
fieldtype: “MultiSelectList”,
get_data: function(txt) {
return ['Admin/Overhead ', 'Curriculum ', 'Events/Showcase ', 'Finance ', 'Fund Raising ', 'HR ', 'L&D ', 'Marketing ', 'Monitoring & Evaluation ', 'Operations ', 'Process Excellence ', 'Product ', 'Team Awards ', 'Technology ']
}
},
{
fieldname: “posting_month”,
label: “Posting Month”,
fieldtype: “MultiSelectList”,
options: posting_months
}
]
};
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

In the above code if I want to get the values for the multiselect using a SQL with joins, how would I do that? I haven’t seen any examples. Every example uses a single doc type to get the values. I tried frappe.db.get.sql, but it doesn’t seem to work. I understand this is primarily JS code and hence some of the frappe APIs may not work. What are the options to get the data? Can I read data in the report and get values from that using the client side script?

Thx

2

Please check the reference:

3

Hi Nihantra, thanks for the response. That solution doesn’t work for my requirement since I need to join 2-3 table to get the result I want. It will work only for a single doc type, right? Is there a way to refer to the server script’s output from the client script? If yes, I can get the values using server script and refer to them from the client script to achieve what I want.

4

That for, you have to build the server script report in the custom app.

5

Can you tell me how to go about doing that? I have my site in a private bench. How do I access the server code?

6

First, you need to create a custom app, then develop the custom server script report, deploy your app in your private bench, install the app on your site, and migrate it.