adembo
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!!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
mlmcc
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?
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.
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
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:
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.
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.
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!
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
Open in new window
You can then add a group on the formula
mlmcc