Monthly complicated report (conditional summation for columns and rows).

[lowkeyloki]

Hello,

It's hard to describe the report problem I have. I am not sure if what I need can be done in filemaker. I need to sort by month, and conditionally add data from other tables. Please read on for the table design and the expected report layout.

3 Tables:

Projects- (Each project will have one or more “children” subprojects)

__kp_projectID

projectName

SubProject- (Each SubProject will have 1 “parent” project and 1 “child” SubProjectTask)

__kp_subProjectID

__kf_projectID

subProjectName

dateA (date field)

dateB (date field)

dateC (date field)

SubProjectTask- (Each SubProjectTask will have 1 “parent” project )

__kp_subProjectTaskID

__kf_subProjectID

quantityL

quantityM

quantityN

totalQuantity(calculation field based on quantityL,M,N)

Expected Report Format:

Total for dateA Total for dateB Total for dateC

Month1 Year

Project X ### ### ###

Project Y ### ### ###

Project Z ### ### ###

SubTotal

Month2 Year

Project X ### ### ###

Project Y ### ### ###

Project Z ### ### ###

SubTotal

The ### should be of total of all Project’s SubProjects’ SubProjectTask.totalQuantity whose dateA (or B or C) fell within that month.

------------------------------------------------------------------------------------------------------------

Here is some example data:

We have a project, projectH

We have subProjectH1:

With

dateA = 1/01/11

dateB = 2/01/11

dateC = 1/01/11

We have SubProjectTaskH1A:

totalQuanity = 6

We have subProjectH2:

With

dateA = 1/01/11

dateB = 3/01/11

dateC = 2/01/11

We have SubProjectTaskH2A:

totalQuanity = 3

We have a project, projectJ

We have subProjectJ1:

With

dateA = 1/01/11

dateB = 2/01/11

dateC = 2/01/11

We have SubProjectTaskJ1A:

totalQuanity = 1

Then the report should be:

Total for dateA Total for dateB Total for dateC

1/11

Project H 9 0 6

Project J 1 0 0

SubTotal 10 0 6

2/11

Project H 0 6 3

Project J 0 1 1

SubTotal 0 7 4

3/11

Project H 0 3 0

SubTotal 0 3 0

4/11

I'm at a total loss for how to approach this report. Please help. Note that the report layout formatting is a bit messed up in the post, please see attachment for actual expected formatting.

[comment]

This would be easy if your 3 dates were separate records in a SubProjectDates table.

[lowkeyloki]

Thanks Comment but I don't think that is a viable option.

The 3 dates are progress milestones (start, end and billed-on dates) for the subProject. I don't think it would be good data base organization to separate them but if that is a necessary evil I'll do it to achieve the result.

Any other ideas to make this report work?

[comment]

I don't think it would be good data base organization to separate them

On the contrary: there is a one-to-many relationship between SubProjects and SubProjectDates, and your current structure of multiple fields of the same kind is not the proper way to resolve it.

The difficulty of getting the monthly summaries is merely a symptom of this flaw: you need the same quantity to "feed" three different sub-summaries. This is not possible when you only have one record.

[lowkeyloki]

I think I see what you are saying. Let me go into a bit more detail.

There are actually 5 date fields for a subproject (start_date, client_due_date, company_due_date2, end and billed-on dates) so that we can track progress of the subproject and there is one totalQuanity associated with that subproject . The collection of dates does not seem like a one to many since each date is a separate related aspect of tracking (1:1 relationship for each date). You give excellent advice and I would like to hear more on the rationale.

These 3 tables are normally viewed from a single layout. The layout's main table is Project and we use a portal to display all of the subproject data. This part of the solution has been in place for months and is working well.

With this layout in mind do you suggest I make one additional table for all subProject "progress" dates (with their date and type), or a table for each of the progress dates or some other option?

[comment]

First - and this is important - my suggestion is not made with any layout in mind. At this point, I am looking purely at the data structure (tables, fields and relationships) with no regard to how this will be presented to the user.

Now, you are correct that one could view the different dates of a subproject as separate attributes (i.e. fields). That is, up to a point. This point is reached when you want to count the same subproject more than once. Take subProjectH2 in your example; it has one quantity (3) and three dates (Jan, Feb, and Mar) - but the total contribution of subProjectH2 to the totals of your report is 9. How can one record with a value of 3 be summarized as 9?

For simplicity, make subProjectH2 the only subproject in your report. IIUC, the intended result would then be:

January

• Project H: 3 0 0

February

• Project H: 0 3 0

March

• Project H: 0 0 3

In order to get this result using Filemaker's summary fields, records must be sorted by month and subProjectH2 must appear in all 3 of the sorted groups. That's just not possible when there is only one subProjectH2 record.

Therefore you must either create 3 related records for subProjectH2 in another table, or have a script assemble your report month-by-month and sum-by-sum (that is find all subprojects whose start_date is in January and get the totals; find all subprojects whose start_date is in February ... find all subprojects whose client_due_date is in January, etc.).

[lowkeyloki]

Thank you. You have confirmed my assumptions about the current database structure and the expected report.

Would you please give me a better detailed description of the script?

[comment]

Would you please give me a better detailed description of the script?

Do you mean the script that would "assemble your report month-by-month and sum-by-sum"?

[lowkeyloki]

Yea. That is the report I am currently trying to wrap my head around.