|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
|
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
|
Click Here
|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
|
| sp_report query gives wrong sum for one account |
| Author |
Message |
| mehl |
| |
| Group | : Members |
| Posts | : 1 |
| Joined | : 2007-05-01 |
|
| Profile |
|
| Posted : 2008-09-21 13:50:30 |
qodbc version 6.00.00.138
QB Pro 2005
Hello --
The following SQL does not correctly retrieve transaction detail values from
one income account for the quarter 4/1/08 through 6/30/08 (Q2)
sp_report ProfitAndLossStandard SHOW
Amount_Title,
Label,
{fn SUBSTRING("Label", 1, 6)} AS AcctNum,
{fn SUBSTRING("Label", {fn LOCATE('· ', "Label")} + 2, 60)} AS AcctName,
{fn SUBSTRING("Label", 3, 4)} AS AcctSuffix,
{fn LOCATE('· ', "Label")} as PosStart,
Amount
parameters DateFrom = {d'2008-04-01'}, DateTo = {d'2008-06-30'}, SummarizeColumnsBy = 'TotalOnly'
The SQL results for Q2 for the problem account include the sum of of 2 journal entry amounts from Q1, dated 3/31/08.
The sums for all the other income accounts are retrieved correctly. Some ot these accounts also have journal entries dated 3/31/08.
The totals for Q1 + Q2 are correct.
Can anyone think of a reason why the sum of only one income account would be wrong using this query?
Thanks in advance for any help.
Larry Mehl
|
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-09-22 10:32:22 |
When things don't make sense, you can force the report to be displayed within QuickBooks so you can see what filter is incorrect. Try doing:
sp_report ProfitAndLossStandard SHOW
Amount_Title,
Label,
{fn SUBSTRING("Label", 1, 6)} AS AcctNum,
{fn SUBSTRING("Label", {fn LOCATE('· ', "Label")} + 2, 60)} AS AcctName,
{fn SUBSTRING("Label", 3, 4)} AS AcctSuffix,
{fn LOCATE('· ', "Label")} as PosStart,
Amount
parameters DisplayReport='Yes', DateFrom = {d'2008-04-01'}, DateTo = {d'2008-06-30'}, SummarizeColumnsBy = 'TotalOnly' |
|
|
|
|