Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Location
    Kampala, Uganda
    Posts
    32

    Question 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.

    How do I get the group total?

    Thanks

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi village idiot (surly not!)

    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, …..

    Hope that makes sense…



    MTB

  3. #3
    Join Date
    Apr 2004
    Location
    Kampala, Uganda
    Posts
    32
    Thanks Mike, that works.

    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?

    What sort of bike have you got?

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi Again

    Sorry about the delay, hvet been a little busy.

    I have not, as yet, found a method of using aggregate function tor SUM. AVE, MAX etc. calulated controls (not bound to a form/report underying query), but it hasn't been for the want of trying!

    Has anyone else found a method ??


    Sadly I have not owned a bike since 1973, but I havn't given up hope! The last one was a Triumph Trophy TR6, and before that a Goldstar DBD34 in full clubmans trim (wish I had it now!!).

    MTB

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •