Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

GL Trial Balance Report

Status
Not open for further replies.

elsenorjose

Technical User
Oct 29, 2003
684
US
Using Exact Macola 9.6.500, I've been asked to create a report that will return a General Ledger trial balance showing the monthly net change for the year along with beginning and ending balances. The attached image shows the output of a SQL query in GP Dynamics that shows what I'm looking for. I'm completely new to Macola and don't know if there's a built in report that does this or if there's a SQL query I could write and save as a view or stored procedure. Many thanks in advance!
 
 http://files.engineering.com/getfile.aspx?folder=d8b95909-2f69-4b7d-853a-ccc2fa8f2c6e&file=gl-trial-balance-results.png
In case anyone is interested, here's what I eventually came up with. It only shows the ending balance for the year in the Total column but it works for now.

SELECT [Company], [GL Account], [Description], [Year],
ISNULL([January],0) + ISNULL([February],0) + ISNULL([March],0) + ISNULL([April],0) + ISNULL([May],0) + ISNULL([June],0) +
ISNULL([July],0) + ISNULL([August],0) +ISNULL([September],0) + ISNULL([October],0) + ISNULL([November],0) + ISNULL([December],0) AS [Total]
, [January], [February], [March], [April], [May], [June], [July], [August], [September], [October], [November], [December]
FROM
(SELECT CASE
WHEN LEFT(DATENAME(MONTH,gl.docdate),13) = 'January' THEN 'January'
WHEN LEFT(DATENAME(MONTH,gl.docdate),13) = 'February' THEN 'February'
WHEN LEFT(DATENAME(MONTH,gl.docdate),13) = 'March' THEN 'March'
WHEN LEFT(DATENAME(MONTH,gl.docdate),13) = 'April' THEN 'April'
WHEN LEFT(DATENAME(MONTH,gl.docdate),13) = 'May' THEN 'May'
WHEN LEFT(DATENAME(MONTH,gl.docdate),13) = 'June' THEN 'June'
WHEN LEFT(DATENAME(MONTH,gl.docdate),13) = 'July' THEN 'July'
WHEN LEFT(DATENAME(MONTH,gl.docdate),13) = 'August' THEN 'August'
WHEN LEFT(DATENAME(MONTH,gl.docdate),13) = 'September' THEN 'September'
WHEN LEFT(DATENAME(MONTH,gl.docdate),13) = 'October' THEN 'October'
WHEN LEFT(DATENAME(MONTH,gl.docdate),13) = 'November' THEN 'November'
WHEN LEFT(DATENAME(MONTH,gl.docdate),13) = 'December' THEN 'December'
end [Month]
, 'Company' AS [Company]
, gl.reknr AS [GL Account]
, acct.oms25_0 AS [Description]
, YEAR(gl.docdate) AS [YEAR]
, CAST(ROUND(gl.bdr_hfl,2,0) AS DECIMAL (18,2)) AS [Amount]
FROM [006].dbo.gbkmut gl LEFT OUTER JOIN [006].dbo.grtbk acct ON gl.reknr = acct.reknr
WHERE docdate BETWEEN DATEADD(yy, DATEDIFF(yy,0,GETDATE()), 0) and DATEADD(yy, DATEDIFF(yy,0,GETDATE()) + 1, -1)

) AS TrialBalance
PIVOT
(
SUM(Amount)
FOR [MONTH] IN (January, February, March, April, May, June, July, August, September, October, November, December)
) AS PVT
 
Take a look at the Excel Add-in for Macola ES or the 3rd party solution F9.

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Synergy, and Crystal Reports. Check out our Macola tools:
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top