SQL schema for creating custom reports

22

I was told to ask here
I would like to get a report that lists all transactions in my Bank account
From April the 1st 2016 to March 31st 2017
coded to Rental income account
so from above is it
SELECT Date, Account, Description, Amount FROM CashTransactions WHERE Date is between 01/04/2016 and 31/03/2017 AND Account= Rental
I tryied it and it says
Expression of type ‘Boolean’ expected (at index 0)

23

You might get what you need by setting the Summary period to that date range and drilling down on the rental income account.

24

But I can not export it

25

I tried this and it says
SELECT Date, Account, Description, Amount FROM CashTransactions WHERE Date > 01/04/2016 AND Date < 31/03/2017
it seems to be my date thats causeing the problem

26

This is a bug that has already been reported. It appeared recently.

27

Ok thanks hpe they fix it soon have a set of accounts waiting to print

28

Hi Jon,

Can you please explain to us how to do it?

29

How to do what ?

30

Jon said above: “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).” I was replying to his post.

31

Go to the Sales Invoices tab and click Export in the bottom right corner, you may need to scroll down first.

32

Wornout 14d
I tried this and it says
SELECT Date, Account, Description, Amount FROM CashTransactions WHERE Date > 01/04/2016 AND Date < 31/03/2017
it seems to be my date thats causeing the problem

Hi Is this above problem solved? the query works good for …where date !=Null . so i think it is the date format we need to know. can some one help this out how to frame the sql function of date and what format the table field stores the date, please

33 
SELECT Date, Account, Description, Amount FROM CashTransactions WHERE Date > DateTime(2016,4,1) AND Date < DateTime(2017,03,31)
34

Thanks Dave ,
Why there is no purchases, no work-in -progress and no direct sales expenses in P& L , when there is sales. or did I missed to see it.
BR

----- Reply message -----

35

https://forum.manager.io/t/build-a-chart-of-accounts/9181

You create it, i think. For custom report I don’t know.

36

Thanks Ariff,

I will go through the link, please answer my simple doubt, how come in an accounting/ billing system purchase is not seen. Possibly it is named different here as ( inventory cost) or how is purchases treated.

And 2nd important thing is the “Profit & Loss account” is more appropriate than income & expenses report for any business. So the P&L will show the purchases, sales, direct expenses and the calculated Gross Profit, Then will include the operational expenses and show the Net Profit. Please confirm Is this the way the Manager generate the P&L and am I on a wrong data,

Best Regards

Ismail

37

If you are selling inventory items, then the expense account is Inventory - Cost.
If you aren’t selling inventory items, then you can create any Purchase account that suit your needs.

If you need those types of accounts - create them for yourself as not everybody does.

Yes.
Manager provides you with the flexibility to design any P&L that best suits your exact requirements.

38

Ok, if i direct sale without bringing to inventory, i can accept a purchase invoice and then copy to sales invoice, perfectly it reflects in Purchase, sale and Gross profit. but then i cannot use the sold item detail for my future offer/quote.

The ideal is i should accept the purchase invoice, bring it to the inventory and then make the sales invoice. so the item remains for future offer as well the qty can be supplied as intervals.

so please tell me how to combine the Cost of Sale + Purchase and show in Purchase A/C. ( i tried to pass a journal, but it does not show these accounts).

Secondly how is it treated, when i short supply my customer or i recieve short supply from my suplier,

And i think its a bug, if i had created a sales invoice and material delivered based on a particular sales order, system should not allow issue one more sales invoice. it become duplicate delivery;

39

There is a sample business called Northwind available here. The sample business has a Profit and Loss statement arranged similarly to what you want. I studied its Chart of Accounts to learn how to arrange my accounts. Good luck.

40

Thanks Vest,
that was a very similar business type.
i am gonu present ‘Manager server edition’ as a Accounting & Billing solution to my Sales,Accounts and Top Management. ( i was exploring with the desktop edition since a week)
before that i would like to know,

  1. From when this software is in use
  2. How many installations are till now running on live business environment (And how many satisfied users)
  3. A big question, - How to recover or import data in an event of discontinue of Manager for what ever reason.
  4. How to access to the raw data or to know the data structure / table schema
41

I am just a user, so I can not answer these questions.