sum of formula returns wrong result

[jlkjr]

I have a transcript report for students written in 8.5 In that report I have a formula that I use for calculating total number of absences (excused or unexcused--separate for each) for a student in a given academic year using a sum. I can't seem to get the unexcused absence portion right. It is giving me a total count of absences from the date of first unexcused absence for the year. The formula seemed to work correctly until this year.

Here is the formula located in the details section:

if isNull({StsAttndDayDtl_2.StsAttndDayDtl_2_Code}) = TRUE or
isNull({StsAttndDayDtl_2.StsAttndDayDtl_2_Academicyear}) = TRUE then
0 else
if {?Pm-StsGrdsGrds_1.StsGrdsGrds_1_Academicyear} =
{StsAttndDayDtl_2.StsAttndDayDtl_2_Academicyear} then
if {StsAttndDayDtl_2.StsAttndDayDtl_2_Code} = "1.0 U" then
1 else if {StsAttndDayDtl_2.StsAttndDayDtl_2_Code} = "0.5 U" then
.5 else 0

The sum is then in the group footer. Any help would be appreciated.

 

[Turkbear]

Hi,
Try using parens to isolate the logic and try this slight mod:

if (isNull({StsAttndDayDtl_2.StsAttndDayDtl_2_Code})= TRUE  or isNull({StsAttndDayDtl_2.StsAttndDayDtl_2_Academicyear})= TRUE)
 then   
0
 else   
(
if {?Pm-StsGrdsGrds_1.StsGrdsGrds_1_Academicyear} = {StsAttndDayDtl_2.StsAttndDayDtl_2_Academicyear}
then
( if {StsAttndDayDtl_2.StsAttndDayDtl_2_Code} = "1.0 U"  then
  1
 else 
 if {StsAttndDayDtl_2.StsAttndDayDtl_2_Code} = "0.5 U" then
 .5 )
else 
0
)

Check the parens to be sure they are correctly matched.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[jlkjr]

The modification still returns 66 unexcused absences instead of 1. The 66 comes from the total number of absences recorded for the student in the school, not just for this year.

 

[lbass]

You should ensure this is the correct logic:

{?Pm-StsGrdsGrds_1.StsGrdsGrds_1_Academicyear} =
{StsAttndDayDtl_2.StsAttndDayDtl_2_Academicyear}

...which appears to mean you are working within a subreport. Maybe check your overall links and the placement of the subreport (report section).

-LB

 

[briangriffin]

Since it's counting everything, I wonder if the fields are truly null. Try putting a test formula in the details:

isnull(({StsAttndDayDtl_2.StsAttndDayDtl_2_Code})

If it's returning false for records that should be true, then you can try trapping for other quasi-null values in the field, such as:

if (isnull(({StsAttndDayDtl_2.StsAttndDayDtl_2_Code}) or
({StsAttndDayDtl_2.StsAttndDayDtl_2_Code}) = "")
or
(isnull(({StsAttndDayDtl_2.Academicyear}) or
({StsAttndDayDtl_2.Academicyear}) = "")

then........

You may have to play around with it to see what's really in the fields - for instance, test for " " also if needed.

 

[jlkjr]

The problem that I have is that there is only one record in the database not 66. Why would it appear 66 times?

 

[lbass]

Can you clarify whether the sum = 66 or whether the sum is appearing 66 times?

-LB

 

[jlkjr]

The sum = 66. It should only be 1.

 

[lbass]

I'm totally confused now, but if you are only expecting one record to execute for this formula per group, then insert a maximum on it at the group level, instead of a sum.

-LB

 

[jlkjr]

I may have misunderstood what you were asking. Using maximum would not allow for more than one as is the case with other students. There is only one unexcused absence record for this student which is repeating in the details 66 times to match the 66 records that appear for excused absences. Each time it looks at the record, which in this case only needs to be once, the formula evaluates correctly. The sum is also working correctly. The problem is the repetition. The sum for excused absences, which uses the same formula on different fields, is calculating correctly.

 

[fisheromacse]

Let me make sure i understand this...you have 2 fields from different tables. One field has 66 records/values for a particular student and the field from the other table has 1 record/value for the same student. Your report is grouped on student.
You want to count the number of values from each of the above 2 fields.

You should get the same number for each unless you have logic (a formula, running total, etc) in place that is able to count based on some independent criteria.

Your current logic appears to be providing you with accurate results, just not the ones you expected.

I suspect that if the student had 2 or more records/values instead of 1 for the 2nd field that your record count would multiple.

Have you tried using Running Totals to count the records?
I would try a distinct count of the record's DTS (if possible) with reset on change of group/student.

 

[lbass]

You have not yet explained the role of the subreport. It appears that the repeating value is coming from a subreport--is this the only value displayed from the subreport? -Is the subreport in the detail section where it is executing multiple times? How did you get the sum to evaluate correctly? Is the sum based on a shared variable? How is it calculated? You couldn't be inserting a summary.

-LB

 

[jlkjr]

lbass, there are two formulas in the details of the subreport. One for excused absences and one for unexcused. There are two sums, one of each formula, in GF1 which sorts on student. This subreport is in GH5 on the main report which is grouped by school; the academic year, the parameter in the subreport, is Group 4; the student is Group 3.

fisheromacse, I am not sure I understand your post. I am not trying to get a distinct count of the records, but to convert the code listed in the formula to a number and get a total of the numbers for that academic year. In this case, the total for this student is 1.

 

[lbass]

So the main report is grouped like this:

grp#1 school
grp#2 ?????
grp#3 student
grp#4 academic year
grp#5 ????? (subreport here)

What are the missing group fields?

The subreport is grouped only on student?

How is the sub linked to the main report? On what fields?

-LB

 

[jlkjr]

group 1 student homeroom
group 2 student last name
group 3 student full name
group 4 academic year
group 5 school

The subreport is grouped only by student full name

The subreport is linked to the main report by student full name and by academic year.

 

[lbass]

Why would school be nested within a student group? Can a student be in more than one school? If you are not linking on group #5, then the sub should be in Group #4. I think you should also show us the subreport record selection formula--so we can know for sure that the link is working appropriately.

-LB

 

[jlkjr]

A student can have more than one school within an academic year if he transferred in mid-year or took a summer course at another school.

Here is the selection formula for the subreport:
{StsSt.StsSt_Studentname} = {?Pm-StsSt.StsSt_Studentname}
and
{StsGrdsGrds_1.StsGrdsGrds_1_Academicyear} = {?Pm-StsGrdsGrds_1.StsGrdsGrds_1_Academicyear}

 

[lbass]

If you are expecting the results to be specific to the school, you need to link on the school. If not, the sub will give you the same data for both schools, and it should be placed in Group #4. So is the problem sum occurring WITHIN the subreport? How was it calculated? Does the sum say 66 and there 66 rows? Maybe you need to just go to database->select distinct. Otherwise, you need to figure out how your table links in the sub are forcing the repetition.

-LB

 

[jlkjr]

Results do not need to be specific to school since the data is only recorded for attendance at our school.

The sum returns 66 because there are 66 lines in the details. However, the database has only one record not 66.

Select distinct in grayed out.

I did notice that the select records based on field was not checked for academic year. When I checked the box the sum returned over 800.

 

[lbass]

And your table links in the sub?

-LB