Custom P&L report

Hi

ERPNext: v12.10.1 (version-12)

Frappe Framework: v12.8.4 (version-12)

I have been spending the past few weeks ( or is it months !) entering transactions into a test system … observing and making adjustments to my setup … clearing all records and then
beginning again. This al so that I can understand how ERPNext does things.

Oh… before I forget … I am planning to use my full production system in a Manufacturing
environment.

I have spent quite a bit of time looking at the manufacturing cycles ( multi-level-BOM ) and
have found a few things that required changing in my setup.

All is good with the exception of one thing …
Within my production environment, I would prefer if my P&L statement does not post the
“Expenses included in valuation” account (5118). This is basically my cost of producing
the product. This cost is already included in the “Cost of good sold” and if I now subtract that ,
I am left with only the expense (cost) of the material.

Below a screenshot of my P&L.

I have been searching on the net and looking at videos about custom scripting to try and
figure out what the best way would be. I have never done coding on ERPNext although
I have a lot of experience in embedded coding (C).

I have even been going through the code on the server ( I am doing self-hosting ) and was
tempted to try and modify the code but I know to little about the way the code works and
fits into the rest of ERPnext. Also, is it not better not to modify core-module-code?

Could someone perhaps provide me with some guidance as to the best way to achieve this?

Thanks

Hi John,

I don’t have easy solutions to offer. But I’ll share some info and advice.

Unlike other ERPs I’ve worked in, ERPNext’s accounting reports are not very “configurable”. There’s no place to specify exactly how your Profit and Loss Statement should look and work. For example, which GL accounts to include/exclude, show in another section, combine with other accounts, etc.

Instead, the P&L Statement Report in ERPNext follows the Chart of Accounts structure rather precisely. To move things around the report, you probably must modify the COA. However, if you modify the COA…that could change how the ERP behaves in terms of validation and posting! :frowning:

Next challenge: The P&L statement is one of the more complex reports in ERPNext. It’s a “Script Report”, and has quite a lot of Python code behind it.

If you haven’t found it already, the path to the P&L Script Report’s code is here.

However, during execution, that code in turn calls other code. Which is very relevant to the report’s output. There’s a lot going on, and plenty of Python to decipher, to understand how it all works. :thinking:

Options I can think of:


Option 1. Attempt to modify the report’s Python code.

Tricky stuff. You possibly only need to add a line or two of Python, filtering out Account 5118. But like a lot of hacking, sometimes the challenge isn’t knowing what code to write. But where to write it. In other words, you need to hide 5118 carefully, so that P&L totals and roll-ups are accurate when you’re done.

The question of whether to touch/not-touch core code is worth another thread/discussion. But most likely, you probably want to -copy- the standard script report, and make your own, cloned version. That way you don’t have to worry about maintaining a fork.

If you’ve never created your own Script Report before? Well, this is probably one of the worst ones to learn from, because it’s complex. :exploding_head:. I recommend you experiment with something easier, to help you learn them.


Option 2. Write your own version using a Query Report.

One of the reasons the Profit and Loss Statement has so much Python code? Because it’s a “one-size-fits-all” report solution. It has filters for Finance Books, Projects, Cost Centers, Year vs. Month vs. Quarter, currency, etc. It’s a lot to handle.

Your own business may not need all those options. Or at least not simultaneously. If you take them away, and focus on P&L fundamentals, the data and logic behind the report becomes much simpler to grasp and manipulate.

So simple, in fact, that you can probably create your own Query Report that produces the same results. But enables you to more-easily filter out particular accounts, or move things around.

Query Reports rely on writing SQL queries. A P&L Statement requires only a few Tables. At a minimum:

  • General Ledger activity: 'tabGL Entry'
  • Chart of Accounts structure: 'tabAccount'

If you’re proficient with SQL, you could construct your own query that produces P&L data. Then display that in ERPNext through the Query Report. You could think about using SQL Common Table Expressions to handle the hierarchy. Or just create a bunch of SQL queries and UNION them together. There are tons of possibilities.

Advantage: You haven’t changed a single line of ERPNext code. And you can make the P&L statement look exactly how you want.

Disadvantage: Reinventing the wheel, from a certain point of view. Requires SQL expertise.


Option 3. Excel or Google Docs spreadsheet.

All your General Ledger activity resides in SQL table 'tabGL Entry'. If you’re a spreadsheet guru, you could construct your P&L report as a spreadsheet. Put the results on Worksheet 1, the logic in Worksheet 2, and the raw GL data in Worksheet 3. Just dump your GL data out of ERPNext into your spreadsheet, and refresh.

Also, this may be nice for your Accountants:

  1. Most are comfortable working in spreadsheets.
  2. Many enjoy having direct access to the data, to munge it, handle exceptions, re-group data, etc.

Are your accountants ultimately taking the ERPNext P&L report, and just exporting to Excel anyway? If so, you could skip a step. By creating the P&L report as Excel in the first place.

You might encounter issues with size and volume. Importing hundreds of thousands of GL transactions into a spreadsheet is not ideal. But people do this all the time. I’m not a huge fan of Excel, personally. Yet sometimes it’s the path of least resistance.


Option 4. Third Party Software.

This is a much longer-term idea and solution. But since we’re talking about reporting, here’s my take on this:

In many ways, ERPNext is a generalist. A “jack of all trades”. It’s good at many tasks. But not necessarily “great”.

To really get the most value from your data, I would consider using specialty reporting tools. Whether something like Microsoft Power BI, or Solver’s financial reporting tools. Or one of hundreds of other possibilities. But there are definitely Reporting Tools that focus on just that: reporting. Or even Reporting Tools that focus on Financial Reporting.

You can find things with drag & drop widgets, built-in data mining and trend analysis, and so much more.

I am -not- suggesting you reach for this option today. Sounds like you just need a quick fix. But if your career involves working with ERP’s long term? Definitely something to think about.

Hope some of the info above helps. You’re doing well; keep it up! As someone who “started at the bottom” with ERPNext, and learned most of its technology from scratch, I definitely empathize with you. :slight_smile:

~Brian

2 Likes

Hi @brian_pond

Firstly a HUGE thanks for your detailed suggestions. As an “ERPNext” beginner, this help is
really encouraging.

I will certainly consider all your options carefully.

I am relieved / happy ( not sure which one ! ) to report that I have in the mean time
created a script report. Reportlist-> New-> Save that created the underlying structure
for a custom script.

But instead of writing my own code, I copied the existing code in there. Ofcourse
one has to go through it carefully because the report name is referenced in the files,
so that needs to be changed. And although the report is not visible on the desktop
in the Accounting Module, Iwill worry about that later. I did find a doc on the internet
that suggests it has somethinig to do with the permissions of the DOC_TYPE that
the report is based on … which in this case is GL Entry. But I shall worry about that
later. I can access the report on the report-list->Show Report.

This has yielded a working P&L report in the old format. i.e. I have simply copied the
report. But this will allow me to make changes without actually modifying core-code.

Reading through the code, I do realise that one requires indepth knowledge of the
objects/ methods. And as I mentioned in my first post, I have written many
embedded C-programs but only one or two very simply python scripts. But I have to give this a shot.

Your “Excell” suggestion is most certainly a “quicker” option. In terms of coding, that may turn out to be simpler, just for the moment. Because I have to enable myself to “dig” into
the PY-code, if not now then definately later.

Furtunately , I have a clone of my production-server that I can “play” with. This is what I have
been doing the past few months and once I find something that must be rectified in a setting
somewhere, I rectify it on my production-server with ofcourse carefull notes for future
reference.

Hopefully I will be able to report even more progress a little later.

Thank you again. Appreciate.

I did some embedded Fortran programming in the early 2000’s. It was fun for a while. There are days when I definitely miss the simplicity. :desktop_computer:

Its a bit crude … but its there !!!

I am starting to understand tuples … so I can work at refining it a bit.!

2 Likes