1、业务背景

在财务三大报表之外，业务需要使用类似的科目可配置方式，开发一个包括销售、管理、研发、财务的四大费用报表。

报表项配置表如下：


报表数据显示格式如下：

FS取值逻辑如下：


每月取值逻辑：根据选择屏幕输入的
公司代码、会计年度、分类账,前面的功能范围、报表项维护的总账科目，取出ACDOCA-POPER等于’01’期间ACDOCA-HSL汇总金额。

2、配置项特殊处理

（1）该报表没有AB左右列区分，使用列编号来区分四大费用类型：A-销售、B-管理、C-研发、D-财务；
（2）明细项太多，每种类型费用汇总项的公式列，写具体公式太麻烦，对未来增加明细项也不太友好，改用“ALL”标识。

3、实现方式（Hana Studio SQL语句）

with lt_acdoca as (select h.zcolumn, h.zrow, h.ztext,a.rbukrs, a.gjahr, a.poper, a.rldnr, a.RFAREA, a.racct, a.DRCRK, ( case when h.REVERSAL = 'X' then - a.hsl else a.hsl end ) as hslfrom ztfi002 as hleft join acdoca as a on a.gjahr = 2023 and a.poper <= 3	--测试数据and (( h.fsaknr = '' and h.tsaknr = '') or ( a.racct between case when h.fsaknr = '' then h.tsaknr else h.fsaknr endand case when h.tsaknr = '' then h.fsaknr else h.tsaknr end)-- 说明：以上or语句在opensql中不兼容，可以改用以下三句等效语句--or ( h.fsaknr = '' and h.tsaknr <> '' and a.racct = h.tsaknr )--or ( h.fsaknr <> '' and h.tsaknr = '' and a.racct = h.fsaknr )--or ( h.fsaknr <> '' and h.tsaknr <> '' and a.racct between h.fsaknr and h.tsaknr )) and (( h.frfarea = '' and h.trfarea = '') or ( a.RFAREA between case when h.frfarea = '' then h.trfarea else h.frfarea endand case when h.trfarea = '' then h.frfarea else h.trfarea end)-- 说明：以上or语句在opensql中不兼容，可以改用以下三句等效语句--or ( h.frfarea = '' and h.trfarea <> '' and a.RFAREA = h.trfarea )--or ( h.frfarea <> '' and h.trfarea = '' and a.RFAREA = h.frfarea )--or ( h.frfarea <> '' and h.trfarea <> '' and a.RFAREA between h.frfarea and h.trfarea )) and ( a.DRCRK = ( case when h.ZJFBS = 'X' then 'S' else '' end )or a.DRCRK = ( case when h.ZDFBS = 'X' then 'H' else '' end )-- 说明：以上两行语句在opensql中不兼容，可以改用以下三行等效语句--( h.ZJFBS = 'X' and h.ZDFBS = 'X' )--or ( h.ZJFBS = 'X' and a.DRCRK = 'S' )--or ( h.ZDFBS = 'X' and a.DRCRK = 'H' ))where h.ztype = 'FE' and h.FORMULA <> 'ALL'and a.poper is not nulland a.rbukrs = 2300  -- 测试数据
)
, lt_lt_acdoca_sum as (select zcolumn, zrow, ztext, poper, sum( coalesce( hsl,0 ) ) as hsl from lt_acdocagroup by zcolumn, zrow, ztext, poper
)
, lt_header as ( select distinct zcolumn, zrow, ztext, ztype, FORMULA from ztfi002 )
select h.zcolumn, h.zrow, h.ztext,sum( coalesce( s.hsl,0 ) ) as hsl,sum( case when s.poper = '001' then coalesce( s.hsl,0 ) else 0 end ) as hsl01,sum( case when s.poper = '002' then coalesce( s.hsl,0 ) else 0 end ) as hsl02,sum( case when s.poper = '003' then coalesce( s.hsl,0 ) else 0 end ) as hsl03,sum( case when s.poper = '004' then coalesce( s.hsl,0 ) else 0 end ) as hsl04,sum( case when s.poper = '005' then coalesce( s.hsl,0 ) else 0 end ) as hsl05,sum( case when s.poper = '006' then coalesce( s.hsl,0 ) else 0 end ) as hsl06,sum( case when s.poper = '007' then coalesce( s.hsl,0 ) else 0 end ) as hsl07,sum( case when s.poper = '008' then coalesce( s.hsl,0 ) else 0 end ) as hsl08,sum( case when s.poper = '009' then coalesce( s.hsl,0 ) else 0 end ) as hsl09,sum( case when s.poper = '010' then coalesce( s.hsl,0 ) else 0 end ) as hsl10,sum( case when s.poper = '011' then coalesce( s.hsl,0 ) else 0 end ) as hsl11,sum( case when s.poper = '012' then coalesce( s.hsl,0 ) else 0 end ) as hsl12,sum( case when s.poper = '013' then coalesce( s.hsl,0 ) else 0 end ) as hsl13,sum( case when s.poper = '014' then coalesce( s.hsl,0 ) else 0 end ) as hsl14,sum( case when s.poper = '015' then coalesce( s.hsl,0 ) else 0 end ) as hsl15,sum( case when s.poper = '016' then coalesce( s.hsl,0 ) else 0 end ) as hsl16
from lt_header as h
left join lt_lt_acdoca_sum as s on s.zcolumn = h.zcolumn and s.zrow = h.zrow
where h.ztype = 'FE' and h.FORMULA <> 'ALL'
group by h.zcolumn, h.zrow, h.ztext
union all
select h.zcolumn, h.zrow, h.ztext,sum( coalesce( s.hsl,0 ) ) as hsl,sum( case when s.poper = '001' then coalesce( s.hsl,0 ) else 0 end ) as hsl01,sum( case when s.poper = '002' then coalesce( s.hsl,0 ) else 0 end ) as hsl02,sum( case when s.poper = '003' then coalesce( s.hsl,0 ) else 0 end ) as hsl03,sum( case when s.poper = '004' then coalesce( s.hsl,0 ) else 0 end ) as hsl04,sum( case when s.poper = '005' then coalesce( s.hsl,0 ) else 0 end ) as hsl05,sum( case when s.poper = '006' then coalesce( s.hsl,0 ) else 0 end ) as hsl06,sum( case when s.poper = '007' then coalesce( s.hsl,0 ) else 0 end ) as hsl07,sum( case when s.poper = '008' then coalesce( s.hsl,0 ) else 0 end ) as hsl08,sum( case when s.poper = '009' then coalesce( s.hsl,0 ) else 0 end ) as hsl09,sum( case when s.poper = '010' then coalesce( s.hsl,0 ) else 0 end ) as hsl10,sum( case when s.poper = '011' then coalesce( s.hsl,0 ) else 0 end ) as hsl11,sum( case when s.poper = '012' then coalesce( s.hsl,0 ) else 0 end ) as hsl12,sum( case when s.poper = '013' then coalesce( s.hsl,0 ) else 0 end ) as hsl13,sum( case when s.poper = '014' then coalesce( s.hsl,0 ) else 0 end ) as hsl14,sum( case when s.poper = '015' then coalesce( s.hsl,0 ) else 0 end ) as hsl15,sum( case when s.poper = '016' then coalesce( s.hsl,0 ) else 0 end ) as hsl16
from lt_header as h
left join lt_lt_acdoca_sum as s on s.zcolumn = h.zcolumn
where h.ztype = 'FE' and h.FORMULA = 'ALL'
group by h.zcolumn, h.zrow, h.ztext
order by h.zcolumn, h.zrow

