SQL schema for creating custom reports

42

Any chance of a schema for Purchase Invoices please

Regards

43

Hello

Lovely application. I need to create a custom report which can provide cost of each inventory location at any given point. Any help will be appreciated.

Thanks

44

First, locations do not have costs associated with them.

Second, if you search the forum on the subject of custom reports, you’ll see many, many responses telling you that the feature is a work in progress, for which there is no documentation.

45

I don’t think a new user should have to search the forum to find out that Custom Reports are a work in progress and there is no documentation for it. The Custom Reports page should be clearly marked that is a work-in-progress, experimental, or however you want to characterize it.

I have run into some issues with it which I consider to be bugs. Is it worth reporting them here?

46

@tut I think he meant Cost Associates with the Inventory which tagged with ‘Specific Location’. Possible to produce I suppose, if its documented.

@csmb for any issues that may related to bug, create new topic for Moderators to assess, and if it found true and able to reproduce or re-trigger the bug by them, they will elevate the topic as bug category.

47

OK, will enter a separate post re the Custom Reports bug.

48

i want to make custom report as per below format, is it possible ?

InvoiceNumber invoiceDate CustomerName TaxAmount TotalAmount

49

As has been said so many times on this Forum, custom reports is a feature still under development. There is no documentation.

But the information you want is available in the Tax Transactions report. If you don’t like the format, you ca export it and change it.

50

Hi Lubos,

great product. As time goes by I use more features and like what I find. However with custom reports, it still has some way to go. It would be great if I could see the details of Sales Invoices, I want to be able to see and analyse the line details so I can see Invoice number, line number description, qty, value and tax as well as cost destination (e.g. Sales, Mileage, Expenses etc).

It would also be good if the customer report, once finished could be saved for re-running on an adhoc basis.

I look forward to your response…

Regards Tim.

51

Hi,

I’m combining another forum post here, but I want to run a custom report that will give me all of the entries from the JournalEntryLines in a specific period in both their original currencies and the base currency. I’ve tried the SQL query as follows:

SELECT Date, Account, Description, Amount, Currency, AccountAmount, AccountCurrency FROM JournalEntryLines WHERE Date >= DateTime(2017,1,1) AND Date <= DateTime(2017,1,31) ORDER BY Account, Date

However I get errors that the fields Amount, AccountAmount and AccountCurrency do not exist. I would assume they have a different name in the JournalEntryLines table - could anyone confirm what these fields are named in this table?

Many thanks,
Rob

52

if you read the forum or this very topic you will understand that the custom reports is not yet a documented feature and is still a work in progress. you cannot expect something to work when it is yet to be functional.

53

Yes, I fully understand it is not yet a documented feature, I was only asking if anyone had the information to hand, or could suggest a solution.

Actually, by playing around with the Custom Reports, I found that this query:

SELECT FROM JournalEntryLines WHERE Date >= DateTime(2017,1,1) AND Date <= DateTime(2017,1,31) AND Account = "XXXX" ORDER BY Account, Date

With the Account name instead of the XXXX text. This will give each entry in JournalEntryLines in its original currency (the field names are “Debit” and “Credit”), and it would it would appear that the conversion to base currency is done elsewhere (i.e. not on the JournalEntryLines table).

Combining this custom report with a straight export from the Summary of the account (which is in the base currency) I can get what I need.

2 Likes
54

hi what about inventory sales?

55

What are you asking? This topic stretches back two years.

56

Hi all,
I know the Custom Reports is not yet a documented feature (as per the comments above), but I was wondering if someone could help me with the report I’m trying to run:

I would like a report of all my billable expenses. (Basically, a report that I can print and/or export, that’s the same as what you see when you click the “Billable Expenses” $xx.xx link from the Balance Sheet or General Ledger report.)

Here’s what I have so far:

SELECT Date, Account, Description, Amount FROM BankTransactions AND SalesInvoices WHERE Account CONTAINS Billableexpenses

I’m not very good with SQL, and have only pieced this together based on previous comments on this thread. Any help would be greatly appreciated. Thank you! :slightly_smiling_face:

57

If you want the same information visible when viewing the drill-down report, why not just export that screen? If you only want a certain date range, only copy the relevant lines when doing the export.

58

Because… until I was trying to explain myself to you in this post, I never looked close enough to see the barely-visible “Export” button. lol.

Thanks for the tip. This program is amazingly adept at helping accounting novices like myself figure out their accounting errors. Glad to know this button is there! :smiley:

(On a side note… @lubos might I suggest making the “Export” button a little more eye-catching? That’s a handy button! I wish I knew it was there sooner. It would have saved me lots of time. :wink: )

59

There is also a Guide about exporting: Manager Cloud.

1 Like
60

Hi,

does anyone know if it is possible to run from the custom reports query the split transactions for bank transactions.
I successfully ran a query on BankTransactions to export the data, however this does not include the detail of where there are multiple lines on a bank transaction.
Running the following query SELECT * FROM BankTransactionLines returns the following error

System.NullReferenceException: Object reference not set to an instance of an object. at ManagerServer.HttpHandlers.Businesses.Business.Reports.CustomReports.CustomReports2.Post()

Cheers

61

Hi, every query in custom reports is filtered by the software. It’s not only a problem to know the name of the table to query but if the table you want to query was putted in the list by the programmer.