Monthly Income/Expense Reports

[David G. Hamblen]

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.