Monthly Income/Expense Reports

Sun Apr 3 12:59:42 EDT 2011

Martin, you can set something up as referenced on this web site:

http://www.sqlteam.com/article/more-trees-hierarchies-in-sql

Check out adjacency lists and materialized path algorithms and you could 
go a few possible directions...

I use mySQL on Linux as a backend and make a copy of the 'production' db 
to use for reporting (just playing around and learning at this point). 
Then I alter the accounts table adding two fields called path and depth, 
then run the following queries on them to populate the new fields. Could 
be varied ways in the form of stored procedures, triggers etc. It's not 
very efficient when you get look closely at it, but I only have to ever 
update it once when the reporting db is refreshed with data from the 
production db.

use `gnucash_development`;
alter table `gnucash_development`.`accounts` add column `path` text; 
alter table `gnucash_development`.`accounts` add column `depth` text;

update accounts set path='', Depth=0 where parent_guid Is Null and 
accounts.name='root account';
update accounts set path=Null, Depth=Null;

update accounts a
inner join accounts p
on (a.parent_guid = p.guid)
set a.depth = p.depth + 1 , a.path = CONCAT(p.path, LTRIM(a.name) , ':')
where p.depth >= 0
and p.path Is Not Null
and a.depth Is Null;

Greg

On 04/01/2011 07:24 PM, Martin Cunningham wrote:
> Please do when you get a chance, David - i would be very interested. I found
> the thread you mentioned here, incidentally:
>
> https://lists.gnucash.org/pipermail/gnucash-user/2009-July/030590.html
>
> Thank you. Great to know there are like-minded users out there.
>
> Martin Cunningham
> T: (415) 448-6854
>
>
> On Fri, Apr 1, 2011 at 3:34 PM, David G. Hamblen<dhamblen at roadrunner.com>wrote:
>
>> I've ported the SQL code posted by markluser back on Jul 20 2009 (you
>> should be able to find the thread in the gnucash-user archives).  His code
>> was for Postgresql, I'm using mysql.  At this point I get a nice Income
>> Statement (aka P&L) by year (or month/day/microsecond)  as well as Balance
>> Sheets by year (or whatever increment I want).  The SQL creates a "view",
>> which can be opened using an OpenOffice Calc Data Pilot (pivot table).
>>
>> I've also expanded a portfolio query which I found in the mailing list (the
>> reference escapes me at the moment).  It produces a Portfolio table within
>> Gnucash which can be plotted, again using OpenOffice calc pivot tables.   I
>> would be happy to share this stuff (given a little time for documenting).
>>
>>
>>
>> On 04/01/2011 05:42 PM, Martin Cunningham wrote:
>>
>>>   Hi -
>>>
>>> I contributed to this thread about a year ago hoping to generate interest
>>> in
>>> starting a library of SELECT queries; when executed against the GnuCash
>>> database, they could serve as ODBC data sources for Excel pivot tables or
>>> to
>>> generate reports in other tools like Access, Crystal Reports, etc. There
>>> doesn't seem to be much interest, or am I looking in the wrong place, or
>>> not
>>> explaining things properly? Im using MySQL as my back end for GnuCash,
>>> incidentally.
>>>
>>> To date, I have been able to produce a functioning query that I'll post
>>> later today that returns every journal entry in the database including
>>> date,
>>> description, debit account, credit account and amount (can't recall how it
>>> handles multiline splits). One key thing that's missing from my query: the
>>> FULL name of the account being debited (or credited), e.g.,
>>> "Assets:Current
>>> Assets:...:My Checking Account" instead of just "My Checking Account",
>>> which
>>> is what i have today. Or assign "Assets" to a column called "AcctLev1",
>>> "Current Assets" to "AcctLev2", etc.
>>>
>>> Has anyone else tried tackling this problem?
>>>
>>> Martin Cunningham
>>> T: (415) 448-6854
>>>
>>>
>>> On Sun, Jul 18, 2010 at 3:53 AM, Geert Janssens
>>> <janssens-geert at telenet.be>wrote:
>>>
>>> On Saturday 17 July 2010, Martin Cunningham wrote:
>>>>
>>>>> Ah - got it. Thank you!
>>>>>
>>>>> Is anyone posting SQL statements they have used to generate reports?
>>>>>
>>>> seems
>>>>
>>>>>   a repository of those would be a useful thing.
>>>>>
>>>>> I think it's a little early for this. The sql backend is currently only
>>>> available for the unstable development series. Most users will wait for
>>>> the
>>>> stable 2.4 release before updating their software, so most users haven't
>>>> played with the sql backend yet.
>>>>
>>>> But if you like you are very welcome to add your results to the GnuCash
>>>> wiki.
>>>> I recently started a "Using GnuCash" section [1], which would be a
>>>> suitable
>>>> place to add useful SQL statements.
>>>>
>>>> Geert
>>>>
>>>> [1] http://wiki.gnucash.org/wiki/Using_GnuCash
>>>>
>>>> Martin Cunningham
>>>>>
>>>>>
>>>>> On Sat, Jul 17, 2010 at 3:34 AM, Geert Janssens
>>>>>
>>>>> <janssens-geert at telenet.be>wrote:
>>>>>
>>>>>> On Friday 16 July 2010, Martin Cunningham wrote:
>>>>>>
>>>>>>> Well I found an answer to one of my questions: the schema is posted
>>>>>>> here<http://wiki.gnucash.org/wiki/SQL>.
>>>>>>> I have downloaded/installed a copy of MySQL and the 'Workbench' GUI.
>>>>>>> Now what - can someone tell me how to connect to the GnuCash data
>>>>>>> source?
>>>>>>>
>>>>>>> Martin Cunningham
>>>>>>>
>>>>>> Did you save your gnucash data into a mysql database already ?
>>>>>>
>>>>>> You can do so by opening your existing data file and use "Save as"
>>>>>> to save it in mysql.
>>>>>>
>>>>>>   From there on, you should be able to simply run your mysql queries in
>>>>>>
>>>>> any
>>>>
>>>>> mysql aware tool (like MySQL workbench).
>>>>>>
>>>>>> Geert
>>>>>>
>>>>>
>>>> _______________________________________________
>>> gnucash-user mailing list
>>> gnucash-user at gnucash.org
>>> https://lists.gnucash.org/mailman/listinfo/gnucash-user
>>> -----
>>> Please remember to CC this list on all your replies.
>>> You can do this by using Reply-To-List or Reply-All.
>>>
>>
>> _______________________________________________
>> gnucash-user mailing list
>> gnucash-user at gnucash.org
>> https://lists.gnucash.org/mailman/listinfo/gnucash-user
>> -----
>> Please remember to CC this list on all your replies.
>> You can do this by using Reply-To-List or Reply-All.
>>
> _______________________________________________
> gnucash-user mailing list
> gnucash-user at gnucash.org
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.
>