November 14, 2007 at 8:37 am
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| val val val val val |SUM(val)/5
Row 2| val val val val |SUM(val)/4
Row 3| val val val val val val |SUM(val)/6
Row 4| val val val |SUM(val)/3
Row 5| val val val val val val |SUM(val)/6
Row 6| val val val val val val |SUM(val)/6
Row 7| val val val val val val |SUM(val)/6
Row 8| val val val val val val |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
November 15, 2007 at 2:08 am
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
November 16, 2007 at 1:40 am
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
November 16, 2007 at 6:42 am
Couldn't you just right click on the row header and click summation?
http://msdn2.microsoft.com/en-us/library/ms251709(VS.80).aspx
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply