One to Many Duplicates Issue 1

[johnquinn]

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:

[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:

[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.

[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

 

[hilfy]

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

http://www.decisionfirst.com

 

[johnquinn]

hilfy,

Thank you! That worked perfect!

John