Thursday, October 2, 2008
Variants of reports
One of my helpful testers (thank you, Michael!) with a big GnuCash file over several years accomplished good results with the following query in Ooo-Base which he then evaluated by the DataPilot in Calc:
SELECT "ACCOUNT-TYPE", "LEVEL2", SUBSTRING ( "DATE_YM" FROM 1 FOR 4 ) AS "DATE_Y", SUM( "VALUE" ) AS "VALUE"
FROM "ALL_TRANSACTIONS"
GROUP BY "ACCOUNT-TYPE", "LEVEL2", "DATE_Y"
With "LEVEL2" instead of "ACCOUNT-NAME" he condenses somewhat the accounts and by isolating the first 4 characters from "DATE_YM" he will have the years instead of the months as columns, altogether a wonderful general overview over the whole file.
SELECT "ACCOUNT-TYPE", "LEVEL2", SUBSTRING ( "DATE_YM" FROM 1 FOR 4 ) AS "DATE_Y", SUM( "VALUE" ) AS "VALUE"
FROM "ALL_TRANSACTIONS"
GROUP BY "ACCOUNT-TYPE", "LEVEL2", "DATE_Y"
With "LEVEL2" instead of "ACCOUNT-NAME" he condenses somewhat the accounts and by isolating the first 4 characters from "DATE_YM" he will have the years instead of the months as columns, altogether a wonderful general overview over the whole file.
Labels: Variants of reports
# posted by Knut @ 8:14 AM 
Comments:
<< Home
I worked on a little modification about the import from the "INCOME" values.
The "INCOME" values are always import with negativ values and I think that is not the best way to represent the money what you get for your work.
I use the following SELECT statment:
SELECT "ACCOUNT-TYPE", "LEVEL2", SUBSTRING ( "DATE_YM" FROM 1 FOR 4 ) AS
"DATE_Y", SUM(CASE WHEN "ACCOUNT-TYPE" IN ('INCOME') THEN -VALUE ELSE VALUE END) AS VALUE
FROM "ALL_TRANSACTIONS" GROUP BY "ACCOUNT-TYPE",
"LEVEL2", DATE_Y
Post a Comment
The "INCOME" values are always import with negativ values and I think that is not the best way to represent the money what you get for your work.
I use the following SELECT statment:
SELECT "ACCOUNT-TYPE", "LEVEL2", SUBSTRING ( "DATE_YM" FROM 1 FOR 4 ) AS
"DATE_Y", SUM(CASE WHEN "ACCOUNT-TYPE" IN ('INCOME') THEN -VALUE ELSE VALUE END) AS VALUE
FROM "ALL_TRANSACTIONS" GROUP BY "ACCOUNT-TYPE",
"LEVEL2", DATE_Y
# posted by 
: November 6, 2008 at 9:05 AM
: November 6, 2008 at 9:05 AM Subscribe to Post Comments [Atom]
<< Home
