none
Total of field from custom code

    Question

  • Hi All,

    I am using the following custom code in a matrix report(SSRS 2008) to find the difference between two columns and it works fine. I added a column and am using the following expression in that column  - Code.GetPreviousValue(Fields!Amount.Value,Fields!Customer.Value)

    However, I need to calculate a percentage based on this column. I need to take the individual values from this column and divide by the total from the same column to get the percentage. How can I grab the total of this expression Code.GetPreviousValue(Fields!Amount.Value,Fields!Customer.Value)? I tried using SUM but it doesn’t give me the total of this column as it is in a group called “trans year”. I also tried specifying the group name in the sum expression but no luck. Any idea how to do this? Below is the custom code.

    Thanks for your help.

    DIM PreviousValue AS Decimal
    
    Dim ServiceName AS String = ""
    
    Public Function  GetPreviousValue(byval Val as Decimal, byval SrvName as string)  as Decimal
    
                    DIM Local_PreviousValue AS Decimal
    
                    
    
                    IF ServiceName <> SrvName THEN
    
                                    ServiceName  = SrvName 
    
                                    PreviousValue  = val
    
                                    Local_PreviousValue  = 0
    
                    ELSE
    
                                    Local_PreviousValue =  val - PreviousValue 
    
                                    PreviousValue  = val
    
                    END IF
    
                    return Local_PreviousValue 
    
    End function
    

    Monday, January 20, 2014 3:34 AM

All replies

  • My suggestion would be to review your dataset and try to add this custom calculations in your actual query so you don't need this custom VB code. However, if you feel that custom code is unavoidable then kindly share screen-capture of this report in-design and preview mode to better understand where number are required since Sum before/after detail rows/columns using custom code may require different strategy.
    Monday, January 20, 2014 5:16 AM
  • Here is the structure of the report. I cannot do this in query unfortunately.

    Monday, January 20, 2014 7:53 AM
  • Try below steps:

    1. Create another variable (i.e. TotalValues "global variable") to store sum values outside GetPreviousValue function.

    DIM TotalValues AS Decimal

    2. Add value of Local_PreviousValue variable in TotalValues variable before GetPreviousValue function's return statement.

    TotalValues = TotalValues + Local_PreviousValue

    3. Create simple Getter function to return this variable's value where every you need it. For example:

    Public Function  GetTotal()  as Decimal

    Return TotalValues

    End Function


    Monday, January 20, 2014 9:58 PM
  • Thank you so much Samiuallah,

    i followed your suggesitons and used the code below.

    DIM PreviousValue AS Decimal
    Dim ServiceName AS String = ""
    DIM TotalValues AS Decimal
    
    Public Function  GetPreviousValue(byval Val as Decimal, byval SrvName as string)  as Decimal
    
    	DIM Local_PreviousValue AS Decimal
    	
    	IF ServiceName <> SrvName THEN
    		ServiceName  = SrvName 
    		PreviousValue  = val
    		Local_PreviousValue  = 0
    	ELSE
    		Local_PreviousValue =  val + PreviousValue 
    		PreviousValue  = val
    	END IF
    	TotalValues = TotalValues + Local_PreviousValue
    	return Local_PreviousValue 
    End function
    
    Public Function  GetTotal()  as Decimal
     
    Return TotalValues 
    
    End Function

    I then added a new columns and using the expression below in that column. But still it is not giving me the total. can you please help?

    =Code.GetTotal()


    Tuesday, January 21, 2014 3:17 AM