In Crystal Reports XI, I'm trying to sum within a sub group, I've grouped by two fields.
Is normally sum by doing something like:
Sum({@Formula_Field},{Group_1_field})
But I've realised this returns all of the formula field within the first grouping. I was wondering if there was a way to sum only then values within the subgroup of group 2 within each of the group 1 groups. I need to have a calculated value that is it's own field for each subgroup which is the same for each line of the subgroup once calculated.
Column_1 Column_2 Column_3
Grouping_1
Grouping_2
Row_1 10 20 30
Row_2 10 10 10
Grouping_2
Row_3 5 5 5
Grouping_1
Grouping_2
Row_4 8 8 9
Grouping_1
Grouping_2
Row_5 6 7 6
Using my formula above for the first grouping it return depending on which column was your formula field:
| Row | Column_1 | Column_2 | Column_3 |
|---|---|---|---|
| 1 | 25 | 35 | 45 |
| 2 | 25 | 35 | 45 |
| 3 | 25 | 35 | 45 |
| 4 | 8 | 8 | 9 |
| 5 | 6 | 7 | 6 |
Ideally I want to know how to sum only the rows in the second grouping something like:
Sum({@Formula_Field},({Group_1_field}&{Group_2_field}))
Although I know that code is nonsense just to hopefully illustrate what I'm trying to achieve, the outcome when it works would give:
| Row | Column_1 | Column_2 | Column_3 |
|---|---|---|---|
| 1 | 20 | 30 | 40 |
| 2 | 20 | 30 | 40 |
| 3 | 5 | 5 | 5 |
| 4 | 8 | 8 | 9 |
| 5 | 6 | 7 | 6 |
The difference being between row 1/2 & 3 where there is a different subgroup between the rows meaning the summing happens across only the first two rows and then separately for row 3 the new sub group.
Thank you for any advice!
// Place this in Details (or Group 2 sections if you prefer)
Sum({@Formula_Field}, {Group_2_field})
This will give you the same value on each row within the current Group 2
Row Column_1 Column_2 Column_3
1 20 30 40
2 20 30 40
3 5 5 5
4 8 8 9
5 6 7 6
You have to keep in mind that {Group_2_field} is the actual field you used to define Group 2 (not a formula unless the group was created on that formula). If {@Formula_Field} itself depends on print-time values, this still works, but if you see evaluation-order issues, see below:
Reset the subgroup sum in Group2 Header by creating a formula called @SubSum_Reset and place it in Group 2 Header:
WhilePrintingRecords;
Global NumberVar SubSum := 0;
Create a formula as@SubSum_Add and place it in Details:
WhilePrintingRecords;
Global NumberVar SubSum;
SubSum := SubSum + {@Formula_Field};
Create a formula called @SubSum_Finalize and place it in Group 2 Footer:
WhilePrintingRecords;
Global NumberVar SubSum;
Global NumberVar SubSumFinal := SubSum;
Finally you can get that in @Subgroup_Total_Display and place it in Details where you need the repeated value
WhilePrintingRecords;
Global NumberVar SubSumFinal;
SubSumFinal;
This should resolve your issue as it guarantees the same (final) total on every line of the subgroup, even for print-time formulas.
Hope it helps