Re: [GNC] future of custom reports in 3.x

[Matthew Pounsett]

Thanks very much to William, David, and Sébastien for those links.  That's
all the documentation I could want.

Cheers!
   Matt
___
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.

Re: [GNC] future of custom reports in 3.x

[Sébastien de Menten]

You can also look at
http://piecash.readthedocs.io/en/latest/object_model.html
Piecash is in fact a python ORM (sqlalchemy) on top of the gnucash dql
tables.

On Tue, Apr 10, 2018, 18:14 Matthew Pounsett  wrote:

> On 10 April 2018 at 11:45, Amish  wrote:
>
> > May be fastest and easiest way would be to first save sample data (say
> > only 1 transaction) to say PostgreSQL database and then use pg_dump to
> dump
> > it to text file.
> >
> > That's an approach, for sure.  However...  there are 24 tables just in my
> personal accounts sqllite db, but 21 gnc-*-sql.cpp files in the SQL
> back-end code, and not all of those are created.  That's an odd mismatch
> that needs to be accounted for.  Also, in the database there are no foreign
> keys defined, which means it's going to be difficult to confirm all of the
> expected relations.  That combination of facts makes for a really difficult
> job reverse-engineering the schema by trial and error.   A pointer to the
> documentation that the developers are using when they do their work would
> make the whole thing a lot easier.
> ___
> 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.

Re: [GNC] future of custom reports in 3.x

[David T. via gnucash-user]

Or consult https://wiki.gnucash.org/wiki/SQL 
 and 
https://wiki.gnucash.org/wiki/images/8/86/Gnucash_erd.png 


David T.


> On Apr 10, 2018, at 8:45 PM, Amish  wrote:
> 
> May be fastest and easiest way would be to first save sample data (say only 1 
> transaction) to say PostgreSQL database and then use pg_dump to dump it to 
> text file.
> 
> Amish.
> 
> 
> On Tuesday 10 April 2018 08:57 PM, Matthew Pounsett wrote:
>> 
>> 
>> On 8 April 2018 at 13:13, John Ralls > > wrote:
>> 
>> 
>>In the meantime there are four alternatives for custom reports:
>>2. Learn SQL and use a SQL backend to extract the data you want.
>>The results are generally amenable to import into a spreadsheet
>>for further processing; you could also install the appropriate
>>ODBC module for your SQL engine of choice and connect to it with
>>Libre/OpenOffice, Microsoft Access, or some similar tool with a
>>custom report writer or your favorite programming language’s SQL
>>interface (e.g. DBAPI for Python).
>> 
>> 
>> Is there some clear documentation of the SQL schema somewhere?  I had a look 
>> through the gnc-*-sql.cpp files, but it's not the most transparent 
>> description of a database that I've ever read. :)   I was considering 
>> putting together a set of Python/SQLAlchemy classes that implement the 
>> object-data mapping, and would be happy to contribute it back to the 
>> project, if people thought that'd be useful.
>> 
> 
> ___
> 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.

Re: [GNC] future of custom reports in 3.x

[William Jackson]

> Is there some clear documentation of the SQL schema somewhere?

https://wiki.gnucash.org/wiki/SQL

https://wiki.gnucash.org/wiki/images/8/86/Gnucash_erd.png
___
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.

Re: [GNC] future of custom reports in 3.x

[Matthew Pounsett]

On 10 April 2018 at 11:45, Amish  wrote:

> May be fastest and easiest way would be to first save sample data (say
> only 1 transaction) to say PostgreSQL database and then use pg_dump to dump
> it to text file.
>
> That's an approach, for sure.  However...  there are 24 tables just in my
personal accounts sqllite db, but 21 gnc-*-sql.cpp files in the SQL
back-end code, and not all of those are created.  That's an odd mismatch
that needs to be accounted for.  Also, in the database there are no foreign
keys defined, which means it's going to be difficult to confirm all of the
expected relations.  That combination of facts makes for a really difficult
job reverse-engineering the schema by trial and error.   A pointer to the
documentation that the developers are using when they do their work would
make the whole thing a lot easier.
___
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.

Re: [GNC] future of custom reports in 3.x

[Amish]

May be fastest and easiest way would be to first save sample data (say 
only 1 transaction) to say PostgreSQL database and then use pg_dump to 
dump it to text file.


Amish.


On Tuesday 10 April 2018 08:57 PM, Matthew Pounsett wrote:



On 8 April 2018 at 13:13, John Ralls > wrote:



In the meantime there are four alternatives for custom reports:
2. Learn SQL and use a SQL backend to extract the data you want.
The results are generally amenable to import into a spreadsheet
for further processing; you could also install the appropriate
ODBC module for your SQL engine of choice and connect to it with
Libre/OpenOffice, Microsoft Access, or some similar tool with a
custom report writer or your favorite programming language’s SQL
interface (e.g. DBAPI for Python).


