Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
How to SUM Group wise Percentage values in the ssrs

Beantwortet How to SUM Group wise Percentage values in the ssrs

  • Wednesday, January 09, 2013 1:11 PM
     
     
    Sign In to Vote
    0

    How to add Percentages values in the SSRS .

    because these are group values which calculated in query department and resource wise and are repeating in query .

    and I need to sum the percentages of all resources of one department but these sum also all the repeating percentages values.


    • Edited by John.Eddie Thursday, January 10, 2013 3:26 PM
    •  
     

All Replies

  • Wednesday, January 09, 2013 1:37 PM
     
     
    Sign In to Vote
    0

    hi

    use this:

    =sum(iif(Fields!Departments.Value=Previous(Fields!Departments.Value),0,Fields!Percentage.Value))

    so this will compare if the previous department is the same, if yes, add 0, if not add the pct value. You can replace the "Fields!Percentage.Value" with an expression if you are calculating it on the fly.

    Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

    k r o o t z


    • Edited by krootz Wednesday, January 09, 2013 1:37 PM
    •  
     
  • Wednesday, January 09, 2013 1:57 PM
     
     
    Sign In to Vote
    0
     
  • Thursday, January 10, 2013 2:05 PM
     
     
    Sign In to Vote
    0

     
  • Thursday, January 10, 2013 2:59 PM
     
      Has Code
    Sign In to Vote
    0

    hi,

    can you try this? add this to the code property of your report:

    public dim pct_total as double
public dim p_dept as string
public dim p_rname as string
public dim p_proj as string

    public function add_pct(byval dept as string, byval rname as string, byval proj as string, byval pct as double) as double
if p_dept<>dept or p_rname<>rname <> p_proj=proj then
    pct_total=pct_total+pct
    end if
    p_proj=proj
    p_dept=dept
    p_rname=rname
return pct
end function

    public function get_total_pct() as double
    return pct_total
end function

    Then in your Percentage expression on the same line as your resource name row, enter

    =Code.add_pct(Fields!Departments.Value,Fields!ResourceName.Value,Fields!ProjectName.Value,Fields.Percentage.Value)
    This function will pass the 4 values and will check whether the previously passed dept, resource, project was the same as before and will only add the pct if they are not.

    Finally, on your total percentage, enter the expression =Code.get_total_pct()

    Sorry I dont have time to double check the code, but you get the idea. If there's any errors let me know.

    Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

    k r o o t z


    • Edited by krootz Thursday, January 10, 2013 3:02 PM
    •  
     
  • Sunday, January 13, 2013 3:32 PM
     
     
    Sign In to Vote
    0

    also showing this error when run the report .

    • Edited by John.Eddie Monday, January 14, 2013 6:40 AM
    •  
     
  • Monday, January 14, 2013 1:08 PM
     
     Answered Has Code
    Sign In to Vote
    0

    hi,

    sorry, was in a hurry, pls change below:

    ___________

    public dim pct_total as double
public dim p_dept as string
public dim p_rname as string
public dim p_proj as string

public function add_pct(byval dept as string, byval  rname as string, byval proj as string, byval pct as
double) as double

if p_dept<>dept or p_rname<>rname or p_proj<>proj then
 pct_total=pct_total+pct
end if 
p_proj=proj 
p_dept=dept
p_rname=rname
end function

public function get_total_pct() as double
 return pct_total 
end function

    The error shows 'name' is not declared, make sure you are using rname, not name in the code.

    Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

    k r o o t z