Monthly Billings Report ? (How much business did we book/Invoice during month X)

[Adrien Monteleone]

In order to use SQL on the Gnucash data, it needs to be saved in that format. This requires the particular backend drivers (sqlite3, MySQL) be installed already for the format to be available as a choice in the Save As dialog. See the installation notes on the wiki for more info.

There is no built-in facility for writing queries. You’d have to access the datafile from something else.

You may be interested in PieCash which allows for accessing either the XML or SQL formatted data file for report writing. https://github.com/sdementen/piecash

This was also posted to the mailing list recently and is an example of using PieCash: https://github.com/ebridges/accounting-reports

Before you get started, you’ll want to look over this: https://wiki.gnucash.org/wiki/FAQ#SQL_Database, and https://wiki.gnucash.org/wiki/SQL and mind the warnings never to WRITE to the data file from outside of Gnucash. (at least for the foreseeable future)

Here’s the SQL table map - yes, it’s ugly: https://wiki.gnucash.org/wiki/images/8/86/Gnucash_erd.png

Unless you are comfortable with Scheme, or learning a new language, your best bet is definitely to stick with the SQL/PieCash route.


Regards,
Adrien

> On Feb 20, 2018, at 4:59 PM, Fran_3 <mailbox0600 at yahoo.com> wrote:
> 
> Thanks Adrien,
> 
> Regarding custom reports... I do SQL but am having gnuCash save account files in it's default XML format.
> 
> Where would I get a list of the tables in gnuCash and their associated fields/columns?
> 
> Is there a window I can open to just put in an SQL query or what?
> 
> Can I create an SQL query that will query the default XML format file format?
> 
> (I've actually never considered using XML for a full fledged database until I just started using gnuCash and discovered that it uses XML)
> 
> Or do I have all of this wrong?
> 
> thanks again.
> 
> 
> 
> On Tuesday, February 20, 2018, 5:14:38 PM EST, Adrien Monteleone <adrien.monteleone at gmail.com> wrote:
> 
> 
> You could leverage a multi-column report perhaps with a customer report in each column, that will get you invoice & payment detail, but no aggregate totals across all customers. (but each customer would have it’s own total)
> 
> Then there is the P&L/Income Statement, but it will show you revenue by type (account) not by customer. It will have a total however.
> 
> Finally, there is the Customer Report. The Sales column will show what you want. Though you may not need the other columns, the only one you can turn off is the expense column. I’ve yet to figure out where to put costs or markup figures for that report to make much sense. It will report a sales total per customer with a grand total.
> 
> If you want to see revenue by type AND customer with totals, you’ll have to make your own report either with SQL or Scheme.
> 
> All of these reports (as are any others in GnuCash) allow you to set either the date of the report, (such as for a Balance Sheet) or the date range the report covers. There are shortcut date selectors for month, quarter, year and ‘accounting period’ (if your fiscal year differs from the calendar) as well as boxes to let you put in any specific dates you want. You’ll find that under the General tab of any report options.
> 
> 
> Regards,
> Adrien
> 
> > On Feb 20, 2018, at 3:41 PM, Fran_3 via gnucash-user <gnucash-user at gnucash.org> wrote:
> > 
> > I'm sure there is a way but I haven't found it yet...
> > How do we run a report to see how much business we booked/invoiced for a particular month?Total and/or including a list of customers and amounts?
> > Ditto for  a specified period of time... current quarter, last quarter, so far this year... so far this date last year... etc?
> > Thanks for any help.
> > Fran 3
> 
> > _______________________________________________
> > gnucash-user mailing list
> > gnucash-user at gnucash.org
> > To update your subscription preferences or to unsubscribe:
> > https://lists.gnucash.org/mailman/listinfo/gnucash-user
> > If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
> > -----
> > Please remember to CC this list on all your replies.
> > You can do this by using Reply-To-List or Reply-All.
> 
> _______________________________________________
> gnucash-user mailing list
> gnucash-user at gnucash.org
> To update your subscription preferences or to unsubscribe:
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.