Knowledgebase: TroubleShooting
[QODBC-Desktop] How to run a Profit and Loss By Class Report in QODBC
Posted by Jack - QODBC Support on 09 June 2015 10:58 AM

Typical Profit and Loss By Class Report in QuickBooks

http://support.flexquarters.com/esupport/newimages/ProfitAndLossByClass/step1.png

Report Displayed in QODBC

Stored Procedure Command

       With QODBC, the same report can be generated using stored procedure reports like this:

       sp_report ProfitAndLossByClass show Amount_Title, Text, Label, Amount
       parameters DateMacro = 'ThisMonthToDate', SummarizeColumnsBy = 'Class'

Predefined DateMacro Options

      All the predefined DateMacro options available to you are:

|All|Today|ThisWeek|ThisWeekToDate|ThisMonth|ThisMonthToDate|ThisQuarter|ThisQuarterToDate
|ThisYear|ThisYearToDate|Yesterday|LastWeek|LastWeekToDate|LastMonth|LastMonthToDate|LastQuarter
|LastQuarterToDate|LastYear|LastYearToDate|NextWeek|NextFourWeeks|NextMonth|NextQuarter|NextYear|

Display in QODBC

http://support.flexquarters.com/esupport/newimages/ProfitAndLossByClass/step2.png

 

 

Change report to use "Cash" reporting

Stored Procedure Command

      To change these reports to use "Cash" reporting, add the ReportBasis parameter:

      sp_report ProfitAndLossByClass show Amount_Title, Text, Label, Amount
       parameters DateMacro = 'ThisMonthToDate', SummarizeColumnsBy = 'Class',ReportBasis='Cash'

ReportBasis Options

      The options are: |Cash|Accrual|None|

 

Profit and Loss By Class Report in QuickBooks in Collapse Mode

      If I collapse the QuickBooks 2013 Sample Rock Castle Construction company file Profit and Loss By Class Report, it looks like this:

http://support.flexquarters.com/esupport/newimages/ProfitAndLossByClass/step3.png

Report Displayed in QODBC

Stored Procedure Command

      By removing the NestedText6 (SubLevel 0) level, I can get similar results using QODBC:

      sp_report ProfitAndLossByClass show Amount_Title, Text, Label, Amount
       parameters DateMacro = 'ThisMonthToDate', SummarizeColumnsBy = 'Class'
      where NestedText6 is NULL

Display in QODBC

http://support.flexquarters.com/esupport/newimages/ProfitAndLossByClass/step4.png

(0 vote(s))
Helpful
Not helpful
Comments (4)
Chris Kirby
14 April 2017 03:50 AM
I'm trying to get a profit and loss by class, filtered to a single class in my QB file. Also, instead of a date macro, is there a way to set the SP statement to use custom dates from a main screen within my database?
Jack
14 April 2017 01:33 PM
Hi Chris,

You can use DateFrom & DateTo parameters for running the report for the specific period.

For Example:

sp_report ProfitAndLossByClass show Amount_Title, Text, Label, Amount parameters DateFrom = {d'2014-10-01'}, DateTo= {d'2014-10-31'}, SummarizeColumnsBy = 'Class',ReportBasis='Cash'

I kindly request you to please refer below mentioned the link to get details about the QODBC table & report schema. You will get details about each report column, parameters which are supported:
http://www.qodbc.com/schema.htm
RG
11 May 2017 10:12 PM
I am trying to build a P&L report that will summarize by Class AND by Month? Is this possible?
Jack
12 May 2017 07:38 AM
Hi,

You cannot build a report that will summarize by Class AND by Month simultaneously. Even QuickBooks UI report does not provide this feature. You can export report two times in excel sheet, first for Class & second for Month and can merge it as per your requirement.

For Example:
sp_report ProfitAndLossByClass show Amount_Title, Text, Label, Amount parameters DateMacro = 'ThisYearToDate', SummarizeColumnsBy = 'Class'

sp_report ProfitAndLossByClass show Amount_Title, Text, Label, Amount parameters DateMacro = 'ThisYearToDate', SummarizeColumnsBy = 'Month'

Please refer below mentioned article for exporting reports in MS Excel:
http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2405/57/how-to-create-sp_reports-using-microsoft-excel-2007

If you are still facing issue, I kindly request you to please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:
http://support.flexquarters.com/esupport/index.php?/Tickets/Submit

We may need the following information, I kindly request you to attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About
2) Screenshot of the issue you’re facing.
Share Entire Log Files as an attachment in text format from
3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
4) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).