Monthly Income/Expense Reports

[Martin Cunningham]

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