Date changes

classic Classic list List threaded Threaded
22 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Date changes

parakrama
Hello Team,

 

I downloaded Gnucash on 17th and tried to record transactions from last
year. However I found that the system does not show any date before
YESTERDAY, 17TH February 2018.

 

My requirement is to record transaction from July 2017 and update upto now
and use it then

 

Can someone tell me how to record backdated transactions and use it?

 

 

Parakrama Weerasinghe

_______________________________________________
gnucash-user mailing list
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

Re: Date changes

David Carlson-4
Start by reading the Tutorial and the Help Manual.  Once you have created a
chart of accounts you just open the register for whichever account you want
to enter a transaction into, type any date into the date box and go from
there.

David C

On Mon, Feb 19, 2018 at 8:22 AM, <[hidden email]> wrote:

> Hello Team,
>
>
>
> I downloaded Gnucash on 17th and tried to record transactions from last
> year. However I found that the system does not show any date before
> YESTERDAY, 17TH February 2018.
>
>
>
> My requirement is to record transaction from July 2017 and update upto now
> and use it then
>
>
>
> Can someone tell me how to record backdated transactions and use it?
>
>
>
>
>
> Parakrama Weerasinghe
>
> _______________________________________________
> gnucash-user mailing list
> [hidden email]
> 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
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

Re: Date changes

Greg Feneis
There is a setting that might prevent you from seeing older transactions
once you've entered them.

When you have a register open, use the View menu

View > Filter by > Show all

If it was set to a date range that excludes an old transaction date, it
would be possible for a newly entered old transaction to seem to
disappear.





Kind regards,

Greg Feneis



On Mon, Feb 19, 2018 at 7:12 PM, David Carlson <[hidden email]>
wrote:

> Start by reading the Tutorial and the Help Manual.  Once you have created a
> chart of accounts you just open the register for whichever account you want
> to enter a transaction into, type any date into the date box and go from
> there.
>
> David C
>
> On Mon, Feb 19, 2018 at 8:22 AM, <[hidden email]> wrote:
>
> > Hello Team,
> >
> >
> >
> > I downloaded Gnucash on 17th and tried to record transactions from last
> > year. However I found that the system does not show any date before
> > YESTERDAY, 17TH February 2018.
> >
> >
> >
> > My requirement is to record transaction from July 2017 and update upto
> now
> > and use it then
> >
> >
> >
> > Can someone tell me how to record backdated transactions and use it?
> >
> >
> >
> >
> >
> > Parakrama Weerasinghe
> >
> > _______________________________________________
> > gnucash-user mailing list
> > [hidden email]
> > 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
> [hidden email]
> 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
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

Re: Date changes

Geert Janssens-4
Op dinsdag 20 februari 2018 08:34:58 CET schreef Greg Feneis:

> There is a setting that might prevent you from seeing older transactions
> once you've entered them.
>
> When you have a register open, use the View menu
>
> View > Filter by > Show all
>
> If it was set to a date range that excludes an old transaction date, it
> would be possible for a newly entered old transaction to seem to
> disappear.
>
>
>
>
>
> Kind regards,
>
> Greg Feneis
>
In addition and contrary to normal account registers, the general ledger is
restricted to one month worth of transactions by default. You can adjust the
filter as Greg suggest for the general ledger as well, but this filter won't
stick so you have to redo it after each gnucash restart.

Regards,

Geert


_______________________________________________
gnucash-user mailing list
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

Re: Date changes

Paul Konnersman
In reply to this post by Greg Feneis
Greg,

I think that you will find what you're looking for here:

MenuBar> File> Accounts> "Day Threshold to Read-Only Transactions (red line)

If you enter zero in that box all transactions will be available in
Read/Write,
If you enter the numeral "n" in that box all transactions will be available
in Read/Write for the last "n" days, but read only if the transaction date
is more than "n" in the past.

Paul

On Tue, Feb 20, 2018 at 2:34 AM, Greg Feneis <[hidden email]> wrote:

