BusinessObjects Board

How to display two detail lines as one

building reports crystal reports legacy
#1

In a report I am working on I have one detail line that displays values from two tables.
Table OR contains the date time stamp I need
Table OB contains the rest of the information I need.
(These two tables are linked by a Set_Id field.)
Table OB contains different types of data depending on the OB_Id field.
If OB_Id = 1 then OB_fldA = weight.
If OB_Id = 2 then OB_fldB = appetite_flg.
Below I have tried to type up a sample of the report. I have it grouped by the OR_DtTm.
I would like to get the report to look like there is one line that contains both the weight and the appetite_flg and eliminate any lines that have weight info and no appetite_flg to go with it (like the 3rd one down with the time of 3:45:30).

How it currently looks:

Date        Weight         Flg         OR_DtTm
6/22/2010   64.8                    6/22/2010 3:44:31PM
6/22/2010    0.0            N       6/22/2010 3:44:31PM
6/22/2010   64.0                    6/22/2010 3:45:30PM
8/3/2010    64.0                    8/3/2010 9:09:01AM
8/3/2010                    Y       8/3/2010 9:09:01AM

How I would like for it to look:

Date         Weight         Flg         OR_DtTm
6/22/2010    64.8            N        6/22/2010 3:44:31PM
8/3/2010     64.0            Y        8/3/2010 9:09:01AM

I hope this makes sense. Any suggestions on how to do this? TIA!

RenaG (BOB member since 2011-04-11)

#2

You’ll need two copies of your OB table to do this. In the Database Expert when you add a table that is already in the report, Crystal will tell you that the table is already in the report and will ask if you want to alias it. If you alias it, it will appear in the table list with ‘_1’ at the end. For this example, we’ll use OB for the weight data and OB_1 for the appetite data.

  • Keep the link from OR to OB.
  • Link from OB to OB_1 (this must be an inner join).
  • In the Select Expert, add criteria for {OB.OB_Id} = 1 and {OB_1.OB_Id} = 2.

You should now be able to get all of your data for each OR_DtTm on one line using the weight field from OB and the appetite field from OB_1. Because you have an inner join from OB to OB_1, weight will not appear without appetite.

-Dell

hilfy :us: (BOB member since 2007-04-16)