Kitt_Shickers
asked on
SSRS 2008r2 Using Aggregate on field with an Expression Getting an Error
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
I've added a call to the "Add_OT_Hrs" function in the field where the expression is and this works fine.
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.
AggregateOnExpressionField.png
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
return OT_Hrs
End Function
Public Function GetTotal()
return tot_OT_Hrs
End Function
I've added a call to the "Add_OT_Hrs" function in the field where the expression is and this works fine.
=Code.Add_OT_Hrs(
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
=Code.GetTotal()
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.
AggregateOnExpressionField.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER