Crystal Reports subtotalling with variables

SOLVED

Crystal Reports 2016 Support Pack 2

I am using variables to do subtotals of values carried forward from a subreport.  I have set up the following formulas:

Initiation formula:

BDG START  - whileprintingrecords; currencyvar bdgfrpd; bdgfrpd := 0
FASSOU START  - whileprintingrecords; currencyvar FASSOUFRPD; FASSOUFRPD := 0

These have been put into the Group Header section and are suppressed.

Subtotaling formula:

BDG ST -  whileprintingrecords; currencyvar BDGFRPD; BDGFRPD := BDGFRPD + {@Sales BDG Paid Freight}
FASSOU ST -  whileprintingrecords; currencyvar FASSOUFRPD; FASSOUFRPD := FASSOUFRPD + {@Sales Fassou Paid Freight}

These have been put into the Detail section and are suppressed.

Display of the subtotal formula:

BDG ST DISP  -  whileprintingrecords; currencyvar BDGFRPD; BDGFRPD
FASSOU ST DISP -  whileprintingrecords; currencyvar FASSOUFRPD; FASSOUFRPD

These have been put into the Group Footer section and are NOT suppressed.

The subtotals being produced are not correct.  In some cases, the last detail lines's value within the group is being added in twice.  In other cases, the subtotal is correct but the other subtotal is including the value from the other subtotal.

I also have formulas to product a grand total set up similarly to these formula for subtotaling and the grand total formula are working fine.

  • 0

    What report are the initialization formulas in?  If it is in the sub-report, it is being suppressed at any point causing your initialization not to happen? Also, sometimes I create sub-sections to control the order in which sub-reports and formulas are processed.

  • 0 in reply to Django

    All of the formulas are in the main report.  The sub-report is used to capture a field from another table and pass it up to the main report.  The sub-report is in section 'Details a' of the main report and the formulas that use the passed up variable along with the formulas that do the totaling are in the section 'Details b' of the main report.  The initializing formulas are in the 'group header #1' section of the main report and the display formulas are in the 'group footer #1' section.

  • 0 in reply to MikeBurch

    Maybe reset the variables in the same formula where you're accumulating them so if that formula is run twice (or the section is being evaluated twice) the second pass will have no affect on the totals again.  That might require an adjustment to when you're incrementing your sub/grand totals but you can do that all within the one formula anyway.

  • 0 in reply to Django

    Do you mean I should put the same syntax used in the initialization formula into the totaling formula?  If so, do I put it at the start or end of the totaling formula?  Or am I misinterpreting what you are saying?

  • 0

    I normally define the variable to be shared across the sub report boundary as "shared"

    In the group on main report to declare and reset value. BTW you can put these in any formula in that group and even in section suppression.

    shared numbervar totalVal:=0;

    In the subreport:

    shared numbervar totalVal;

    totalVal:=detailAmt + otherAmt;

    >>>

    Shared variables are used throughout the main report and all of its sub reportsShared variables are even more general than global variables. ... To use shared variables, it must be declared and assigned a value before it can be used in the main report and subreports.

  • 0 in reply to MikeBurch
    SUGGESTED

    Something like this which will be processed after your sub-report:

    whileprintingrecords;

    shared numberVar ValueToSum;

    shared numberVar GroupSum;

    shared numberVar GrandSum;

    GroupSum := GroupSum + ValueToSum;

    GrandSum := GrandSum + ValueToSum;

    local numberVar TheValue;

    TheValue := ValueToSum;

    ValueToSum := 0;

    TheValue;

    That's a long way of attempting to solve the issue.  So this way if your accumulation formula, above, is somehow run multiple times, it doesn't matter because the "ValueToSum" shared variable is reset by the formula once it is done with it. So you can run this over and over again with no ill effects.

  • +1 in reply to Django
    verified answer

    Thanks Django.  But it seems that the report is now working without your suggestion.  When I could not get the report to work last week I suppressed the group footer section where the 'display' formula was printing.  I went back to this report this morning with the intention of putting the changes in you suggested but when I unsuppressed this section, did NOT make the changes you suggested and ran the report the doubling of the last records value no longer happened.  I ran the report for various months and the subtotals are now showing correctly each time.  I will keep this suggestion for the totaling formula handy though in case I run into this issue again.