This is also referred to as a "cross tab" report where the rows and columns of data come from different groups of records.

If I understand your example correctly, the intersection of year 1, row 1 is the total of amount from all invoices with a YearOfOperation of year 1 and in an OPEX category of 1. (I have no idea what OPEX is.)

If that's correct, you can set up a third table OpexCategories with these fields:

CategoryOfOpex, gYear1, gCompanyID, cYear2, cYear3...cYear5, cSum1, cSum2 ... cSum5

You'd create 8 records in this table with values of 1...8 in the CategoryOfOpex field.

The two fields that start with g will have global storage specified.

cYear2 will be a calculation field defined as gYear1 + 1, cYear3 will be defined as gYear1 + 2 and so forth through cYear5.

Before we can define the cSum1...cSum5 fields we need to set up the needed relationships:

OpexCategories------<Invoices|year1, ----<Invoices|year2, on thru Invoices|Year5

Invoices|year1 thru Invoices|year5 are additional Tutorial: What are Table Occurrences? of the Invoices table.

The match fields for OpexCategories to Invoices|Year1 would be:

OpexCategories::gCompanyID = Invoices|year1::_fkCompanyID AND

OpexCategories::gYear1 = Invoices|year1::YearOfOperation

The match fields for the OpexCategories to match to Invoices|year2:

OpexCategories::gCompanyID = Invoices|year2::_fkCompanyID AND

OpexCategories::cYear2 = Invoices|year2::YearOfOperation

and this pattern would continue for the other 3 relationships.

Then you can define cSum1 as a calculation field:

Sum ( Invoices|year1::Amount )

The other 4 calculation fields would follow the same pattern: Sum ( Invoices|year2::Amount ), Sum ( Invoices|year3::Amount ), etc.

You can then set up a list view layout with gCompanyID and gYear in the header with the OpexCategory and 5 cSUM fields in the body to form the 8 rows of yearly totals arranged in 5 yearly columns. gCompanyID can be formatted with a value list of Company ID's and company names. So that you can select the year for year 1, select a company and then the layout updates to show totals for that company over the 8 categories and over 5 years of data.

Note: There's a simpler approach using summary fields and filtered portals that can produce the same totals, but then you can't chart from that data like you can using this approach.

Note: This is the "nonSQL" method for getting what you need. If you have a working knowledge of SQL, you can dispense with the 5 added relationships, the cYear fields and define the cSum fields as calculations using the Sum in SQL queries.