export to excel all on the same row

[ericb123]

Using CR11 & Sql2k5, I have a report with a sub-report, all on one row of my report. This looks great in Crystal, but when I export the report to excel-data only (which is mostly what the report is used for) the sub-report comes out on a separate row.

Is there a way to keep the sub-report on the same line as the main report when exporting? Any help is greatly appreciated, thanks!

 

[raja2008]

aS FAR AS I KNOW THAT'S HOW CRYSTAL IS DESIGNED.
i HAD THE SAME RPOBLEM.
that's what i found out.
Since crystal doesn't know how much space the sub is going to take it displays ina seperate row.

Please post if you find a solution .


I did got it to work somehow ,i don't remember exactly.

try using the other Excel format not the dataonly.

 

[lbass]

It is possible to export a subreport in the same row as other fields, as long as you do the following:

1-Select all objects in the row->format->same size->height and also format them to align->bottoms.
2-Attach the subreport to guidelines, both vertical and horizontal. This is hard to do, but is possible. Test to see if it is attached by trying to move the subreport column horizontally and all items in the row vertically.
3-All other items in the row should also be attached to guidelines.
4-Minimize the section height.

I had the same problem and this worked for me.

-LB

 

[ericb123]

thanks lbass, but how do I attach the report to guidelines? I've got the guidelines around the subreport, but I don't think they're attached to them.

Thanks!

 

[lbass]

This is really trial and error, and you can't tell as easily as with a regular database field, as I don't think the corners turn red. So move the sub as you would a field so that it snaps to the guideline, and then test by moving the guidelines (vertical and horizontal). It might help to magnify the view while you are doing this.

-LB

 

[chelseatech]

Another approach could be for the subreport to save it's values in shared variables. Then back in the main report, have a formula to display each shared variable. The subreport can have all it's sections suppress, but in the main report, it must be in a section above the one with the shared variable formulas.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[ericb123]

Hmm, thanks. I tried reading the variables from the sub report, and I've got it working correctly. However, the subreport is in the section above where I want my variables, which is giving me the correct values, but when I try to "Hide" the section with the subreport, it won't pass the variables anymore.

Is there a way to hide or suppress this section, because I don't need to actually see the subreport anymore, but still get the values passed from it?

Thanks!

 

[lbass]

Suppress all of the sections within the subreport, format the subreport->subreport tab->suppress blank subreport, and then format the section containing the subreport to "suppress blank section". You cannot hide or suppress the section containing the sub, or suppress the subreport object directly.

-LB

 

[ericb123]

Awesome! Thanks so much everyone!!

 

[ericb123]

Thanks again, this works great in Crystal, the report displays correctly. However, when exporting to excel, it puts an empty row between each record, I'm assuming this is for the section with the suppressed subreport.

Is there a way to suppress this blank row? I have a lot of data and the empty rows in excel double the size of my worksheet.

Thanks again!

 

[lbass]

Please retry my original suggestion. It really does work.

-LB

 

[softpak]

Thanks lbass!

 

[mmm76]

LB - have you ever tried exporting a sub report to Access? I did what you recommended in this post and it worked. But my report is too big for excel - any suggestions?
Thank!

 

[lbass]

Sorry, I can't help you--I'm not an Access user.

-LB

 

[flailcozy]

I was having the same issue as ericb123 regarding the Excel export. I followed lbass's instructions to get the section with the blank subreport to suppress (thanks!) This works perfectly when viewing the report in the viewer, as well as exporting it most formats (doc, rtf, pdf).

However, although the blank section is suppressed in all these situations, when exporting as an xls file as "data only," the blank sections were still interpreted as blank lines and filled the Excel spreadsheet with a ton of extra rows.

Luckily, I did find that, if you export the report as regular Excel (not "data only"), then the blank rows do not show up.

My issue was that I wanted to use the "data only" format is that with the "regular" Excel export option, Crystal likes to merge cells like crazy. the end users of my reprot want to take the Excel sheet I give them to run totals or other formulas on the data. The merged cells make that all but impossible. What I did to get around that cell merging in this case (and lbass describes something similar, above, although for different reasons), was to make all my fields the same width and height (including the subreport). Then I positioned the fields how I wanted them to appear as a grid, in the exported report. The trick here is to then fill in all the empty fields with blank text fields. For example, say I had a report that contained three columns, and three rows, as follows:

A1 A2 A3
B1 B2 B3
C1 C2 C3

But, when I filled in all the fields I wanted on my report, I didn't have any fields filled in B2 and C2, I would just put an empty textbox in those spots.

fieldA1 fieldA2 fieldA3
fieldB1 txtbxB2 fieldB3
fieldC1 txtbxC2 fieldC3

Crystal sees these empty text boxes as valid placeholders and doesn't merge the fields as it would if you just left the report canvas empty there. These blank textboxes don't mess up any of the suppress settings you've selected, since they are read as blank.

So, with the placeholder fields + the suppress settings, you can get "data only" formatting with the regular Excel export, and not have blank rows in your report.

Note: The fake grid you are creating is across all sections--you need to make sure the entire report looks just like the exported grid you want. You only need to make sure you're padding fields on the left, though. If I didn't have any data in A3, I could leave that spot empty and it wouldn't affect anything.

This drove me NUTS all day today, so I hope this helps someone avoid that!

-JC