Selecting one Record in Group for Sum

nerdygirlgear · Mar 31, 2020

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!

kray4660 · Mar 31, 2020

First off you need to provide more information. What are you really counting (it is not real clear)? You probably need multiple running totals (or formulas), one for each total. You did not provide what criteria is needed for each running total.

nerdygirlgear · Mar 31, 2020

Hi kray4660,
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

kray4660 · Mar 31, 2020

Since it appears that you are doing a grand total, I do not think groups matter. So I would set up each running total to only evaluate on MSO Row = 1 (use the actual field name) and have a second filter be for the class (i.e, class = 'F', class = 'M', etc.)

nerdygirlgear · Mar 31, 2020

Hi Kray4660, It appears I am having a hard time with this because if I were doing this in SQL this would essentially query this recordset and select all records where MSORow =1 by Class. I will admit I know my way around SQL better than Crystal. Now, having tried this recommendation, I believe I did not execute it properly because I am getting an error message: A running total cannot refer to a print time formula Details: @CountMSORow1

Thanks again for your help on this problem.

kray4660 · Mar 31, 2020

Of course the other way (if you want to avoid the running totals in Crystal. Make another query (not linked) or make a sub-report to show the values (I am assuming your totals are for the report footer). Since you are familiar with SQL, this should be an easy solution.

nerdygirlgear · Mar 31, 2020

Unfortunately, I have to keep it in Crystal because this is eventually will get embedded into another report as a subreport. So, back to the grind. So, here is where I am stuck: set up each running total to only evaluate on MSO Row = 1 (use the actual field name) and have a second filter be for the class (i.e, class = 'F', class = 'M', etc.)

When I tried this, I got the previous error message. Am I using the wrong approach for this problem?

lbass · Mar 31, 2020

You need to show the content of the 3 formulas you are using to designate the MSO fields (and of any nested formulas within these).

-LB

nerdygirlgear · Apr 1, 2020

The 3 are:
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

lbass · Apr 1, 2020

You don’t need any of your MSO formulas. As long as you are grouping on client, and sorting in ascending order by level, you can just insert a running for each class and set them up like this:

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

nerdygirlgear · Apr 1, 2020

[highlight #FCE94F]This worked! Thank you all so much for your help.[/highlight]

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

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!

Selecting one Record in Group for Sum

nerdygirlgear

Programmer

kray4660

MIS

nerdygirlgear

Programmer

kray4660

MIS

nerdygirlgear

Programmer

kray4660

MIS

nerdygirlgear

Programmer

lbass

Technical User

nerdygirlgear

Programmer

lbass

Technical User

nerdygirlgear

Programmer

Similar threads

Part and Inventory Search

Sponsor