Add additional table/fields/data in ERPNext script reports.

This document assumes you already know creating basic script reports. 

See a script report below, with fuel expense data for employees.

Now we are trying to add an additional table on the same report, with an additional comment below that.

We will display an additional table ( marked In red-box in the above pic) and some text ( marked in blue ) 

In the .py file, we are passing four variables to html.

These are four variables. 

return columns, data, message, chart, report_summary,data_to_be_printed

 columns 

data ( the original data ) 

 message ( message to be displayed on screen ) 

chart ( chart details ) 

report_summary ( summary you can see on the top ) 

data_to_be_printed ( Here we are passing additional table and additional text ) 

We can pass anything on data_to_be_printed variable to the report, see the following lines of code

---------------------------------------------

ext_data = get_salary()

 emp_data = get_emp_list()

data_to_be_printed={“ext_data”:ext_data,”condition_text”:’XXXXXXXXX This is an extra-data for testing XXXXXXXX .n’,”emp_data”:emp_data}

— — — — — — — — — — — — — — — -

def get_emp_list(): ( this function to pass a table data to report )

 data = frappe.db.sql(“””select

 emp.first_name as “first_name”,emp.date_of_birth as “dob”,emp.date_of_joining as “date_of_join” from `tabEmployee` emp

 “””,as_dict=True)

 print(‘data of empl : ‘,data)

 return data

— — — — — — — — — — — — — — — — — — — — 

def get_salary(): ( This is just a dummy data , in case you want to pass data other that SQL)

 emp_local_data=[]

 basic = 1000

 loop_range=20

 for i in range(loop_range):

 name = ‘Emp ‘+str(i)

 month=’April’

 salary = basic+i

 dict_value = {

 “name”:name,

 “month”:month,

 “salary”:salary

 }

 emp_local_data.append(dict_value)

 return emp_local_data

— — — — — — — — — — — — — — — 

Now let’s make some changes in .html file

------------------------------------------------

let emp_data = raw_data.skip_total_row.emp_data;

— — — — — — — — — — — — — — — — — — — -

<table class=”table table-striped table-bordered”>

<thead>

 <tr>

 <th>

 Name

 </th>

 <th>

 DOB

 </th>

 <th>

 Date of join

 </th>

 </tr>

 </thead>

<tbody>

{% for(let i=0;i<emp_data.length;i++) { %}

{% const local_emp_data =emp_data[i] %}

<tr>

<td>

 {%= local_emp_data.first_name %}

 </td>

<td>

 {%= local_emp_data.dob %}

 </td>

<td>

 {%= local_emp_data.date_of_join %}

 </td>

</tr>

{% } %}

</tbody>

</table>

— — — — — — — — — — — — — — — — 

now add an additional text in the bottom 

<small class=”text-right text-left” style=”font-style: italic;”>

 Note: {%= raw_data.skip_total_row.condition_text %}

</small>

— — — — — — — — — — — — — — — — — — — — — — 

We have uploaded all related files here. 

https://github.com/ERPGulf/docs/blob/main/my_report_r_001.py

https://github.com/ERPGulf/docs/blob/main/my_report_r_001.html

You can write to support@ERPNext.com if you need more clarification on the above.

-----------

You can find the variable skip_total_row  on chrome-debug. Helpful to troubleshoot . See the screenshot below.