1 Reply Latest reply on Apr 23, 2012 4:52 PM by philmodjunk

    Hierarchical Report with data from multiple related tables



      Hierarchical Report with data from multiple related tables


      I have a FileMaker Pro 12 database model that contains many related tables.  One-to-many and many-to-many relationships. In order to create a report that contains nested data from multiples tables I execute the following procedure:

      1. Open Excel

      2. Engage the ODBC Client driver from Excel to connect to FileMaker Pro 12

      3. Go to the Microsoft Query

      4. Execute a complex JOIN query

      5. Save the extracted data into the Excel workbook

      6. Go back to FileMaker and import the Excel file into my Report table

      7. Run the FileMaker report (multiple Sub-Summary sections)

      Is there a better way (more native to FileMaker) to create a nested report using a complex JOIN query?

      This is very basic report generation task in SQL Server 2008 SSRS.

        • 1. Re: Hierarchical Report with data from multiple related tables

          Can't really say without knowning the precise design of your tables, relationships, and report.

          It sounds like you've encountered a classic and long standing limitation on what filemaker can do with related data in a report, but it also could be completely unecessary as it depends on your relational structure and your report design.

          In cases where such a report table is needed, it's usually possible to use Import Records and/or looping scripts to build the records in the report table--that's no real improvment here unless you can get sneaky and update the denormalized data via script triggers during data entry and editing--again an option that is not always possible nor practical in every situation.