Report of the Week - Customer Balance Report

Report of the Week - Customer Balance Report

How much do your customers owe you? How about customers with payments long overdue? How much advance payments do you have in hand? For all these questions, the Customer Balance Report provides you with the answer. This week we'll see how to create a customer balance report based on your transactions over a specific period.




You can create this report over Zoho Books or Zoho Finance data.


To create a customer balance report, you need to follow the below steps.


  • Define the period for analysis (by creating a Query Table)
  • Get the Opening balance, Debit and Credit (by creating a Query Table)
  • Join the above two Query Tables 
  • Create the Customer Balance Report on top of the above joined table
  • Get Closing Balance (by defining a Report Formula)

Define the period for analysis  (Query Table)

Define the period for which you want to analyze the customer balance. The following query will set the period as from the beginning of the year till the current date. 


SELECT
start_day(Year, current_date()) as 'From Date', current_date() as 'To Date'




You can also create a Table and manually enter the From Date and To Date for analysis.

Get Opening balance, Debit and Credit (Query Table)

Now you need to get the opening balance for each customer at the beginning of the period, and debits and credits during the period. The following query gets these details. 


/*Opening Balance from Invoices*/

SELECT
"Customer ID",
"Total (BCY)" as 'Opening Balance',
0 as 'Debit',
0 as 'Credit'
FROM  "Invoices"

JOIN "Period for Analysis" ON "Invoices"."Invoice Date"  < "Period for Analysis"."From Date"  

UNION ALL

 SELECT
/*Opening Balance from Credit Notes*/ 

   "Customer ID",
"Total (BCY)" * -1,
0,
0
FROM  "Credit Notes"

JOIN "Period for Analysis" ON "Credit Notes"."Credit Note Date"  < "Period for Analysis"."From Date"  
WHERE "Credit Notes"."Credit Note Date"  < "Period for Analysis"."From Date"

UNION ALL

 SELECT
/* Credit notes in Current period*/ 

   "Customer ID",
0,
"Total (BCY)" * -1,
0
FROM  "Credit Notes"

JOIN "Period for Analysis" ON "Credit Notes"."Credit Note Date"  > "Period for Analysis"."From Date"
AND "Credit Notes"."Credit Note Date"  < "Period for Analysis"."To Date"  

UNION ALL

 SELECT
/*Opening Balance from Customer Payments*/ 

   "Customer ID",
"Amount (BCY)" * -1 as "Opening",
0,
0
FROM  "Customer Payments"
JOIN "Period for Analysis" ON "Customer Payments"."Payment Date"  < "Period for Analysis"."From Date"  
UNION ALL
 SELECT
/*Invoices in the current period*/ 
   "Customer ID",
0,
"Total (BCY)",
0

FROM  "Invoices"

JOIN "Period for Analysis" ON "Invoices"."Invoice Date"  > "Period for Analysis"."From Date"
AND "Invoices"."Invoice Date"  < "Period for Analysis"."To Date"  
UNION ALL
 SELECT
/*Customer Payments in the current period*/ 

"Customer ID",
0,
0,
"Amount (BCY)"

FROM  "Customer Payments"
JOIN "Period for Analysis" ON "Customer Payments"."Payment Date"  > "Period for Analysis"."From Date"
AND "Customer Payments"."Payment Date"  < "Period for Analysis"."To Date"  
UNION ALL
 SELECT
/*Opening Balance from Debit Notes*/ 
    "Customer ID",
"Transaction Amount (BCY)",
0,
0

FROM  "Accrual Transactions"
JOIN "Period for Analysis" ON "Accrual Transactions"."Transaction Date"  < "Period for Analysis"."From Date"  

WHERE "Entity Type"  in ( 'debit_note'  )
UNION ALL
 SELECT
/*debit Note in current period*/ 

"Customer ID",
0,
"Transaction Amount (BCY)",
0
FROM  "Accrual Transactions"
JOIN "Period for Analysis" ON "Accrual Transactions"."Transaction Date"  > "Period for Analysis"."From Date"
AND "Accrual Transactions"."Transaction Date"  < "Period for Analysis"."To Date"  
WHERE "Entity Type"  in ( 'debit_note'  )

