You’re offline. This is a read only version of the page.
1,771
Hi,
I want to create GL trail balance report. I would like to know what are the tables need to use to retrieve below mentioned data from SQL SSRS
|
Account |
Description |
Beginning Balance |
Debit |
Credit |
Net Change |
Ending Balance |
|
10-0100-0110-01101 |
EQUIP - COMPUTERS COST |
2,650,391.74 |
10,452.69 |
0 |
10,452.69 |
2,660,844.43 |
|
10-0100-0110-01102 |
EQUIP - SOFTWARE COST |
1,733,668.86 |
0 |
0 |
- |
1,733,668.86 |
|
10-0100-0110-01103 |
EQUIP - TELEPHONES COST |
61,403.93 |
0 |
0 |
- |
61,403.93 |
*This post is locked for comments
I have a number of blog posts with code for different versions of the General Ledger Trial Balance: victoriayudin.com/.../general-ledger-sql-views
Hope this helps with what you're looking to do.
You can use this query and alter it to fit your needs:
/*
Trial Balance Query
*/
declare @Year int
declare @cmp varchar(4)
set @Year = 2018;
With TB (CompanyID,CompanyDescription,Account,[Description],BeginBal,Debit,Credit,NetChange,Ending) as
(
select A.Segment1 [CompanyID],
C.dscriptn [CompanyDescription],
[account number] ACCOUNT,
B.DSCRIPTN [Description],
SUM(CASE When [Period ID] = 0 THEN [Period Balance] ELSE 0 END) AS BEGINBAL,
SUM(CASE When [Period ID] > 0 THEN [Debit Amount] ELSE 0 END) AS Debit,
SUM(CASE When [Period ID] > 0 THEN [Credit Amount] ELSE 0 END) AS Credit,
SUM(CASE When [Period ID] > 0 THEN [Period Balance] ELSE 0 END) AS NetChange,
SUM([Period Balance]) AS ENDING
FROM (AccountSummary A
left join gl40200 B
on A.Segment2 = b.SGMNTID)
left join gl40200 C on
A.segment1 = c.sgmntid
WHERE [Account Type]='posting account'
and [Year] = @Year
and b.sgmtnumb = 2
and c.sgmtnumb = 1
GROUP BY [segment1]
,[Account Number]
,b.dscriptn
,C.dscriptn
)
Select [CompanyID]
,[CompanyDescription]
,Account
,[Description] = case [Description]
when '' then (select top 1 actdescr from gl00100 where MNACSGMT = Account)
else [Description]
end
,BeginBal
,Debit
,Credit
,NetChange
,Ending
from TB
order by CompanyID,account;
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (