REPORTFORM;BS1.frf
TITLE;§º´ØÅáººÊÃØ»
SQL;drop table if exists _BS_Sum&
SQL;create table _BS_Sum
SQL;select glmst.GLGROUP, glgroup.glgroupdesc, glmst.GLTYPE,
SQL;sum(gltr.DEBIT) as tdebit , sum(gltr.CREDIT) as tcredit, 0000000000.00 as xbal,
SQL;SPACE(50) AS bgroup,SPACE(50) AS bgroup2,0 as line,glgroup.glgroupe, "01" as xindex,0 as repeng
SQL;from gltr inner join glmst on gltr.GLID = glmst.GLID
SQL;left join glgroup on glmst.GLGROUP = glgroup.glgroup
SQL;where (left(gltype,1) = 'A' or left(gltype,1) = 'L' or left(gltype,1) = 'O')
SQL;and gltr.datedoc <= :m11
SQL;group by glmst.GLGROUP, glgroup.glgroupdesc, glmst.GLTYPE,glgroup.glgroupe&
SQL;
SQL;drop table if exists _BS_Sum0&
SQL;create table _BS_Sum0
SQL;select sum(debit) as tdebit, sum(credit) as tcredit 
SQL;from gltr  inner join glmst on gltr.GLID = glmst.GLID
SQL;where (left(gltype,1) = 'R' or left(gltype,1) = 'C' or left(gltype,1) = 'E')
SQL;and datedoc <= :m11&
SQL;
SQL;insert into _BS_Sum 
SQL;select "O04", "¡ÓäÃÀÒÂã¹»Õ","O1-·Ø¹", tdebit,tcredit,0.00,"","",0,"Profit/Loss (Current Year","01",0
SQL;from _BS_Sum0&
SQL;
SQL;drop table if exists _BS_Sum0&
SQL;
SQL;update _BS_Sum inner join config on _BS_Sum.xindex = config.xindex
SQL;set _BS_Sum.repeng = 1 where config.repeng = "Y"&
SQL;
SQL;
SQL;update _BS_Sum set line = 1, bgroup = "·ÃÑ¾ÂìÊÔ¹", bgroup2 = "·ÃÑ¾ÂìÊÔ¹"
SQL;where left(GLGROUP,1) = "A"&
SQL;
SQL;update _BS_Sum set line = 2, bgroup = "Ë¹ÕéÊÔ¹/à§Ô¹Å§·Ø¹", bgroup2 = "Ë¹ÕéÊÔ¹"
SQL;where left(GLGROUP,1) = "L"&
SQL;
SQL;update _BS_Sum set line = 3, bgroup = "Ë¹ÕéÊÔ¹/à§Ô¹Å§·Ø¹", bgroup2 = "à§Ô¹Å§·Ø¹"
SQL;where left(GLGROUP,1) = "O"&
SQL;
SQL;update _BS_Sum set xbal = tdebit - tcredit where left(gltype,1) = 'A'&
SQL;update _BS_Sum set xbal = tcredit - tdebit where left(gltype,1) <> 'A'&
SQL;
SQL;update _BS_Sum set glgroupdesc = glgroupe where repeng = 1&
SQL;update _BS_Sum set line = 1, bgroup = "Assets", bgroup2 = "Assets"
SQL;where left(GLGROUP,1) = "A" and repeng = 1&
SQL;update _BS_Sum set line = 2, bgroup = "Liabilities/Capital", bgroup2 = "Liabilities"
SQL;where left(GLGROUP,1) = "L" and repeng = 1&
SQL;update _BS_Sum set line = 3, bgroup = "Liabilities/Capital", bgroup2 = "Capital"
SQL;where left(GLGROUP,1) = "O" and repeng = 1&
SQL;
SQL;select * from _BS_Sum order by line, glgroup
CONDITION;ÊÔé¹ÊØ´ ³ ÇÑ¹·Õè~datedoc ~LDATE~1~~
EXCELSQL;drop table if exists _BS_Sum&
EXCELSQL;create table _BS_Sum
EXCELSQL;select glmst.GLGROUP, glgroup.glgroupdesc, glmst.GLTYPE,
EXCELSQL;sum(gltr.DEBIT) as tdebit , sum(gltr.CREDIT) as tcredit, 0000000000.00 as xbal,
EXCELSQL;SPACE(50) AS bgroup,SPACE(50) AS bgroup2,0 as line,glgroup.glgroupe, "01" as xindex,0 as repeng
EXCELSQL;from gltr inner join glmst on gltr.GLID = glmst.GLID
EXCELSQL;left join glgroup on glmst.GLGROUP = glgroup.glgroup
EXCELSQL;where (left(gltype,1) = 'A' or left(gltype,1) = 'L' or left(gltype,1) = 'O')
EXCELSQL;and gltr.datedoc <= :m11
EXCELSQL;group by glmst.GLGROUP, glgroup.glgroupdesc, glmst.GLTYPE,glgroup.glgroupe&
EXCELSQL;
EXCELSQL;drop table if exists _BS_Sum0&
EXCELSQL;create table _BS_Sum0
EXCELSQL;select sum(debit) as tdebit, sum(credit) as tcredit 
EXCELSQL;from gltr  inner join glmst on gltr.GLID = glmst.GLID
EXCELSQL;where (left(gltype,1) = 'R' or left(gltype,1) = 'C' or left(gltype,1) = 'E')
EXCELSQL;and datedoc <= :m11&
EXCELSQL;
EXCELSQL;insert into _BS_Sum 
EXCELSQL;select "O04", "¡ÓäÃÀÒÂã¹»Õ","O1-·Ø¹", tdebit,tcredit,0.00,"","",0,"Profit/Loss (Current Year","01",0
EXCELSQL;from _BS_Sum0&
EXCELSQL;
EXCELSQL;drop table if exists _BS_Sum0&
EXCELSQL;
EXCELSQL;update _BS_Sum inner join config on _BS_Sum.xindex = config.xindex
EXCELSQL;set _BS_Sum.repeng = 1 where config.repeng = "Y"&
EXCELSQL;
EXCELSQL;
EXCELSQL;update _BS_Sum set line = 1, bgroup = "·ÃÑ¾ÂìÊÔ¹", bgroup2 = "·ÃÑ¾ÂìÊÔ¹"
EXCELSQL;where left(GLGROUP,1) = "A"&
EXCELSQL;
EXCELSQL;update _BS_Sum set line = 2, bgroup = "Ë¹ÕéÊÔ¹/à§Ô¹Å§·Ø¹", bgroup2 = "Ë¹ÕéÊÔ¹"
EXCELSQL;where left(GLGROUP,1) = "L"&
EXCELSQL;
EXCELSQL;update _BS_Sum set line = 3, bgroup = "Ë¹ÕéÊÔ¹/à§Ô¹Å§·Ø¹", bgroup2 = "à§Ô¹Å§·Ø¹"
EXCELSQL;where left(GLGROUP,1) = "O"&
EXCELSQL;
EXCELSQL;update _BS_Sum set xbal = tdebit - tcredit where left(gltype,1) = 'A'&
EXCELSQL;update _BS_Sum set xbal = tcredit - tdebit where left(gltype,1) <> 'A'&
EXCELSQL;
EXCELSQL;update _BS_Sum set glgroupdesc = glgroupe where repeng = 1&
EXCELSQL;update _BS_Sum set line = 1, bgroup = "Assets", bgroup2 = "Assets"
EXCELSQL;where left(GLGROUP,1) = "A" and repeng = 1&
EXCELSQL;update _BS_Sum set line = 2, bgroup = "Liabilities/Capital", bgroup2 = "Liabilities"
EXCELSQL;where left(GLGROUP,1) = "L" and repeng = 1&
EXCELSQL;update _BS_Sum set line = 3, bgroup = "Liabilities/Capital", bgroup2 = "Capital"
EXCELSQL;where left(GLGROUP,1) = "O" and repeng = 1&
EXCELSQL;
EXCELSQL;select * from _BS_Sum order by line, glgroup
USECROSSTAB;N
CROSSTABFRF;N
CROSSTABHEAD;
CROSSTABFIELD;
