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

    
    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

Open in new window


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 ) )
		)
	)

Open in new window


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()

Open in new window


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.

AggregateOnExpressionField.png
Where am I going wrong?

Thanks in advance.
AggregateOnExpressionField.png
Kitt_ShickersAsked:
Who is Participating?
 
nishant joshiTechnology Development ConsultantCommented:
Hey Kitt,

As per my understanding on your requirements, you don't need to create function to get total.In total field you can directly add below code directy sum of hours which will produce total for you.

=Sum(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 ) )
		)
	))

Open in new window

0
 
Kitt_ShickersAuthor Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.