Knowledgebase
[QODBC-Desktop] How to run a Profit and Loss Standard Report in QODBC
Posted by Juliet (QODBC Support) on 07 April 2010 07:48 AM

Typical Profit and Loss Standard Report in QuickBooks

Report Displayed in QODBC

Stored Procedure Command

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

       sp_report ProfitAndLossStandard show Text, Label, Amount_1 as Amount, Amount_1_Title as "As of"
       parameters DateMacro = 'ThisMonthToDate', SummarizeColumnsBy = 'TotalOnly'

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

 

Profit and Loss Standard Report (by month for the whole year) in QuickBooks

      Some users prefer to see their Profit and Loss report by month for the whole year:

Report Displayed in QODBC

Stored Procedure Command

      With QODBC, the same report can be generated by setting the SummarizeColumnsBy parameter to month like this:

      sp_report ProfitAndLossStandard show Text, Label, Amount_1_Title as Month_1, Amount_1,
      Amount_2_Title as Month_2, Amount_2, Amount_3_Title as Month_3, Amount_3,
      Amount_4_Title as Month_4, Amount_4, Amount_5_Title as Month_5, Amount_5,
      Amount_6_Title as Month_6, Amount_6, Amount_7_Title as Month_7, Amount_7,
      Amount_8_Title as Month_8, Amount_8, Amount_9_Title as Month_9, Amount_9,
      Amount_10_Title as Month_10, Amount_10, Amount_11_Title as Month_11, Amount_11,
      Amount_12_Title as Month_12, Amount_12, Amount_13_Title as Total, Amount_13 as Total_Amount
      parameters DateMacro = 'ThisYear', SummarizeColumnsBy = 'Month'

Predefined SummarizeColumnBy Options

      All the predefined SummarizeColumnsBy options available to you are:

|Class|Customer|Day|Employee|FourWeek|HalfMonth|ItemDetail|ItemType|Month|Payee
|PaymentMethod|PayrollItemDetail|Quarter|SalesRep|SalesTaxCode|ShipMethod|Terms
|TotalOnly|TwoWeek|Vendor|Week|Year|

Display in QODBC

 

Change report to use "Cash" reporting

Stored Procedure Command

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

      sp_report ProfitAndLossStandard show Text, Label, Amount_1 as Amount, Amount_1_Title as "As of"
      parameters DateMacro = 'ThisMonthToDate', SummarizeColumnsBy = 'TotalOnly', ReportBasis='Cash'

ReportBasis Options

      The options are: |Cash|Accrual|None|

 

Profit and Loss Standard Report in QuickBooks in Collapse Mode

      If I collapse the QuickBooks 2007 Sample Rock Castle Contruction company file Profit and Loss Standard Report it looks like this:

Report Displayed in QODBC

Stored Procedure Command

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

      sp_report ProfitAndLossStandard show Text, Label, Amount_1 as Amount, Amount_1_Title as "As of"
      parameters DateMacro = 'ThisMonthToDate', SummarizeColumnsBy = 'TotalOnly'
      where NestedText6 is NULL

Display in QODBC

(183 vote(s))
Helpful
Not helpful

Comments (9)
Sami
20 September 2012 10:33 AM
What I find so inetresting is you could never find this anywhere else.
Brian Hoppe
14 July 2014 02:51 PM
Hi - is there any way to make the month the column title rather than a separate column? Then I would have exactly what I need!
Jack
03 September 2014 09:40 AM
Hi Brian,

I would like to inform you that you can not set column as a Header. But you can change column name using alias "as".
For Example:
sp_report ProfitAndLossStandard show Text, Label, Amount_1 as Amount, Amount_1_Title as "As of" parameters DateMacro = 'ThisMonthToDate', SummarizeColumnsBy = 'TotalOnly'

I would like to inform you that you can set columns as header using programing through VBA code or you can export report in spreadsheet than you can use lookup or formula as per your requirement.

Please refer below mentioned article for sample VBA code & export report in MS Excel:
http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2571/0/using-quickbooks-data-with-vba
http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2405/57/how-to-create-sp_reports-using-microsoft-excel-2007
Mike Maltby
25 September 2014 02:35 AM
Hi. Can the stored procedures be incorporated into an Access database so that I can generate multiple tables, & hence required management reports?
Jack
25 September 2014 09:02 AM
Hi Mike,

Please refer below mentioned article to create Pass-Through Report using Microsoft Access:
http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2359/53/how-to-create-pass-through-report-using-microsoft-access-2003
Atif
15 October 2015 01:18 PM
None of these are working for me.

When I try this

sp_report ProfitAndLossStandard show Text, Label, Amount_1 as Amount, Amount_1_Title as "As of"
parameters DateMacro = 'ThisMonthToDate', SummarizeColumnsBy = 'TotalOnly'

It gives an error saying that column "text" does not exist. How can i get rid of this ? has the column name changes since then ?
Jack
16 October 2015 07:44 AM
Hi Atif,

Are you running this report through QODBC Online?


If you are running ProfitAndLossStandard through QODBC Online, then I would like to share that number of columns & parameters in QODBC Online version & Desktop versions are different. So you can’t find same columns/parameters in QODBC Online same as QODBC Desktop.

Please refer below mentioned link for QODBC Schema:
http://qodbc.com/schema.htm

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

I kindly request you to share more information about the issue you’re facing, so that we can locate the problem quickly.

We may need following information, I kindly request you to attach below listed files when replying to the ticket.

1) Screenshot of QODBC Setup Screen -- > About (Start>>All Programs>> QODBC Driver for use with QuickBooks>> QODBC Setup Screen >> About Tab )
2) Screenshot of the issue you’re facing.
3) Share the SQL statement you’re using.
Share Entire Log Files as an attachment in text format from
4) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
5) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Michael C
30 October 2017 07:42 PM
I'd like to filter the P&L by month to be a singular class rather than all classes. Is this possible?
Jack
31 October 2017 06:43 AM
Hi Michael,

You can use ClassFilterFullNames parameter in ProfitAndLossStandard report for filtering data according to Class. Please refer below query for the same.

For Single class filter:
sp_report ProfitAndLossStandard show Text, Label, Amount_1 as Sep, Amount_2 as Oct, Amount_3 as Nov, Amount_4 as Dec, Amount_5 as Jan, Amount_6 as Feb, Amount_7 as Mar, Amount_8 as Apr, Amount_9 as May, Amount_10 as Jun, Amount_11 as Jul, Amount_12 as Aug, Amount_13 as Total parameters DateMacro = 'LastYear', SummarizeColumnsBy = 'Month', ClassFilterFullNames='New Construction' where NestedText6 is NULL

For Multiple class filter:
sp_report ProfitAndLossStandard show Text, Label, Amount_1 as Sep, Amount_2 as Oct, Amount_3 as Nov, Amount_4 as Dec, Amount_5 as Jan, Amount_6 as Feb, Amount_7 as Mar, Amount_8 as Apr, Amount_9 as May, Amount_10 as Jun, Amount_11 as Jul, Amount_12 as Aug, Amount_13 as Total parameters DateMacro = 'LastYear', SummarizeColumnsBy = 'Month', ClassFilterFullNames='New Construction','Remodel' where NestedText6 is NULL

If you are still the facing issue, 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, Please 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
Refer: How to take screenshot: www.qodbc.com/links/screenshot.htm
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).