Monthly Income/Expense Reports

[Martin Cunningham]

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