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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kitt_ShickersAuthor Commented:
Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.