Skip to main content
Question

Customer Ledger Aged Analysis


Forum|alt.badge.img+3

Hi,

As part of our monthly reconciliations, we are required to send to our banking partner an aged debtors. I want to use IFS to do this rather than manually creating an excel sheet which is taking too long, however they’ve advised they need the report by transaction date, rather than due date. I can’t seem to find the function to change this when ordering the report, is anyone able to offer assistance?

Thanks

Chanu_Yazi
Hero (Partner)
Forum|alt.badge.img+11
  • Hero (Partner)
  • January 7, 2025

Hi ​@chenderson24 

Have you tried to create a query to extract aged debtor data and order by transaction date instead of due date.?

 

For example;

SELECT
    customer_id,
    invoice_id,
    transaction_date,
    amount_due,
    age
FROM
    customer_ledger
WHERE
    amount_due > 0
ORDER BY
    transaction_date;
 


Ralph Gericke
Hero (Employee)
Forum|alt.badge.img+11

Hi you can also use

  1. the Customer Open Item Balance History, export and structure the items in Excel
  1. Use customer Credit Management (you have to update the date at the last day of the month) Aging Buckets can be setup in the Basic Data, also export into excel.
  2. Audit Interface

           You will receive 2 Files.
 

The text File contains the results from the report (I agree you have to pimp it a little bit in Excel ;)
 

The export can be used in also non german countries like this.The export Format is always

semicolon separated and can be easily imported into Excel.

Note that there are multiple other audit sources available, some view based some report based. The export will always have a text file as a result (the xml files describes the columns of the text file)


Forum|alt.badge.img+3
  • Do Gooder (Customer)
  • January 9, 2025

Thanks both, i will try and run the report again as per your instructions and will let you know how i get on!


Forum|alt.badge.img+3
  • Do Gooder (Customer)
  • January 9, 2025

Hi both, i’ve still been unable to get this report, i need to be able to back date it to our aged debtors as per 31.12.24 and have it by transaction date, do you have any ideas?


Forum|alt.badge.img+19

Hi, 

See the option 1 from Ralph.  It’s a valid solution. A couple comments may help.   

First you need to create a category in the customer open balance history category.  In this you will assign the company / companies that are used for a given category. 

Next you need to create an open balance history record.  This would normally be back dated for example December 31. Simply new record, enter date and save. IFS creates the data set for that record. Really, shockingly easy, and it works. 

This process will essentially recreate the balances as of a historical date such as month end.   Then if you look at the details of that open balance history record, you will see the data you need.  It’s not in the traditional aging buckets, but you can do that in excel.  

Another option (I don’t have documented for Cloud), is the ability to export report type data to excel. The concept is run the IFS report in detail, and export that to excel for refined reporting

A more elegant way combines the balance history with a Business Reporter (formerly Business Analytics). The business reporter tool is a sophisticated IFS addon to excel that allows users to create very nice-looking reports. Once created, you run it as needed just like any other IFS report. The concept is to create the history, then use the report to present that data in a meaningful way. 

Best regards, 

Thomas


Forum|alt.badge.img+3
  • Do Gooder (Customer)
  • January 16, 2025

Thanks Thomas, I have will attempt option 1 again and let you know, will hopefully revisit this next week, thanks all for you help


Forum|alt.badge.img+3
  • Do Gooder (Customer)
  • January 28, 2025

Hi ​@Chanu_Yazi ​@Thomas Peterson 

 

I have tried to create the quick report but get the following error:

 

Sql Expression is wrong [SELECT customer_id, invoice_id, transaction_date, amount_due, age FROM customer_ledger WHERE amount_due > 0 ORDER BY transaction_date;].

 

See quick report screenshot, can you help?

 

Thanks

 

 


Forum|alt.badge.img+19

Hi, 

If you're going to create a quick report… we should do this in a more correct way.  

I say this because the reports in IFS have a ton of business logic. Select this type of record, not this record, maybe calculate xyz. Getting a custom report to produce the exact same results as an IFS report (especially a complicated report) is challenging.  That logic - recreate a customer open item data set is not easy.  The reformatting of the IFS report is easy. 

In IFS, when we run a report, the output from that report can be seen in a view. The view name ends in REP.  The process we use is to run the IFS report, have a quick report to reformat that REP view based on your needs. In this, you get the ID from the report archive (assume 1234 is the ID), then select the REP data where result_key = ‘1234’.  

For this report select * from .cust_open_item_rep
where result_key = '144550'

You will need to reformat the SQL based on what you need. This is simply a framework / concept we have used many many times. 

This will reformat the results created from the IFS report (that you previously ran) into the output you want.    See report archive to get that identifier (result key).

User Process - Run the IFS report. Get the result_key.   Run the quick report with the result key. 

I honestly believe other ways are better / easier.  But if you want a quick report (SQL) then this is probably the best way.


Forum|alt.badge.img+3
  • Do Gooder (Customer)
  • January 28, 2025

Hi Thomas, i feel like i’m overcomplicating things here, i do not need a quick report i just didn’t know how else to run the query that ​@Chanu_Yazi advised in the first reply. 


Forum|alt.badge.img+19

Hi, 

Agree, and that’s why both Ralph and I recommend option 1. 

For option 1, to really get an ideal solution, still do option 1, but then ask IT to create a quick report selecting and formatting the data you need in the way you need it. 


Forum|alt.badge.img+3
  • Do Gooder (Customer)
  • January 28, 2025

Hi Thomas, thanks, i already tried option 1 but my customer open balance history screen is blank, see screenshot, do you know why this is?

 

 


Forum|alt.badge.img+3
  • Do Gooder (Customer)
  • January 28, 2025

Hi Thomas,

Apologies, i have now followed the process for assigning the categories etc and the open balances are there now, i will look at this in excel to see if i can get the right format.

Thanks for all your help with this!!


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings