Using XI: Getting data on one row 1

[meltingpot87]

Hi everyone! I am too new to CR but i know my way around it a little. I tried this method, and it don't seem to work for me. I need to get data on one line. For example, for my data.. I have this:
Child_ID
Child_BirthDate
Child_LastName
(this data is on the same table)

Referral_Num
(this data is on another table)

For referral_num, there are many referral numbers a child can have (up to 5). I need to display them all on one line.

The data comes out like this:

Example:

LastName Child_ID Child_BirthDate Referral_Num
Smith 001 4/5/2010 1
Smith 001 4/5/2010 2
Smith 001 4/5/2010 3
Smith 001 4/5/2010 4

I want the data to come out like this instead:

LastName Child_ID Child_BirthDate Referral_Num Referral_Num
Smith 001 4/5/2010 1 2

Any recommendations or suggestion? Any form of suggestions or recommendations is greatly appreciated!

 

[lbass]

Insert a crosstab that uses LastName, Child ID and ChildBirthdate as row fields, Referral_Num as the column field, and max of referral num as the summary field. Then go into the customize style tab and check "suppress subtotals for the row fields. Place the crosstab in the report header or report footer.

-LB

 

[meltingpot87]

Thank you so much for responding. I am trying the Crosstab method, but I would like to know how to do the report the regular way. I need to customize some fields as the report progresses. Any suggestions?

 

[lbass]

A crosstab is as much a "regular" way as any other approach.

Anyway, you could also handle this by grouping on the childID (or a concatenation of Name and ID), and then create these formulas:

//{@reset} for the group header:
whileprintingrecords;
stringvar refno;
if not inrepeatedgroupheader then
refno := "";

//{@accum} to be placed in the detail section and suppressed:
whileprintingrecords;
stringvar refno := refno + totext({table.refno},0,"")+" ";

Add as many spaces as you want between refnos. Remove the totext(,0,"") if the refno is already a string.

//{@Display} to be placed in the group footer:
whileprintingrecords;
stringvar refno;

Drag the other fields into the group footer also, and then suppress the group header and detail section.

-LB

 

[meltingpot87]

This is exactly what I need! THank you so mych LB.
Although, some of the records are repeating more than once.

Example:
Smith 3 2 2

Smith only has 3 and 2

 

[lbass]

You have to use the display formula in the group footer, not the {@accum} formula (if that is what you are doing).

Otherwise, if you unsuppress the detail section, are refno's repeating? If so, change the {@accum} formula to:

//{@accum} to be placed in the detail section and suppressed:
whileprintingrecords;
stringvar refno;
if instr(refno,totext({table.refno},0,"")) = 0 then
refno := refno + totext({table.refno},0,"")+" ";

If referral numbers can be greater than 9, you would have to modify this last approach a bit.

-LB

 

[meltingpot87]

Thank you so much! This works perfectly!!

 

[meltingpot87]

Hey LB, if you could be kind enough to help me with one more little detail, I highly appreciate it. I've been working on this for hours.

The reports work perfectly, generating reports the way I want it. Although, after looking closely at the data after printed, I notice some of the records were not showing a data that is associated with child_ID. For example, some children has more than 1 team_id. Here's an example

team_id child_id referral_num service_num
200 001 1,2 3,4

This child has more than 1 team_id, and a referral_num is associated with a service_num

The desire data is:

team_id child_id referral_num service_num
200 001 1 3
300 001 2 4

 

[lbass]

This is not one more little detail. This is an entirely different problem. Look at your last sample--you are now showing that the reference numbers are in fact already in the same line, and you want them on separate lines and you have added another field (service_num) that isn't represented in the sample or explained.

-LB

 

[meltingpot87]

Yes, I started to add in more fields to better understand what the data is reporting. As for service_num, I did the same thing with service_num as you gave me for referral_num.
To better understand the data a little more, if I can explain, a referral_num is associated with a specific service_num.
The command given by you, works perfectly, but the desired data I need now is where the referral_num shows the associated service_num on each line, along with the associated team_id (since each child can have many team ids)

So far, the report is showing as mentioned aboved:
(where referral_num 1 is associated with service_num 3)

team_id child_id referral_num service_num
200 001 1,2 3,4

 

[lbass]

Still not clear. Please show what data are you starting with (before my formulas) and the results you want to see. In your initial post, you said you wanted referral numbers to appear in one line, and now you seem to be saying they already are. Look at your previous post and what you identified as "desired data".

-LB

 

[meltingpot87]

I do see where I have confused you now, my fault. Well, before adding in your formulas, here is an example of the data I have.

table1:
team_id
child_id
last_name

table2:
referral_num

table3:
service_num

Here's the report that it outputs without any formulas:

teamid childid last name ref_num ser_num
200 001 Smith 1 2
200 001 Smith 1 4
300 001 Smith 5 6


Here's the report when I put in your formulas:

teamid childid last_name ref_num ser_num
200 001 Smith 1,1,5 2,4,6

(the report isn't showing that Smith has another teamid which is "300")

So, what I need the new report to show is:
1. data being pull per teamid
2. data with the associated ref_num to be on the same line as ser_num

teamid childid last_name ref_num ser_num
200 001 Smith 1,1 2,4
300 001 Smith 5 6

 

[lbass]

You need to add a second group on team ID and move the reset formula to the teamID group header, and place the display formula in the team ID group footer and then suppress the other sections.

-LB

 

[meltingpot87]

Hey Lb, is there anyway ref_num and ser_num records be displayed as individually for example..

ref_num_1 ref_num_2
1 2

We must export the data to excel and have those records in separate columns instead of the same columns.

 

[meltingpot87]

I posted a Thank you post May 4th and I guess I never hit submit.

Thank you much LB from the last post, it worked perfectly!! You are so clever!