Creation of Custom Reports as provided for in the Reports Menu

The reason for considering a custom report is to get the Bank Accounts to reflect individual balances, and secondly to construct an Income Statement and Balance Sheet that suits the way I work.

The Cash Accounts tab shows statement balances, uncleared payments, and uncleared deposits, with the resulting net balance for all individual bank accounts you have created. What information do you want that is not there?

The Profit and Loss Statement (another name for income statement) is highly customizable through the Chart of Accounts settings, at least within the range of what actually constitutes a P&L. What are you trying to show that you haven’t been able to find a way to do?

Likewise, the Balance Sheet is highly customizable, especially after the recent introduction of custom control accounts. Again, what are you trying to show that you haven’t figured a way to do?

I am treasurer for a lifestyle village so all our transactions relate to bank entries and I put a cheque no or receipt no in the data entry so when I print it would be nice to see the reference
Regards
Trevor

Lubos or Tut,
I have been seeking Custom Reports for many months now. The Profit & LOss Statement can be manipulated a little bit with positioning of items but it does not reflect the Accounting Standards where the entity has a secondary source of income (sometimes called Extraordinary Income).

For the Debtors, I want to be able to create my own report that extracts pieces of information from Sales Invoices, Cash received and shows a balance at the beginning and end of year.
ie O/Bal Sales/receivable Paid C/Bal
Joe
Blow
You
Me

Similarly for a Statement of Income & Expenditure or Receipts and Payments, I want to be able to put at the TOP of the report, the OPENING balance in Cash at Bank and after Income less Expenes generate the Closing balance.

Balance at bank at beg. of year $xxxx
Receipts:



Total receipts xxxx
Less: Payments




Total Payments xxxx
Operating SUrplus xxxx

Closing Balance xxxx

Can you help me design reports like these please?

It’s not a question of the information not being there, it is a question of the information being in the legally required format. Some improvement has been effected by re-categorising some of the Balance Sheet Accounts, but more needs to be done. Please advise me what custom control accounts are.
However, I would be reasonably happy if I could just get the Bank Accounts shown individually.

Read [16.5.32] Added ability to create custom control accounts for cash accounts. More documentation should be coming out shortly. This is a brand new feature.

I don’t understand your statement. You can certainly set up additional income accounts in your chart of accounts. Please clarify.

This is provided by the General Ledger Summary for balance sheet and income statement accounts. That report does not supply transaction details, however. Export those from the Cash Accounts tab by clicking on the bank account’s balance.

Manager provides accounting reports that have become standard literally over centuries of practice. Someone can always think of some new way to slice and dice data. That’s why the export feature is there. And custom reports will help.

Hello Tut,
I thank you for replying and sending that link to Custom Reports - which I will now try. I had been searching for instructions on how to setup custom reports and was at a loss. You are right, there are numerous ways of doing the same thing and basically, the structure of the standard reports is compliant with Accounting Standards. Because my organisation is not a listed company the stakeholders require specific things. so, I will give the link a try.
Ta.
MicheleA

Be forewarned, @MicheleA. As I said in my second post in this thread, documentation for custom reports is very limited so far. Unless you are an SQL wizard, I suspect you’ll get farther by manipulating spreadsheets. Or you can try educating your stakeholders to accept standard accounting practices. :wink:

@MicheleA . Have you tried Cash Summary Report? You can get receipt and payments report there which you will export and make a little manipulation to get the receipt and payments report you wanted.

I also want to add that, we have to understand that an accounting software cannot do all reporting for the user. Some of the fact and figures you need will be some where in the system, you just have to fetch and make a report. People in various industries are all requesting for reports or formats that suits them. But of course there are some fundamental reports that Manager must have which I think Manager is doing well on.

The layout of the Profit and loss Statement that you have does not allow for Extraordinary Profits or Abnormal Income. But I have devised a new sequence in teh Chart of Accounts so that these sources of income are printed later after ORDINARY OPERATIONS. the problem is in the aggregation or subtotal from these income sources. as there is no way of report ing that.

