Basic (?) summary question

[truelifeajf]

Hi all,

 

Seasoned FM developer here but really never done a lot with reports.

 

So me thinks this might be a simple question...

 

How do I have a summary field in a trailing grand summary, but summing a field from a parent table?

 

See the attached screen shot which should explain pretty quickly what I'm trying to do.

 

I think the issue is, I can have a trailing grand summary of a field that's based on the current table (and of fields showing the "body" section), but I can't have a trailing grand summary of a field that lives in another table and / or displayed in a sub-summary part.

[comment]

See the attached screen shot which should explain pretty quickly what I'm trying to do.

 

Not quite, I am afraid. What is a "case number"? Is it the same thing as a "project"?

[truelifeajf]

Case number is just like a "project".

 

So a case (project) has many tasks.

 

The case (project) is the sub-summary part of the report

 

Tasks is the body of the report

[comment]

I see. Let me first try and explain the difficulty here. As a rule, reports report on a found set - and related values do not mix well with found set summaries. For example, let's look at these three projects:

Project A
• Amount = 100
• 5 related tasks {A1, A2, A3, A4, A5}

Project B
• Amount = 200
• 2 related tasks {B1, B2}

Project C
• Amount = 300
• 3 related tasks {C1, C2, C3}

 

Now, if your found set in Tasks includes all 10 records, the total Amount to be shown is 600. But, if the found set contains only 3 records - say A1, B1 and C1 - the total Amount to be shown is still 600, since the number of related projects has not changed.

OTOH, if the found set in Tasks contains only tasks from Projects B and C, then the total Amount is supposed to show 500 - and this is regardless of how many tasks are found, as long as at least one task from each project is included. Do you see the anomaly here?


The solution is to create a calculation field in the Tasks table that will divide the parent amount by the number of child records included in the found set. For this purpose, you will need a summary field sCount that counts any field in the Tasks table that cannot be empty, e.g. TaskID. The calculation then can be =
 

Projects::Amount / GetSummary ( sCount ; ProjectID )

where ProjectID is the foreign key to Projects (i.e. Case Number in your example).

Finally, define another summary field to total the calculation field and place it in the grand summary part.
 

[truelifeajf]

Ooooo... I don't get it! But I will implement it as best as I can and report back.

 

It sounds like you understood the question so I'm confident your solution will work.

 

BRB

[truelifeajf]

Ok, I've attempted to implement this but reading your answer a few times...

 

Firstly, the "retainer" field is in the "project" table. So, whether we're showing some or all of the tasks for a project in the found set, the project "retainer" will always (and should always) be the same amount.

 

So while I do see the anomoly you're pointing out, I just want the total of all retainers for all displayed projects, regardless of how many tasks are displayed.

[comment]

Not sure where you got stuck with this - does the attached work for you?

TotalParentAmount.fp7.zip

[truelifeajf]

Legend... thanks for that... helps a lot