Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: CR XI - Grouping troubles Post Reply Post New Topic
Author Message
Mebaby3
Newbie
Newbie
Avatar

Joined: 22 Apr 2014
Location: United States
Online Status: Offline
Posts: 7
Quote Mebaby3 Replybullet Topic: CR XI - Grouping troubles
     Posted: 22 Apr 2014 at 7:43am
So ... I have data similar to this
Col1 Col2 Col3 Col4
17P1E1512-403 S-CA4611 ComponentA 2
17P1E1512-403 S-CA4611 ComponentB 1
17P1E1512-403A S-CA4625 ComponentA 2
17P1E1512-403A S-CA4625 ComponentB 1
17P1E1512-403A S-CA4625 ComponentC 5
17P1E1512-403M S-CA4785 ComponentA 2
17P1E1512-403M S-CA4785 ComponentB 1
17P1E1512-403M S-CA4785 ComponentC 5
17P1E1512-403M S-CA4785 ComponentD 2
17P1E4005-525 S-CA4830 Component X 3
17P1E4005-525 S-CA4830 Component Y 3
17P1E4005-525R S-CA5583 Component X 3
17P1E4005-525R S-CA5583 Component Y 3
17P1E4005-525R S-CA5583 Component Z 3

I started a report with this goal...
To find the highest revision for each col1 data string ie.. 17P1E1512-403M is the highest revision for this part as 17P1E4005-525R is the highest for its part...

I split the data initially for col1 so I could find the max for each set and it seemed to work except since the data was split all of the values for col2 proved true and I end up with all the S-CA's for every revision and their parts... like this

Col1 Col2 Col3 Col4
17P1E1512-403M S-CA4611 ComponentA 2
17P1E1512-403M S-CA4611 ComponentB 1
17P1E1512-403M S-CA4625 ComponentA 2
17P1E1512-403M S-CA4625 ComponentB 1
17P1E1512-403M S-CA4625 ComponentC 5
17P1E1512-403M S-CA4785 ComponentA 2
17P1E1512-403M S-CA4785 ComponentB 1
17P1E1512-403M S-CA4785 ComponentC 5
17P1E1512-403M S-CA4785 ComponentD 2
17P1E4005-525R S-CA4830 Component X 3
17P1E4005-525R S-CA4830 Component Y 3
17P1E4005-525R S-CA5583 Component X 3
17P1E4005-525R S-CA5583 Component Y 3
17P1E4005-525R S-CA5583 Component Z 3

which is inaccurate data, well seemingly but it rings true from the split...

So I tried using a summary on col2 and it does give me the actual Max for that field for each of the max col1 values but outside of the group header it still lists all the data I don't need...

Can I avoid this with the split or is there another method to finding the max for each build set in col1?

This is what I am trying to accomplish...

Col1 Col2 Col3 Col4
17P1E1512-403M S-CA4785 ComponentA 2
17P1E1512-403M S-CA4785 ComponentB 1
17P1E1512-403M S-CA4785 ComponentC 5
17P1E1512-403M S-CA4785 ComponentD 2
17P1E4005-525R S-CA5583 Component X 3
17P1E4005-525R S-CA5583 Component Y 3
17P1E4005-525R S-CA5583 Component Z 3

My brain is about to bust from all the ways I have tried ...

Can someone help me get to the third set of data from the first set?
The eye sees only what the mind is prepared to comprehend.
Robertson Davies
IP IP Logged
kostya1122
Senior Member
Senior Member
Avatar

Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
Quote kostya1122 Replybullet Posted: 22 Apr 2014 at 8:50am
you could try
in select expert>>group...
right(col2,4) = maximum(right(col2,4),col1)
IP IP Logged
Mebaby3
Newbie
Newbie
Avatar

Joined: 22 Apr 2014
Location: United States
Online Status: Offline
Posts: 7
Quote Mebaby3 Replybullet Posted: 22 Apr 2014 at 9:35am
The only group I currently have is the group by
@split Max of col1
split({col1}, "-")[1]
and
@split Max of col2

basically just my two summaries...

which group should I do it under?
or are you suggesting a new group?


Edited by Mebaby3 - 22 Apr 2014 at 9:38am
The eye sees only what the mind is prepared to comprehend.
Robertson Davies
IP IP Logged
kostya1122
Senior Member
Senior Member
Avatar

Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
Quote kostya1122 Replybullet Posted: 22 Apr 2014 at 9:46am
in my scenario you need to group on col1 and col2
to get your end result.
IP IP Logged
Mebaby3
Newbie
Newbie
Avatar

Joined: 22 Apr 2014
Location: United States
Online Status: Offline
Posts: 7
Quote Mebaby3 Replybullet Posted: 22 Apr 2014 at 10:43am
I am attempting to put it in but it is giving me the error that a field belongs there.... here is my formula after inserting my fields... am I missing syntax?

right({FS_Item.ItemNumber},4) = maximum(right({FS_Item.ItemNumber},4), {FS_CustomerItem.CustomerItemNumber})
The eye sees only what the mind is prepared to comprehend.
Robertson Davies
IP IP Logged
kostya1122
Senior Member
Senior Member
Avatar

Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
Quote kostya1122 Replybullet Posted: 22 Apr 2014 at 11:06am
maybe
tonumber(right({FS_Item.ItemNumber},4)) = maximum(tonumber(right({FS_Item.ItemNumber},4)), {FS_CustomerItem.CustomerItemNumber})
IP IP Logged
Mebaby3
Newbie
Newbie
Avatar

Joined: 22 Apr 2014
Location: United States
Online Status: Offline
Posts: 7
Quote Mebaby3 Replybullet Posted: 22 Apr 2014 at 12:13pm
Now it says too many arguments have been given to this function
The eye sees only what the mind is prepared to comprehend.
Robertson Davies
IP IP Logged
kostya1122
Senior Member
Senior Member
Avatar

Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
Quote kostya1122 Replybullet Posted: 22 Apr 2014 at 12:35pm
try creating a formula for 
formula1
tonumber(right({FS_Item.ItemNumber},4))
then
formula1 = maximum(formula1,{FS_CustomerItem.CustomerItemNumber})
IP IP Logged
Mebaby3
Newbie
Newbie
Avatar

Joined: 22 Apr 2014
Location: United States
Online Status: Offline
Posts: 7
Quote Mebaby3 Replybullet Posted: 22 Apr 2014 at 12:43pm

Okay I got that...
this isnt a numeric field though... so I will try with right again

It isnt giving me max on the col1...


Edited by Mebaby3 - 22 Apr 2014 at 12:47pm
The eye sees only what the mind is prepared to comprehend.
Robertson Davies
IP IP Logged
Mebaby3
Newbie
Newbie
Avatar

Joined: 22 Apr 2014
Location: United States
Online Status: Offline
Posts: 7
Quote Mebaby3 Replybullet Posted: 23 Apr 2014 at 4:43am
I just asked her to go in and set a marker for the data that is relevant... I feel like the data kicked my butt
The eye sees only what the mind is prepared to comprehend.
Robertson Davies
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

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.