The addition of the TRACKING CODE to your standard P&L report has resolved one huge problem I had in writing an event-specific report - why not add the Tracking Code function to ALL your reports to enable custom reports without SQL query knowledge - which doesn’t seem to work anyway, unless it’s just that I am unable to determine your Table & Column Names.

I would also like to see a method to alter the layout of your reports. Can you suggest a third-party software package that will do precisely that for your customised version of SQLite? I have tried several open source SQL query programs, which successfully open your .manager format files, but so far without success in interrogating them. PLEASE HELP WITH THIS VITAL FUNCTION!

PS: I am the Treasurer of & use your program for a charity’s accounts, & I love it - apart from the above, & the awful way it automatically allocates available funds to any outstanding invoices, even if they’re not paid yet - please make this an option to be disabled, as it was before!!!

Thank you for all your hard work. Happy New Year.

1 Like

This should not be a problem. Funds are allocated only when received/paid against the customer’s/supplier’s subaccount in Accounts receivable/payable. If the funds were designated for a specific invoice, that should have been selected when the transaction was entered. If not, why are the funds being received/paid? If they were not for an invoice, they should have been posted to some other account. If they were, money is fungible, so what difference does it make? If the funds are for some special purpose other than an invoice, the transaction should be posted to an advance payment/deposit account, either asset or liability, depending on which way the transaction is flowing.

When you enter the Receive Money you have two options (1) allocate to specific Invoices or (2) don’t allocate to specific invoices (oldest gets paid first, then next oldest etc). The following example demonstrates both

Firstly, please do not assume we are all brainless & do not know how to use your software.

You have obviously never had experience of Incomplete Accounts - this is where the client has provided little or no information, or disparate information going back years, where accounts have to be collated from various sources of information & data inputted correctly only when it comes to light, verifying its validity against what is known & applying it accordingly. Hence, aged invoices are inputted & payment applied WHEN the information confirming it is found.

Another scenario your software does not cater for is Payment on Account, where partial payment is applied to an invoice or invoices, which can then be reconciled against bank statements. Your automatic allocation of funds is a complete nonsense & is wholly unnecessary - when I allocate funds to an AR or AP invoice, that is a true reflection of the actual movement of money on any given date, which is what any accounting package should do - show where the money ACTUALLY is, not where you think it should be - ask any Accountant!

I am surprised that you have concentrated on this matter alone as it was a secondary issue in my post. You have not addressed the primary matter of your dire reporting functions. I was requesting that the Tracking Code be applied to all of your reports to make them more configurable (which worked well on a P&L Report), & asking if you can suggest a third-party solution to this.

As I said, I can access your modified SQLite database with various reporting programs, but not interrogate it to ascertain the precise Table & Column Names - but I strongly suspect that even if I were to guess these correctly, your Custom Report function would still not work!

I feel this is a matter that should be addressed urgently as people have been complaining about it for quite some time, as I know you are aware, & it is a vital accounting function to be able to correctly report financial affairs for any given period in any particular circumstance.

Although I have reservations about these two issues, I would add that your software is otherwise excellent & very pleasant to use. Addressing the reporting function & adding the ability to prevent automatic allocation of funds would make it perfect!

2 Likes

Dear Sir

You have absolutely no knowledge of my experience so I would strongly suggest that you should desist from casting dispersions on what you don’t know about other forum contributors.

My response above was based on that part of your post where I could assist based on your expressed concerns. The other parts of your post (tracking codes) requires a response from the programme developers and that is not I.

Yes it does, in fact you have the total control over both Accounts Receivables & Account Payables payment allocations. You can build up any fragmented documentation and match specific invoices & payments (and reconcile bank statements) or as you state “show where the money ACTUALLY is”.

In the instance that a piece of payment data is recorded prior to knowing the correct invoice allocation, then that payment is initially posted to an “Unallocated Payments” account. This is the exact same process used by forensic accounting teams when doing fraud and missing millions type of investigations and hence the elimination of automatic allocations.

Firstly, you are right that I too made an assumption, & I apologise for my misconception of your position. By bad!

