Skip to main content

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).
Steps
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.

Questions

  • 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?

 

 

Hi @Thomas Widmoser ,

We have thesame case reported for customer issue and it is still under investigations state, The issue we have is, create ad-hoc report category including tables Request and Place. Then create ad-hoc report and include request id from request table, place_id, name and external_reference etc fields from place table. When previewing, no data will be available in the ad-hoc report for fields coming from place table.

 

According to some investigations cause  was found as, request must full fill below requirement to give above mentioned data in the report,

request.place_id=place.place_id AND request.place_id_to_bill=place.place_id AND request.alt_place_id_to_bill=place.place_id AND request.place_id_req_issd=place.place_id

 

Once I run SQL query and created above relationship, I received data for above mentioned columns in the report. But, we must be able to receive data without above mentioned relationship, because, for example, always request.place_id_to_bill=place.place_id relationship is not true in actual business scenario.

I will update you upon PD team updates.

Furthermore, it is good to create custom reports, to receive data from different joined tables without any issue. Hope this helps.

 

Kind Regards,

Kalpani

Hi @Kalpani Dissanayake ,

thanks for your response. Therefore we will wait for the final feedback by PD.

regards,

Thomas

Hi @Thomas Widmoser 

 

I have received feedback from PD. This project involves redesigning some parts of the ad-hoc reports functionality and it will take some time. However, your issues will be resolved with this redesigning. Many thanks.

 

Kind Regards,

Kalpani.

Thanks! Therefore we will wait for the redesigning.

regards,

Thomas

Terms & ConditionsCookie settings