【SAP Abap】一条SQL语句实现支持报表项配置的财务报表

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 hsl
	from ztfi002 as h
	left 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 end
				and 
					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 end
				and 
					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 null
	  and a.rbukrs = 2300  -- 测试数据
)
, lt_lt_acdoca_sum as (
	select zcolumn, zrow, ztext, poper, sum( coalesce( hsl,0 ) ) as hsl 
	from lt_acdoca
	group 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 (
    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 HSL
    FROM ZTFI002 AS H
    LEFT 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 NULL
      AND A~RBUKRS IN @R_BUKRS
      AND A~RLDNR = @P_RLDNR
  )
  , +LT_LT_ACDOCA_SUM AS (
    SELECT ZCOLUMN, ZROW, ZTEXT, POPER, SUM( COALESCE( HSL,0 ) ) AS HSL
    FROM +LT_ACDOCA
    GROUP 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 ZCOLUMN, ZROW
  INTO CORRESPONDING FIELDS OF TABLE @GT_DATA.

ENDFORM.

最终结果如下：

5、总结

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

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