Hi there,

I'm trying to obtain a listing of Transactions with their associated Class values. I've read that you can use the Stored Procedures (like sp_report ProfitAndLossbyClass threads in the Forum).

However, I'm trying to use direct SQL statements via QODBC. Here's an example I'm working with to isolate the Design Income transactions for the standard Profit and Loss by Class report for Rock Castle Construction (seeing the transactions that add up to $36,729.25)

SELECT  Account.AccountNumber, Account.Name, Account.FullName, Account.AccountType, Transaction.TxnDate, Transaction.AccountRefListID,Transaction.AccountRefFullName, Transaction.EntityRefListID,Transaction.EntityRefFullName,Transaction.RefNumber,Transaction.Amount  FROM Account INNER JOIN Transaction  ON Account.ListID = Transaction.AccountRefListID WHERE Account.AccountNumber = '40110' AND Transaction.TxnDate >= {d'2011-01-01'} AND Transaction.TxnDate <= {d'2011-12-15'}

This query gets me almost everything I need.. except for the Class values - so I could see what portion is "New Construction" or "Remodel".

Can you please advise on how (or if) this is possible to achieve?

Many thanks in advance. Good site!

D'Arcy..

The Transaction table doesn't show the Class but it can be seen using a CustomTxnDetail report instead like this:-

sp_report CustomTxnDetail show Text, Blank, TxnType, Date, RefNumber, Name, Memo,
Account, ClearedStatus, SplitAccount, Class, Debit, Credit, RunningBalance
parameters DateMacro = 'ThisMonthToDate', SummarizeRowsBy = 'TotalOnly',
OpenBalanceAsOf = 'Today'

See: How do I run a CustomTxnDetail Report? for more information.

SQL Statement 1:     sp_report CustomTxnDetail show Date, Account, AccountType, Amount, Class parameters DateMacro = 'All', SummarizeRowsBy = 'Account'

SQL Statement 2:     sp_report CustomTxnDetail show Date, Account, AccountType, Amount, Class parameters DateMacro = 'ThisMonthToDate', SummarizeRowsBy = 'TotalOnly', OpenBalanceAsOf = 'Today'

I am trying to pull ALL transaction records from a 2007 QBW file from the begining of the file until present day using the Connection String above.  I tested with SQL Statement 2 and it appears to have worked.  However when I change it to SQL Statement 1 it does not return any values and appears to enter a continuous loop.  What should my SQL statement look like to retrieve the records I want?

This can take a long time depending on the amount of transaction rows that will be returned. Try breaking the report into year by year chunks like this instead:

sp_report CustomTxnDetail show Date, Account, AccountType, Amount, Class
parameters DateFrom = {d'2005-01-01'}, DateTo = {d'2005-12-31'},
SummarizeRowsBy = 'Account'

sp_report CustomTxnDetail show Date, Account, AccountType, Amount, Class
parameters DateFrom = {d'2006-01-01'}, DateTo = {d'2006-12-31'},
SummarizeRowsBy = 'Account'

sp_report CustomTxnDetail show Date, Account, AccountType, Amount, Class
parameters DateFrom = {d'2007-01-01'}, DateTo = {d'2007-12-31'},
SummarizeRowsBy = 'Account'

sp_report CustomTxnDetail show Date, Account, AccountType, Amount, Class
parameters DateFrom = {d'2008-01-01'}, DateTo = {d'2008-12-31'},
SummarizeRowsBy = 'Account'