SQL schema for creating custom reports

Where can I find the database schema, especially fields and table names, so I can build custom SQL queries? I’ve seen several questions to this end posted on the Forum here, but I haven’t found the answer.

Even a basic list of tables and major field names would be very helpful.

Tables you can try:

  • BankTransactions
  • BankTransactionLines
  • JournalEntryLines
  • SalesInvoices
  • Customers
  • Suppliers
  • Employees

It’s better if you ask what report would you like to create. Custom reports don’t have 100% coverage yet but I keep adding more.

2 Likes

I would like to be able to see the addresses for all of my customers together on a custom report.

Hi

Try:
SELECT * FROM JournalEntryLines
SELECT * FROM BankTransactions
SELECT * FROM SalesInvoices
SELECT * FROM Customers
SELECT * FROM Suppliers
SELECT * FROM Employees
SELECT * FROM BankTransactionLines

1 Like

Okay, how about a Custom Report that breaks down my sales for 2015 (or any arbitrary period) by Customer? (When I say “sales” here, I mean “amounts invoiced to the customer,” preferably excluding Billable Expenses – but if it has to include Billable Expenses, then so be it.)

This would be terribly useful at this time of year, in order to reconcile income tax filings for payments reported by each client (Form 1099-MISC here in the U.S.) against company records of billings.

Something like this:

I can’t quite figure out the Manager flavour of the SQL to generate this table. Can you please help? (My SQL is weak, but I tried this and it doesn’t work for many reasons: SELECT SUM(TotalAmount) FROM SalesInvoices WHERE IssueDate BETWEEN '2015-01-01' AND '2015-12-31' GROUP BY CustomerName;)

To keep things simple, this request is just for a straightforward Custom Report of total invoiced amounts for the specified period broken down by customer. Ideally, though, since this is probably a very commonly needed report for any services-based company, it would be nice to have a formal Sales Report that includes this information and breaks it down by Income account. Something like this:

…and, if possible, an option to flip the hierarchy, with each customer listed underneath each Income account:

I managed to export all of my invoices for the year to Excel, used the Excel sort and subtotal functions, and then did some formatting to come up with the report I needed for the year (sans the breakdown by Income account).

Here’s what it looks like:

This format might be an alternative to the reports I mocked up in the previous posting. The report shows at a glance how much each customer purchased from me and how much I’ve yet to collect at the end of the accounting period.

It would be nice to be able to access one of these reports through the Reports menu without having to go through an export and Excel magic. Still, just getting the SQL for a simple Custom Report as described at the top of the previous post would be very helpful in the meantime.

3 Likes

Hi

I’m constantly getting questions about my billable time - is this table available in custom reports please?

Thanks

Stu

Not at the present time. No reports are actually “available” in custom reports. The point of custom reports is for you to run your own, based on queries you input. But that entire capability is still a work in progress.

What question are you trying to answer about billable time? You asked if “this table” was available. It was not clear what “table” you were referring to. Meanwhile, the Billable Time register lists 8 parameters. You can sort by any of them and export for further manipulation in a spreadsheet. You could conduct a wide range of analysis that way and generate reports from that. The only other information available not included in the register is rate and invoice number. Those are one click away on individual billable time entry screens.

Thanks for your prompt reply. Perhaps you misunderstood or simply don’t know.

In a database (which is the backend data store for many appa, including manager), data ia stored in tables. @lubos has already stated that several of those tables are available for querying, including ‘employees’, ‘banktransactionlines’, etc.

I was asking whether the ‘billable time’ table was available to be queried. I’ve tried several names to no avail and hence I posted a question.

Thanks for the additional information. So far as I am aware, the only information on data structure that has been provided publicly comprises the sales invoice variables at Manager Cloud. I am open to correction, if something else has come out.

As I said, custom reports is still under development and documentation is almost nonexistent.

I am still curious what type of information you are trying to extract. It may be available in some other way.

OK thank you. I wanted to slice and dice it a bit more, ie reporting hours per customer or project and monthly summaries of hours billed. I can do all of this in a spreadsheet (exporting the data from Manager) but just thought if the data was available in custom reports I could write my own SQL query to get exactly what I wanted.

All good.

Improved custom reports have been promised, and project accounting has been mentioned, too. Until then, I think spreadsheets will have to suffice.:disappointed:

1 Like

The reports that show CustomerName column, should also return a unique identifier for that customer (eg CustomerId) so that JOIN with another table is accurate.

For example, if I want to JOIN tables Customers and SalesInvoices on a common column related to customers’ identity, right now my only option is ‘CustomerName’ column. What it should actually be is a unique column like ‘CustomerId’.

In Custom Reporting, how do I get to the details of Sales Invoices? I can do “select * from SalesInvoices” but this does not give the detail I am looking for… Is there an equivalent to what I would think to be SaleInvoiceLines??

1 Like

Have this queries been removed?

Select * from BankTransactions
Select * from BankTransactionLines

@Davide, it seems like

Select * from BankTransactions

has changed to

SELECT * FROM CashTransactions

Once you know the table names, you can use standard SQL commands to extract whatever you need, e.g.:

SELECT Date, Account, Description, Amount FROM CashTransactions WHERE Amount > 200.00 AND Amount < 1000.00 AND Contact != NULL ORDER BY Account ASC, Amount, Date DESC

Unfortunately, I don’t know the other table names.

1 Like

@dram Thanks. By the way, you cannot query all the tables since the queries are filtered by some code.

Hi. I have tried exporting the report to excel but the result is no correct. Can you tell me how did you manage to do it, export correctly to excel ? Thank you

What tells you the result is not correct ?

Thanks for the response. Data is correct, but when exported, I am not able to produce a correct spreadsheet, not all the data goes in the corresponding column. When importing into excel, I assume it is a tab separated file.

1 Like