Crystal Report - Group like data in form

Hello, I am looking for some insight on how I can create a report from a defined group of criteria for my project Im working on. So this is a transcript I am producing that gets all of its data from a MSSQL DB that I imported the data into. I set up the report with the following:

CourseID StudentLastName StudentFirstName SchoolName Grade Credits

I have the Course ID as a table in the DB that I would like to have it auto pick a subject for that class, as my import does not identify that as its just something we do.

So take for example course ID 455Algebra. If the CourseID field above that I linked the courseid table to has a 455Algebra, I want it to auto create a header for that and call it Math. So if there were a 455Algebra and a 677Geometry and a 822Trig listing of all the classes that one student took. Then the student also has CourseID 221EnglishLit and CourseID 889USHistory, I am trying to get the output on the report to show:

MATH
455Algebra    Algebra II              A-
677Geometry    Geometry I       C+
822Trig      Intro to Trig               D

ENGLISH
221Englit    English Literature     A

HISTORY
889USHISTORY  United States History II     C+


Please let me know if there are any suggestions out there. Thanks!!
LVL 2
ademboAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
I would recommend creating a separate table in your database for the class grouping whether you call it Topic or CourseGroup or whatever.  Then add a column to your Course table and fill that value with the appropriate CourseGroup reference.

Below is a basic process that you could follow though it would need to be tweaked a bit for your situation/naming


CREATE TABLE CourseGroup
(
   CourseGroupID INT IDENTITY(1, 1),
   Description VARCHAR(50)
)

INSERT INTO CourseGroup (Description)
SELECT 'Math'
UNION ALL
SELECT 'History'
UNION ALL
SELECT 'English'

ALTER TABLE Course ADD CourseGroupID INT NULL

UPDATE Course
SET CourseGroupID = 1
WHERE CourseID LIKE '%Algebra%'

Once you've got the groups set up the way you want the report grouping becomes rather tedious.

Your other option is a rather tedious and error prone case statement

SELECT CourseID, StudentLastName, ...,
   CASE
      WHEN CourseID LIKE '%Algebra%' THEN 'Math'
      WHEN CourseID LIKE '%Trig%' THEN 'Math'
     ...
   END
FROM Course
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlmccCommented:
If you can't change the data source then you can do it with Crystal formulas

The formula will have to account for all possible course Ids and it will have to be updated should the course ids change

Something like
If {CourseIdField} IN ['455Algebra', '677Geometry', '822Trig'] then
     'Math'
Else If {CourseIdField} IN ['221Englit'] then
    'English'
Else If ...
...
Else
     'Unknown Master Subject'

Open in new window


You can then add a group on the formula

mlmcc
0
ademboAuthor Commented:
Both sound like excellent ideas. I do have the full list of both course ID and the subjects. Are there things that either of those ways would be easier to deal with down the road? As there certainly will be changes, with new course ID and others.

Thanks
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Brian CroweDatabase AdministratorCommented:
Using a data-driven process as opposed to a code-driven one will be much easier to maintain going forward.  You could update the courses/subjects mapping without making any changes to the report or needing to redeploy.
0
mlmccCommented:
I agree if you can change the table then that is the way to do it.

mlmcc
0
ademboAuthor Commented:
Bricrowe,

I have a question on the statement you wrote. On the:
UPDATE Course
SET CourseGroupID = 1
WHERE CourseID LIKE '%Algebra%'

Where is the CourseGroupID = 1 coming from? How does it know what CourseGroupID = 1 is?
0
Brian CroweDatabase AdministratorCommented:
It is being automatically generated by the IDENTITY column in CourseGroup.
0
ademboAuthor Commented:
BriCrow,


Hello, I am trying to run that SQL script and I am getting errors back from it.

Msg 156, Level 15, State 1, Line 30
Incorrect syntax near the keyword 'SET'.

Line 30 is SET CourseGroupID = 1
0
Brian CroweDatabase AdministratorCommented:
Have you populated the CourseGroup (or whatever you've chosen to call that table)?  Have you changed the table reference in the UPDATE query to match your tablename?  In practice I would run those queries (or something similar) I provided one at a time and not as once.

The scripts I provided weren't meant to be taken exactly as written since I didn't have your schema information.  Also, keep in mind you will need to run multiple UPDATE queries to populate all of the course records with the appropriate group identifier.

Basically the logic of the process is to:
Create a table that holds the different subject groups
Add a column to the course table to hold a reference to a subject group
Populate that column with the appropriate reference from the subject group table
Join the tables when querying for the report to allow grouping on the subject group

An alternative would be to create a VARCHAR column on the course table and just add the string ("Math", "History", "English", etc.) but this is poorly normalized and would be harder to maintain going forward but you would have to make the determination based on your situation.
0
ademboAuthor Commented:
OK, thanks for the follow up, that certainly is helpful. I will try a few things and update/close case as the resolution nears!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.