Grouping By Top Value

[DFeinB]

Hello!

First time poster, so I apologize for any errors in advance.

I am having some difficulty displaying only the top value within a group.

I currently have the report formatted as such:

Group # 1: (Scores)
Details (User ID) (Name) (Scores)

Group # 1 has a specified order of:
0 -25
26 - 50
51 - 75
76 - 100

Each user takes a test and has the ability to take the test more than once.
I am attempting to only display the top score for each user and then group the scores.

The problem is a person can be in multiple groups, for example if they score a 20 and a 55. I only want to show that person's 55 and disregard the 20.

I am running Crystal Reports Ver. 11

Thanks!

-D

 

[briangriffin]

Create group 2 on user ID. Hide group header 2 and the details section.

In group footer 2, place name and maximum(score).

Since there is now a summary field at the group level, you can use the group sort expert to sort group 2 by score if desired.

 

[DFeinB]

I attempted this solution and the report breaks the group into scores before evaluating the maximum value of the User ID group.

Is there some way to have Crystal evaluate the the maximum score value before the first grouping is created?

 

[DFeinB]

Another thing I tried was using a Top Nth sort. But I encountered the same issue of the first group breaking up the scores.

 

[briangriffin]

Yes, of course, sorry...

The easiest way would be to base your report on a command if that's possible:

select ID, max(score) as score from yourdb.yourtable group by ID

Join additional tables to the command as your would any other table. Then your existing grouping will work fine.

If that is not an option please advise and we'll come up with a purely Crystal solution.

 

[DFeinB]

Unfortunately, that is not an option. I will need a purely Crystal solution if possible.

 

[briangriffin]

Just to be sure we're on the same page, I was a little misleading when I said 'purely Crystal' solution. I'm talking about a command object within Crystal, not anything created on the database server. So technically it's still purely Crystal.

I want to be sure because any other approach would almost surely use a subreport, and that can be a performance hog. Besides being the easiest to implement, the command would definitely be the fastest refresh of your data.

If you understood this already I apologize. In my opinion, commands within Crystal are invaluable because they are often the best solution while not creating a lot of clutter on your database server.

 

[DFeinB]

I have never used a command object before, so I would be interested to get your input on that.

I just can't modify any of the tables or create any new joins, which if I understand correctly is what a command object avoids.

 

[briangriffin]

Great, I try to avoid subreports if at all possible.

Create a new report and base it on the same connection you're currently using. Click the plus sign next to the connection if needed, but the first item will be Add Command. Double click it.

Here you can paste in any valid SQL that returns a dataset. I don't know your backend database platform, but this syntax should work with anything:

select ID, max(score) as score from yourdb.yourtable group by ID

Click OK and you'll see on the right side 'Command'. The important thing is that it behaves exactly as any other table.

Now put the ID and score fields in the detail section - remember that now the score field is actually the max score for that ID. Now create your specified grouping and you're done. You can go back and join additional tables to the command if needed.

By the way, specified grouping if often easier by just creating a formula and grouping on the formula, rather than slogging through the specified group expert:

//{@ScoreGroup}
if {score} in [0 to 25] then "0 - 25"
else

if {score} in [26 to 50] then "26 - 50"
else

if {score} in [51 to 75] then "51 - 75"
else

if {score} in [75 to 100] then "75 - 100"

 

[DFeinB]

Thank you, briangriffin!

I'll have to read up a bit on SQL commands, as I don't use them often. And do a little testing this weekend.I'll try to update on Monday when I can access the report again. Since I don't have access from home.

 

[lbass]

You could alternatively set this up as a SQL expression {%maxscore} like this:

(
select max(`score`)
from table A
where A.`userID`=table.`userID`
)

The punctuation depends upon your database. Then use a record selection formula like this:

{table.score}={%maxscore}

You can then create a range formula and group on that and the person will appear only once.

-LB