Troubleshooting: SP_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 can get records from the ProfitAndLossBudgetPerformance report. I am executing the following 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 pop-up occurring. From the QODBC logs, I noticed the following 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=" continue on error">

<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 in 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 "fiscal year" is required in the ProfitAndLossBudgetPerformance report. So, the parameter "fiscal year" is missing in the SQL statement. After including the required parameter, you can get all records from the ProfitAndLossBudgetPerformance report. Please refer to the query below:

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 to the article mentioned. You can get detailed information about how to execute sp_report in QODBC:

How to use the QuickBooks Reporting Engine with QODBC