show all values of subreport on same row 1

[johnhugh]

Hi,
I'm using Crystal Reports 10.
I have an item list report which shows an item number and manufacturer numbers.
The manufacturer numbers come from a subreport. One item number may have several manufacturer numbers.
I export the report to Excel and was wondering whether I could show all the item numbers in 1 row?

Instead of:
Item #|Manufacturer #1
|Manufacturer #2
|Manufacturer #3

I'd like to see
Item #|Manufacturer #1|Manufacturer #2|Manufacturer #3

Appreciate any help.

 

[lbass]

In the sub, create formulas like this:

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

//{@accum} - Add this to the detail section:
whileprintingrecords;
stringvar x := x + {table.manfacturer}+" ";

Then create a display formula for the subreport footer section:

whileprintingrecords;
stringvar x;

Suppress all sections within the sub except the report footer.

Make sure you attach the sub to the horizontal and vertical guidelines in the main report to ensure a correct export.

-LB

 

[johnhugh]

Thanks lbass.
Thats a good idea. Will give it a try.

 

[johnhugh]

Thanks again lbass.
That is working now.

I used the below code in the details section of my subreport.

whileprintingrecords;
stringvar ManufacturerNo;

IF ManufacturerNo = "" THEN
ManufacturerNo := ManufacturerNo + {ICIOTH.MANITEMNO}+" "
ELSE
ManufacturerNo := ManufacturerNo + " | " + {ICIOTH.MANITEMNO}+" ";


When exporting my report to Excel is it possible to have manufacturer numbers exported into seperate columns somehow?
Instead of xyz | abc in one column have them in 2 separate columns.

 

[lbass]

Replace the subreport footer formula with separate formulas, one for each mfg:

whileprintingrecords;
stringvar ManufacturerNo;
stringvar array eachMFG := split(ManufacturerNo," ");
if ubound(eachMFG)>=1 then
eachMFG[1]

For the second one, use:

whileprintingrecords;
stringvar ManufacturerNo;
stringvar array eachMFG := split(ManufacturerNo," ");
if ubound(eachMFG)>=2 then
eachMFG[2]

...etc., up to the maximum number of companies.

-LB

 

[RTag]

Item list with a subreport per item means that if you have 100 items in the list you will call the database 101 times.

You can join all manufacturer numbers on the database side (which will be the right way) or you can try to group the data by item and use the formulas by lbass (which will allow you to keep the processing in crystal reports). Both ways will require just one call to the database.

Of course both ways may be not applicable, but it will worth to check them if the report will show more than few items.

BTW, what is your database type?

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.