Monthly Income/Expense Reports

Martin Cunningham martin.cunningham at gmail.com
Fri Apr 1 19:14:10 EDT 2011


Very nice! I'll give it a try. NULLIF may be equivalent to Nz.

Thank you!

Martin Cunningham
T: (415) 448-6854


On Fri, Apr 1, 2011 at 3:18 PM, Robin Chattopadhyay <robinraymn at gmail.com>wrote:

> Hi-
>
> I've had good luck with a recursive query. It works with my CoA because I
> have no more than five levels. You would have to add another level of
> recursion if you have more than five levels deep.
>
> Call this query "accounttree":
>
> SELECT
> Nz([l4].[code],Nz([l3].[code],Nz([l2].[code],Nz([l1].[code],Nz([top].[code],"")))))
> AS acct_code,
> Nz([l4].[guid],Nz([l3].[guid],Nz([l2].[guid],Nz([l1].[guid],Nz([top].[guid],"")))))
> AS acct_guid, top.code, top.name, L1.code, L1.name, L2.code, L2.name,
> L3.code, L3.name, L4.code, L4.name, L3.hidden, L4.hidden
> FROM ((((accounts AS root LEFT JOIN accounts AS [top] ON root.guid =
> top.parent_guid) LEFT JOIN accounts AS L1 ON top.guid = L1.parent_guid) LEFT
> JOIN accounts AS L2 ON L1.guid = L2.parent_guid) LEFT JOIN accounts AS L3 ON
> L2.guid = L3.parent_guid) LEFT JOIN accounts AS L4 ON L3.guid =
> L4.parent_guid
> WHERE (((top.code) Not Like "9*") AND ((root.account_type)="ROOT") AND ((
> root.name)="Root Account"))
> ORDER BY top.code, L1.code, L2.code, L3.code, L4.code;
>
> From there, I can have a query that joins the splits table to the query on
> the acct_guid. Like this:
>
> SELECT splits.*, accounttree.*
> FROM splits INNER JOIN accounttree ON splits.account_guid =
> accounttree.acct_guid
>
> NOTE: Nz is a function that's available in Microsoft Access that lets you
> return a value when otherwise you would get a null value; I don't know if a
> similar function is available in other applications/languages. [perhaps
> COALESCE(arg1, arg2, arg3, argN...) would work instead?]
>
> Robin
>
>   On Fri, Apr 1, 2011 at 4:42 PM, Martin Cunningham <
> martin.cunningham at gmail.com> 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.
>>
>
>


More information about the gnucash-user mailing list