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.