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 GL account. Running sp_report Journal through a linked SQL Server returns errors stating that FullName does not exist.

I am using the below query to access the Account detail, but I can't access the Account Name detail from the 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 provides in QuickBooks SDK.

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

QuickBooks SDK does not provide a full name, and we could not add it to the sp_report response.

But yes, there is a workaround, you can query the 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 the Account name as "10020 · Account Receivable," i.e., "AccountNumber ·Name."

We need to query an Account table using the Join function to access account details. 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 the Account column output combined using dot ".". So we need to use substring & other SQL functions to remove this dot & compare the account name with the 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'