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 <mailbox0...@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.montele...@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@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@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@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@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.
