When creating ad hoc reports, we understood that we need to consider parent child relationships when adding fields. E.g.: If we are using Request, Task and Place, the following tables are needed to create the adhoc report.
The parent table will be REQUEST, and primary key will be Request_id. Hence Request_id from Request table should be added first.
Next, the columns of TASK and PLACE tables, which are child tables of REQUEST table, need to be added. Note that each table should be mapped directly to the parent table with the primary key (request_id).
1. Add the Request_id field from REQUEST.
2. Add the Task_id field from TASK.
3. Preview. Data will populate
4. Add more fields from TASK table.
5. Add fields from place table (as place_id of PLACE table is mapped with place_id of REQUEST table)
Via that guideline most columns / tables can be added without getting the error message (see https://community.ifs.com/service-fsm-touch-apps-document-management-mwo-mro-pso-43/ad-hoc-report-categories-7276)
Now the issue is still that many columns are not filled with the corresponding data when exporting to excel (or even in preview). In our Test-Examples, many columns don't display the expected data.
Our approach is typically to search via sql query tool for the corresponding data fields, create the necessary joins and then try to rebuild that in the ad hoc reports.
- Why are some columns not filled with data when adding field by field in the ad hoc report?
- Are ad hoc reports meant to support that sql based approach - meaning joining multiple tables and creating exports for joined data?
- Is there any technical documentation of ad hoc reports available, which we could make use of?
- Or is it a must that those reports need to be created in visual Studio first and there is no simple excel export for joined tables?