crystal-reports - 交叉表报表查询

Question

我已经使用 SQL 查询创建了一个交叉表报告，该报告将根据日期参数和分支参数显示分支的 P/L 语句。

这是报告的输出：

是否可以在报告末尾将总收入和总费用之间的差异作为单独的一行？

下图显示了水晶报表的设计：

报告中使用的查询：

SELECT
CASE
WHEN T2.GroupMask = '4' THEN 'Revenue'
else 'Expenses'
end [Flag],case when t3.BPLId = '1' then 'HO'
WHEN T3.BPLId = '4' THEN 'TEST BRANCH'
ELSE ' '
END [BPLId]
,T1.Account, T2.[AcctName],Isnull((SELECT SUM(T3.Debit - T3.Credit) FROM OJDT T2

INNER JOIN JDT1 T3 ON T2.TransId = T3.TransId

WHERE DateDiff(dd,T2.RefDate,'20200401') > 0 AND T3.Account LIKE T1.Account

GROUP BY T3.Account),0) 'Opening balance',

SUM(T1.Debit) 'Debit', SUM(T1.Credit) 'Credit',

SUM(T1.Debit - T1.Credit) AS 'Balance'

FROM OJDT T0

INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

INNER JOIN OACT T2 ON T1.Account = T2.AcctCode

LEFT JOIN OBPL T3 ON T3.BPLId = T1.BPLId

WHERE T0.RefDate >= '20200401' AND T0.RefDate <= '20200430' AND T3.BPLId in ('4')

and t2.GroupMask in ('4','5','6','7')

GROUP BY

T1.Account,t3.BPLId, T2.[AcctName], t2.Groupmask,T2.FatherNum

Having SUM(T1.Debit - T1.Credit) != 0

order by t2.GroupMask,t2.FatherNum

标签： crystal-reportscrystal-reports-2010sapb1