×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Selecting one Record in Group for Sum

thread149-1801918

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!

RE: Selecting one Record in Group for Sum

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.

RE: Selecting one Record in Group for Sum

(OP)
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

RE: Selecting one Record in Group for Sum

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.)

RE: Selecting one Record in Group for Sum

(OP)
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.

RE: Selecting one Record in Group for Sum

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.

RE: Selecting one Record in Group for Sum

(OP)
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?

RE: Selecting one Record in Group for Sum

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

RE: Selecting one Record in Group for Sum

(OP)
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

RE: Selecting one Record in Group for Sum

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

RE: Selecting one Record in Group for Sum

(OP)
This worked! Thank you all so much for your help.

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close