Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Grouping by Maximum Value Post Reply Post New Topic
Author Message
Dfein
Newbie
Newbie
Avatar

Joined: 21 Jun 2012
Location: United States
Online Status: Offline
Posts: 3
Quote Dfein Replybullet Topic: Grouping by Maximum Value
     Posted: 22 Jun 2012 at 6:48am
Hi All,

I've been struggling with this report that groups test scores. Users take a test and are allowed to take it multiple times. Currently I have it formatted as such:

Group # 1 (Score)
Group # 2 (User ID)
[User ID] [Name] [Score]

Group # 1 Specified Order:
0 - 25
26 - 50
51 - 75
76 - 100

I have attempted to use a formula to only use the maximum score value within Group # 2, however, Group # 1 sorts the scores first, so a person could have a score of 35 in one group and 55 in another. My goal is to somehow eliminate the 35 score.

I have also tried doing a top Nth based on the maximum score value under group # 2.

Thanks,

-D
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 22 Jun 2012 at 9:04am
first, standard answer...stored proc and do the logic there as it is much more robust.
 
non stored proc answer:
grouping by userid and then by score will allow you to display the high score per person.
 
Wanting to group the scores first will defeat this as CR will put each person in a number group, which may not be the highest.  If you group from highest to lowest, you still can get dupes, unless you place each user into a string or an array and check if that name has already been displayed...but your number grouping would have to be turned upside down, with the high scores first, otherwise you will get each user's low score.
 
HTH
IP IP Logged
Dfein
Newbie
Newbie
Avatar

Joined: 21 Jun 2012
Location: United States
Online Status: Offline
Posts: 3
Quote Dfein Replybullet Posted: 22 Jun 2012 at 9:20am
Thank you for your response.
 
My main grouping needs to be by score, because the the primary results I am looking for is how many of each score range has been achieved.
 
I am using the second group was an attempt to prevent a person from showing up twice and to try and get the highest score of each person.
 
I do not have to keep the second group. I just need a way to group by score range.
 
Unfortunately, I also need to find some way to do this solely in Crystal. Is that possible?
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 22 Jun 2012 at 9:42am
are you looking for unique scores...are you going to be doing any other analysis, averages, counts etc of the data?  As they will require a different solution.
 
If I had to do this report, I would:
1 list/group the scores from high to low, unlike the low to high that you have.
2 2nd grouping by userid
3 dynamically suppress scores for users already printed.
   Section Expert/suppress/x-1 button:
shared stringvar used;
local booleanvar print := not (instr(used,  "|" + {table.userid} + "|") > 0);
 
if print then
  used := used + "|" + {table.userid} + "|";
 
print
 
that should give you a good start...at least that is what I would start with
IP IP Logged
Dfein
Newbie
Newbie
Avatar

Joined: 21 Jun 2012
Location: United States
Online Status: Offline
Posts: 3
Quote Dfein Replybullet Posted: 22 Jun 2012 at 10:01am
I am looking to get a unique score with a count of all the scores in each range. For example
 
0 - 25       Total:  35
26 - 50     Total:  22
51 - 75     Total:  15
76 - 100   Total:    5
 
If a person scores a 55 and an 85. I just want to count the 85 and throw out the 55.
IP IP Logged
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum