Page 1 of 1

One to Many Report

PostPosted: Thu Apr 30, 2015 11:01 pm
by RMichalowski
I have 3 tables, Main, Sub1 and Sub2. The main table has a one-to-many relationship with each of the other two. It is set up this way because the number of Sub1 and Sub2 entries will vary for each Main entry. IE: I may have 1 Sub1 entry and 5 Sub2 entries for a main entry. I can combine my tables via view or query and the data gets laid out as below.
Main | Sub1 | Sub2
MRecord 1 | Record 1 | Record 1
MRecord 1 | Record 1 | Record 2
MRecord 1 | Record 1 | Record 3
MRecord 1 | Record 1 | Record 4
MRecord 1 | Record 1 | Record 5
MRecord 2 | Record 2 | Record 3
MRecord 2 | Record 3 | Record 3
MRecord 2 | Record 4 | Record 6

I am trying to create a report to display this data. I am not sure if there is a way to better filter the data before it goes into the report or if the filter should in fact take place in the report. I can group the main records, but I can’t find a way to prevent the “detail” records from repeating. My report looks like so:
MRecord 1
Sub1 = Record 1 Sub2 = Record 1
Sub1 = Record 1 Sub2 = Record 2
Sub1 = Record 1 Sub2 = Record 3
Sub1 = Record 1 Sub2 = Record 4
Sub1 = Record 1 Sub2 = Record 5
MRecord 2
Sub1 = Record 2 Sub2 = Record 3
Sub1 = Record 3 Sub2 = Record 3
Sub1 = Record 4 Sub2 = Record 6

I want it to look like this:
MRecord 1
Sub1 = Record 1 Sub2 = Record 1
Record 2
Record 3
Record 4
Record 5
MRecord 2
Sub1 = Record 2 Sub2 = Record 3
Record 3 Record 6
Record 4

Please note Sub2 = Record 3 does repeat, but under a different MRecord.
I hve the same information in the attached document with better formatting.
May the person that helps me find great rewards! :o

Thank You
Ron

Re: One to Many Report

PostPosted: Fri May 01, 2015 4:41 pm
by MTP
Are you using the built-in report wizard or have you installed the extension Oracle Report Builder? Oracle Report Builder definitely allows multiple groups, each with their own heading (make sure to turn the headings on for each group), which sounds like what you want.