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
|
|
How do I run a ProfitAndLossBudgetVsActual Report? |
Author |
Message |
|
Posted : 2006-05-05 10:39:11 |
Hi,
I am trying to map the sp_report command for following Quick Books Premier 2006 report.
Reports --> Budget & Forecasts --> Budget vs Actual --> Profit & Loss By Account --> Account by Month.
I have following mapping from QODBC documentation (http://www.qodbc.com/docs/html/qodbc/20/reports/sp_report_detail.asp ):
Is the mapping correct? The reason is that I don't get all the entries from sp_report example
sp_report BalanceSheetBudgetVsActual show Amount_Title, Budget_Title, Difference_Title, Percent_Title, Text, Label, Amount, Budget, Difference, Percent parameters FiscalYear = 2006, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'
Please help me.
Thank you,
Piyush Varma |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-05-05 12:19:57 |
The example is only an example report layout only, you can change the columns to be displayed to suit your requirements like I've done here:
sp_report ProfitAndLossBudgetVsActual show Text, Label, Amount, Budget, Percent parameters FiscalYear = 2006, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'
For USA users of QuickBooks, QODBC has the same reports that can be found in QuickBooks:
BalanceSheetBudgetOverview
sp_report BalanceSheetBudgetOverview show Amount_Title, Label, Amount parameters FiscalYear = 2006, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'
BalanceSheetBudgetVsActual
sp_report BalanceSheetBudgetVsActual show Amount_Title, Budget_Title, Difference_Title, Percent_Title, Text, Label, Amount, Budget, Difference, Percent parameters FiscalYear = 2006, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'
ProfitAndLossBudgetOverview
sp_report ProfitAndLossBudgetOverview show Amount_Title, Text, Label, Amount parameters FiscalYear = 2006, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'
ProfitAndLossBudgetPerformance
sp_report ProfitAndLossBudgetPerformance show Amount_Title, Text, Label, Amount parameters FiscalYear = 2006, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'
ProfitAndLossBudgetVsActual
sp_report ProfitAndLossBudgetVsActual show Amount_Title, Budget_Title, Difference_Title, Percent_Title, Text, Label, Amount, Budget, Difference, Percent parameters FiscalYear = 2006, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'
The parameters and filters that are available in a report can be found by running:
sp_paramters sp_reportname
So to report by customer (job) we do:
sp_report ProfitAndLossBudgetVsActual show Amount_Title, Budget_Title, Difference_Title, Percent_Title, Text, Label, Amount, Budget, Difference, Percent parameters FiscalYear = 2006, SummarizeBudgetColumnsBy = 'Customer'
To see how these stored procedures are used, see: How do I use the QuickBooks Reporting Engine with QODBC? I've heard something about sp_report ?
|
|
|
|
|
Posted : 2007-09-28 07:24:47 |
Hi,
I need to create a version of Profit and Loss Budget Versus Actual report. The QODBC report I used was ProfitAndLossBudgetVsActual with parameters as : From:= 1/1/2007, To:= 8/31/2007, Columns:= Totals Only, and then click Collapse button to see only high level entries. I tried various combinations of parameters but could not get the format. The output has only four columns, Jan-Aug 07; Budget; $over Budget; and % of Budget. I will greatly appreciate your help please. Thank you. Piyush
The Quick Books report format is below:
what I don't want and don't see are 4501, 4502,..6501,6502,etc. They are collapsed into a higher level of 4500, 6500, etc. |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-09-28 08:11:36 |
This forum isn't a consulting service, so you will need to show me the QuickBooks report both collapsed and uncollapsed (so I can tell what values you really want) and how far you have got to with your SQL statements. |
|
|
|
|
Posted : 2007-09-28 08:20:25 |
Hi Tom,
Thank you for the quick reply. Below are the top and bottom parts of the QB report:
Thank you. Piyush |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-09-28 08:26:26 |
I see, you want this report to look like the first one above it without all the xxxx.1, xxxx.2 etc, sub-values. |
|
|
|
|
Posted : 2007-09-28 09:27:03 |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-11-14 14:12:32 |
Ok to collapse the report use where {fn LOCATE(':', "RowData", 2)} = 0 or Text <> ''
sp_report ProfitAndLossBudgetVsActual show Text, Label, Amount, Budget, Percent parameters FiscalYear = 2006, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account' where {fn LOCATE(':', "RowData", 2)} = 0 or Text <> '' |
|
|
|
|
Posted : 2007-11-15 06:32:33 |
Thank you, Tom. It will help. Piyush |
|
|
|
|
Posted : 2007-12-05 06:02:31 |
The budget numbers are missing from one of our books. I use following SQL statement:
sp_report ProfitAndLossBudgetVsActual show Text, Label, Amount, Budget parameters BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account', FiscalYear = 2007
The Budget_1 to Budget_11 blanks, and Budget_12 and Budget_13 are zeros. While the report in Quick Books Premier Accountant Edition 2006 shows the budget numbers. Some budget numbers for accounts are zeros.
Any clues please? Since this report goes to the management, it is rather urgent to resolve the issue.
Thank you very much.
Piyush |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-12-05 07:39:04 |
Sorry I don't see your problem:
sp_report ProfitAndLossBudgetVsActual show Text, Label, Amount, Budget, Percent parameters FiscalYear = 2006, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account' where {fn LOCATE(':', "RowData", 2)} = 0 or Text <> ''
|
|
|
|
|
Posted : 2007-12-05 08:11:46 |
The Budget_x amounts from VB Demo are zeros or missing for some Accounts while there are values reported in Quick Books report.
Can I access a table which holds budget values? So that I can verify the numbers in a different way.
Piyush |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-12-05 08:18:07 |
Intuit doesn't provide access to budgets other than within reporting. |
|
|
|
|
Posted : 2007-12-05 09:17:35 |
I see the budget figures for sub-accounts and the top level account is blank in Quick Books via Company --> Planning and Budgeting --> Set Up Budgets. In that screen, does the selection of Current Class from the drop down-list matter? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-12-05 09:42:58 |
Yes, you need to check how the budget values have been entered in QuickBooks including the Fiscal Year. |
|
|
|
|
Posted : 2008-05-06 08:35:57 |
I am trying to create a budget vs actual and when i did as listed below it worked and brought up the actuals for all the periods but not the budget amounts. I do have a budget by class and am able to run the report directly in QuickBooks. |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-05-06 09:27:11 |
You need to check how the budget values have been entered in QuickBooks including the Fiscal Year. |
|
|
|
|
Posted : 2008-05-06 09:43:34 |
They are entered correctly in QBooks. Here is a print shot of a few lines from the budget vs actual report in QBooks:
|
Jan 08 |
Budget |
Feb 08 |
Budget |
410100 · EpiForce Product - Domestic |
116,750.00 |
233,730.00 |
0.00 |
354,867.00 |
410110 · EpiForce Product - Foreign |
88,785.50 |
|
0.00 |
|
410500 · EpiForce Maint - Domestic |
22,645.91 |
37,222.00 |
21,184.88 |
37,222.00 |
|
|
|
|
|
|
|
|
|
|
600400 · Bonuses |
31,000.00 |
30,717.00 |
31,000.00 |
30,717.00 |
600500 · Car Allowances |
750.00 |
|
750.00 |
|
600610 · Medical Insurance |
47,401.46 |
59,400.00 |
40,156.24 |
59,400.00 |
600620 · Dental Insurance |
4,423.57 |
5,600.00 |
4,175.39 |
5,600.00 |
Our budget is broken out further by class/dept. Here is what I have entered in the sql query:
sp_report ProfitAndLossBudgetVsActual show Text, Label, Amount, Budget, Percent parameters FiscalYear = 2008, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account' where {fn LOCATE(':', "RowData", 2)} = 0 or Text <> ''
And here is some of the returned data:
Label |
Amount_1 |
Amount_2 |
|
|
|
|
|
|
410100 · EpiForce Product - Domestic |
116750 |
0 |
410110 · EpiForce Product - Foreign |
88785.5 |
0 |
410500 · EpiForce Maint - Domestic |
22645.91 |
21184.88 |
|
|
|
600400 · Bonuses |
31000 |
31000 |
600500 · Car Allowances |
750 |
750 |
600610 · Medical Insurance |
47401.46 |
40156.24 |
600620 · Dental Insurance |
4423.57 |
4175.39 |
Why doesn't that budget info show up? Thank you |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-05-06 15:05:23 |
Try running the report expanded:-
sp_report ProfitAndLossBudgetVsActual show Text, Label, Amount, Budget, Percent parameters FiscalYear = 2008, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account', DisplayReport='Yes'
I've added the DisplayReport='Yes' parameter to force the report to be displayed in QuickBooks so you can sort out what the report is doing. |
|
|
|
|
Posted : 2008-05-07 03:48:10 |
When I run it expanded the budget amounts aren't there even when displayed in QuickBooks but when I run it in QuickBooks without going through sql/Excel it is there. I am using the budget "Profit and Loss by Account and Class" could the sql statement I am using be choosing another budget? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-05-07 11:58:07 |
Sorry, we only have a ProfitAndLossByClass, there's no "Profit and Loss by Account by Class":-
sp_report ProfitAndLossByClass show Text, Label, Amount parameters DateMacro = 'ThisYearToDate'
Technically I can do:
sp_report ProfitAndLossBudgetVsActual show Text, Label, Amount, Budget, Percent parameters FiscalYear = 2008, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Class', SummarizeBudgetRowsBy = 'Account'
But I don't get any report values returned. |
|
|
|
|
Posted : 2008-05-08 02:03:43 |
Sorry, I'm a little confused. I have 2 issues:
1) When I run: sp_report ProfitAndLossBudgetVsActual show Text, Label, Amount, Budget, Percent parameters FiscalYear = 2008, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account' where {fn LOCATE(':', "RowData", 2)} = 0 or Text <> ''
my budget amounts do not show up. They are in Quickbooks though.
2) I am also trying to get a profit and loss budget vs actual by class. |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-05-08 07:42:14 |
Sorry, the sp_reports interface isn't perfect and Intuit hasn't provided little documentation on how to use it. The DisplayReport='Yes' parameter forces the report to be displayed in QuickBooks so you can debug it. If budget figures don't display correctly then you need to run another report within QuickBooks and then compare the filters and setting between the two reports to see what's different or what the problem may be. |
|
|
|
|
Posted : 2008-05-09 07:48:49 |
I have done as you suggested and DisplayReport='Yes'. All of the filters and settings are exactly the same as when I run it directly in Quickbooks. I believe the issue is that my budget in Quickbooks is by account and class and your sp_report is not pulling the budget report for account and classes. I need to be able to see the budget figures.
Thank you |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-05-09 07:54:48 |
Sorry, the Intuit qbXML SDK does not have a ProfitandLossBudgetvsActualbyClass report. Try changing the BudgetCriterion = 'AccountsAndClasses' as shown in the next post. |
|
|
|
|
Posted : 2008-05-09 08:02:20 |
Hi,
For one of our books I faced a similar problem and when I used BudgetCriterion = 'AccountsAndClasses', it brought budget values.
The full syntax of the command I used is
"sp_report ProfitAndLossBudgetVsActual show Text,Label,Amount,Budget,NestedText1,NestedText2,NestedText3,NestedText4, NestedText5,NestedText6,NestedText7,NestedText8,NestedText9,AccountType,AccountNumber,AccountName,AccountListID,AccountHasChildren,ReportBasis,RowType parameters BudgetCriterion = 'AccountsAndClasses', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account', FiscalYear = 2008"
It might help. |
|
|
|
|
Posted : 2008-05-10 06:37:55 |
Piyush,
You are wonderful! Thank you. That worked to get me the budget figures. Thanks again. |
|
|
|
|
Posted : 2008-05-10 14:03:26 |
I am able to get the Profit and Loss Actual vs Budget but when I run it, it only does through this month so it gives me Jan, Feb, Mar, Apr, and May. How do I tell it to print all months Jan-Dec for the current fiscal year? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-05-12 08:12:40 |
Future months will only appear if there are (future) transactions. |
|
|
|
|
Posted : 2008-05-13 02:24:06 |
There are budget amounts. When I run the report in QuickBooks it shows the budget amounts even if there are no actual transactions. Isn't there a way to get the budget to show up? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-05-13 10:01:14 |
|
|
|
|