Knowledgebase
[QODBC-Desktop] SP_REPORT ProfitAndLossBudgetPerformance is returning no rows.
Posted by Jack - QODBC Support on 05 August 2014 01:45 PM

TroubleshootingSP_REPORT ProfitAndLossBudgetPerformance is returning no rows.

Problem Description 1:

I am running ProfitAndLossBudgetPerformance through MS Query & It is returning no rows. But using QuickBooks UI I am able to get records from ProfitAndLossBudgetPerformance report. I am executing below query:

Sp_report ProfitAndLossBudgetPerformance show Amount_Title, Text, Label, Amount parameters DateFrom={d'2013-01-01'}, DateTo={d'2013-02-20'}, BudgetCriterion='AccountsAndClasses', SummarizeBudgetColumnsBy='Date', SummarizebudgetRowsBy='Class'

There is no error popup occurred. From the QODBC logs, I noticed below error:

2014-06-18 19:39:03 QODBC Ver: 14.00.00.302 *********************************************************************************************************************
IsAService: False
SQL Statement: Sp_report ProfitAndLossBudgetPerformance show Amount_Title, Text, Label, Amount parameters DateFrom={d'2013-01-01'}, DateTo={d'2013-02-20'}, BudgetCriterion='AccountsAndClasses', SummarizeBudgetColumnsBy='Date', SummarizebudgetRowsBy='Class'
Error Getting XML in BuildAndLoadXMLForNextRecord
Input XML:

<?xml version="1.0" encoding="ISO-8859-1"?>

<?qbxml version="11.0"?>

<QBXML>

<QBXMLMsgsRq onError="continueOnError">

<BudgetSummaryReportQueryRq requestID="1">

<BudgetSummaryReportType>ProfitAndLossBudgetPerformance</BudgetSummaryReportType>

<BudgetCriterion>AccountsAndClasses</BudgetCriterion>

<ReportPeriod>

<FromReportDate>2013-01-01</FromReportDate>

<ToReportDate>2013-02-20</ToReportDate>

</ReportPeriod>

<SummarizeBudgetColumnsBy>Date</SummarizeBudgetColumnsBy>

<SummarizeBudgetRowsBy>Class</SummarizeBudgetRowsBy>

</BudgetSummaryReportQueryRq>

</QBXMLMsgsRq>

</QBXML>

 

Problem Description 2:

Is there any way to have a date prompt in this stored procedure instead of a fixed fiscal year? I am running the query into Excel using Microsoft Query. It appears to give me information regarding the current month (i.e. February info since this is the month of February) for the fiscal year identified in the parameters. Is there any way I can replace FiscalYear with a FromDate and ToDate parameter?

Solutions:

As per QuickBooks SDK the parameter "FiscalYear" is required in ProfitAndLossBudgetPerformance report. So there is the parameter "FiscalYear" missing in SQL statement. After including required parameter you can get all records from ProfitAndLossBudgetPerformance report. Please refer below query:

sp_report ProfitAndLossBudgetPerformance show Amount_Title, Text, Label, Amount parameters DateFrom={d '2013-01-01'}, DateTo={d '2013-02-20'},FiscalYear=2013, BudgetCriterion='AccountsAndClasses', SummarizeBudgetColumnsBy='Date', SummarizebudgetRowsBy='Class'

Please refer below mentioned article. You can get detailed information about how to execute sp_report in QODBC:

How to use the QuickBooks Reporting Engine with QODBC

(0 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).