> There is a setting that might prevent you from seeing older transactions
> once you've entered them.
>
> When you have a register open, use the View menu
>
> View > Filter by > Show all
>
> If it was set to a date range that excludes an old transaction date, it
> would be possible for a newly entered old transaction to seem to
> disappear.
>
>
>
>
>
> Kind regards,
>
> Greg Feneis
>
>
>
> On Mon, Feb 19, 2018 at 7:12 PM, David Carlson <
> [hidden email]>
> wrote:
>
> > Start by reading the Tutorial and the Help Manual.  Once you have
> created a
> > chart of accounts you just open the register for whichever account you
> want
> > to enter a transaction into, type any date into the date box and go from
> > there.
> >
> > David C
> >
> > On Mon, Feb 19, 2018 at 8:22 AM, <[hidden email]>
> wrote:
> >
> > > Hello Team,
> > >
> > >
> > >
> > > I downloaded Gnucash on 17th and tried to record transactions from last
> > > year. However I found that the system does not show any date before
> > > YESTERDAY, 17TH February 2018.
> > >
> > >
> > >
> > > My requirement is to record transaction from July 2017 and update upto
> > now
> > > and use it then
> > >
> > >
> > >
> > > Can someone tell me how to record backdated transactions and use it?
> > >
> > >
> > >
> > >
> > >
> > > Parakrama Weerasinghe
> > >
> > > _______________________________________________
> > > gnucash-user mailing list
> > > [hidden email]
> > > 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
> > [hidden email]
> > 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
> [hidden email]
> 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.
>



--
*Paul M. Konnersman*
*[hidden email] <[hidden email]>*
*781-639-0616*
_______________________________________________
gnucash-user mailing list
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

Re: Date changes

Greg Feneis
Paul,

I replied with a solution, I'm not searching for anything.

You may want to address Parakrama,






Kind regards,

Greg Feneis



On Tue, Feb 20, 2018 at 11:30 AM, Paul Konnersman <[hidden email]>
wrote:

> Greg,
>
> I think that you will find what you're looking for here:
>
> MenuBar> File> Accounts> "Day Threshold to Read-Only Transactions (red
> line)
>
> If you enter zero in that box all transactions will be available in
> Read/Write,
> If you enter the numeral "n" in that box all transactions will be
> available in Read/Write for the last "n" days, but read only if the
> transaction date is more than "n" in the past.
>
> Paul
>
> On Tue, Feb 20, 2018 at 2:34 AM, Greg Feneis <[hidden email]> wrote:
>
>> There is a setting that might prevent you from seeing older transactions
>> once you've entered them.
>>
>> When you have a register open, use the View menu
>>
>> View > Filter by > Show all
>>
>> If it was set to a date range that excludes an old transaction date, it
>> would be possible for a newly entered old transaction to seem to
>> disappear.
>>
>>
>>
>>
>>
>> Kind regards,
>>
>> Greg Feneis
>>
>>
>>
>> On Mon, Feb 19, 2018 at 7:12 PM, David Carlson <
>> [hidden email]>
>> wrote:
>>
>> > Start by reading the Tutorial and the Help Manual.  Once you have
>> created a
>> > chart of accounts you just open the register for whichever account you
>> want
>> > to enter a transaction into, type any date into the date box and go from
>> > there.
>> >
>> > David C
>> >
>> > On Mon, Feb 19, 2018 at 8:22 AM, <[hidden email]>
>> wrote:
>> >
>> > > Hello Team,
>> > >
>> > >
>> > >
>> > > I downloaded Gnucash on 17th and tried to record transactions from
>> last
>> > > year. However I found that the system does not show any date before
>> > > YESTERDAY, 17TH February 2018.
>> > >
>> > >
>> > >
>> > > My requirement is to record transaction from July 2017 and update upto
>> > now
>> > > and use it then
>> > >
>> > >
>> > >
>> > > Can someone tell me how to record backdated transactions and use it?
>> > >
>> > >
>> > >
>> > >
>> > >
>> > > Parakrama Weerasinghe
>> > >
>> > > _______________________________________________
>> > > gnucash-user mailing list
>> > > [hidden email]
>> > > 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
>> > [hidden email]
>> > 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
>> [hidden email]
>> 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.
>>
>
>
>
> --
> *Paul M. Konnersman*
> *[hidden email] <[hidden email]>*
> *781-639-0616 <(781)%20639-0616>*
>
_______________________________________________
gnucash-user mailing list
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

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