Is there some clear documentation of the SQL schema somewhere?  I had 
a look through the gnc-*-sql.cpp files, but it's not the most 
transparent description of a database that I've ever read. :)   I was 
considering putting together a set of Python/SQLAlchemy classes that 
implement the object-data mapping, and would be happy to contribute it 
back to the project, if people thought that'd be useful.




___
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.

Re: [GNC] future of custom reports in 3.x

[Matthew Pounsett]

On 8 April 2018 at 13:13, John Ralls  wrote:

>
> In the meantime there are four alternatives for custom reports:
> 2. Learn SQL and use a SQL backend to extract the data you want. The
> results are generally amenable to import into a spreadsheet for further
> processing; you could also install the appropriate ODBC module for your SQL
> engine of choice and connect to it with Libre/OpenOffice, Microsoft Access,
> or some similar tool with a custom report writer or your favorite
> programming language’s SQL interface (e.g. DBAPI for Python).


Is there some clear documentation of the SQL schema somewhere?  I had a
look through the gnc-*-sql.cpp files, but it's not the most transparent
description of a database that I've ever read. :)   I was considering
putting together a set of Python/SQLAlchemy classes that implement the
object-data mapping, and would be happy to contribute it back to the
project, if people thought that'd be useful.
___
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.

Re: [GNC] future of custom reports in 3.x

[Sébastien de Menten]

Re running reports within gnucash, I have toyed with the idea to be able to
call from gnucash a local web server to run a report and return the HTML to
gnucash (or even other mimetype like an excel, csv or word file). It needed
the Web Client module for guile (
https://www.gnu.org/software/guile/manual/html_node/Web-Client.html) which
is only avail as of guile 2.0.

With gnucash 3.0 that uses guile 2.2, I will work again on this and I am
more than open to extend the discussion to have a simple protocol/standard
to call a web server (the web server implements a simple Web API to get the
options and another one to get the result when passing both the gnucash URI
and the value of the options). My web server was running locally and was
implemented (obviously) with python and piecash.

Anyone interested ?

sebastien


On Apr 9, 2018 07:14, "Saša Janiška"  wrote:

John Ralls  writes:

> While we would like to replace the reporting system with something
> more flexible and user friendly we’re a very long way from being in a
> position to even start on it.

OK - that's nice to know. ;)


> In the meantime there are four alternatives for custom reports:

> 4. With an XML file use your favorite language's XML bindings with
> XPATH or XQUERY to retrieve the data and format your report.

I'd rather stay away from fiddling with XML stuff.


> 3. Learn Python and use either the python bindings or PieCash (also
> requires the SQL backend) to query the database and write custom
> reports.

I'll take a closer look at this option.


> 2. Learn SQL and use a SQL backend to extract the data you want. The
> results are generally amenable to import into a spreadsheet for
> further processing; you could also install the appropriate ODBC module
> for your SQL engine of choice and connect to it with Libre/OpenOffice,
> Microsoft Access, or some similar tool with a custom report writer or
> your favorite programming language’s SQL interface (e.g. DBAPI for
> Python).

This one seems to be close to 2nd option...


> 1. Learn Scheme and qof-query to make custom reports for use inside
> GnuCash.

This seems to be the most lucrative option enabling one to take
advantage of GC's infrastructure, but just wonder if replacing qof-query
with SQL query query would still allow using it along with Scheme since
"make reports for use inside GnuCash" certainly has its adavantages?


Sincerely,
Gour

--
You have a right to perform your prescribed duty, but you
are not entitled to the fruits of action. Never consider
yourself the cause of the results of your activities,
and never be attached to not doing your duty.


___
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.

Re: [GNC] future of custom reports in 3.x

[Geert Janssens]

Op maandag 9 april 2018 07:12:29 CEST schreef Saša Janiška:
> John Ralls  writes:
> > 3. Learn Python and use either the python bindings or PieCash (also
> > requires the SQL backend) to query the database and write custom
> > reports.
> 
> I'll take a closer look at this option.
> 
> > 2. Learn SQL and use a SQL backend to extract the data you want. The
> > results are generally amenable to import into a spreadsheet for
> > further processing; you could also install the appropriate ODBC module
> > for your SQL engine of choice and connect to it with Libre/OpenOffice,
> > Microsoft Access, or some similar tool with a custom report writer or
> > your favorite programming language’s SQL interface (e.g. DBAPI for
> > Python).
> 
> This one seems to be close to 2nd option...

It is similar but not the same. PieCash reimplements the gnucash business 
logic in python, where our python bindings use the gnucash built-in business 
logic.

> 
> > 1. Learn Scheme and qof-query to make custom reports for use inside
> > GnuCash.
> 
> This seems to be the most lucrative option enabling one to take
> advantage of GC's infrastructure, but just wonder if replacing qof-query
> with SQL query query would still allow using it along with Scheme since
> "make reports for use inside GnuCash" certainly has its adavantages?

That's the long term plan - replacing qof-query with sql query. And that's 
what John hinted at - we're still far away from that. And chances are scheme 
will be replaced as well, though we don't know yet by what (we recently came 
across the mustache template language that may be considered, but again we're 
still far away from that).

