Unanswered: Reports: Group totals when using a user defined function
I'm working on a complicated report.
The details rows of the report show the yield of different crops for a farmer for a season eg column 1 shows cotton, column 2 shows maize etc.
The value for each crop comes from a function. I had to put it in a function because the query itslef to get the yield is quite complicated, not just a straightforward query.
All the farmers are grouped into co-operatives.
On teh report I have my grouping set up OK, but I cant get the summary totals for each crop in the group. The totals for the acreage appears OK because acreage is just a field in the query. The totals dont appear for each crop because those values are coming from a function.
I've tried naming the fields eg txtCottonYield, and then in the group footer putting =SUM[txtCottonYield], but when I run the report the dialog pops up asking me to enter the value for txtCottonYield as if its a parameter.
I assume that the user defined function has arguments that reference the field in the report records detail rows.
If this is the case, then I suggest that you put the User Defined Function in the report’s underlying query as a field source, with the fields as arguments as before. You will then be able to use the sum([CottonYield]) function in the group footer as the values are part of the report query and not calculated on the report.
ie. SELECT UserDefinedFunctionName( [CottonField1Name], …..) AS CottonYield, UserDefinedFunctionName( [MaizeField1Name], …..) AS MaizeYield, …..
My query is now rather large though, and I know as soon as someone else gets there hands on it they'll tinker with it and break it. Is there definetly no way to use a SUM function on a field that uses as user defined function?