GnuCash - User mailing list
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
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

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

Adrien Monteleone
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 <[hidden email]> 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
> [hidden email]
> 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
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

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

GnuCash - User mailing list
 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 <[hidden email]> 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 <[hidden email]> 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
> [hidden email]
> 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
[hidden email]
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
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

Re: Date changes

Stan Brown
In reply to this post by parakrama

On 2018-02-19 09:22, [hidden email] wrote:

> I downloaded Gnucash on 17th and tried to record transactions from
> last year. However I found that the system does not show any date
> before YESTERDAY, 17TH February 2018.
>
> My requirement is to record transaction from July 2017 and update
> upto now
>
> Can someone tell me how to record backdated transactions and use it?
Hi. New user here, but I'll jump in.

I'm not sure what you mean by "does not show any date before". When you
enter transactions, you ENTER a date. Sure, it shows you a date by
default, but don't have to use that. Just highlight the date shown, and
enter 2017-07-15 or whatever (in your date format).

There's a nice shortcut. In Edit » Preferences, on the Date/Tie tab,
select the last radio button, "in a sliding 12-month window" and select
8 for "this many months before the current month." Then you can enter a
date as 7-15 (for example, but use your format) and it will supply 2017
because the "window" runs from 8 months before now to 4 months after now.

--
Regards,
Stan Brown
Tompkins County, New York, USA
http://BrownMath.com
http://OakRoadSystems.com>

>  
>
>  
>
> Parakrama Weerasinghe
>
> _______________________________________________
> gnucash-user mailing list
> [hidden email]
> 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
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

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

Adrien Monteleone
In reply to this post by GnuCash - User mailing list
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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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
> > [hidden email]
> > 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
> [hidden email]
> 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
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

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

GnuCash - User mailing list
 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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
> > [hidden email]
> > 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
> [hidden email]
> 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
[hidden email]
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
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

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

Adrien Monteleone
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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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
> > > [hidden email]
> > > 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
> > [hidden email]
> > 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
> [hidden email]
> 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
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

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

Fred Bone
On 21 February 2018 at 14:04, Adrien Monteleone said:

[...]
> 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)

Er, no ...
https://en.wikipedia.org/wiki/SQLite#History

_______________________________________________
gnucash-user mailing list
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

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

Adrien Monteleone
I stand corrected and humbled. Perhaps I should be more careful to verify what I read, certainly before passing it along.

Thanks for the lesson.

Regards,
Adrien


> On Feb 21, 2018, at 3:41 PM, Fred Bone <[hidden email]> wrote:
>
> https://en.wikipedia.org/wiki/SQLite#History




_______________________________________________
gnucash-user mailing list
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

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

Derek Atkins
In reply to this post by GnuCash - User mailing list
Hi,

Fran_3 via gnucash-user <[hidden email]> writes:

>  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

SQLite is not MySQL.  And MySQL is not IIS.

SQLite is a software library than implements a SQL-based DB within a
local file.  So from a USER'S perspective, you get a single file (just
like you get a single file with the current XML backend).  However
internally the data is stored and accessed like a SQL datastore.

Down the road, I expect SQLite to become the "default" storage
mechanism, because you get all the benefits of SQL (save-on-commit, etc)
without the drawbacks of requiring a DBA or running server that you need
to configure.

For #3/#4, yes, PieCash is a python library that re-implements a bunch of
gnucash functionality.  Note that it does NOT use the GnuCash API, so
it's quite possible that using PieCash could destroy your data.  YMMV.
You are safe using it for read-only purposes.

The GnuCash developers always say that the only supported means to
manipulate your GnuCash data is via the GnuCash API.

> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.

-derek

--
       Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
       Member, MIT Student Information Processing Board  (SIPB)
       URL: http://web.mit.edu/warlord/    PP-ASEL-IA     N1NWH
       [hidden email]                        PGP key available
_______________________________________________
gnucash-user mailing list
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

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

GnuCash - User mailing list
In reply to this post by Adrien Monteleone
 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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
