Link to home
Start Free TrialLog in
Avatar of adembo
ademboFlag for United States of America

asked on

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!!
ASKER CERTIFIED SOLUTION
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mike McCracken
Mike McCracken

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
Avatar of adembo

ASKER

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
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.

mlmcc
Avatar of adembo

ASKER

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?
It is being automatically generated by the IDENTITY column in CourseGroup.
Avatar of adembo

ASKER

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
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.
Avatar of adembo

ASKER

OK, thanks for the follow up, that certainly is helpful. I will try a few things and update/close case as the resolution nears!