Member Login

Username
Password
Forget Password
New Sign Up
Search Forum

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

Forum : How do I run a Profit & Loss Budget Overview Report?Search Forum

Forum Home > QODBC - ODBC Driver for QuickBooks > QODBC Reporting Forum

 New Topic 
 
 Post Reply 
[1]  
 How do I run a Profit & Loss Budget Overview Report? 
 Author   Message 
  Tom 
  6c3c1_sdk-qodbc.gif
 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'

 

  Top 
  loosegroove 
  
 Group: Members 
 Posts: 9 
 Joined: 2007-06-02 
 Profile
 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 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 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).

 

  Top 
  loosegroove 
  
 Group: Members 
 Posts: 9 
 Joined: 2007-06-02 
 Profile
 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. 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 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. 

  Top 
  David Haroldsen 
  
 Group: Members 
 Posts: 13 
 Joined: 2007-03-10 
 Profile
 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 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 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).

 

  Top 
  David Haroldsen 
  
 Group: Members 
 Posts: 13 
 Joined: 2007-03-10 
 Profile
 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? 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 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. 

  Top 
 New Topic 
 
 Post Reply 
[1]  

Jump to