Knowledgebase: Connection
[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 can get records from the ProfitAndLossBudgetPerformance report. I am executing the 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 occurring. From the QODBC logs, I noticed the 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=" 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 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 "fiscal year" is required in the ProfitAndLossBudgetPerformance report. So there is 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 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 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

(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).