4 Replies Latest reply on Feb 21, 2012 11:20 AM by philmodjunk

    Creating report with many-to-many relation (crosstab?)



      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  
      Program A
      1. Requirement A 10 3  
      2. Requirement B 5 8  
      Program B      
      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.


        • 1. Re: Creating report with many-to-many relation (crosstab?)

          Perhaps, to clarify, the following is what should happen:

          -For each project, lookup (through program) which requirements belong to that project.

          -For each of these requirements calculate how many people (participates) have the status "yes" "no" and "N/A" in that project.

          -Next project

          I've this feeling it's either gonna be this script (as written here). I should use some kind of X-join (to find all the requirements for that project), and move calculations to the project-table.

          • 2. Re: Creating report with many-to-many relation (crosstab?)

            Is this the report you want?

            Program X

              # Yes # No  
            Program A
            1. Requirement A 10 3  
            2. Requirement B 5 8  
            Program B      
            1. Requirement C 20 3  
            2. Requirement D 19 4  

            Program Y

              # Yes # No  
            Program C
            1. Requirement E 10 3  
            2. Requirement F 5 8  
            Program D      
            1. Requirement G 20 3  
            2. Requirement H 19 4  

            In other words, You want the same report, but grouped by program?

            If so, it would seem you can base your layout on Requirements and add a sub summary part "when sorted by" and specify either the RequirementsID field or a name field from the related Program table.

            • 3. Re: Creating report with many-to-many relation (crosstab?)

              Dear PhilModJunk,

              Thank you for your quick reply! You are correct. Just to clarify we call the toplevel "programs" and the sublevel "projects". And I've indeed just managed to create a layout like you say, but with the following properties:

              -Based on: Requirements Filled

              -Subsummary 1: Program ID

              -Subsummary 2: Project ID

              -Subsummary 3: Requirement ID (and filled this with additional fields)

              -Body: empty (hidden)

              But the problem now, that if I want to export this report to excel, It's empty. Any idea how to work around it?

              Futhermore, if I base my layout on Requirements instead of Requirements Filled, it will not list all the requirement for each project. (probally because these records do not exist for project in the lookup: project -> program -> requirement. It will only show the requirements for the first project, and omit other projects with similiar requirements. 

              So for now it seems to work, expect for 'save to excel' function, since the body is empty.

              • 4. Re: Creating report with many-to-many relation (crosstab?)

                Aren't Requirements and Requirements Filled occurrences of the same table?

                I specified Requirements on that understanding. If so, you should be able to perform a find to pull up the same records from a layout based on either table. I specified Requirements so that you could include data from the related Project table on your layout and/or as part of your sort order.

                What options are you using to export to excel? Are you using the "group by" options to get your sub summary data exported as single rows in the excel file? Make sure your "group by" selections match your sub summary part definitions.