Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

One to Many Duplicates Issue 1

Status
Not open for further replies.

johnquinn

Technical User
Dec 7, 2005
2
US
I've read that this same issue has been asked several times but I haven't been able to find the correct solution for my problem.

I have a one to many issue. I have a custom stored proc that returns patient, order, and assessment data. For each patient they can have multiple orders and multiple assessments. Each of the three tables are linked by the PatientVisitID. There is no link between the orders and assessments other than PatientVisitID. Each of orders and the assessments have their own unique ID's.

Here is what my raw data looks like:
Code:
[b][u]PatientAcct[/u]  [u]PatientName[/u]   [u]DischargeDate[/u]  [u]OrderDate[/u]  [u]AssessmentDate[/u][/b]
12345        John Smith     2013-01-08    2013-01-05    2013-01-06
12345        John Smith     2013-01-08    2013-01-05    2013-01-07
12345        John Smith     2013-01-08    2013-01-05    2013-01-08
12345        John Smith     2013-01-08    2013-01-07    2013-01-06
12345        John Smith     2013-01-08    2013-01-07    2013-01-07
12345        John Smith     2013-01-08    2013-01-07    2013-01-08

I've created a group for the patient and that took care of the duplicates. I then created groups on the OrderID and AssessmentID and placed the OrderDate in the OrderID Group and then placed the AssessmentDate in the AssessmentID group.

<GH1>Patient Name with PatientAcct, PatientName, and DischargeDate
<GH2>OrderID with OrderDate
<GH3>AssessmentID with AssessmentDate
<Detail>suppressed
<GF3>suppressed
<GF2>suppressed
<GF1>blank

So here is what the data comes out like in the report:

Code:
[b][u]PatientAcct[/u]  [u]PatientName[/u]   [u]DischargeDate[/u]  [u]OrderDate[/u]  [u]AssessmentDate[/u][/b]
12345        John Smith     2013-01-08    
                                          2013-01-05   2013-01-06
                                                       2013-01-07
                                                       2013-01-08
                                          2013-01-07   2013-01-06
                                                       2013-01-07
                                                       2013-01-08

Here is what I would like it to look like and I can't figure it out for the life of me.

Code:
[b][u]PatientAcct[/u]  [u]PatientName[/u]   [u]DischargeDate[/u]  [u]OrderDate[/u]  [u]AssessmentDate[/u][/b]
12345        John Smith     2013-01-08    
                                          2013-01-05   2013-01-06
                                          2013-01-07   2013-01-07
                                                       2013-01-08

Thank you in advance!!

John
 
You can't do this with both orders and assessments in the same query. The only way you're going to get the data in the format that you want is to use a subreport for either Orders or Assessments. You want to select the one that will always have the fewest number of records and then do something like this:

- Keep the patient data in the patient group header.
- Add a patient header section and put the subreport in that section in the correct column.
- Turn on "Underlay Following" for this new section.
- Keep the dates for the data that's not in the subreport in the section where it is currently located.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top