Monthly Income/Expense Reports
Gregory
rollenwiese at yahoo.com
Sun Apr 3 13:05:17 EDT 2011
take this out of the order, I only run that when I messing around or
want to regenerate the paths and depths.
update accounts set path=Null, Depth=Null;
Greg
On 04/03/2011 12:59 PM, Gregory wrote:
> 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.
>>
>
More information about the gnucash-user
mailing list