https://bugs.freedesktop.org/show_bug.cgi?id=53656
--- Comment #1 from t...@tim-passingham.co.uk 2012-08-18 15:16:47 UTC ---
I have produced extracts of one of the set of queries I use that worked fine in
previous versions of Calc. These are below. I'm aware some of my queries are
exactly 'ideal', but they are derived from old queries I've been using for
years on other systems and I haven't seen the need to rationalise them.
The query used by the Pivot Table is the 1st one -
ExpensesPersonalTotalsByYearBase. This sums the expenses for each expense type
in ExpensesPersonalByYearBase. It ensures that there is an entry for every
year for every type of expense, even if 0, by linking this data to
YearTypesExpensesBase. That query gets an entry for every type for every year,
with the years derived from the YearsBase query.
I then created a pivot table that I filtered by Year, listing the totals for
every possible type of expense., for example, a few rows, with year filter at
the start, would look like this (with invented numbers!):
yr 2010
Type
Accomodation 3000
Accounting Services 1200
American Express 20
Bank charges 78
Bicycles 30
etc...
In Calc 3.6 all the amounts are in ascending order from top to bottom, and are
not associated with the types.
Queries follow.....................
ExpensesPersonalTotalsByYearBase
SELECT `YearTypesExpensesBase`.`yr`, `YearTypesExpensesBase`.`Type`, SUM(
`ExpensesPersonalByYearBase`.`baseamount` ) AS `SumOfbaseamount` FROM { OJ
`YearTypesExpensesBase` LEFT OUTER JOIN `ExpensesPersonalByYearBase` ON
`YearTypesExpensesBase`.`TypeId` = `ExpensesPersonalByYearBase`.`TypeId` AND
`YearTypesExpensesBase`.`yr` = `ExpensesPersonalByYearBase`.`Yr` } GROUP BY
`YearTypesExpensesBase`.`yr`, `YearTypesExpensesBase`.`Type` ORDER BY
`YearTypesExpensesBase`.`yr` ASC, `YearTypesExpensesBase`.`Type` ASC
ExpensesPersonalByYearBase
SELECT LEFT( `DebitDate`, 4 ) AS `Yr`, ROUND( `Amount` / `baserate`, 2 ) AS
`baseamount`, `Type`.`TypeId`, `Type`.`Type` FROM `MyAccounts`.`Movement` AS
`Movement`, `MyAccounts`.`Type` AS `Type`, `MyAccounts`.`Class` AS `Class`,
`MyAccounts`.`Item` AS `Item` WHERE `Movement`.`MovementId` =
`Type`.`MovementId` AND `Class`.`ClassId` = `Item`.`ClassId` AND
`Item`.`TypeId` = `Type`.`TypeId` AND ( `Movement`.`CreditExp` ) = TRUE AND (
`Class`.`PersonalExpense` ) = TRUE
YearTypesExpensesBase
SELECT `YearsBase`.`yr`, `Type`.`TypeId`, `Type`.`Type` FROM `YearsBase`,
`Movement` INNER JOIN `Type` ON `Movement`.`MovementId` = `Type`.`MovementId`
WHERE ( ( ( `Movement`.`CreditExp` ) = TRUE ) )
YearsBase
SELECT LEFT( `From`, 4 ) AS `yr` FROM `ReportingPeriod` WHERE ( ( (
`ReportingPeriod`.`CalendarYear` ) = TRUE ) )
--
Configure bugmail: https://bugs.freedesktop.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
