Adding Courses together in formula in Crystal Reports

Question

I'm creating a custom Transcript for our school and at the bottom of the report I need to add all the courses together for "Credits Earned". Most are 1 point each, but then there are a few that are half points.

I started creating a formula for this and it works just fine, but now it's starting to get confusing with all the different variations the student could have. haha.

Is there a simpler way of doing this? Thanks.

Here's what I have so far:

.5 credit are - Physics, Bio, Chemistry, Personal Project, Debate I/II, Intro to Film, MMUN

If isnull ({Export_20230208T155906_.Comment}) then Count ({Export_20230208T155906_.Course Title})
else
If {Export_20230208T155906_.Course Title} = "Personal Project" 
and {Export_20230208T155906_.Comment} = "Biology: .5" then Count ({Export_20230208T155906_.Course Title}) + 1 
else
If {Export_20230208T155906_.Course Title} = "Personal Project" 
and {Export_20230208T155906_.Comment} = "Chemistry: .5 credit Physics: .5 credit" then Count ({Export_20230208T155906_.Course Title}) + 1.5 
else
If {Export_20230208T155906_.Course Title} = "Personal Project" 
and {Export_20230208T155906_.Comment} = "Biology: .5 credit Chemistry: .5 credit Physics: .5 credit" then Count ({Export_20230208T155906_.Course Title}) + 2 
else
If {Export_20230208T155906_.Course Title} = "IB MYP Intro to Film 1" 
and {Export_20230208T155906_.Comment} = "Biology: .5" then Count ({Export_20230208T155906_.Course Title}) + 1 
else
If {Export_20230208T155906_.Course Title} = "IB MYP Intro to Film 1" 
and {Export_20230208T155906_.Comment} = "Chemistry: .5 credit Physics: .5 credit" then Count ({Export_20230208T155906_.Course Title}) + 1.5 
else
If {Export_20230208T155906_.Comment} = "Biology: .5 credit" then Count ({Export_20230208T155906_.Course Title}) + .5 
else
If {Export_20230208T155906_.Comment} = "Chemistry: .5 credit" then Count ({Export_20230208T155906_.Course Title}) + .5 
else
If {Export_20230208T155906_.Comment} = "Physics: .5 credit" then Count ({Export_20230208T155906_.Course Title}) + .5 
else
If {Export_20230208T155906_.Comment} = "Biology: .5 credit Chemistry: .5 credit" then Count ({Export_20230208T155906_.Course Title}) + 1 
else
If {Export_20230208T155906_.Comment} = "Biology: .5 credit Physics: .5 credit" then Count ({Export_20230208T155906_.Course Title}) + 1 
else
If {Export_20230208T155906_.Comment} = "Chemistry: .5 credit Physics: .5 credit" then Count ({Export_20230208T155906_.Course Title}) + 1 
else
If {Export_20230208T155906_.Comment} = "Biology: .5 credit Physics: .5 credit" then Count ({Export_20230208T155906_.Course Title}) + 1 
else
If {Export_20230208T155906_.Comment} = "Biology: .5 credit Chemistry: .5 credit Physics: .5 credit" then Count ({Export_20230208T155906_.Course Title}) + 1.5 

Answer 1

Why not include in the export the number of credits for each course? You can then simply sum the value in that column.

If for some odd reason your database doesn't have that value, it should be added to your database.

That would remove the need for you to assign credit values to courses in your reporting.

Answer 2

If you can't access that value, one option is to create your own table and join to it. That would allow you to delegate the responsibility of assigning the correct value to someone on the academic side.

Otherwise, if you must maintain this logic in a Crystal formula, then simply create a detail level formula that returns the number of credits for that row. Then, simply SUM that formula at whatever level of grouping you need (e.g. Student, DEPT, Grand Total). There is no need to accumulate the value using a variable.

Also, the detail-level formula can use OR logic to avoid so many branches. Another excellent mechanism to avoid many IF THEN branches is the use of a SELECT CASE statement.