I cannot see how else to record a Payment on Account other than in Spend Money from the invoice in question & input the actual amount paid against that invoice. Having done that on several occasions, on returning to the supplier account, unallocated sums have been erroneously allocated. Perhaps I am missing something? I know that automatic allocation used to be optional & I wish it were again, & I know I am not alone in this.

On the question of reporting - my Board of Trustees has requested I provide a Transaction Report on a specific event’s income & expenses which occurred during the year. I was able to provide an accurate P&L Statement using the Tracking Code, but I cannot see any way to isolate that specific event from any other income & expenses for a Transaction Report. I would be grateful for any ideas since the Custom Report does not function.

I do hope that the developers address the issue of reporting soon, but can you help in the meantime?

If you are directly matching up an invoice and payment then “Spend Money from the Purchase Invoice in question” is one option. The other option is from the Cash Accounts tab, select - Spend Money - “bank” and for the Account line enter Accounts Payable + Supplier + Invoice.

For this to occur, means that you have entered a Spend Money transaction without any allocation to a specific Invoice. As mentioned above, for such payment data to be recorded prior to knowing the correct invoice allocation, then the payment is initially posted to an “Unallocated Payments” account, located under BS - Liabilities.

For example, if you have a payment for 500 but only 300 can be allocated to a specific invoice then the balancing 200 is posted to Unallocated Payments. This way the Supplier account remains in sync and the 500 is available for bank reconciliation purposes.

Once determined, the Unallocated Payment can either be re-assigned or transferred via Journal.

In my “experience” the simplicity approach for such accounting re-builds is to request from the Suppliers a transaction statement and then process that as your source for the Purchase Invoice documentation. Also, the statement “could” advise how payments received were allocated. Then marry up the clients scarps to the statements rather then use the scraps to create the accounts themselves.

and it still is, nothing has changed. The location where the overpayments were stored has been modified but the underlining processing of the transaction remains unaltered.

I must be missing something as I didn’t have an “Unallocated Payments” option, so I’ve created it in Special Accounts. I still feel my problem is not being addressed (unless I am misunderstanding your responses).

As I am working on three years’ legacy & incomplete accounts, I am inputting data as I come across it, including payments on account of various supplier invoices throughout the three years, assigning them to the right invoice(s). Of course, as I get through the data & come to the end, all will become clear as to when full payment of each invoice has actually been made, but I do not want automatic assignment of funds to invoices not yet paid in full until I find the actual payment relating to that invoice - & I cannot see an option to turn this function off, which I understand once did exist.

I’ve never known Payments on Account to be so allocated to partially unpaid invoices in other accounting software, rather than sitting on the supplier’s account as a PoA, & I found it frustrating. However, having now created an “Unallocated Payments” account, I will try that option for the unallocated portion in the interim, & reassign as data comes to light.

As to the reporting function, I am delighted with the new Tracking Code option, & am using that enthusiastically to create custom reports - however, this currently only applies to the P&L reports. Could this function be included with the other reports - for example, Transactions? This would allow customised Transaction reports to become a piece of cake. It would also be great if more than one Tracking Code could be used in each report - & that would negate your Customised Report function having to be used, so that all those having problems with it would most likely no longer need to use it. I’ve never got it to work as I cannot determine the precise Column Headers!

Also on my wish list would to be able to reformat your reports in terms of layout - & I appreciate that may be way down the line, but if multiple Tracking Codes could be implemented in ALL your reports, I would be a very happy bunny indeed!

Happy New Year to all at Manager.io.

This is not an option. That was a reference made by @Brucanna in his post of 8 days ago to an account you would create. There is, by the way, no reason to use Special Accounts for that. It can be an ordinary liability account.

This option never existed. The structure of how it worked was once different and somewhat more complex, but it has never been optional.

This is not new, but has been in the program for at least three years.

This isn’t going to happen, and couldn’t happen in a number of the reports because it would make no sense. What you have been writing about from the beginning will be handled when job costing comes on line. That is second on the Roadmap. Tracking codes were never meant for project accounting, but for handling different company divisions. They’ve only been a clumsy workaround, and no effort is going to be put into enhancing capabilities of a workaround when a purpose-designed module is being worked on.