| Custom Summary report for last year and the prior year with the change |
| Author |
Message |
| Mike |
| |
| Group | : Members |
| Posts | : 15 |
| Joined | : 2006-09-02 |
|
| Profile |
|
| Posted : 2007-03-10 09:53:47 |
I would like to get a custom summary that lists all of the active account amounts for last year and the prior year and the change.
I can do that in QB. When I try to do it in QODBC I can't get it to list all of the accounts. Do you have a solution?
Thanks. |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-03-10 10:19:47 |
|
|
|
| Mike |
| |
| Group | : Members |
| Posts | : 15 |
| Joined | : 2006-09-02 |
|
| Profile |
|
| Posted : 2007-03-10 15:38:39 |
|
|
|
| Mike |
| |
| Group | : Members |
| Posts | : 15 |
| Joined | : 2006-09-02 |
|
| Profile |
|
| Posted : 2007-03-10 15:39:57 |
|
|
|
| Mike |
| |
| Group | : Members |
| Posts | : 15 |
| Joined | : 2006-09-02 |
|
| Profile |
|
| Posted : 2007-03-10 15:41:14 |
|
|
|
| Mike |
| |
| Group | : Members |
| Posts | : 15 |
| Joined | : 2006-09-02 |
|
| Profile |
|
| Posted : 2007-03-11 17:02:32 |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-03-13 09:32:10 |
The closest I got to this report was:
sp_report CustomSummary show Text, Label, Amount_10 as "Oct'06 - Sep 07", Amount_9 as "Oct'05 - Sep 06",
(Amount_9 - Amount_10) as Change
parameters DateMacro = 'All', SummarizeRowsBy = 'Account',
SummarizeColumnsBy = 'Year', Calendar = 'FiscalYear',
ReturnRows = 'All', ReportBasis='Accrual'
however once I forced all the accounts to be shown, I also noticed several asset account values to be zero when they were not!
I've reported this bug to the QODBC engineer. |
|
|
|
| Mike |
| |
| Group | : Members |
| Posts | : 15 |
| Joined | : 2006-09-02 |
|
| Profile |
|
| Posted : 2007-03-13 10:38:20 |
| The trick to getting the report from QuickBooks is selecting "All Accounts" on the filter page. Doing that in conjuction with selecting "Account List" for "Display rows by" on the Display tab produces the report out of QuickBooks. |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-03-13 11:00:40 |
Yes, that's done in this stored procedure report with the ReturnRows = 'All' and SummarizeRowsBy = 'Account' parameters. I have since worked out that:
sp_report CustomSummary show Text, Label, Amount_10 as "Oct'06 - Sep 07", Amount_9 as "Oct'05 - Sep 06",
(Amount_9 - Amount_10) as Change
parameters DateMacro = 'All', SummarizeRowsBy = 'Account',
SummarizeColumnsBy = 'Year', Calendar = 'FiscalYear',
ReturnRows = 'All', TxnFilterTypes = 'All', ReportBasis='Accrual', DisplayReport=1
which uses the SummarizeRowsBy = 'Account' parameter (which has the following allowed values:
|Account|BalanceSheet|Class|Customer|CustomerType|Employee|
IncomeStatement|ItemDetail|ItemType|Payee|PaymentMethod|
PayrollItemDetail|PayrollYtdDetail|SalesRep|SalesTaxCode|
ShipMethod|TaxLine|Terms|Vendor|VendorType|)
runs with a "All income/expense accounts" filter default and not with a "All accounts" filter. That's why all non income/expense accounts are being displayed as zero values.
Using TxnFilterTypes = 'All' didn't help either, so I'm still investigating this report further.
|
|
|
|
