SQL to generate Gnucash sub accounts list in cross tabulation format

[Sébastien de Menten]

Hi T C, Wm,

Indeed, for this kind of needs, using piecash (http://piecash.readthedocs.io)
may be a better alternative than pure SQL.
You can find in attachement the python code that would do more or less what
you wish (if I understood it correctly).
I have used directly what is described here
http://piecash.readthedocs.io/en/latest/tutorial/index_existing.html#access-to-objects
.

sebastien

On Tue, Sep 20, 2016 at 12:40 PM, Wm <Wm... via gnucash-user <
gnucash-user at gnucash.org> wrote:

> On 19/09/2016 18:07, T C via gnucash-user wrote:
> > Hi everyone-
> > Just wanted to throw this (below) SQL solution out there, in case
> folks needed to generated a cross tabulation table of nested sub
> accounts in Gnucash. The SQL code below assumes there are up to four
> levels of sub accounts. You'll have to modify it if you are interested
> in more or less levels of sub accounts. Also, the SQL code below assumes
> you're only interested in four top level accounts: "Assets", "Credit",
> "Expenses", "Income". Again, you can modify as need. If you save your
> Gnucash file as a SQLite file and download a SQLite editor such as
> SQLiteStudio, you can run the SQL below to build an accounts cross
> tabulation (spreadsheet) table of sub accounts:
> > DROP TABLE IF EXISTS accounts_xtb;
> > CREATE TABLE accounts_xtb AS
>
> the DROP and CREATE isn't reliable as the table won't be there
> consistently, reason being GnuCash doesn't know about it when it creates
> a db
>
> More importantly, welcome to the GnuCash SQL solutions team.
>
> did you look at
> http://lists.gnucash.org/pipermail/gnucash-user/2014-December/057344.html
> and decide it was crap?  recursive is the way to go these days unless
> you're fighting a battle I'm not aware of.
>
> > Search terms:Accounts crosstabAccounts crosstabulationAccounts cross
> tabulation
> > SQLsqlitesqlite3Accounts spreadsheetAccounts subaccountsAccounts sub
> accountsAccounts nestedSubaccount columnssub-accounts
>
>
> also, I'm not sure it counts as a crosstab, but maybe I'm more
> particular about terms than you.
>
> Sebastien has made most progress so keep an eye out for his (rare these
> days) posts and don't ever write to a GnuCash db unless you are very
> sure you know what you are doing.
>
> --
> Wm
> _______________________________________________
> 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.
>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: crosstab.py
Type: application/octet-stream
Size: 1008 bytes
Desc: not available
URL: <http://lists.gnucash.org/pipermail/gnucash-user/attachments/20160922/539055c4/attachment.obj>