Alpha Video Training
Results 1 to 7 of 7

Thread: Calculated Fields In Reports

  1. #1
    Mike Atkinson
    Guest

    Default Calculated Fields In Reports

    I have an AR module within a customer data base. I am trying to create an automated bank deposit preparation report. The report automatically grabs deposit items from a payments transaction database (matched by date), but only should grab certain types of payments (Cash, Checks, and Money Order) to print on the report. Payment types ar distinguished by a payment type field in the payments DB, and filtered by an expression on the report (i.e. Payment_Type="MOR".or.Payment_Type="CSH".or.Payment_Type="MOR"). This works fine on the surface, and the report only prints the transactions that match the expression criteria (i.e. the report does not print payments types "BTU", or "PST", or "CRDT")

    The Problem comes with the calculated summary fields on the report. When I try to use calculated fields on the report (to give a transaction count, and total), the calculated fields are including transactions that are filtered out by the report criteria. The report's calculated fields include data from records that do not print on the report itself. So I end up with a page with 3 transactions printed (i.e. the 3 that match the filter criteria for the report), but below that a report calculated field that shows 5 deposit items (i.e. counting 2 that are filtered out on the report), and a total that does not match (again counting records that should be filtered out by the report filter expression).

    Any ideas?

  2. #2
    "Certified" Alphaholic
    Real Name
    Wanda Tucker
    Join Date
    May 2001
    Location
    Northern California
    Posts
    1,444

    Default RE: Calculated Fields In Reports

    Hi Mike,
    Are you using GRAND as your group, or your GROUP group (the date field?) in the count and total? Your expression should be totaling the group, not the grand level.

    I don't at all understand how or why records are being printed on the report that have been filtered out. If they are printing, then they haven't effectively been filtered out.

    Also, try RUNCOUNT(fieldname) for your running count. I haven't worked with it for awhile, so if you run into a snag, post it and I'll find an example.

    Wanda

  3. #3
    "Certified" Alphaholic
    Real Name
    Russ Boehle
    Join Date
    Mar 2000
    Location
    Connecticut
    Posts
    2,767

    Default RE: Calculated Fields In Reports

    You can define a calc field such as
    If(expression=.t.(or whatever), value, O) where expression is the filter. Then do a total on this. You will thereby eliminate the filtered values.

    Russ

  4. #4
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default RE: Calculated Fields In Reports

    And if you modify that expression slightly

    You can define a calc field such as
    If(expression=.t.(or whatever), 1, O)

    and total that calc field, you have a count of filtered items.

  5. #5
    Volunteer Moderator
    Real Name
    James A. Talbott, Jr.
    Join Date
    Apr 2000
    Location
    Lexington, KY
    Posts
    1,470

    Default RE: Calculated Fields In Reports

    Mike,

    As an alternative to the good answers already suggested, you could export only those records you want to a temporary table, and print the report from that table. Just remember to Zap (empty) the table every time you do a new export.

    "Export, sort, and report", says Barry Rochford. He should know, he has been doing it for a number of decades ( I think he started when he was about 5 (grin).

    Jay Talbott

  6. #6
    Mike Atkinson
    Guest

    Default RE: Calculated Fields In Reports

    Thank you everyone for all your excellent feedback. I learned a lot of stuff for future reference, and ultimately came to the conclusion that I was going about the report the wrong way. I was trying to merge 3 different sub-reports (from each transaction database) into a single deposit summary report. I was far better off using groups. Used the grouping concept, the totals started working perfectly. I am still convinced there is a solution to do it the other way, but I was making it too complicated for myself. Anyway, great feedback. I have a solution that is working now. Thank you all so much for your tips.

  7. #7
    "Certified" Alphaholic
    Real Name
    Wanda Tucker
    Join Date
    May 2001
    Location
    Northern California
    Posts
    1,444

    Default RE: Calculated Fields In Reports

    Hi Mike,
    Glad you found a solution. Thanks for letting us know.
    Wanda

Similar Threads

  1. Suppress calculated field in reports
    By Paul Main in forum Alpha Five Version 6
    Replies: 7
    Last Post: 03-16-2005, 01:28 PM
  2. Form Calculated fields in Reports
    By Paul Main in forum Alpha Five Version 6
    Replies: 5
    Last Post: 03-07-2005, 02:50 PM
  3. Calculated Fields vs. Calculated Field Rule
    By Rhett Scott in forum Alpha Five Version 4
    Replies: 12
    Last Post: 07-07-2002, 05:58 AM
  4. Storing calculated fields in DB fields
    By Torrence Jones in forum Alpha Five Versions 2 and 3
    Replies: 6
    Last Post: 11-11-2000, 08:34 AM
  5. Calculated fields & Reports
    By Lloyd Frank in forum Alpha Five Version 4
    Replies: 4
    Last Post: 07-27-2000, 05:48 AM

Bookmarks

Posting Permissions

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