Returning a values from the small column/field to the same row

[rmttbone]

I have to write a report that wants three ssn's, dob's, etc. for each person on one row. The returned values are to be inserted intoo columns named DOB1, DOB2, etc. The individual values are contained in their own row, in a table. Each has a unique ID and a foriegn key to link them to the person. Does anyone know how what type of formulas that will work for this problem?

 

[satinsilhouette]

Sounds from your description that you don't need a formula. If each dob is in its own row, contained in its own table, and you need it to appear in one row per person in the report, that you may have to bring that table in multiple times, alias the tables names, and then link on the appropriate fields. Then pull in your information all in a row.

You will get a warning when you bring the table in more than once, telling you it already exists in the table line up, go ahead and tell it you want to alias it. You can right click on the table and rename it after you have brought it in.

 

[dgillz]

Sounds like you need to format the report for multiple columns, like a mailing label style report.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"making predictions is tough, especially about the future" - Yogi Berra

 

[rmttbone]

I tried that, but how do you get it to select the next value, nthlargest, etc.?

 

[dgillz]

I am unfamiliar with nthlargest. Are you wanting to sort thedata in a particular way?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"making predictions is tough, especially about the future" - Yogi Berra

 

[rmttbone]

Yes, here is a sample of what I am trying to complete:

Data (located in three different rows in the table):
ID DOB
1 01-01-1959
2 02-02-1959
3 03-03-1959

data needed returned in one row in my report:

client_id Name dob1 dob2 dob3
1 John Doe 01-01-1959 02-02-1959 03-03-1959

I sort to get them in the correct order. And, I have tried minimum ({client_dob.dob}, {client_dob.client_ID}) for my dob1, and nthsmallest (2, {client_dob.dob}, {client_dob.client_ID})for dob2 or nthlargest (2, {client_dob.dob}, {client_dob.client_ID})for dob2, and maximum ({client_dob.dob}, {client_dob.client_ID}) for dob3.

The problem I encountered is when I use nthsmallest (2, {client_dob.dob} to obtain dob2, it displays the same value as minimun (dob1), and the opposite occurs when I use nthlargest (2, {client_dob.dob}, {client_dob.client_ID}) to obtain dob2, it returns the same as maximum (dob3).

 

[lbass]

This looks like a parent child relationship. How do you know those DOBs belong to John Doe? If the John Doe field or another field repeats itself for this set of DOB, then group on that field, and use a variable to accumulate the results in the group footer:

//{@reset} to be placed in the group header:
whileprintingrecords;
stringvar dob := "";

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar dob := dob + totext({table.dob},"MM-dd-yyyy")+", ";

//{@displaydob1} for the group footer:
whileprintingrecords;
stringvar dob;
stringvar array dobx := split(dob,", ");
if ubound(dobx) >= 1 then
dobx[1]

//{@displaydob2}:
whileprintingrecords;
stringvar dob;
stringvar array dobx := split(dob,", ");
if ubound(dobx) >= 2 then
dobx[2]

//etc.

-LB

 

[Turkbear]

Hi,
Please satisfy my idle curiousity:
How can 1 Client have 3 DOBs ? ( or 3 SSNs legally).







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[rmttbone]

They are persons that use alias DOB's, SSN's, etc.

 

[rmttbone]

I am using crystal 10, and I am having trouble grouping the results of a formula. I need to count the number of occasions that an event takes place, and I need to group it in the following groups:

1,2,3,or 4

I used this select case statement to create a value:
numbervar tests;

select count({UAOrders.OrderID},{UAOrders.Client_rsn})
case 1 to 1 : tests:=1
case 2 to 2: tests:=2
case 2 to 3: tests:=3
case 4 to 1000000: tests:=4

default: tests:=0

OrderID is the unique ID and is created when a new sample is obtained. Client_rsn is the unique ID for the client that gives the new sample.

I named the formula Frequency, and it will display in the detail section of my report when I drag the formula from the Field Explorer. But, the Frequency formula is not visible/accessible in the group expert or sort expert.


Thanks in Advance

 

[lbass]

Please start a new thread since this is a new topic.

-LB