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

group subtotals to summary

edited May 2004 in Subreports
Hi,

I need to implement the following calculations. In my detail band I have a
group which has some subtotals. In the summary band I need to display the
sum of the subtotals. Moreover, some variables in the summary based are
based on this sum. Is it possible to calculate the sum using ReportBuilder
features or do I have to use queries?

TIA

Comments

  • edited May 2004

    Below are 2 articles that address this question. (note: These articles are
    from the Calculations thread of the Tech Tips newsgroup).

    ------------------------------------------------------
    Tech Tip: Calculating a Summary based on Group Totals
    ------------------------------------------------------

    Question: I am using a Variable in the Group Footer to conditionally
    calculate totals for each group. I want to add a second Variable to the
    Summary that accumulates the results calculated for each group.

    The following example uses two Variable components: vCustomerTotal and
    vCustomerSummary. The variable vCustomerTotal has its Timing defined to
    Reset on GroupEnd.


    There are two options for accumulating the summary total.

    1. Use the vCustomerTotal OnCalc event to accumulate the detail and the
    summary.

    example:

    procedure vCustomerTotalOnCalc(Sender: TObject; var Value: Variant);
    begin

    if (plCustomer['Amountpaid'] > 100.00) then
    begin
    {sum detail}
    Value := Value + plCustomer['Amountpaid'];

    {accumulate summary}
    vCustomerSummary.Value := vCustomerSummary.Value +
    plCustomer['Amountpaid'];
    end;

    end;


    2. Set the timing of the Summary OnCalc to "GroupBeforeFooter" and
    accumulate the detail variable results. Note that here we use
    GroupBeforeFooter because the detail variable has its Reset defined as
    "GroupEnd" - implying that if we try to use GroupEnd for our summary
    calculation the accumulated results will be 0.

    example:

    procedure vCustomerTotalOnCalc(Sender: TObject; var Value: Variant);
    begin

    {sum detail}
    if plCustomer['Amountpaid'] > 100.00 then
    Value := Value + plCustomer['Amountpaid'];

    end;


    procedure vCustomerSummaryOnCalc(Sender: TObject; var Value: Variant);
    begin

    {accumulate summary}
    Value := Value + vCustomerTotal.Value;

    end;



    ------------------------------------------------------------------------
    TECH TIP: Performing Calculations
    ------------------------------------------------------------------------

    Calculations can be done either on the data access side
    or within ReportBuilder.

    When designing reports there are always decisions to be made as to
    how much processing to do on the data side versus the report side.
    Usually doing more on one side can greatly simplify the other. So it is
    often a personal choice based on the power and flexibility of the data
    and report tools being used.


    DataAccess
    ----------

    a. Use SQL - using SQL you can perform many common calculations:

    example: Select FirstName + ' ' + LastName As FullName,
    Quantity * Price AS Cost,


    b. Delphi TDataSets enable you to create a calculated TField object
    and use the DataSet.OnCalcFields event

    c. Perform any amount of data processing, summarizing, massaging
    etc. to build a result set (query or temp table) to feed to the report.


    ReportBuilder
    -------------

    Calculations in ReportBuilder are performed primarily using
    the TppVariable component.

    a. Set the Variable.DataType

    b. Code the calculations using the Variable.OnCalc event.

    c. Use the Timing dialog to control the timing of the OnCalc event.
    To access the Timing dialog, right click over the Variable
    component and select the Timing... option from the speed menu.

    d. Set the LookAhead property to True, when you need to display
    summary calculations in the title, header, group header, etc.

    e. To perform calculations based on the results of other
    calculations use the Calc Order dialog of the band. To access
    the Calc Order dialog, right click over the Band component
    and select the Calc Order... option from the speed menu.


    By using TppVariable components ReportBuilder will take care of caching
    intermediate results of accumlated calcs that cross pages.

    There are a number of calculation examples in the main demos.dpr
    project.

    ---

    Additional Notes:

    1. Do NOT use Band.BeforePrint or Band.AfterPrint. These events fire
    multiple times and therefore should not be used for calculations.

    2. Do NOT store results in variables that exist outside of the reports.
    For example - form level variables.



    --
    Tech Support mailto:support@digital-metaphors.com
    Digital Metaphors http://www.digital-metaphors.com




    --
    Nard Moseley
    Digital Metaphors Corporation
    www.digital-metaphors.com


    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
This discussion has been closed.