In the report Editor follow the following steps:
- Before you click on any fields or bands in the report, go to the property grid and find Script Language, change this to Visual Basic if not already set.
- Click on the total field that you want to calculate a weighted average total.
- Select the blue box in the corner, and select the summary ellipses.
- In the Summary Editor, change summary function to Custom and change the summary running to Group or Report as you normally would with calculating a total.
- Select Scripts from the black bar under the main report
Copy the following script into the script editor
'Percent HD Production Current PD
Dim CurEgg as Double
Dim CurEggHD As Double
Private Sub label37_SummaryReset(ByVal sender As Object, ByVal e As System.EventArgs)
CurEgg = 0
CurEggHD = 0
End SubPrivate Sub label37_SummaryRowChanged(ByVal sender As Object, ByVal e As System.EventArgs)
CurEgg += GetCurrentColumnValue("Egg_PctHDProd_PD") * GetCurrentColumnValue("HouseCount_EndCount_PD")
CurEggHD += GetCurrentColumnValue("HouseCount_EndCount_PD")
End SubPrivate Sub label37_SummaryGetResult(ByVal sender As Object, ByVal e As DevExpress.XtraReports.UI.SummaryGetResultEventArgs)
e.Result = CurEgg / CurEggHD
e.Handled = True
End Sub
- You will need to make a couple changes to the script.
- You will need to change the red highlighted label name in the script. Match this to what the total field label name is:
'Percent HD Production Current PD
Dim CurEgg as Double
Dim CurEggHD As Double
Private Sub label37_SummaryRowChanged(ByVal sender As Object, ByVal e As System.EventArgs)
CurEgg += GetCurrentColumnValue("Egg_PctHDProd_PD") * GetCurrentColumnValue("HouseCount_EndCount_PD")
CurEggHD += GetCurrentColumnValue("HouseCount_EndCount_PD")
End SubPrivate Sub label37_SummaryGetResult(ByVal sender As Object, ByVal e As DevExpress.XtraReports.UI.SummaryGetResultEventArgs)
e.Result = CurEgg / CurEggHD
e.Handled = True
End Sub
Private Sub label37_SummaryReset(ByVal sender As Object, ByVal e As System.EventArgs)
CurEgg = 0
CurEggHD = 0
End Sub - You will then need to change the fields used in the script, get the exact same field names from the field list. (This calculation says to multiply Rate of Lay Percent by End bird Count and then divide that by End Bird Count.
'Percent HD Production Current PD
Dim CurEgg as Double
Dim CurEggHD As Double
Private Sub label37_SummaryRowChanged(ByVal sender As Object, ByVal e As System.EventArgs)
CurEgg += GetCurrentColumnValue("Egg_PctHDProd_PD") * GetCurrentColumnValue("HouseCount_EndCount_PD")
CurEggHD += GetCurrentColumnValue("HouseCount_EndCount_PD")
End SubPrivate Sub label37_SummaryGetResult(ByVal sender As Object, ByVal e As DevExpress.XtraReports.UI.SummaryGetResultEventArgs)
e.Result = CurEgg / CurEggHD
e.Handled = True
End Sub
Private Sub label37_SummaryReset(ByVal sender As Object, ByVal e As System.EventArgs)
CurEgg = 0
CurEggHD = 0
End Sub - The last thing in the script that will need to be modified is the names we are giving the calcs. These can not be the same between different scripts so you need to make up a name for each.
'Percent HD Production Current PD
Dim CurEgg as Double
Dim CurEggHD As Double
Private Sub label37_SummaryRowChanged(ByVal sender As Object, ByVal e As System.EventArgs)
CurEgg += GetCurrentColumnValue("Egg_PctHDProd_PD") * GetCurrentColumnValue("HouseCount_EndCount_PD")
CurEggHD += GetCurrentColumnValue("HouseCount_EndCount_PD")
End SubPrivate Sub label37_SummaryGetResult(ByVal sender As Object, ByVal e As DevExpress.XtraReports.UI.SummaryGetResultEventArgs)
e.Result = CurEgg / CurEggHD
e.Handled = True
End Sub
Private Sub label37_SummaryReset(ByVal sender As Object, ByVal e As System.EventArgs)
CurEgg = 0
CurEggHD = 0
End Sub - Validate that there are no errors in your scripts
- Now that we are done with the script, we will go back to the total field. While your field is selected go to the property grid and find Scripts and expand it.
- In Summary Get Result, Summary Reset, and Summary Row changed find the corresponding script that matches your label name.
- In Summary Get Result, Summary Reset, and Summary Row changed find the corresponding script that matches your label name.
- Make sure to save your report. Run and check your results.
Script Samples
Here are some other weighted average script samples:
Eggs/HH Total Period
'Eggs/HH Total Period
Dim LtdEgg as Double
Dim LtdHh as Double
Private Sub label77_SummaryReset(ByVal sender As Object, ByVal e As System.EventArgs)
LtdEgg = 0
LtdHh = 0
End Sub
Private Sub label77_SummaryRowChanged(ByVal sender As Object, ByVal e As System.EventArgs)
LtdEgg += GetCurrentColumnValue("Egg_Eggs_HH_LTD") * GetCurrentColumnValue("HouseCount_HeadAdded_LTD")
LtdHh += GetCurrentColumnValue("HouseCount_HeadAdded_LTD")
End Sub
Private Sub label77_SummaryGetResult(ByVal sender As Object, ByVal e As DevExpress.XtraReports.UI.SummaryGetResultEventArgs)
If LtdHh = 0 then exit sub
e.Result = LtdEgg / LtdHh
e.Handled = True
End Sub
Eggs/HH Total Period Standard
'Eggs/HH STD
Dim StdEhh as Double
Dim StdEhhHD as Double
Private Sub label78_SummaryReset(ByVal sender As Object, ByVal e As System.EventArgs)
StdEhh = 0
StdEhhHD = 0
End Sub
Private Sub label78_SummaryRowChanged(ByVal sender As Object, ByVal e As System.EventArgs)
StdEhh += GetCurrentColumnValue("HouseCount_HeadAdded_LTD") * GetCurrentColumnValue("Egg_Eggs_HHStd_LTD")
StdEhhHD += GetCurrentColumnValue("HouseCount_HeadAdded_LTD")
End Sub
Private Sub label78_SummaryGetResult(ByVal sender As Object, ByVal e As DevExpress.XtraReports.UI.SummaryGetResultEventArgs)
e.Result = (StdEhh / StdEhhHD)
e.Handled = True
End Sub
Case Weight Cur
Dim label107case as Double
Dim label107dz as Double
'Case Weight Cur
Private Sub label107_SummaryReset(ByVal sender As Object, ByVal e As System.EventArgs)
label107case = 0.0
label107dz = 0.0
End Sub
private label107case as double = 0
private label107dz as double = 0
Private Sub label107_SummaryRowChanged(ByVal sender As Object, ByVal e As System.EventArgs)
Dim caseWt as double = GetCurrentColumnValue("EggWt_AvgCsWtLbs_PD")
Dim dz as double = GetCurrentColumnValue("Egg_DzProduced_PD")
If dz = 0 Then Exit Sub
label107case += caseWt * dz
label107dz += dz
End Sub
Private Sub label107_SummaryGetResult(ByVal sender As Object, ByVal e As DevExpress.XtraReports.UI.SummaryGetResultEventArgs)
If label107dz = 0 then exit sub
e.Result = label107case / label107dz
e.handled = True
End Sub