Report Calculation

[mmcconnell]

Greetings! I am new to this list.

I am trying to build a report that utilizes several calculations and I seem to be going around in circles with If and Case statements.

This is for a student tracking database.

One student ID number can have many scholarship records (they are in 2 different tables).

Values are hard-coded for testing; they will be user-selectable in the final UI.

I am trying to report on the current semester (fall 2011) and the next semester (spring 2012) individually and then reporting the sum of those 2 amounts.

The reporting of the amount is also dependent of the status of the student. In this example, I want to see the amount of students who are enrolled, and everyone else should be reported as 0.

I started by creating an unstored calculation field called fall_scholarship and using the following calculation: If ( Semester="Fall" and Year=2011 and Students::Status="Enrolled"; Award ; 0 ). This returns exactly the expected data.

My problem is with the spring semester. At this point, there is no spring 2012 data, so a similar If statement would return 0. However, for the report, I need to see an annual amount, so I tried the following calculation:

Case ( Semester="Spring" and Year=2012 and Students::Status="Enrolled" ; If(Award=0; fall_scholarship; Award); Award)

The problem is that it works fine if there is nothing in the spring scholarship (it duplicates the fall amount), but in the rare instance that there is a value in the spring, I don't get the correct value.

Worse yet, when I look at the scholarship table, I will have multiple entries for my test student. In the example attached, the student has been given a scholarship of 1 in the fall and 2 in the spring, for a total of 3. Instead I see one line with 2 (fall "real" and fall copy) and another line with 2 (fall 0 and spring 2)

Is there a better way to attack this?

[comment]

I am trying to report on the current semester (fall 2011) and the next semester (spring 2012) individually and then reporting the sum of those 2 amounts.

This should be handled by finding and sorting - not by calculations.

I want to see the amount of students who are enrolled, and everyone else should be reported as 0.

Do you really need "everyone else" on this report? Perhaps you should include only students who are enrolled in this report.

[mmcconnell]

This should be handled by finding and sorting - not by calculations.

If I use finding and sorting, i will still have to change some of the scholarship results to 0 and fill in missing data (spring numbers). That's why I started with a calculation in the first place.

Finding and sorting still leaves me with a similar issue with data on 2 lines when I need it to report on one.

Do you really need "everyone else" on this report? Perhaps you should include only students who are enrolled in this report.

I do need to show absolutely everyone on the report. This is a requirement of another office on campus. I am attempting to put my data into a format used by everyone else on campus.

[comment]

I do need to show absolutely everyone on the report.

OK, then you do need a calculation field like =

Case ( Students::Status="Enrolled" ; Amount )

then summarize this field instead of the original award amount field.

Note that if the report is produced from the Awards table, it will include only students that have received awards in the reported period - not "absolutely everyone". Reporting on all students needs to be done from the Students table and is a bit more difficult.

[mmcconnell]

OK, then you do need a calculation field like =

Case ( Students::Status="Enrolled" ; Amount )

then summarize this field instead of the original award amount field.

Note that if the report is produced from the Awards table, it will include only students that have received awards in the reported period - not "absolutely everyone". Reporting on all students needs to be done from the Students table and is a bit more difficult.

I believe this is where I started. If ( Semester="Fall" and Year=2011 and Students::Status="Enrolled"; Award ; 0 ) This works exactly as intended with the fall amount. The issue is that I need to show the spring amount on the same line, even when there is no spring amount in the database at this point. That is the reasoning behind reporting the fall amount twice and summarizing fall and spring amounts. I need to show all three values on one line for each student: ID|Name|Fall Amount| Spring Amount|Total Amount|Last Modified Date

I am running this report from the Students table. The user will perform an initial find to limit the total number of students in the report, but it could easily be a list of 1000.

I can certainly do the find the other way around and get the same result: find all fall 2011 and spring 2012 in the awards table, but I am still unsure how to make the corresponding spring data appear in the same line. The student ID is the same for both records, but I need to see both values, so a simple sum will not give me what I need. The example I posted originally shows 1 student with different spring and fall awards that need to be reported on (this is the Award table).

Thank you.

[comment]

What exactly should your report look like?

[mmcconnell]

What exactly should your report look like?

In this example, test 1 has a status of Enrolled and no data for spring. test 2 has another status; award is not seen. test 3 has different amounts in fall and spring and a status of Enrolled.

Thank you.

[comment]

How about using filtered portals?

Report11.zip

[mmcconnell]

How about using filtered portals?

This, as I am sure you know, is a brilliant solution you have created, and takes me 99% of the way there. I have been testing the implementation of this all morning and I have hit another brick wall.

I need to summarize Fall 2011 and Spring 2012. The solution you created works well if the year is the same, but not with different years. I have modified the report, adding another global called gReportYearSpring and modifying the spring portal to match. This now gives me correct data in both fall and spring fields. Updating the summary portal with an OR statement now gives me exactly the expected the result if a student has Fall 2011 and Spring 2012 awards.

The final issue is that if the spring 2012 award is null (not 0; 0 is a value amount), I need the spring 2012 award to be the same as the fall 2011 and provide a sum of those amounts.

I have attached an update to your solution. Specifically, I need Larry to have his fall 2011 semester award duplicated in the spring 2012 report. Later, I will be adding a real Spring 2012 amount to Larry, and that should be reported and may be different from the Fall award.

I'm guessing that this should be done with a Case statement in the portal filter, but I cannot seem to get the syntax to work. I will also need to modify the total box to sum Fall and Spring no matter where the values are coming from.

Would it be helpful to have a table that contains the school year (i.e. 2011-2012, 2012-2013, etc.)? these are known values, and for the purposes of this report, it will always be Fall 20XX and Spring (Fall 20XX+1).

Thank You.

Report11.zip

[comment]

You don't need the additional global field: you can make the "Spring" portal filter =

Awards::Year = Students::gReportYear + 1

and

Awards::Semester = "Spring"

and

Students::Status = "Enrolled"

and the "Total" =

(

Awards::Year = Students::gReportYear

and

Awards::Semester = "Fall"





or



Awards::Year = Students::gReportYear + 1

and

Awards::Semester = "Spring"

)



and

Students::Status = "Enrolled"

The final issue is that if the spring 2012 award is null (not 0; 0 is a value amount), I need the spring 2012 award to be the same as the fall 2011 and provide a sum of those amounts.

That doesn't make sense, at least not by itself. Presumably, you want this only temporarily - until the spring 2012 awards are known? How would Filemaker know that Larry has no spring 2012 awards because it's not the time yet - and not because he didn't get any?

The other point is that you want to report data that you don't have. It would be easy to change the "Spring" portal filter so that it passes "Fall" records under some condition. But there is only one set of "Fall" records, and a summary field can only summarize existing records.

[mmcconnell]

That doesn't make sense, at least not by itself. Presumably, you want this only temporarily - until the spring 2012 awards are known? How would Filemaker know that Larry has no spring 2012 awards because it's not the time yet - and not because he didn't get any?

The other point is that you want to report data that you don't have. It would be easy to change the "Spring" portal filter so that it passes "Fall" records under some condition. But there is only one set of "Fall" records, and a summary field can only summarize existing records.

You are again, correct. I am breaking the "rule" of trying to solve a user-caused problem with a technological solution. There is no technical reason that the annual award cannot be input at the same time.

I have a meeting scheduled with the stakeholders next week where I will try to convince them that what they are asking for is not feasible and their data entry policy needs to change if they want the report to be in this format.

Thank you so much for your help and suggestions!