显示结果如下：

4、实现方式（Abap OpenSQL语句）

将以上SQL语句复制到Abap代码中，检查代码语句，根据错误提示进行调整，最终调整代码如下：

FORM FRM_GET_DATA.WITH +LT_ACDOCA AS (SELECTH~ZCOLUMN, H~ZROW, H~ZTEXT,A~RBUKRS, A~GJAHR, A~POPER, A~RLDNR,A~RFAREA, A~RACCT, A~DRCRK,( CASE WHEN H~REVERSAL = 'X' THEN - A~HSL ELSE A~HSL END ) AS HSLFROM ZTFI002 AS HLEFT JOIN ACDOCA AS A ON  A~GJAHR = @P_RYEAR AND ( @P_POPER IS INITIAL OR A~POPER <= @P_POPER )AND (( H~FSAKNR = '' AND H~TSAKNR = '')OR ( H~FSAKNR = '' AND H~TSAKNR <> '' AND A~RACCT = H~TSAKNR )OR ( H~FSAKNR <> '' AND H~TSAKNR = '' AND A~RACCT = H~FSAKNR )OR ( H~FSAKNR <> '' AND H~TSAKNR <> '' AND A~RACCT BETWEEN H~FSAKNR AND H~TSAKNR ))AND (( H~FRFAREA = '' AND H~TRFAREA = '')OR ( H~FRFAREA = '' AND H~TRFAREA <> '' AND A~RFAREA = H~TRFAREA )OR ( H~FRFAREA <> '' AND H~TRFAREA = '' AND A~RFAREA = H~FRFAREA )OR ( H~FRFAREA <> '' AND H~TRFAREA <> '' AND A~RFAREA BETWEEN H~FRFAREA AND H~TRFAREA ))AND (( H~ZJFBS = 'X' AND H~ZDFBS = 'X' )OR ( H~ZJFBS = 'X' AND A~DRCRK = 'S' )OR ( H~ZDFBS = 'X' AND A~DRCRK = 'H' ))WHERE H~ZTYPE = 'FE' AND H~FORMULA <> 'ALL'AND A~POPER IS NOT NULLAND A~RBUKRS IN @R_BUKRSAND A~RLDNR = @P_RLDNR), +LT_LT_ACDOCA_SUM AS (SELECT ZCOLUMN, ZROW, ZTEXT, POPER, SUM( COALESCE( HSL,0 ) ) AS HSLFROM +LT_ACDOCAGROUP BY ZCOLUMN, ZROW, ZTEXT, POPER), +LT_HEADER AS ( SELECT DISTINCT ZCOLUMN, ZROW, ZTEXT, ZTYPE, FORMULA FROM ZTFI002 )SELECT H~ZCOLUMN, H~ZROW, H~ZTEXT,SUM( COALESCE( S~HSL,0 ) ) AS HSL,SUM( CASE WHEN S~POPER = '001' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL01,SUM( CASE WHEN S~POPER = '002' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL02,SUM( CASE WHEN S~POPER = '003' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL03,SUM( CASE WHEN S~POPER = '004' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL04,SUM( CASE WHEN S~POPER = '005' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL05,SUM( CASE WHEN S~POPER = '006' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL06,SUM( CASE WHEN S~POPER = '007' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL07,SUM( CASE WHEN S~POPER = '008' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL08,SUM( CASE WHEN S~POPER = '009' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL09,SUM( CASE WHEN S~POPER = '010' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL10,SUM( CASE WHEN S~POPER = '011' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL11,SUM( CASE WHEN S~POPER = '012' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL12,SUM( CASE WHEN S~POPER = '013' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL13,SUM( CASE WHEN S~POPER = '014' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL14,SUM( CASE WHEN S~POPER = '015' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL15,SUM( CASE WHEN S~POPER = '016' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL16FROM +LT_HEADER AS HLEFT JOIN +LT_LT_ACDOCA_SUM AS S ON S~ZCOLUMN = H~ZCOLUMN AND S~ZROW = H~ZROWWHERE H~ZTYPE = 'FE' AND H~FORMULA <> 'ALL'GROUP BY H~ZCOLUMN, H~ZROW, H~ZTEXTUNION ALLSELECT H~ZCOLUMN, H~ZROW, H~ZTEXT,SUM( COALESCE( S~HSL,0 ) ) AS HSL,SUM( CASE WHEN S~POPER = '001' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL01,SUM( CASE WHEN S~POPER = '002' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL02,SUM( CASE WHEN S~POPER = '003' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL03,SUM( CASE WHEN S~POPER = '004' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL04,SUM( CASE WHEN S~POPER = '005' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL05,SUM( CASE WHEN S~POPER = '006' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL06,SUM( CASE WHEN S~POPER = '007' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL07,SUM( CASE WHEN S~POPER = '008' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL08,SUM( CASE WHEN S~POPER = '009' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL09,SUM( CASE WHEN S~POPER = '010' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL10,SUM( CASE WHEN S~POPER = '011' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL11,SUM( CASE WHEN S~POPER = '012' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL12,SUM( CASE WHEN S~POPER = '013' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL13,SUM( CASE WHEN S~POPER = '014' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL14,SUM( CASE WHEN S~POPER = '015' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL15,SUM( CASE WHEN S~POPER = '016' THEN COALESCE( S~HSL,0 ) ELSE 0 END ) AS HSL16FROM +LT_HEADER AS HLEFT JOIN +LT_LT_ACDOCA_SUM AS S ON S~ZCOLUMN = H~ZCOLUMNWHERE H~ZTYPE = 'FE' AND H~FORMULA = 'ALL'GROUP BY H~ZCOLUMN, H~ZROW, H~ZTEXTORDER BY ZCOLUMN, ZROWINTO CORRESPONDING FIELDS OF TABLE @GT_DATA.ENDFORM.

最终结果如下：

5、总结

通过以上方法实现的报表，可以大大减少abap代码量，充分利用hana数据库性能，很大程度的提高数据查询效率。

原创文章，转载请注明来源-X档案