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 Profit & Loss Budget Overview Report? |
Author |
Message |
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-04-13 09:58:55 |
A typical Profit & Loss Budget Overview Report for the current Fiscal Year to Date looks like this:
Note: The QuickBooks Sample files set the QuickBooks date to December 15, 2007, hence the current Fiscal Year shown above is October 1 through December 15, 2007.
With QODBC the same report can be generated using stored procedure reports like this:
sp_report ProfitAndLossBudgetOverview show ReportSubtitle, Text, Label, Amount parameters FiscalYear = 2007, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'
All the predefined parameter values available to you in a report can be found by running:
sp_parameters reportname
Some users prefer to see their Profit & Loss Budget Overview report by month for the whole year. This can only be done for previous fiscal years:
With QODBC the same report can be generated by setting the Fiscal Year to 2006 like this:
sp_report ProfitAndLossBudgetOverview show ReportSubtitle, Text, Label, Amount_1 as Oct, Amount_2 as Nov, Amount_3 as Dec, Amount_4 as Jan, Amount_5 as Feb, Amount_6 as Mar, Amount_7 as Apr, Amount_8 as May, Amount_9 as Jun, Amount_10 as Jul, Amount_11 as Aug, Amount_12 as Sep, Amount_13 as Total_Amount parameters FiscalYear = 2006, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'
|
|
|
|
|
Posted : 2007-06-25 13:30:39 |
I am trying to run this report to get budget numbers for the full year 2007. I would like numbers for each of the 12 months of the year as well as a total for the year. I am trying to run the following sp_report:
sp_report ProfitAndLossBudgetOverview show AccountListID,AccountNumber,AccountName,AccountFullName,AccountType, Amount_1 AS Jan, Amount_2 AS Feb, Amount_3 AS Mar, Amount_4 AS Apr,Amount_5 AS May, Amount_6 AS Jun, Amount_7 AS Jul, Amount_8 AS Aug,Amount_9 AS Sep, Amount_10 AS Oct, Amount_11 AS Nov, Amount_12 AS Dec, Amount_13 as Total_Amount parameters FiscalYear = 2007, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'
However, when I try to run the report I get the following error message:
Column not found: Amount_8
I am able to run this report in QuickBooks and get the full year by adjusting the Date to 'This Fiscal Year' or by changing the From & To to be '1/1/07 - 12/31/07'.
How can I get the full year by month using the QODBC driver?
--
Bronson |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-06-26 08:02:50 |
As stated above, "Some users prefer to see their Profit & Loss Budget Overview report by month for the whole year. This can only be done for previous fiscal years." You can only get up to the year to date for the current year (not future months). |
|
|
|
|
Posted : 2007-06-26 08:52:52 |
Why can we not get future months through QODBC? I am able to get a full year (including future months) in a report using QuickBooks. |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-06-26 09:01:51 |
The SummarizeBudgetColumnsBy = 'Date' can't see future dates. The columns are being created only when a date is found. If you have future dated transactions, then those months will appear. |
|
|
|
|
Posted : 2008-04-01 10:41:08 |
Hello, I would like to be able to run the overview report with these options: BudgetCriterion = 'AccountsAndCustomers', SummarizeBudgetColumnsBy = 'Customer', SummarizeBudgetRowsBy = 'Account' but I also need to be able to filter on Customer/Job. Otherwise I get all the columns for every job and this is far from ideal. In the report ProfitAndLossByJob there is the option to filter on job/column via "EntityFilterFullNameWithChildren" Is there any way to do the equivalent here? I realize it's not in the parameters list but it would be useful if there were some way to do this.
Thanks in advance, Dave |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-04-01 11:41:12 |
Try running:
sp_report ProfitAndLossBudgetOverview show ReportSubtitle, Text, Label, Amount parameters FiscalYear = 2007, BudgetCriterion = 'AccountsAndCustomers', SummarizeBudgetColumnsBy = 'Customer', SummarizeBudgetRowsBy = 'Account', DisplayReport = 'Yes'
which will also force the report to be displayed in the QuickBooks User Interface. Once the report completes, work out what customer/job column(s) you're interested and then just display those columns only. For example:
sp_report ProfitAndLossBudgetOverview show ReportSubtitle, Text, Label, Amount_5 as "Job Name" parameters FiscalYear = 2007, BudgetCriterion = 'AccountsAndCustomers', SummarizeBudgetColumnsBy = 'Customer', SummarizeBudgetRowsBy = 'Account'
would just display the figues for 5th customer/job column only (and label the column ... just change Job Name to what the job name really is). |
|
|
|
|
Posted : 2008-04-09 03:51:33 |
Thanks Tom,
This solution does work, however I am facing a situation where I have more than 2000 "Amounts" which takes a very very long time to process as you might imagine. The thing that is odd about this is that I only want to display non-zero job/budgets which seems to be the default if I select 2007 as the FiscalYear. I have a new 2008 budget for some of the customers/jobs, but when I run the report on 2008 I get a column for every customer in QuickBooks, most of which do not have or need budgets. Is there any way at all I can filter out the zero/no budget data? Getting all this data makes this method almost unworkable. If this is not possible is there any way at all to extract detail budget data by job by other means? Other reports or queries? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-04-09 09:57:21 |
Sorry, the interface provided by Intuit's qbXML SDK within QuickBooks is rather primitive when it comes to working with budget data. |
|
|
|
|