Selecting one Record in Group for Sum
Selecting one Record in Group for Sum
(OP)
Hi All,
I am working in Crystal Reports and am having a hard time getting the sum of a specific record in each group.
I am basically getting the min level within each group and having that counted towards the grand total.
Basically only counting the most important record and summing that for the full recordset.
I tried formulas and running totals but they don't get me to the result I am after.
As an example;
I would like the summary to be as follows:
F 6
LO 0
M 2
NC 0
Grand Total 8
Client Level Class MSORow fxMSO2 MSODesc
6714 1 F 1 1 1.00
6714 2 M 2 1 0.00
6714 2 M 3 1 0.00
12641 1 F 1 1 1.00
12641 1 F 2 1 0.00
12641 2 M 3 1 0.00
21079 1 F 1 1 1.00
21079 2 M 2 1 0.00
21079 5 LO 3 1 0.00
24734 1 F 1 1 1.00
25346 1 F 1 1 1.00
26437 2 M 1 2 1.00
26437 2 M 2 2 0.00
29039 2 M 1 2 1.00
29039 4 NC 2 2 0.00
29039 4 NC 3 2 0.00
29039 4 NC 4 2 0.00
29039 4 NC 5 2 0.00
29218 1 F 1 1 1.00
29218 1 F 2 1 0.00
29218 2 M 3 1 0.00
29218 2 M 4 1 0.00
29218 2 M 5 1 0.00
Any help is appreciated.
Thanks!
I am working in Crystal Reports and am having a hard time getting the sum of a specific record in each group.
I am basically getting the min level within each group and having that counted towards the grand total.
Basically only counting the most important record and summing that for the full recordset.
I tried formulas and running totals but they don't get me to the result I am after.
As an example;
I would like the summary to be as follows:
F 6
LO 0
M 2
NC 0
Grand Total 8
Client Level Class MSORow fxMSO2 MSODesc
6714 1 F 1 1 1.00
6714 2 M 2 1 0.00
6714 2 M 3 1 0.00
12641 1 F 1 1 1.00
12641 1 F 2 1 0.00
12641 2 M 3 1 0.00
21079 1 F 1 1 1.00
21079 2 M 2 1 0.00
21079 5 LO 3 1 0.00
24734 1 F 1 1 1.00
25346 1 F 1 1 1.00
26437 2 M 1 2 1.00
26437 2 M 2 2 0.00
29039 2 M 1 2 1.00
29039 4 NC 2 2 0.00
29039 4 NC 3 2 0.00
29039 4 NC 4 2 0.00
29039 4 NC 5 2 0.00
29218 1 F 1 1 1.00
29218 1 F 2 1 0.00
29218 2 M 3 1 0.00
29218 2 M 4 1 0.00
29218 2 M 5 1 0.00
Any help is appreciated.
Thanks!
RE: Selecting one Record in Group for Sum
RE: Selecting one Record in Group for Sum
Thanks for the clarifying question.
The goal is to add the record in each group that is marked as MSO Row = 1.
MSORow, fxMSO2, and MSODesc are all formulas I created to accomplish this task but I seem to be getting stuck at only summing the row marked as MSO=1 from each group.
Client Level Class MSORow fxMSO2 MSODesc
29218 1 F 1 1 1.00
29218 1 F 2 1 0.00
29218 2 M 3 1 0.00
29218 2 M 4 1 0.00
29218 2 M 5 1 0.00
29039 2 M 1 2 1.00
29039 4 NC 2 2 0.00
29039 4 NC 3 2 0.00
29039 4 NC 4 2 0.00
29039 4 NC 5 2 0.00
RE: Selecting one Record in Group for Sum
RE: Selecting one Record in Group for Sum
Thanks again for your help on this problem.
RE: Selecting one Record in Group for Sum
RE: Selecting one Record in Group for Sum
When I tried this, I got the previous error message. Am I using the wrong approach for this problem?
RE: Selecting one Record in Group for Sum
-LB
RE: Selecting one Record in Group for Sum
MSORow: Running total, tboff.class, type of summary is count, evaluate for each record on change of field tbcl.id
fxMSO2: formula, minimum({tboff.level},{tbcl.id})
fxMSODesc: formula, if {#MSORow} = 1 then 1 else 0
RE: Selecting one Record in Group for Sum
Summary field: Level
Summary: Sum
Evaluate: Use a formula:
(
Onfirstrecord or
{tbcl.id}<>previous({tbcl.id})
) and
{table.class}=“F”
Change the class value for each of the four classes.
Reset: Never
For the grand total, you could either use an RT that omits the last formula line, or you could create a new formula that adds the RTs together. All results must be in the report footer. Be sure to enter your own quote marks (don’t copy my formula), as my iPad curly quotes will cause a formula to fail.
-LB
RE: Selecting one Record in Group for Sum
Here's how I set it up.
I created a total of 8 Running Total fields
Each (1-8) was as follows:
I named the field SummLevel1
Field to Summarize: Level (in this case I used the numeric field)
Summary: Count
Evaluate: Use a Formula ( Onfirstrecord or {tbcl.id}<>previous({tbcl.id})) and {table.class}=“1”
Once I had one for each level I brought them into the group header and also to the report footer stacked up in one column.
I then added labels for each level description and stacked those up and lined them up accordingly.
That created a list similar to what I wanted as an end result.
F 6
LO 0
M 2
NC 0
I left out the grand total because I did not need, just because this is a subreport.
Again, many thanks to all!
N