> > > [hidden email]
> > > 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
> > [hidden email]
> > 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
> [hidden email]
> 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
[hidden email]
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
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

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

GnuCash - User mailing list
In reply to this post by Derek Atkins
 Scott,
Thanks for your comments. As I mentioned to Adrien, I don't think I'm going to charge off and start developing my own gnuCash queries using any tool. We are going to just use it out of the box and develop work-arrounds where necessary/
We are still learn the application but so far really like it.
Thanks again for the help.
Fran3

    On Thursday, February 22, 2018, 10:31:02 AM EST, Derek Atkins <[hidden email]> wrote:  
 
 Hi,

Fran_3 via gnucash-user <[hidden email]> writes:

>  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

SQLite is not MySQL.  And MySQL is not IIS.

SQLite is a software library than implements a SQL-based DB within a
local file.  So from a USER'S perspective, you get a single file (just
like you get a single file with the current XML backend).  However
internally the data is stored and accessed like a SQL datastore.

Down the road, I expect SQLite to become the "default" storage
mechanism, because you get all the benefits of SQL (save-on-commit, etc)
without the drawbacks of requiring a DBA or running server that you need
to configure.

For #3/#4, yes, PieCash is a python library that re-implements a bunch of
gnucash functionality.  Note that it does NOT use the GnuCash API, so
it's quite possible that using PieCash could destroy your data.  YMMV.
You are safe using it for read-only purposes.

The GnuCash developers always say that the only supported means to
manipulate your GnuCash data is via the GnuCash API.

> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.

-derek

--
      Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
      Member, MIT Student Information Processing Board  (SIPB)
      URL: http://web.mit.edu/warlord/   PP-ASEL-IA    N1NWH
      [hidden email]                        PGP key available
 
_______________________________________________
gnucash-user mailing list
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

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

Adrien Monteleone
In reply to this post by GnuCash - User mailing list
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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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
> > > > [hidden email]
> > > > 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
> > > [hidden email]
> > > 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
> > [hidden email]
> > 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
> [hidden email]
> 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
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

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

Colin Law
In reply to this post by GnuCash - User mailing list
On 22 February 2018 at 18:36, Fran_3 via gnucash-user <
[hidden email]> wrote:

>  Scott,
> Thanks for your comments. As I mentioned to Adrien, I don't think I'm
> going to charge off and start developing my own gnuCash queries using any
> tool. We are going to just use it out of the box and develop work-arrounds
> where necessary
>

In that case I don't think there is any point doing anything other than
sticking to the default XML format.

Colin


> We are still learn the application but so far really like it.
> Thanks again for the help.
> Fran3
>
>     On Thursday, February 22, 2018, 10:31:02 AM EST, Derek Atkins <
> [hidden email]> wrote:
>
>  Hi,
>
> Fran_3 via gnucash-user <[hidden email]> writes:
>
> >  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
>
> SQLite is not MySQL.  And MySQL is not IIS.
>
> SQLite is a software library than implements a SQL-based DB within a
> local file.  So from a USER'S perspective, you get a single file (just
> like you get a single file with the current XML backend).  However
> internally the data is stored and accessed like a SQL datastore.
>
> Down the road, I expect SQLite to become the "default" storage
> mechanism, because you get all the benefits of SQL (save-on-commit, etc)
> without the drawbacks of requiring a DBA or running server that you need
> to configure.
>
> For #3/#4, yes, PieCash is a python library that re-implements a bunch of
> gnucash functionality.  Note that it does NOT use the GnuCash API, so
> it's quite possible that using PieCash could destroy your data.  YMMV.
> You are safe using it for read-only purposes.
>
> The GnuCash developers always say that the only supported means to
> manipulate your GnuCash data is via the GnuCash API.
>
> > Please remember to CC this list on all your replies.
> > You can do this by using Reply-To-List or Reply-All.
>
> -derek
>
> --
>       Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
>       Member, MIT Student Information Processing Board  (SIPB)
>       URL: http://web.mit.edu/warlord/   PP-ASEL-IA    N1NWH
>       [hidden email]                        PGP key available
>
> _______________________________________________
> gnucash-user mailing list
> [hidden email]
> 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
[hidden email]
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.
12