Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Grouping using multiple values in a text field Post Reply Post New Topic
Author Message
Mobin
Newbie
Newbie
Avatar

Joined: 28 Jan 2009
Location: United Kingdom
Online Status: Offline
Posts: 2
Quote Mobin Replybullet Topic: Grouping using multiple values in a text field
    Posted: 28 Jan 2009 at 4:47am
Hi Guys,
 
I need some urgent help on a problem which I think someone can easily solve.
 
All I'm trying to do is group a report using multiple values in a text field.  I'll try to explain in simple terms.
 
I have tutors on a database.  These tutors can teach a number of subjects.  Say Mr Smith can teach English, Maths and Physics.  I have a text field for each tutor, and in this field for Mr Smith I have typed "English Maths Physics".
 
In my code I have written something like this:
 
if "English" in {tutor_person.Tutor Pay Point} then "Languages" else
if "Maths" in {tutor_person.Tutor Pay Point} then "Arithmatic" else
if "Physics" in {tutor_person.Tutor Pay Point} then "Sciences" else "Others"
 
I have grouped my report by the above formula and it almost works.  It's just that Mr Smith shows up only under the grouping "Languages" which is right, but i also need his name under the groupings "Arithmatic" and "Sciences".
 
i.e. I sort of need the formula to repeat and check the whole field for the different values and group Mr Smith in all groups rather than just group him using the first value.  I know "Else" is probably not the right thing to use but what is?
 
Very grateful if you could help.
 
Kindest regards,
 
Mobin.  
Life is too short to get stressed.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 28 Jan 2009 at 6:05am
Once a record is used in one grouping it will not appear in any other group at that same level. You could make sub reports for each category and Use select statements in each report to limit the records using instr options.

IP IP Logged
Mobin
Newbie
Newbie
Avatar

Joined: 28 Jan 2009
Location: United Kingdom
Online Status: Offline
Posts: 2
Quote Mobin Replybullet Posted: 28 Jan 2009 at 6:11am
Thanks for replying mate.
 
I do actually have a report with many sub-reports and the report works, but it takes forever and often crashes my computer.  Hence I was trying to get away from that.
 
I tried to write my problem in the simplest way possible.  In reality each subject is represented by 2 letters.  So the text field would "EN" instead of "English" and "MA" instead of Maths.
 
Is there a possible code that could be return based on positions.  So something like If the first 2 positions of teh field have "EN" then English or if MA then Maths.
 
Then write code for the next 2 letter 2 positions.  Or is this not possible too?
 
Sorry if I'm stating the obvious.  I'm not an expert at SQL.
Life is too short to get stressed.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 28 Jan 2009 at 6:54am

DBlank is correct.  There is only 1 line of data, so there is only 1 line to display on the report.  What you are wanting, I think, is to have 3 lines for mr smith.  1 for english, 1 for math, and 1 for physics...now you can split up where to print mr smith.

Crystal is reading the data a row at a time and deciding what to do with it.  The subreports requery the data and, in effect, are getting duplicate rows so that they can be reported on multiple times.  So if only sees 1 row, it will only print 1 row...it can't go back
 
I realize the example is simple and that there is probably more to the report, but if it was just getting a list of tutors for subjects, you could create lists of tutors for each subject...In the detail section you would have a formula that would scan for keywords, and if it found a keyword, it would add the name to a variable say LanguageTutors.  At the end of the group it would list out the different tutor types.  Oh yeah, the detail section would be suppressed, just the formula would run.
 
If you created a stored procedure to get your data you should be able to make the 1 row for mr smith become 3.
 
Hope this helps.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 28 Jan 2009 at 9:05am

2 other possible approaches.

The first is similar to lockwelles in that you could create distinct views per subject and join them back together to get a row per teacher per subject and then just group on the subjects or teacher as needed.
If you want to try to reuse your current report get rid of your if then statement because it won't work. The first condition it meets will prevent it from getting to the next condition and this is why it is excluding your names from the second and third values even though they are seperate subreports. Instead add select statements for each sub report per class to filter the data for each report to just that data that is applicable for that class. Examples:
Instr({table.classfield},"EN")>0 can be used for English
Instr({table.classfield},"MA")>0 can be used for Math
etc.
Also make sure your sub-report links are good if you need them at all.
IP IP Logged
Post Reply Post New Topic
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



This page was generated in 0.031 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.