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

Thu Feb 22 13:55:05 EST 2018

Fran3, consider this option down the road should you take the SQL/Gnucash plunge:

Setup a cloud instance (if you’re comfortable with that) to house your GnuCash data file and MySQL server. (or setup access for the users to the IIS server accordingly) I suppose you could also just point the IIS/cloud instance to the GoogleDrive location.

For now, you’ll still need to sync the data file rather than risking multi-user edits. But this setup would allow for any user with access to the cloud instance/IIS (via a browser) to run the report scripts since they are running from the cloud instance (or IIS server) rather than their own machines. If you generate the reports as PDF they’ll be viewable right in the browser. (I think all modern browsers support PDF rendering these days, even Edge, not sure about IE 11 though.)

Best of luck to you.

Regards,
Adrien

> On Feb 22, 2018, at 12:26 PM, Fran_3 <mailbox0600 at yahoo.com> wrote:
> 
> Thank you Adrien. Your comments have been very helpful.
> 
> I'm running IIS and MySQL on my PC as I do some database work... having moved from Micrsoft Access... but I don't want to make all our small staff to have to be running a server and mysql.
> 
> The way we have it setup is the gnuCash file is saved locally on our pc's and mirrored on a special Google Drive account... meaning we setup a special Google Drive account and sync'd it to our local machines.
> 
> Now we can use gnuCash at work or at home as the account file is sync'd to those computers and available on the local hard drive.
> 
> I'll have to think carefully before charging off and trying to create my own custom queries for gnuCash.
> 
> I'll give the PieCash utilities a closer look.
> 
> Thanks again,
> 
> Fran3
> 
> 
> On Wednesday, February 21, 2018, 3:05:19 PM EST, Adrien Monteleone <adrien.monteleone at gmail.com> wrote:
> 
> 
> If you need MySQL, you have to install a MySQL server. (on any platform that doesn’t have it included) IIS is one way to do that on Windows. You can also install a WAMP stack. (Windows, Apache, MySQL, PHP) There are one-click installs available online. I would highly recommend installing PhpMyadmin if you go that route as it makes database admin a bit easier than the CLI interface. (it uses a web browser)
> 
> SQLite is a flat-file db using SQL principles. It has some limitations and doesn’t work well for all situations. iTunes is probably the most widely used app to employ it. (I think it was even designed by Apple especially for iTunes, then later released to the public as a general purpose db) One of the main limitations is it does not store date/time stamps like MySQL or the XML file. So you may have issues there with some otherwise well-formed queries.
> 
> You can query the file using SQL, and it has some internal representations that are table-like, but it is not the same as MySQL/MariaDb.
> 
> It is not a version of MySQL, but you are correct, it does not require a server instance. I’m not familiar with its use on Windows, but I’m sure that info isn’t hard to find.
> 
> PieCash is a Python-like interface to the Gnucash data files. (in whatever format they may be) It gives you functions you can use to write Python scripts to accomplish your tasks, in this case, retrieving data for custom reporting. I’ve yet to experiment with it myself, but it is discussed much here on the list. Until Gnucash becomes a proper database application, it is probably the easiest path for external reporting, data manipulation, and integration with other apps. As far as I understand it, it is not a collection of programs to execute. (though some examples may be included) That would be your own custom Python scripts I just mentioned. The GitHub link I gave you from ebridges is just such an example of some report scripts you can write using PieCash.
> 
> Though if you’ve saved in a db format (as opposed to XML) you can certainly write SQL statements directly to read the Gnucash tables without using PieCash. (just don’t WRITE to those tables!) Again, either via CLI or PhpMyadmin.
> 
> You could also write custom XML parsers in some other language if you like, but PieCash already has that covered, so it’s probably easiest to not re-invent the wheel.
> 
> Unfortunately, that’s all I have to offer. If you need more detailed help, I’ll have to defer to someone already working with either PieCash or MySQL. (I plan to, I just haven’t had the time to dig in yet)
> 
> 
> Regards,
> Adrien
> 
> > On Feb 21, 2018, at 11:12 AM, Fran_3 <mailbox0600 at yahoo.com> wrote:
> > 
> > Re: Converting to MySQL...
> > 
> > When I develop in MySQL on Windows I install IIS localhost server and MySQL... but that is not practical on other office computers.
> > 
> > 1 - I'm not familiar with sqLite but a quick Google makes me think it is a way to run MySQL on a local PC without running server software like IIS... right?
> > 
> > 2 - Or is sqLite a desktop version of MySQL that requires no server?
> > 
> > 3 - Also, it looks like PieCash is a collection of Python programs... right?
> > 
> > 4 - And if so then you simply have to have the Python interrupter installed on your machine and then run the appropriate PieCash program after editing in the name and path to the gnuCash file... right?
> > 
> > Thanks again Adrien.
> > 
> > Fran3
> > 
> > 
> > 
> > 
> > 
> > 
> > On Tuesday, February 20, 2018, 9:54:08 PM EST, Adrien Monteleone <adrien.monteleone at gmail.com> wrote:
> > 
> > 
> > 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.
> > 
> > _______________________________________________
> > 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.