Print Page | Close Window

Combine Rows

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=13718
Printed Date: 17 May 2024 at 9:31pm


Topic: Combine Rows
Posted By: KevV
Subject: Combine Rows
Date Posted: 12 Jul 2011 at 7:13am
I have a report that is grouped by route numbers and have the route number listed in the header. I then have stores listed in the detail section below it that are on that route like:
 
 
 
Route: 33054
 
store:
101
102
103
104
 
Route: 33055
 
105
106
107
 
 
What I want is for it to look like:
 
Route: 33054
 
101 102 103 104
 
Route: 33055
 
105 106 107
 
 
 
Thess fields can all be in the header or detail section it doesn't really matter. The store field is not always the same and can have anywhere from 1 to 18 stores. I tried the "Format with Multiple Columns" in the deatil section expert but it gave me issues if I tried to put any other field in the detail section or if I moved it away from the right side of the page so I really need a formula or something where I can put the store field in the center or left side of the page and do the "Underlay" feature or add other fields in to the same section
 
Thanks
KevV
 
33054


Replies:
Posted By: DBlank
Date Posted: 12 Jul 2011 at 7:30am
if you can place them in a footer you can use 3 variable formula
 
GH
whileprintingrecords;
shared stringvar X:=""
 
Details
whileprintingrecords;
shared stringvar X:= X + {table.field} + "   "
 
G F
whileprintingrecords;
shared stringvar X;
supress the g header formala and details section

Posted By: KevV
Date Posted: 12 Jul 2011 at 10:24am

That worked great. I now found on some of the routes there maybe more than one order per store. I need to have it just show the store once. I tried the "suppress if duplicated" in the format field  but it didn't do anything. Is there a way to supprress the duplicates?

Thanks
KevV

Posted By: DBlank
Date Posted: 12 Jul 2011 at 11:25am
whileprintingrecords;
shared stringvar X:= X + (if previous({table.field}) = {table.field} then "" else {table.field} + "   ")

Posted By: KevV
Date Posted: 12 Jul 2011 at 11:36am
Where does that formula go?
 
Thanks
KevV

Posted By: DBlank
Date Posted: 12 Jul 2011 at 11:37am
replaces the details formula from earlier

Posted By: KevV
Date Posted: 12 Jul 2011 at 11:40am
Thats what i thought but it didn't do anything. I then tried replcing the footer formula and it took the last one away even if there were three or more.
 
Thanks
KevV

Posted By: KevV
Date Posted: 12 Jul 2011 at 11:43am
Not sure what I had in there but I tried it from scratch again and it worked. Thanks for the help.
 
KevV

Posted By: KevV
Date Posted: 13 Jul 2011 at 9:34am
Is there any way to sort the stores depending on a value in another field in the database?
 
Thanks
KevV

Posted By: DBlank
Date Posted: 13 Jul 2011 at 9:50am
maybe but keep in mind that the group sort supercededs other sorts
TOP N might be something...
what is your requirement and what data is needed for it?

Posted By: KevV
Date Posted: 13 Jul 2011 at 10:09am

I am not quite sure on the question. The orders are loaded in to trucks in reverse sequence of the way they are delivered. There is a field in the databse called "stop" with values of 5 10 20 30 and so on.

 
Thanks
KevV
 

Posted By: DBlank
Date Posted: 13 Jul 2011 at 10:48am

IF I understand you correctly,

Go to the sort expert (The A - Z connected by arrows button).
you should have a group 1 route number already in the sort fields box
in the available fields select the "stop" field and move it to the sort and make it desc or asc based on your need.
Does that do it?

Posted By: KevV
Date Posted: 13 Jul 2011 at 11:09am
I do have the route as a group and it is sorted by it. If i add the stop field it reruns the report and it goes blank like if a join is incorrect but it is in the same table as most of the other fields are in. Once it runs even if I take the sort out it still comes back as a blank report.
 
Thanks
KevV

Posted By: DBlank
Date Posted: 13 Jul 2011 at 11:12am
unsuppress your details and see if the data is still in the report just getting hidden in an unexpected way

Posted By: KevV
Date Posted: 13 Jul 2011 at 11:21am
Nothing there. One thing that is strange is when I put the sort in it has data in the report footers but when I switch to the second page it as if I want to refresh data or use saved data and no matter which one I click it reurns the report and that is when all the data disappears. The only way to get the report to show anything again is to close the report and reopen it.

Posted By: DBlank
Date Posted: 13 Jul 2011 at 11:33am
you have it set to save the data with the report.
When you close it is is pulling in the saved data.
Close it, open it so it appears fine, make no changes , hit F5 and OK to refresh the report. Does your data disappear?
I think it will. Adding the new field does the same thing so my guess is somehow your source is lost.

Posted By: KevV
Date Posted: 13 Jul 2011 at 11:36am
I dont have it set to save the data with report. When I open the report it just has the design tab and I have to run it to get the data but it does populate. Once I put the sort in and try to change to the next page it disappeaers.

Posted By: DBlank
Date Posted: 13 Jul 2011 at 11:46am
Really weird....never heard of that one. so if you go back to page 1 it is all gone too, correct?

Posted By: KevV
Date Posted: 13 Jul 2011 at 11:46am
yep

Posted By: KevV
Date Posted: 13 Jul 2011 at 12:04pm
The thing I find the strangest is that when I put the sort in it drops a few of the routes but most of the data stays when it runs but if I try to go to another page in the report then I lose everything. If I unsuppress the detail section I can put the stop field in it and run the report and it runs fine and shows the stop and if I try to add it to the sort it dosen't do anything.


Print Page | Close Window