Create a report from multiple related tables
I have a database which contains Patient details. It has mainly three related tables:
1. Slide Image which contains patient information with MR no. unique to each patient.
2. SurgPath table which has many to one relation with Slide Image table. One patient can have multiple surgical path.
3. Image Path table which has many to one relation with SurgPath table. One SurgPath can contain multiple imagepaths. For eg.,
SlideImage SurgPath ImagePath
A1 AA12 path//abc.jpg
I would like to create a report showing the data from these tables. Currently I created a layout on SurgPath table, with related fields from SlideImage and ImagePath tables. In this case, I can see only the first one imagepath entry from ImagePath table. I can use portal in this case to see all imagepaths of a particular SurgPath entry. But, It doesn’t work in my case as I need to set the number of lines in portal and sometimes a SurgPath doesn’t have any imagepath, so it occupies unnecessary space in the report, which looks weird.
· Is there a way I can show a report looks like a report generated from a single table? I mean, multiple imagepaths related with same MR no. and Surgpath contains duplicate entries from SlideImage and SurgPath tables, which is not possible in case of portal.
· Do I need to create a temporary table which merges all the data from related tables? How to do that?
· If I need to use the portal, how I can show the report without showing the boxes/lines of portals?
I saw many posts regarding this issue, but I couldn’t understand the solutions. Please help me in this. Thanks in advance!