How to calculate a Total avg column in Matrix?

• rossetto_ax

  Say Hey Kid

  Points: 666

  More actions

  November 14, 2007 at 8:37 am

  #119391

  Hi!!

  I've to calculate a Total column in Matrix with a specific formula. Is it possible ?

  [font="Arial"] Col 1|Col 2|Col 3|Col 4|Col 5|Col 6|TOTAL

  Row 1|valvalvalvalval |SUM(val)/5

  Row 2|valval valval|SUM(val)/4

  Row 3|valvalvalvalvalval|SUM(val)/6

  Row 4|val val val |SUM(val)/3

  Row 5|valvalvalvalvalval|SUM(val)/6

  Row 6|valvalvalvalvalval|SUM(val)/6

  Row 7|valvalvalvalvalval|SUM(val)/6

  Row 8|valvalvalvalvalval|SUM(val)/6[/font]

  Note that I've all the datas of the Matrix (Col / Row / val) except the value of the, here names, TOTAL columns that I've to calculate in the Report.

  Thanks a lot.

  Alex

• Rajesh Patavardhan

  SSCrazy

  Points: 2801

  More actions

  November 15, 2007 at 2:08 am

  #751403

  One option is to use the Custom Scripting option.

  Not sure whether this is the best way.

  Following is a sample script to put in the Code section of report

  public function GetAvg(byval ParamArray test())

  dim objVal as object

  dim intCount as integer

  dim decSum as decimal

  decSum = 0

  intCount = 0

  For each objVal in test

  If isnumeric(objval)

  decSum = decSum + objVal

  intCount = intCount + 1

  end if

  next

  if decSum = 0 then

  GetAvg =0

  else

  GetAvg = decSum/intCount

  end if

  end function

  You can call this now from your report by passing all the values

  For example if you have 6 columns in reports and 7th column is Average

  in 7th column =Code.GetAvg(co1,col2,....,col6)

  Hope this helps

• rossetto_ax

  Say Hey Kid

  Points: 666

  More actions

  November 16, 2007 at 1:40 am

  #751733

  Rajesh Patavardhan (11/15/2007)

  ---

  One option is to use the Custom Scripting option.

  Not sure whether this is the best way.

  Following is a sample script to put in the Code section of report

  public function GetAvg(byval ParamArray test())

  dim objVal as object

  dim intCount as integer

  dim decSum as decimal

  decSum = 0

  intCount = 0

  For each objVal in test

  If isnumeric(objval)

  decSum = decSum + objVal

  intCount = intCount + 1

  end if

  next

  if decSum = 0 then

  GetAvg =0

  else

  GetAvg = decSum/intCount

  end if

  end function

  You can call this now from your report by passing all the values

  For example if you have 6 columns in reports and 7th column is Average

  in 7th column =Code.GetAvg(co1,col2,....,col6)

  Hope this helps

  Hi Rajesh!

  thanks a lot. Now I'm going to try... :w00t: It's Ingenious !!!

  I've founded another way by adding in DataSet source a "UNION ALL" with a query to make the calculate avarage columns, but since I don't like the "UNION" in query, I think that your solution should be the best.

  THANKS

  Alex

• Robert O'Byrne

  SSCrazy

  Points: 2017

  More actions

  November 16, 2007 at 6:42 am

  #751808

  Couldn't you just right click on the row header and click summation?

  http://msdn2.microsoft.com/en-us/library/ms251709(VS.80).aspx