One to Many Report

Getting your data onto paper - or the web - Discussing the reports features of Base

One to Many Report

Postby RMichalowski » Thu Apr 30, 2015 11:01 pm

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
Attachments
tables.docx
(14.34 KiB) Downloaded 71 times
Open Office 4.1 on Windows 7 / Ubuntu 14.04
RMichalowski
 
Posts: 6
Joined: Thu Apr 30, 2015 10:48 pm

Re: One to Many Report

Postby MTP » Fri May 01, 2015 4:41 pm

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.
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
MTP
Volunteer
 
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA


Return to Reporting

Who is online

Users browsing this forum: No registered users and 2 guests