Filter for Cost Center & Projects in A/R & A/P reports

[Devens]

Account Receivable & Account Receivable Summary Report

• Filter for Cost Center (Logic - We use Cost Centers to separate out transactions for various Divisions. Person responsible for collectin for various division may be different and they should be able to see only relevant information).
• Filter for Sales Person (Logic similar to above)

Account Payable & Account Payable Summary Report

• Filter for Cost Center
• Filter for Project (Logic - Generally one project is one customer & Many Vendors. If a payment is received against a project and we need to distribute amount to Bills against this project only, this filter would be very useful).

[Devens]

Hello @PawanMeh, any update on this?

[Devens]

Alternatively, if you can suggest from which base report we can set filter and make custom report. (we need Account recivable report for a cost center wherein information can be available Party-wise and not sales invoice wise.

Please see following screenshot which explains what problem we face. From Sales Register (Sales Invoice List) we are trying to prepare account receivable report by putting filters as shown below. But, it shows mutlitple rows for each invoice (showing row for each item line).

Moreover in above report, we are not using any field from Sales Invoice Item table. Then also it shows item rows.

Could you please advise on this.

[Devens]

@manassolanki - Can I request to take this on little priority? (Urgency is only for A/P Summary to add filters for cost-center).

[nabinhait]

@Devens Implementing Accounts Receivable and Payable based on cost center / project / sales person is really difficult. Currently we don't tag a cost center / project to balance sheet accounts, it's only tagged to the income/expense account. The amount posted against customer/supplier account based on gross amount but the amount for income/expense account is taken for item level with the cost center value. Still I will give more thoughts on it, how we can achieve that.

And regarding the second problem, the rows are repeating because you have filtered based on cost center, which is a field from Item table. To group those into one row, you need to create a Query / Script Report.

[Devens]

@nabinhait - Thank you for your reply.

Generally, Sales Invoices are for "One Project" and "One Cost Center" only.

In case of Purchase Invoices also, in most of the cases, they are for One Project and One Cost Center only.

So, these fields are not required to put at Item level for Sales Invoice. For purchase invoice also, we believe that, it can be avoided to keep at Item level (or it should be optional at setup level).

We have tried to put "Project" and "Cost Center" field at document (Invoice) level (pl see following screenshot. This would serve the purpose for outstanding reports (Pl see second screenshot).

We suggest to provide "Customization" in standard reports also. So, we can put filters (and alter columns) like this and resolve the issues.

[SaiFi0102]

I am working on this

[SaiFi0102]

#17321

[Anurag810]

#17668

[wildman-web]

Project Filter on js file:
{
"fieldname": "project",
"label": __("Project"),
"fieldtype": "Link",
"options": 'Project'
},

Account Receivable Report Python File:

Line:357(approx.)
project field is fetching from sales invoice
def get_invoice_details(self):
self.invoice_details = frappe._dict()
if self.party_type == "Customer":
si_list = frappe.db.sql(
"""
select name, due_date, po_no ,project
from tabSales Invoice
where posting_date <= %s
""",
self.filters.report_date,
as_dict=1,

Project Filtering before data return

line:55(approx)

def run(self, args):
self.filters.update(args)
self.set_defaults()
self.party_naming_by = frappe.db.get_value(
args.get("naming_by")[0], None, args.get("naming_by")[1]
)
self.get_columns()
self.get_data()
self.get_chart_data()
# print(f'\n{self.data}\n')
if self.filters.get('project'):
new_data=[]
for data in self.data:
if data.get('project') == self.filters.get('project'):
new_data.append(data)
self.data=new_data

	return self.columns, self.data, None, self.chart, None, self.skip_total_row