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'

To bring all the amount columns, use the following SQL statement

       sp_report ProfitAndLossStandard show Text, Label, Amount as Amount, Amount_Title 
       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