Hi!

I'm using QODBC release 8 to import data to Excel from Quickbooks Pro 2008 (US) via Microsoft Query.

I try to import last months P&L by class using the following SQL-code;

SP_REPORT ProfitAndLossByClass

SHOW Text, Label, Amount_1 as Manning, Amount_5 as Mfg, Amount_6 as Sales, Amount_7 as Sus_Eng, Amount_8 as TX_OH, Amount_11 as US_Ops, Amount_12 as Unclassified, Amount_13 as Total
PARAMETERS DateMacro = LastMonth

When I run the query I receive the following error.

Column not found: Amount_5.

When I remove Amount_5 from the code the same error applies for Amount_6 - Amount_13.

The strange thing is that when I run the query with the following SQL-code;

SP_REPORT ProfitAndLossByClass
PARAMETERS DateMacro = LastMonth

I don't receive any errors, and I can see 'Amount_5' in the Microsoft Query window.

Have anyone got any idea what might cause the error message from the first SQL-code?

Jan Erik

Try:

SP_REPORT ProfitAndLossByClass show Text, Label, Amount_1 as Manning, Amount_5 as Mfg, Amount_6 as Sales, Amount_7 as Sus_Eng, Amount_8 as TX_OH, Amount_11 as US_Ops, Amount_12 as Unclassified, Amount_13 as Total
parameters DateMacro = 'LastMonth', SummarizeColumnsBy = 'Class'

Or if that doesn't work:

SP_REPORT ProfitAndLossByClass show Text, Label, Amount_1 as Manning, Amount_5 as Mfg, Amount_6 as Sales, Amount_7 as Sus_Eng, Amount_8 as TX_OH, Amount_11 as US_Ops, Amount_12 as Unclassified, Amount_13 as Total
parameters DateMacro = 'ThisYearToDate', SummarizeColumnsBy = 'Class'

You essentially can't display columns in months that don't have any values. You can first check by doing:

SP_REPORT ProfitAndLossByClass show Text, Label, Amount parameters DateMacro = 'LastMonth', SummarizeColumnsBy = 'Class'

Thanks for your reply Tom!

You were correct, the error was caused by columns without values.

 I need to be able to stack out the P&L per month, so I can't use "ThisYearToDate".

I could be able to use the code you gave me for checking which cells had values;

SP_REPORT ProfitAndLossByClass show Text, Label, Amount parameters DateMacro = 'LastMonth', SummarizeColumnsBy = 'Class'

but then I would need a way to automatically put the correct heading to the respective 'Amount'.

By automatically I mean that the heading would be correct even if new data were entered and hence a new 'Amount'-column inserted.

 I am new to SQL, so I'm wondering if that could be done by a SQL-code?

Jan Erik

SP_REPORT is a stored procedure that simply calls the QuickBooks reporting engine used within QuickBooks to generate a report. So the columns that are returned are totally dependent as in this case on what transactions have been done for what class. If you are trying to automate this you first need to run:

SP_REPORT ProfitAndLossByClass show Amount_Title, Amount_Count
parameters DateMacro = 'LastMonth', SummarizeColumnsBy = 'Class'

as this will give you the names and number of AMOUNT columns that will be returned for the report:

Based on the returned result I would then know that I need to execute:

SP_REPORT ProfitAndLossByClass show Text, Label, Amount_1 as Unclassified, Amount_2 as Total
parameters DateMacro = 'LastMonth', SummarizeColumnsBy = 'Class'

Thanks again for your reply Tom!

Can the two codes you mentioned somehow be nested, or must I manually enter the heading in the second code?

I'm a summer-intern, and no one else in the company knows anything about programming, so the reports need to be fully automated before I leave. Are there any other methods to automatically produce P&L reports with headings or would I be better off combining the SQL-code with a VBA-code like this;

SP_REPORT ProfitAndLossByClass
PARAMETERS DateMacro = LastMonth

and then delete irrelevant rows by a Macro in Excel?

Jan Erik

You can combine both into a single statement:

SP_REPORT ProfitAndLossByClass show Amount_Title, Amount_Count,
Text, Label, Amount parameters DateMacro = 'LastMonth', SummarizeColumnsBy = 'Class'

and then build macros to copy the titles to columns headers x times depending on the count value.