Data Report - Custom Field Calculation

[mailzero28]

I have created a report using the data environment.


Col 1 Col2 Col3
Weight Cost Price/kg (ie Weight/Cost)

<------------Sub Total--->

Sum(weight) : Sum(Cost) Sum(weight)/ Sum(Cost)

What I need in Col3 is the answer to Sum(Cost)/Sum(Weight) to get an average per kilo figure - is it possible to do a custom calculation in this instance to create the value that should go into Col3 Sub Total or am I wasting my time. This is real simple Access - Is it possible in VB6?

Thank you in advance for your help.

 

[BlackDice]

I often had that problem in VB, which prompted me to make my own report-writing class, but this doesn't need all that evidently. Anyway, here's what I do when I need some type of sub-report or anything like that.

I had a client record that I needed a subreport of their children for. I put a label on the report instead of a function box, then make a separate recordset that I use to get the calculation and put it in a string variable. Then put the string as the label's caption on the report. The report doesn't even have to be loaded yet, calling on any of the report's controls will load the report itself. Then you just simply say:

rptMyReport.Sections("Details").Controls("lblCalc").caption = strCalc

Luckily my report-writing class has more events than VB's datareport designer, so I can do that for every section of every page. The way I just explained will only work if it's for a single record at a time. If you do it that way, just make sure you also set the report object = nothing before closing your app, otherwise it will still be in memory and your app won't be closed properly.

Hope this helped ! Oh here's a sample of my code if you wanted to see it: (this does everything for the report)


Private Sub cmdPrint_Click()
Dim strCalculation As String
Dim strNames As String
Dim strGenders As String
Dim strDOBs As String
Dim strDOHLs As String

Dim rstClient As ADODB.Recordset
Dim rstChildren As ADODB.Recordset
Const cDBLTAB As String = vbTab & vbTab

Set rstClient = gcnn.ADORecordset( _
gcSELECT & "concat(p.firstname ,' ',p.lastname) as Name, " _
& "concat(a.Address1,' ',a.address2) as address," _
& "concat(a.city,', ',a.state,' ',a.zipcode) as CSZ" _
& gcFROM & gcTBL_CLIENT & " c" _
& gcINNERJOIN & gcTBL_PEOPLE & " p" & gcON & "c.ClientID=p.PeopleID" _
& gcINNERJOIN & gcTBL_ADDRESS & " a" & gcON & "a.AddressID = c.AddressID" _
& gcWHERE & "peopleid=" & glngPeopleID)

If rstClient.EOF Then GoTo ExitProc

Set rstChildren = gcnn.ADORecordset( _
gcSELECT & "concat(p.FirstName,' ',p.LastName) as ChildName, " _
& "c.DOB,g.Gender,d.DegreeOfHL" _
& gcFROM & gcTBL_CHILD & " c" _
& gcINNERJOIN & gcTBL_PEOPLE & " p" & gcON & "c.ChildID=p.PeopleID" _
& gcLEFTJOIN & gcTBL_GENDER & " g" & gcON & "c.GenderID=g.GenderID" _
& gcLEFTJOIN & gcTBL_DEGREEOFHL & " d" & gcON & "c.DegreeOfHLID=d.DegreeOfHLID" _
& gcWHERE & "c.ClientID=" & glngPeopleID)

strChildren = "Firstname" & vbTab & vbTab & "Lastname" & vbTab & vbTab & "DOB" & vbCrLf
With rstChildren
Do While Not .EOF
strNames = strNames & .Fields("ChildName") & vbCrLf
strGenders = strGenders & .Fields("gender") & vbCrLf
strDOBs = strDOBs & .Fields("Dob") & vbCrLf
strDOHLs = strDOHLs & .Fields("Degreeofhl") & vbCrLf
.MoveNext
Loop
End With

With rptClientInfo
With .Sections("details")
.Controls("lblChildName").Caption = strNames
.Controls("lblGender").Caption = strGenders
.Controls("lblDOB").Caption = strDOBs
.Controls("lblDOHL").Caption = strDOHLs
End With
Set .DataSource = rstClient
.Show vbModal, DetermineOwnerFRM
End With
end sub

bdiamond