UNION ALL
 SELECT
/*Opening Balance for Customers*/ 
    "Customer ID",
"Transaction Amount (BCY)",
0,
0
FROM  "Accrual Transactions"
JOIN "Period for Analysis" ON "Accrual Transactions"."Transaction Date"  <"Period for Analysis"."From Date"  
WHERE "Entity Type"  in ( 'customer_ob_invoice'  )
 AND "Accrual Transactions"."Debit or Credit"  = 'debit'
UNION ALL
 SELECT
/*Opening Balance for Customers*/ 
   "Customer ID",
0,
"Transaction Amount (BCY)",
0
FROM  "Accrual Transactions"
JOIN "Period for Analysis" ON "Accrual Transactions"."Transaction Date"  > "Period for Analysis"."From Date"
AND "Accrual Transactions"."Transaction Date"  < "Period for Analysis"."To Date"  
WHERE "Entity Type"  in ( 'customer_ob_invoice'  )
 AND "Accrual Transactions"."Debit or Credit"  = 'debit'




Join Tables with Customer Data

Join the query table with your customer data to get the above details for each customer. 



Create Customer Balance Report

Create a new Pivot View and add columns as follows.

  • Row - Customer Name for Customers table. 
  • Data - Opening Balance, Debit and Credit from Customer Balance query table.


Get Closing Balance (Report Formula)

To complete the customer balance report, you need to find the Closing Balance. Create a Report Formula to get this. Click the formula icon and enter the below formula. 


"1. Customer Balance.Opening Balance" + "2. Opening Balance.Debit" - "3. Opening Balance.Credit"


Report will be generated as follows. Apply the required theme. 




The following is the final report. 




    • Sticky Posts

    • Tip of the Week - Spot Risky Sales with Conditional Formatting

      In Zoho Analytics, small tweaks can lead to big insights. One such feature is Conditional formatting based on other columns, your key to instantly spotting where sales success is overshadowed by product returns. Our tip this week shows you how to apply

    • Recent Topics

    • CBSA - GST CHARGES on imports

      Hi there, We have a questions about landed cost categorization. We received a shipment from overseas. CBSA invoiced us for the GST on the items. Now we entered the CBSA-GST as a separate bill and attached it as landed cost to the main invoice based on

    • Zoho Books

      How do I manually insert opening balance?

    • Sales order & purchase order item links for item details

      This is fantastic for checking lots of things, I use it a lot. It would be great to see it extended to invoices & bills On another note, may as well throw in my favourite whinge ..... Wish you guys would get the PO receive differences sorted urgently,

    • Bank charges are applied. Please select a bank account.

      Hello, I'm trying to add bank charges to a customer payment, but I get the error message "Bank charges are applied. Please select a bank account." I found this old thread, where it says that I need to "select a Bank account for the 'Deposit To' dropdown

    • How to add receipts

      How to add receipts

    • Support for auto-upgrade in TrueSync (for Windows)

      WorkDrive TrueSync app now supports auto-upgrading to the latest version for Windows OS. You must manually download and install the TrueSync app version 3.4.0 to avail this feature. Download the latest TrueSync app for Windows (version 3.4.0) Supported

    • WorkDrive API Documentation

      WorkDrive provides users and developers an extensive set of APIs to help integrate functionalities of Zoho WorkDrive with other Zoho applications and third-party tools. We have published the official WorkDrive API Documentation page for all external users.

    • March 15, 2023: Zoho Docs is discontinued

      As of today (March 15, 2023) Zoho Docs is discontinued for all users. We would like to thank our customers for trusting us for so many years! Going forward, we're confident you'll enjoy using Zoho WorkDrive for all your advanced file management and collaboration

    • Introducing WorkDrive 4.0: Enhanced productivity. Advanced data administration. (Phase 1)

      Hello All, We're excited to share the release of WorkDrive 4.0, which includes important new features and enhancements focused primarily on productivity, secure collaboration, data administration, integrations, and user experience. Read the official announcement

    • External download link limit

      Can You please help us to understand this For Zoho WorkDrive external users, the download limit is a maximum of 5 GB total download size and a maximum of 50 first-level files and folders What is the meaning of first level? We are using these files in

    • Dynamically catching new file creations

      I have a team folder with many subfolders, and in those folders we add new documents all the time. I'd like to have a workflow or script to notify me (and then take other actions) when a file is added anywhere in that structure that ends in "summary.txt".

    • Rotate an Image in Workdrive Image Editor

      I don't know if I'm just missing something, but my team needs a way to rotate images in Workdrive and save them at that new orientation. For example one of our ground crew members will take photos of job sites vertically (9:16) on his phone and upload

    • Workflow workdrive rollout

      Hi! When will workflow be rolled out to all users? Thanks.

    • Creating and managing a Team Folder using WorkDrive TrueSync

      Hello everyone, Are you tired of constantly switching between your Desktop TrueSync app and the WorkDrive web app to create and manage Team Folders? We’ve made things easier for you. You can now create and manage Team Folders directly within the TrueSync

    • Edit images seamlessly with WorkDrive's built-in Image Editor

      Are you tired of switching between multiple tools just to make simple edits to your images? We understand the hassle, which is why Zoho WorkDrive now comes with a built-in image editing tool, powered by Zoho Annotator. This tool allows you to edit images

    • Supercharge your email workflow with WorkDrive's add-in for Microsoft Outlook

      Consider this: You’re handling a critical project, and your inbox is packed with important attachments, email threads, and client communications. The back-and-forth routine of downloading files to your computer, uploading them to WorkDrive, and manually

    • Secure and promote your content with Custom Watermarking

      Imagine this: You’re a professional photographer who regularly shares your work online with potential clients and collaborators. Recently, you notice that some of your images have been reposted without any credit or permission. This not only impacts your

    • Join us in Singapore for the Zoho WorkDrive User Group meetup!

      Hello, everyone! Exciting news! We'll be hosting an upcoming Zoho WorkDrive user group meetup in the beautiful city of Singapore this November. At this Zoho User Group meetup, we'll guide you through ways to use WorkDrive as a platform and build custom

    • WorkDrive TrueSync now supports ARM64-based Windows devices!

      We’re excited to announce that the Zoho WorkDrive TrueSync app now fully supports Windows devices with ARM64 architecture! Whether you're working on an ARM-based device or an x64 processor, you can now enjoy the same seamless file synchronization experience

    • Option to Disable Download for Documents Shared via Permalink

      Dear Zoho Writer Team, Currently, when sharing a Writer document using the regular permalink (Collaborators with external users), there is no option to restrict the ability to download the document. While the external share link allows such restrictions,

    • How to Download a File from Zoho WorkDrive Using a Public Link

      How to Download a File from Zoho WorkDrive Using a Public Link If you're working with Zoho WorkDrive and want to download a file using a public link, here's a simple method to do so using API or a basic script. This approach helps developers or teams

    • domain not verified error

      Hi when i try to upload a video from zoho creator widget to zoho work drive iam getting domain not verified error.I don't know what to do .In zoho api console this is my home page url https://creatorapp.zoho.com/ and this is my redirect url:www.google.com.Iam

    • Live Webinar: Getting Started with Zoho WorkDrive - A Complete Overview

      Hello everyone, We’re excited to invite you to our upcoming live webinar! Discover how to set up your team, bring in your data, and make the most of WorkDrive’s collaboration, organization, AI, and security capabilities. This session is perfect for anyone

    • Calendly One-way sync- Beta Access

      Hello Community, Many of our Zoho Calendar users have expressed their interests in Zoho Calendar and Calendly integration. We've been tightly working on with Calendly team to provide a two-way sync between Calendly and Zoho Calendar. However, there have

    • The year that was at Zoho Calendar 2023- Part 2

      In continuation with our previous post on all the exciting updates and improvements that have shaped Zoho Calendar over the past 12 months, Lets delve into more: Bring your calendars together- Introducing Zoho Calendar and Outlook calendar synchronisation

    • Tip of the week #18: Change the event organizer in Zoho Calendar.

      We cannot always be available to conduct an event when we organise one. In these circumstances, you can use Zoho Calendar to change the event organizer at any moment before the event begins. This way, you can avoid cancelling the event while still taking

    • Tip of the week #20: Create and manage multiple personal calendars.

      Zoho Calendar provides users with the facility to create and manage as many calendars as required. All these calendars can be managed and edited as per user requirements. You can alter the calendar view, make changes to the calendar theme, share the calendar

    • Tip of the week #24: Subscribe to the calendars of a Zoho Calendar user.

      Calendars that are created by Zoho Calendar users can also be added to your Zoho calendar. All public calendars listed by the users will be available when you enter the email address. You can choose the calendar you need to subscribe to. Once the email

    • Tip of the week #26: Import/ Export calendars in Zoho Calendar.

      Any calendar on the web or calendars that you create in any other calendar application can be imported in to Zoho Calendar. This will help you to add the events from the calendars that you import to your Zoho Calendar. You also have the option to export

    • Removing calendar for zoho email group

      How do I make it so that an email group created in Zoho Mail does NOT have a calendar? I have a couple groups for our phone systems voicemails - one for each department. Voicemail recordings are sent to this groups email address so they have access to

    • Tip of the week #27: Edit personal calendars in Zoho Calendar.

      In Zoho Calendar, the personal calendars you create can be edited to make changes you need to make. Edit a Personal Calendar The following changes can be made to the personal calendar by editing it: Calendar title Calendar color Reminders and Description

    • Tip of the week #28: Show/ hide, enable/ disable and empty/ delete your calendars in Zoho Calendar.

      The popularity of online calendars has soared in recent years. It's used both for personal and professional reasons. Calendars have evolved into an effective productivity tool in our lives, from creating events for birthdays and anniversaries to scheduling

    • Tip of the week #30: Share calendars publicly in Zoho Calendar.

      In Zoho Calendar, calendars that are created under My Calendars can be shared publicly. Making your calendar public allows others to view it. When you need to share your calendar with a larger group, public sharing can help. You can restrict others from

    • Tip of the week #31: Share your personal calendars within organization.

      Keep your Organization members aware of what's happening. In Zoho Calendar, you can share your personal calendar with all the members in your organization using the Share with org option.When you enable org sharing for a particular personal calendar,

    • Tip of the Week #33: Appointment scheduler in Zoho Calendar.

      In Zoho Calendar, you can use the Schedule Appointment option to share your appointment request form with the public, allowing people to fill out the form to request an appointment with you. This form can be embedded on your website or blog. Visitors

    • Tip of the Week #34: Embed Calendars using Zoho Calendar

      You can make your calendars public and visible to the general public by embedding them in your websites/blogs using Zoho Calendar. You can use the embed code to add your own calendars to your website's/ blog's HTML code, and the calendar will appear on

    • Tip of the week #35: Migrate to Zoho Calendar from Google Calendar.

      If you are looking to move your Google Calendar events to Zoho Calendar, never worry about missing out the events from your Google Calendar. You can migrate the events from Google Calendar using the export option and import it to Zoho Calendar and manage

    • Tip of the week #36: Migrate to Zoho Calendar from Outlook Calendar.

      If you've been using Outlook calendar and looking to migrate to Zoho Calendar, you can seamlessly export your calendars from Outlook and import them into Zoho Calendar without losing any events, participants, and the reminders set for each event. To migrate

    • Shared calendar issues and duplications

      Apparently there was a calendar update?  Now when I schedule an event for a team member that has shared his calendar with me, the event makes me the organizer and adds the event to my calendar as well.  Previous to this "update" I would scheduled an event

    • Subscribed Calendar

      Hi i have subscribed to a calendar for Holidays in Canada it shows all the holidays perfectly but every one of them has the word Canada before the rest of the name is there a way to remove that word Canada? It takes up a lot of space in the square on

    • Next Page