Link to home
Create AccountLog in
Avatar of Kitt_Shickers
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

    
    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.

User generated image
Where am I going wrong?

Thanks in advance.
AggregateOnExpressionField.png
ASKER CERTIFIED SOLUTION
Avatar of nishant joshi
nishant joshi
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Kitt_Shickers
Kitt_Shickers

ASKER

Thank you