To access a sub total from a summary field like you can get in a sub summary layout part. Use the GetSummary function. The "break field" parameter is the same field that you would select as the "when sorted by" field in the sub summary layout part. Just like the sub summary part, the records have to be sorted by the break field and the total is determined by what records are in your current found set.
Oh Phil....... so close.
I got it to display but the summary is still not correct.
In the Authorized headcount table I have several job titles. Each one has an authorized headcount. I created a summary field to Total the authorized headcount.
In the People table I created a calculation field to GetSummary ( totalHeadCountAuth, workcenter) hoping to sum the authorized headcount based on the workcenter. I based the breakfield on the workcenter in the Headcount table, not the current work center for the people in the people table.
After I based the break field on the headcount table, the get summary calculation field is now visible in the sub summary part that is sorted by workcenter.
But..... the total that is visible is still only the first job title authorized headcount listed in the headcount table for that work center. I can't seem to get it to total the headcount for multiple job titles in one work center. The number in the far right is the authorized headcount for the individuals specific job title.
Just like placing a summary field in a sub summary part to show a subtotal only works for a summary defined in the layouts table--not a related table. GetSummary must be set up to work with summary fields in the same table as your report layout's table. It only works off the current found set and sort order. You won't get accurate results when you then attempt to access a field that uses that function from the context of another table.
I'm not sure that I have your tables/relationships correct:
AuthHeadCounts::WorkCenter = Employees::WorkCenter AND
AuthHeadCounts::Job TItle = Employees::JobTitle
Is that correct? If not, please upload a screen shot of the relevant table occurrences.
I think I have gone down a rabbit hole so I am going to try creating portals.
I have a portal with just one job title in it (on the right in the image). I have another portal with the same job title as its filter and only one row and a summary field to tell me the total number of that job title (on the left in the image)
I have another field in the same table with the total authorized for that job title. For example. I have a job title of manager. I also have a summary field in the table of the total number of managers authorized.
How can I subract the summary field that is filtered in the portal from the total authorized? I want the Delta
You cannot access the subtotal in your filtered portal in your calculation.
Please post or confirm the relationships between the tables involved.