SSRS SQL Server 2008r2
I'm trying to perform an aggregate (Sum) on a field within a GROUPing that contains an expression.
The field where I want to SUM to appear is within a different GROUPing.
I've created the following Function within Report Properties
Dim public tot_OT_Hrs As Decimal
Public Function Add_OT_Hrs(ByVal OT_Hrs As Decimal) AS Decimal
tot_OT_Hrs = tot_OT_Hrs + OT_Hrs
Public Function GetTotal()
I've added a call to the "Add_OT_Hrs" function in the field where the expression is and this works fine.
IIF(Sum(cdec(Fields!HrsWorked.Value)) > cdec(Parameters!StdWorkingHrs.Value),
Sum(cdec(Fields!HrsWorked.Value)) - cdec(Parameters!StdWorkingHrs.Value) + Sum(cdec(Fields!Rate1Hrs.Value))
, Sum(cdec(Fields!Rate1Hrs.Value ) )
Fields!Rate1Hrs.Value is the field in which the expression resides in and Fields!HrsWorked.Value is an adjacent field. Both are defined as DECIMAL(10,2) in the proc.
However, the field where I want to total to appear I've added the following
and all that is returned here is 0.00 on every row in the GROUPing. If I initialise the Dim public tot_OT_Hrs As Decimal variable to say 1.2 then 1.2 is returned on every row in the GROUPing. The Add_OT_Hrs function isn't working as expected.
Where am I going wrong?
Thanks in advance.