[longlam]

Hello All,

I am looking to recreate our income statements and balance sheets in Crystal Reports using the ODBC connection. The problem is, I don’t know where to start. For instance, how does Netsuite generate the Total Amount in the Accounts Receivable so quickly and not only overall but it can be broken up into different Financial Periods very quickly. What tables should I be looking into to start figuring out these figures?
This is a cached copy. Click here to see the original post.

[elena]

RE: Replicating Income Statements/Balance Sheets

Hello Long,

If you want your Financials by date: use TRANSACTIONS, TRANSACTION_LINES, ACCOUNTS views

If you want your Financials by period: use TRANSACTIONS, TRANSACTION_LINES, ACCOUNTS and ACCOUNTING_PERIODS views

Here is the sample sql, which you can modify in order to get Income Statement by date that you need:

select

accounts.account_id,

accounts.type_name,

accounts.name account,

sum(transaction_lines.amount) balance

from

transactions, transaction_lines, accounts

where

transactions.transaction_id = transaction_lines.transaction_id and

transactions.trandate between to_date(’01/01/2003′, ‘MM/DD/YYYY’) and to_date(’12/31/2003′, ‘MM/DD/YYYY’) and

accounts.account_id = transaction_lines.account_id and

transaction_lines.non_posting_line = ‘No’ and

accounts.type_name in (‘Income’,’Other Income’,’Expense’,’Other Expense’,’Cost of Goods Sold’)

group by

accounts.account_id,

accounts.type_name,

accounts.name

order by

accounts.type_name,

accounts.account_id

This sql basically provides you with with the list of all accounts that belong to Income Statement and the amount posted to these accounts between 1/1/2003 and 12/31/2003.

[longlam]

RE: Replicating Income Statements/Balance Sheets

What does this do?

transaction_lines.non_posting_line = ‘No’

Also, how does the transaction_line = 0 play into this and it acts as the total for each transaction. Does this also pull in all the additional journal entries? I’m guessing it does.

[Author]

Posts