Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Running total from a subreport only showing last value

Status
Not open for further replies.

doodledump

Programmer
Sep 4, 2024
4
So I've got a report that tracks employee currency based on position. One position uses a different method (Think tracking hours VS tracking an activity) to record their currency and I've moved the calculations for that position to a subreport. I need the subreport to return a running total of how many times an action was performed per person. This running total is working fine in the subreport, but when I pass it back to the main report it only passes the last value back.

For Example:
Person1: Action1
Person1: Action2
Person1: Action3
Running Total = 3

Person2: Action1
Running Total = 1

Only has Running Total returning 1 six times (I suspect because there are six "rows"). Any help is appreciated!

Extra Info: I'm using visual studio 2013 with Crystal Reports plugin version 13.0.13. These are my shared variable formulas:
// @MainFormula
WhilePrintingRecords;
Shared NumberVar Count;
Count

// @SubFormula
WhilePrintingRecords;
Shared NumberVar Count := {#RTotalCount}

The main reports layout is: GroupHeader(Date) - Subreport, Details - @MainFormula, GroupFooter(Date) - Main Crosstab
and the subreports layout is: ReportHeader - Column Names, Details - subreport details, GroupFooter(Person) @SubFormula
 

Step-by-Step Solution:​

  1. Modify the Shared Variable Calculation in the Subreport:Ensure that the shared variable is accumulating correctly within the subreport, not just updating for each record. You need to reset the variable correctly for each person and accumulate the values as you process records.
    Update your @SubFormula in the subreport as follows:

(Crystal)
WhilePrintingRecords;
Shared NumberVar Count;
Count := Count + {#RTotalCount}; // Accumulate the count

Make sure you reset Count at the beginning of each person group:

In the Group Header (or wherever it makes sense to reset for each person):
(Crystal)
WhilePrintingRecords;
Shared NumberVar Count := 0; // Reset at the start of each person

  • Pass the Accumulated Value to the Main Report:Ensure that the shared variable is placed in the correct section of the subreport to ensure it carries the accumulated value when passed back. This should typically be in the Group Footer section where it sums up the values for the group.
  • Modify the Main Report to Accumulate Values:In the main report, you also need to accumulate the values passed back from the subreport rather than directly displaying them. Here’s how:
    In the main report's @MainFormula, change it to accumulate:
(Crystal)
WhilePrintingRecords;
Shared NumberVar TotalCount; // A separate variable for accumulating in the main report
Shared NumberVar Count; // The variable from the subreport

TotalCount := TotalCount + Count; // Accumulate the count from subreport
Count; // Display the latest count from the subreport

Reset the Main Report’s Accumulation:To prevent accumulation across unintended records, reset TotalCount at appropriate locations (e.g., at the start of each Group Header for a new date):

(Crystal)
WhilePrintingRecords;
Shared NumberVar TotalCount := 0; // Reset for each new date or group

  1. Ensure Proper Placement of Formulas:
    • Place the reset formulas (Shared NumberVar Count := 0;) in appropriate group headers in both subreport and main report.
    • The formulas that accumulate should be in group footers or sections where you want the cumulative values.

Explanation:​

  • Shared Variables: They maintain values between subreports and main reports, but their scope is sensitive to the report's execution flow.
  • Accumulation vs. Replacement: By accumulating the shared variable value in the subreport and then continuing accumulation in the main report, you ensure that each value is properly accounted for.
  • Resetting Values: It’s crucial to reset shared variables at the right time to prevent incorrect carryover of values across unrelated records.
By ensuring that values are correctly accumulated in the subreport and then again in the main report, you should get the expected running total displayed correctly for each person without the last value overwriting the others.
 
You cannot return a per-record or per-group result from a subreport. You can only return the final value of a calculation.

-Dell
 
In your main report, assuming you have groups, do you repeat the group header at the top of each page?
 
Hey guys, thanks for the replies! As far as I can tell, hilfy is correct that subreports can't send back values that change mid report (like a running total)

For those curious, its because crystal reports executes its logic top to bottom. The subreport will complete all calculations in its section before moving on to the next, thus only passing the last assigned value out when its done

I ended up needing to make database changes to get the results I needed, but doctorlee's explination was helpful for those looking to get a running total across groups
(AKA the example from my first post would pass back 4 as a total of actions across every person)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top