Geert


___
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.

Re: [GNC] future of custom reports in 3.x

[Saša Janiška]

John Ralls  writes:

> While we would like to replace the reporting system with something
> more flexible and user friendly we’re a very long way from being in a
> position to even start on it.

OK - that's nice to know. ;)

> In the meantime there are four alternatives for custom reports:

> 4. With an XML file use your favorite language's XML bindings with
> XPATH or XQUERY to retrieve the data and format your report.

I'd rather stay away from fiddling with XML stuff.

> 3. Learn Python and use either the python bindings or PieCash (also
> requires the SQL backend) to query the database and write custom
> reports.

I'll take a closer look at this option.

> 2. Learn SQL and use a SQL backend to extract the data you want. The
> results are generally amenable to import into a spreadsheet for
> further processing; you could also install the appropriate ODBC module
> for your SQL engine of choice and connect to it with Libre/OpenOffice,
> Microsoft Access, or some similar tool with a custom report writer or
> your favorite programming language’s SQL interface (e.g. DBAPI for
> Python).

This one seems to be close to 2nd option...

> 1. Learn Scheme and qof-query to make custom reports for use inside
> GnuCash.

This seems to be the most lucrative option enabling one to take
advantage of GC's infrastructure, but just wonder if replacing qof-query
with SQL query query would still allow using it along with Scheme since
"make reports for use inside GnuCash" certainly has its adavantages?


Sincerely,
Gour

-- 
You have a right to perform your prescribed duty, but you
are not entitled to the fruits of action. Never consider
yourself the cause of the results of your activities,
and never be attached to not doing your duty.

___
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.

Re: [GNC] future of custom reports in 3.x

[John Ralls]

While we would like to replace the reporting system with something more 
flexible and user friendly we’re a very long way from being in a position to 
even start on it.

In the meantime there are four alternatives for custom reports:
1. Learn Scheme and qof-query to make custom reports for use inside GnuCash.
2. Learn SQL and use a SQL backend to extract the data you want. The results 
are generally amenable to import into a spreadsheet for further processing; you 
could also install the appropriate ODBC module for your SQL engine of choice 
and connect to it with Libre/OpenOffice, Microsoft Access, or some similar tool 
with a custom report writer or your favorite programming language’s SQL 
interface (e.g. DBAPI for Python).
3. Learn Python and use either the python bindings or PieCash (also requires 
the SQL backend) to query the database and write custom reports.
4. With an XML file use your favorite language's XML bindings with XPATH or 
XQUERY to retrieve the data and format your report.

Regards,
John Ralls

> On Apr 8, 2018, at 3:33 AM, Amish  wrote:
> 
> I wonder the same too. Although new reports are enough for me now.
> 
> But I had to delete lots of old custom reports because they no more worked 
> (or compiled)
> 
> But in future I may want to have my own custom reports.
> 
> I wish there was GnuCash library with we could simply call 
> Gnc_LoadFile("path") and then we could also do all the magical things that 
> GnuCash does!
> 
> Amish
> 
> On Sunday 08 April 2018 02:38 PM, Saša Janiška wrote:
>> Hello,
>> 
>> while having problems to load certain (ytd-budget.scm) report in 2.6.20
>> under (Fedora) Linux i nthe attempt to return back to GnuCash from
>> (h)ledger, I wonder if there is some plan in regard to the future of
>> custom reports, iow. is it still recommended to learn some Scheme in
>> order to achieve/tweak  what is missing, do you envision using Python or
>> something else suitable to enable end-users write custom reports (more)
>> easily?
>> 
>> 
>> Sincerely,
>> Gour
>> 
> 
> ___
> 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.

Re: [GNC] future of custom reports in 3.x

[Amish]

I wonder the same too. Although new reports are enough for me now.

But I had to delete lots of old custom reports because they no more 
worked (or compiled)


But in future I may want to have my own custom reports.

I wish there was GnuCash library with we could simply call 
Gnc_LoadFile("path") and then we could also do all the magical things 
that GnuCash does!


Amish

On Sunday 08 April 2018 02:38 PM, Saša Janiška wrote:

Hello,

while having problems to load certain (ytd-budget.scm) report in 2.6.20
under (Fedora) Linux i nthe attempt to return back to GnuCash from
(h)ledger, I wonder if there is some plan in regard to the future of
custom reports, iow. is it still recommended to learn some Scheme in
order to achieve/tweak  what is missing, do you envision using Python or
something else suitable to enable end-users write custom reports (more)
easily?


Sincerely,
Gour



___
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.