Troubleshooting: How to Access Account Name details from Journal Report Using SQL Server

Problem Description:

I need some guidance on extracting data from the general ledger. In particular, I need to see the FullName of the GL account. Running sp_report Journal through a linked SQL Server returns errors stating that FullName does not exist.

I am using below query to access Account detail, but I can't access Account Name detail from output. Please help:

select
isnull(Account,'')Account,
isnull(Amount,0)Amount,
isnull(Date,'01/01/1900') [Date],
isnull(Credit,0) Credit,
isnull(Debit,0) Debit,
isnull(Memo,'') Memo,
isnull(Name,'') Name,
isnull(RefNumber,'') RefNumber,
isnull(RowNumber,0) RowNumber,
isnull(RowType,'')Rowtype,
isnull(SplitAccount,'')SpliAccount,
isnull(TxnID,'')TxnID,
isnull(TxnNumber,'')TxnNumber
from
openquery(QREMOTE,'sp_report Journal show Account,Amount,Date,Credit,Debit,Memo,Name,RefNumber,RowNumber,RowType,SplitAccount,TxnID,TxnNumber parameters DateFrom = {d''2017-12-01''}, DateTo = {d''2017-12-05''}') where RowType = 'DataRow'    

Solutions:

From sp_report we would not be able to include FullName instead of the information QuickBooks is providing in QuickBooks SDK.

Instead, you may use JournalEntryLine table which will show detail information of the Journal and you can apply filters to achieve the desired output.

QuickBooks SDK does not provide full Name and we would not be able to add it to the sp_report response.

But yes, there is a workaround, you can query Account table and Link the result of Sp_report to Account and get the FullName.

Linking two tables would not be straightforward, as QuickBooks sends Account name as "10020 · Account Receivable" i.e. "AccountNumber ·Name"

To access account details we need to query an Account table by using the Join function. Also, we need to use Substring & other SQL functions to compare data for achieving desired results because the report output contains Account Number & Account FullName in Account column output is combined using dot ".". So we need to use substring & other SQL functions to remove this dot & compare the account name with Account table.

Please refer below query:

select
isnull(Account,'')Account,
isnull(Amount,0)Amount,
isnull(Date,'01/01/1900') [Date],
isnull(Credit,0) Credit,
isnull(Debit,0) Debit,
isnull(Memo,'') Memo,
isnull(a.Name,'') Name,
isnull(RefNumber,'') RefNumber,
isnull(RowNumber,0) RowNumber,
isnull(RowType,'')Rowtype,
isnull(SplitAccount,'')SpliAccount,
isnull(TxnID,'')TxnID,
isnull(TxnNumber,'')TxnNumber,
b.FullName as AccountFullName,
c.FullName as SplitFullName
from openquery(QREMOTE,'sp_report Journal show Account,Amount,Date,Credit,Debit,Memo,Name,RefNumber,RowNumber,RowType,SplitAccount,TxnID,TxnNumber parameters DateFrom = {d''2017-12-01''}, DateTo = {d''2017-12-05''}') a
left join
openquery(QREMOTE, 'select * from account') b on
SUBSTRING ( a.Account ,0 , CHARINDEX (' · ', a.Account) )= ISNULL(CONVERT(varchar(50),b.AccountNumber),'')
and
REPLACE(a.Account,SUBSTRING ( a.Account ,0 , CHARINDEX (' · ', a.Account) ) + ' · ','') = ISNULL(CONVERT(varchar(50),b.Name),'') left join
openquery(QREMOTE, 'select * from account') c on
SUBSTRING ( a.SplitAccount ,0 , CHARINDEX (' · ', a.SplitAccount) )= ISNULL(CONVERT(varchar(50),c.AccountNumber),'')
and
REPLACE(a.SplitAccount,SUBSTRING ( a.SplitAccount ,0 , CHARINDEX (' · ', a.SplitAccount) ) + ' · ','') = ISNULL(CONVERT(varchar(50),c.Name),'')
where RowType = 'DataRow'