1. Hope you are familiar with BAM Reports architecture and how it works. Its pretty straight forward.
2. First you create a DataSource to any external Database giving the db host, port, sid, username/password. This database contains like Tables, Views, etc etc.
3. You create a DataObject that References any component of this database like Table, View etc. So you have a SQL that joins multiple tables and fetches data. Simply Create a View for that SQL Query on database side like database View. Then create a BAM Data Object for that View. It shows all Columns from that View. Simply Rename them if you want or just leave as it is. Just run the View and it shows all the data records. Remember this is dynamic. There is no need to refresh or trigger. Say if the table had a new row, the View will just show that new row.
4. Now create a Report (Web or excel format) for the above Data Object. Report by default will show all the column from Data Object. Pick the columns you want to add to that Report.
Run the Report. Data in Report is 100% Dynamic. If backend tables had new rows or deleted rows, just run the Report and you will see the results.