GL Trial Balance Report

[elsenorjose]

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!

 

[elsenorjose]

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

 

[dgillz]

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:

http://www.gainfocus.biz/exceladdin.html