Accounts Receivable (AR) report using Query Report in ERPNext

1

Need Help: Accounts Receivable (AR) using Query Report in ERPNext

Hi everyone :waving_hand:

I’m currently working on a Query Report in ERPNext and need some guidance regarding Accounts Receivable (AR) reporting.

What I’m trying to do

I want to build an AR / Accounts Receivable Summary–type report using a Query Report (SQL), similar to the standard Accounts Receivable Summary report.

I’m aware that ERPNext internally uses Payment Ledger Entry, but I’m struggling to implement this correctly in a Query Report.

:folded_hands: What I need help with

  1. Has anyone successfully built an AR / Accounts Receivable report using a Query Report (SQL)?
    If yes, could you please share the approach or logic you followed?

  2. Is it recommended to avoid Query Reports for AR and use Script Reports only?
    From your experience, is AR too complex for SQL-only reports?

  3. How do you safely handle optional filters like:

    • customer_group

    • sales_person
      in Query Reports without getting KeyError or “not enough arguments for format string” errors when filters are empty?

  4. Is there any official or recommended SQL example for AR using Payment Ledger Entry?
    I couldn’t find a clean reference that matches the standard report output.

  5. If anyone has already implemented a Query Report version of AR Summary, I’d really appreciate it if you could share or guide me.

2

Yeah i tried creating a query report once for multi company and it got stuck because the filter needed at least one company (and i wanted all the companies merged OR a single filtered company) … so i did not continue… maybe we need to a raise a github issue to bring it to their attention
If successful, pls post your sollution here so that others benefit from it