Print Page | Close Window

concatenate rows

Printed From: Crystal Reports Book
Category: Crystal Reports for Visual Studio 2005 and Newer
Forum Name: Writing Code
Forum Discription: .NET programming API, report integration
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=2393
Printed Date: 07 May 2024 at 11:13pm


Topic: concatenate rows
Posted By: mdmendoza
Subject: concatenate rows
Date Posted: 20 Feb 2008 at 8:43pm
does anyone knows how to concatenate rows into 1?

example:

ID No        Name            Time In  
1               Greg               9:00
1               Greg              10:00
2               Vince               9:30
3               Tom                 6:00


i expect to have this result:

ID No        Name            Time In  
1               Greg           9:00,10:00
2               Vince               9:30
3               Tom                 6:00

thnx!


Replies:
Posted By: hilfy
Date Posted: 21 Feb 2008 at 6:22am
This should work.
 
In your report, group on name or id number (whichever order you want your data to appear.)
 
Create a formula called TimeIn:
 
Stringvar TimeIn; 
if previousIsNull({table.groupfield}) or ({table.groupfield} <> previous({table.groupfield})) then 


  TimeIn := {table.TimeInField};


else


  TimeIn = TimeIn + ", " + {table.TimeInField};


TimeIn
If TimeIn is not a string field, you'll need to convert it to a string in your formula.
 
You then setup your data in the group footer using the formula instead of the field.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: mdmendoza
Date Posted: 21 Feb 2008 at 4:41pm
the else function is highlighted and gives an error message of "The remaining text does not appear to be a part of the formula." 

Posted By: BrianBischof
Date Posted: 21 Feb 2008 at 5:03pm
Remove the semi-colon from the line right before it.

-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

Posted By: mdmendoza
Date Posted: 21 Feb 2008 at 6:03pm
@BrianBischof

thx for the help... now i dont have errors.

BUT
the output seems to have a problem... it doesn't show both time

Posted By: BrianBischof
Date Posted: 21 Feb 2008 at 6:10pm
which time does it show? The first one or the last one? 

-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

Posted By: mdmendoza
Date Posted: 21 Feb 2008 at 7:24pm
it shows the second time of the employee with 2 time logs... how can i show the first time?

Posted By: BrianBischof
Date Posted: 21 Feb 2008 at 7:29pm
Well, try this instead. In the group header, reset TimeIn to "". 

Global StringVar TimeIn := "";

Then in the Details section use the formula
Global StringVar TimeIn;
TimeIn = TimeIn + ", " + {table.TimeInField};



-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

Posted By: mdmendoza
Date Posted: 21 Feb 2008 at 9:44pm
the first formula u told me worked... but the problem is, the output looked like this:

ID No        Name            Time In  
1               Greg                9:00
1               Greg           9:00,10:00
2               Vince               9:30
3               Tom                 6:00

instead of:

ID No        Name            Time In  
1               Greg           9:00,10:00
2               Vince               9:30
3               Tom                 6:00

thx again..

Posted By: BrianBischof
Date Posted: 21 Feb 2008 at 10:28pm
Ah, I see. You need to put your data in the group footer section instead of the Details section. Dell mentioned it at the end, but I guess it wasn't clear enough. And you need to hide the Details section so that you only see the Group Footer. Give it a try and let me know if that worked for you.

-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

Posted By: mdmendoza
Date Posted: 21 Feb 2008 at 10:50pm
@BrianBischof

now im having this output:

D No        Name            Time In  
1               Greg                    ,10:00
2               Vince               9:30
3               Tom                 6:00

im really thankful for your patience with me... just this little problem and then im done...

Posted By: mdmendoza
Date Posted: 22 Feb 2008 at 12:57am
@BrianBischof

Thx for all the help... I made my formula running... thx for teh help!

@Dell

Thx for your formula... I hope you'll help more newbies like me. Thx!


Print Page | Close Window