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:

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

221Englit    English Literature     A

889USHISTORY  United States History II     C+

Please let me know if there are any suggestions out there. Thanks!!
Who is Participating?
Brian CroweConnect With a Mentor Database 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

   CourseGroupID INT IDENTITY(1, 1),
   Description VARCHAR(50)

INSERT INTO CourseGroup (Description)
SELECT 'History'
SELECT 'English'


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, ...,
      WHEN CourseID LIKE '%Algebra%' THEN 'Math'
      WHEN CourseID LIKE '%Trig%' THEN 'Math'
FROM Course
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
Else If {CourseIdField} IN ['221Englit'] then
Else If ...
     'Unknown Master Subject'

Open in new window

You can then add a group on the formula

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.

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

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.
I agree if you can change the table then that is the way to do it.

ademboAuthor Commented:

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

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

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
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.
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!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.