Home End User
New Blog Posts: Merging Reports - Part 1 and Part 2

Multi-page report with page totals

edited January 2003 in End User
rb6.03 Enterprise
InterbaseServer 6.05 (I think)

Report:

A customer listing with $ amount per record and sorted A-Z on last name.

There are duplicate names with differing $ amounts.

I am having problems counting per page: Total $ and Total Customers
(unduplicated).

Any assistance/suggestions are greatly appreciated.

Comments

  • edited January 2003
    In your query do you need to use the SQL keyword DISTINCT? Do you need the
    duplicate names? I would create a calculated field on your dataset for first
    name + last name. Order by this calculated field. Place the total
    calculations in the page footer band.


    Cheers,

    Jim Bennett
    Digital Metaphors


  • edited January 2003
    No I am not using DISTINCT. Yes, I need the duplicate names but only a count
    of each name once.
    I did create the calculated field on last name + first name + middle initial
    and ordered by that field - but I am still stuck on the single count of the
    name.

  • edited January 2003
    No, I am not using DISTINCT. Yes, I do need the duplicate names. I did
    create a calculated field, last name + first name + mi and ordered on that
    field.
    But I still need a count of the names that does not include the duplicates.

    Thanks, Veronica

  • edited January 2003
    You can build a list as the report generates the first pass (of a two pass
    report) before any pages are generated. Use a TStringlist object to store
    the list of values and then check its count property. Here is an example:
    http://www.digital-metaphors.com/tips/CountDistinct.zip

    You could also use a one pass report and use a TppVariable and its OnCalc
    event handler to check the previous datapipeline value, and increment the
    count stored in the value of a TppVariable. You'll want to check the
    Datapipeline.State to see if ppdaFirstRecord is in the set to determine if
    you are on teh first record. There are two methods on the pipeline Prior
    and Next which you can use to move back one record, check is value and then
    move forward again. Or you can use another TppVariable to store the previous
    record value as the report traverses normally so you won't have to move
    around in the dataset at all. Make sure to set the CalcOrder of the
    TppVariables so that the variable which stores the previous value in the
    piepline calculates last in the same band as the other variable.


    Cheers,

    Jim Bennett
    Digital Metaphors


This discussion has been closed.