SQL Server Report Builder 2.0中使用custom code实现sum distinct

有时在报表中，我们会将在数据库中具主从关系的两个表（head 和 detail）的数据统一放在一行里显示，即既有主表（detail）的数据，又有从表（head）的数据。这个时候，如果我们需要对从表的某一个数值进行求和，则不能简单地使用sum，因为从表数据有可能根据其对应的主表有多行也显示成了多行，这个时候如果简单计和，则很明显有重复的数据。

 

举例说明：

 

主表为学生信息：学生编号、学生姓名、辅导老师编号；

从表为老师信息：老师编号、老师姓名、工资；

 

在一个辅导老师需要辅导多名学生的前提下，假设我们的报表格式为：

辅导老师姓名、工资、学生姓名

 

那么同一个老师的数据将在多行出现，这个时候如果要计算全校老师的工资总和，则需要使用sum( 工资, distinct 老师编号)这种格式。

 

然而，在SQL Server Report Builder 2.0中，本身的系统函数不能对数据根据某一列或多列先进行distinct再求和，我们需要通过custom code来实现。

 

在report的属性对话框中的Code区域，我们写上：

 

   Private _DistinctTotal As System.Collections.Generic.Dictionary(Of String, Decimal)

   Private ReadOnly Property DistinctTotal() As System.Collections.Generic.Dictionary(Of String, Decimal)

       Get

           If (_DistinctTotal Is Nothing) Then

               _DistinctTotal = New System.Collections.Generic.Dictionary(Of String, Decimal)

           End If

           Return _DistinctTotal

       End Get

   End Property

   Public Function GetDistinctTotal(ByVal oid As String, ByVal amount As Decimal) As Decimal

       If (Not Me.DistinctTotal.ContainsKey(oid)) Then

           Me.DistinctTotal.Add(oid, amount)

           Return amount

       Else

           Return 0

       End If

   End Function

 

然后在计总和的字段的值表达式里写上：

=Sum( code.GetDistinctTotal( Fields!老师编号.Value, Fields!工资.Value))

 

这样，结果就符合我们的需求了。

 

如果你的报表分了多个组，比如上述报表按科目来对老师的工资汇总，格式如下：

 

语文、语文辅导老师工资总和

辅导老师姓名、工资、学生姓名

 

数学、数学辅导老师工资总和

辅导老师姓名、工资、学生姓名

 

生物、生物辅导老师工资总和

……

 

然后每一组需要计一次和，如果有的老师既辅导语文也辅导数学，那么仅按老师编号来distinct显然是不对的，因为变量_DistinctTotal As System.Collections.Generic.Dictionary是全局作用域的，如果在语文组中该老师已经计算了工资的话，在数学组中GetDistinctTotal函数将返回0，因此，我们在汇总的时候还应该加上科目组别进行distinct：

 

=Sum( code.GetDistinctTotal( Fields!科目组别.Value & Fields!老师编号.Value, Fields!工资.Value))

//可以不需要另设参数，只需要叠加起来，仍然作为一个参数即可。

 

依此类推，如果有多个分组需要distinct来计和，只需要依次添加分组字段到第一个参数即可（如果存在空值，则需转换成空字符，或者依分组的相应逻辑处理）。