Crystal Reports中如何将缺勤公式与迟到折算缺勤公式相加？

现有设置与问题 #

分组结构：

• 分组1：student
• 分组2：class
• 分组3：StudentWorkList_.Excuse Type（分为Absent和Tardy两类）

需求：将缺勤（Absent）次数，加上迟到（Tardy）按每3次折算1次缺勤后的总数，再按 /76*100 计算占比。

当前问题：单独计算Absent和Tardy折算值的公式结果正确，但直接相加两个公式无效果。

现有公式 #

Absent 公式 #

if GroupName ({StudentWorkList_.Excuse Type}) = "Absent" then Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type})/76*100

Tardy 折算公式 #

if GroupName ({StudentWorkList_.Excuse Type}) = "Tardy" then
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 3 then 1 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 4 then 1 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 5 then 1 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 6 then 2 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 7 then 2 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 8 then 2 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 9 then 3 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 10 then 3 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 11 then 3 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 12 then 4 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 13 then 4 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 14 then 4 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 15 then 5 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 16 then 5 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 17 then 5 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 18 then 6 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 19 then 6 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 20 then 6 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 21 then 7 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 22 then 7 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 23 then 7 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 24 then 8 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 25 then 8 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 26 then 8 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 27 then 9 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 28 then 9 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 29 then 9 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 30 then 10 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 31 then 10 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 32 then 10 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 33 then 11 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 34 then 11 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 35 then 11 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 36 then 12 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 37 then 12 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 38 then 12 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 39 then 13 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 40 then 13 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 41 then 13 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 42 then 14 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 43 then 14 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 44 then 14 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 45 then 15 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 46 then 15 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 47 then 15 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 48 then 16 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 49 then 16 /76*100 else
if Count ({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) = 50 then 16 /76*100

问题原因 #

当前两个公式仅在对应分组（Absent或Tardy）内返回数值，其他分组下返回空值（Null），直接相加时Null会导致整个结果为Null，因此看不到求和效果。同时，Tardy的折算公式可以用数学方法大幅简化，无需大量if判断。

解决方案 #

步骤1：简化Tardy折算逻辑 #

每3次迟到折算1次缺勤，本质是对迟到次数做向下取整除法，用Floor()函数即可实现：

Floor(Count({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) / 3)

步骤2：创建跨分组汇总公式 #

要实现Absent和Tardy折算值的求和，需要用Sum()函数针对整个student或class分组统计数据，公式如下（根据需要选择汇总层级）：

// 计算当前分组下Absent的总次数
Sum(
    if {StudentWorkList_.Excuse Type} = "Absent" then 
        Count({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) 
    else 0,
    {student} // 替换为{class}可按班级汇总
) 
// 加上Tardy折算后的缺勤数
+ Sum(
    if {StudentWorkList_.Excuse Type} = "Tardy" then 
        Floor(Count({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) / 3) 
    else 0,
    {student} // 替换为{class}可按班级汇总
)
// 计算最终占比
/ 76 * 100

步骤3：公式放置位置 #

将该汇总公式拖到student分组的组尾或class分组的组尾，确保它能获取到对应分组下所有Absent和Tardy的统计数据。

如果需要在Excuse Type分组内显示汇总值，可使用局部变量存储统计结果：

local numbervar absent_total := Sum(
    if {StudentWorkList_.Excuse Type} = "Absent" then 
        Count({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) 
    else 0,
    {student}
);
local numbervar tardy_convert := Sum(
    if {StudentWorkList_.Excuse Type} = "Tardy" then 
        Floor(Count({StudentWorkList_.Excuse Type}, {StudentWorkList_.Excuse Type}) / 3) 
    else 0,
    {student}
);
(absent_total + tardy_convert) /76*100

内容的提问来源于stack exchange，提问作者AS6144