Summary fields (in Portal) based on different filters
I initially have 2 tables :
Project ---< Revenue
In my Revenue table, I have a Year field, different Revenue fields and also a field that gives more info on the year (first year, go-live, etc). For each project, I have 10 records in the Revenue table (1 record/year).
In my Project table I have my project name but also a couple of characteristics for that project (type, etc).
I have created a layout linked to my project table where I show the project details as also the revenue figures. The revenue "table" is created via multiple portals (one cell/portal) as I needed to have my columns (different revenues) as lines and lines (years) as columns.
What I would now need is to have the exact same table but which is showing me - cross project - the sum per revenue.
Also, I would need to apply different types of filters using the extra field of both the Project & Revenue Table in order to see specific sums. I.e. show only projects of type A, only the project years of "go live", etc.
I should also be able to filter not only on specific values but also on the "exclusion" of some values.
In order to have the sums correctly, I figured out that I should create a new table "Global" which has only one record and that all projects could be linked to that record. I tested this with a summary field and it seems to work.
But how do I set up the filters? I saw a couple of threads explaining that filtering in search mode does not affect the summary fields of a portal but what is than the best way to work?
Thanks in advance for your input