Error handling with regards to a caculation

I have a report that calculates a students GPA based on two fields in a dataset:

TrmGpaPts/CreditAwd = GPA SQL below

My issue is that the students with 0 (zero) or NULL credits awarded results in a "divide by zero error". How can I place a 0 (zero) in the GPA column when the student has 0 or NULL credits?

Thanks

select
school.schname as School,
rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname) as Student,
trnscrpt.suniq as Ident,
stugrp_active.graden as Grade,
trnscrpt.graden as Grd_Lvl,
sum(trnscrpt.gradcrawd) as CreditAwd,
round(sum(case when Trnscrpt.GpaCrAtt is null then 0 else Trnscrpt.GpaCrAtt end * gpamarks.gpavallvl0),3) AS TrmGpaPts

from
dbo.trnscrpt
inner join dbo.stugrp_active on (trnscrpt.suniq = stugrp_active.suniq) INNER JOIN
school ON stugrp_active.schoolc = school.schoolc INNER JOIN
gpamarks ON trnscrpt.marksetc1 = gpamarks.marksetc AND trnscrpt.markawd1 = gpamarks.mark

where
school.schname = @.school and
stugrp_active.graden =@.graden and
trnscrpt.graden = @.trnscrptgraden and
trnscrpt.termc = @.termc

group by
school.schname,
trnscrpt.termc,
rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname),
trnscrpt.suniq,
stugrp_active.graden,
trnscrpt.graden

order by
School,
Student

When CreditAwd is zero then you don't want to do the division (TrmGpaPts/CreditAward)

You should put something like this in the expression for your report to get the GPA.

=IIf(Fields!CreditAward.Value = 0, "N/A", (Fields!TrmGpaPts.Value) / IIf(Fields!CreditAward.Value = 0, 1, Fields!CreditAward.Value))

|||

Thanks! I see the general direction I need to take.

|||Glad I could help.

|||

Using GregSQL's expression from above, I'm getting the error below.

What does this mean to specify the data set scope and where would I do that?

The Value expression for the textbox ‘GPA’ refers to the field ‘CreditAward’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

Build complete

|||It's saying that field is not in your dataset. Open your dataset in business intelligence studio (View -> datasets). Right click Report Datasets and click refresh. See if CreditAward is then added to your dataset in that window. If not, then you are not selecting it properly in your SQL query.

|||

Thanks. Makes sense once I spotted the typing error.