Creating report with many-to-many relation (crosstab?)
Dear fellow FM-users,
First off, let me ensure you that I've searched your forums (and the web) for a solution and spend quite some time experimenting in FM trying to get this done, but to no avail. So hopefully your expertise can point me in the right direction and perhaps it is something simple I'm overlooking.
First off, my table setup, I've enclosed (a part of) my ERD in this message. My question is regarding the requirements. Note that "requirements" and "requirements link" are the same table. In fact the DB works as follows:
-In a program people define the various requirements for that program. This will be stored in the "requirements" table with the corresponding program ID.
-Each time a person is added to a project (that is a record is created in the "partcipates" table. (project is a combination of a program with a zone), the "requirements filled" records are created with the 'filled' = "no" in a script. This is because I also want to show which requirements have not been filled, rather then have them missing. So for each partcipating person I get the full list of requirements for that program, with 'filled' either "yes" or "no". Displayed in a portal (requirement filled) on 'partcipates'
-Because of the extra table occurance (requirements link), I can create a report per program with for each requirement listed how many people have that requirements set to "no" or "yes". This is done through calculating fields in the requirements filled table: for example: Cal Filled Yes: If(filled="yes";1;0). Which are summed in the "requirements" table for example: sum Currently Yes: Sum(Requirements Filled::cal Filled Yes), evaluated from Requirements Link.
I hope you're still with me!
-Then the "requirements" table (not the requirements linked" table) is used to create the report, with "program" as a subsummary.
This setup works:
|# Yes||# No|
|1. Requirement A||10||3|
|2. Requirement B||5||8|
|1. Requirement C||20||3|
|2. Requirement D||19||4|
However I run into trouble when try to creatte a report with the same information, but with an addtional subsummary "project" or "area". I think this is because the requirements are linked directly to "program" and not to "project". In addtion: it is important that the Excel export takes the information with it. (so fields in Subsummary fields are tricky?)
What I've tried:
-Create additional TO for the tables: requirements, requirements filled, project and program. Link them in various orders even with x-joins. However the winning combination seemed not to be in there. The closed I've come is:
Project ----> Requirements Filled -----> Requirements
Then create a report based on "project", then add portals to the body, which displays "requirements", however the calculated fields do not seem to display the correct information. Furthermore, I'm afraid this is not the most elegant solution (portals on reports) moreover since I'd have to dynamically resize them or something to display a need report.
The only other thing I've came up with is to create 'report script', using a seperate table where are script stores values it calculated by looping through records and FoundCounts but that seems not be ideal either.
Just for the records, I could easily do it in SQL or something, but I'm struggling a bit with FM's way of evaluting. Or I'm missing something obivious here? Any pointers are welcome. I'd also be happy to send you (a clone of) the DB privately if needed.