Crystal Excel Export Subreport problem

im trying to export a Crystal Report to Excel that has several separate subreports for header and details. I have set the variable ExcelAreaType to AreaSectionKind.Detail in the report's ExcelFormatOptions object :

excelFormatOpts.ExcelAreaType =AreaSectionKind.Detail;

The resulting Excel file has a properly formatted detail section but the report header and page header are shifted to the right.

On changing the code to:

excelFormatOpts.ExcelAreaType =AreaSectionKind.PageHeader;

I get a properly formatted header but the details section field are shifted.

My question is this: How do we specify different Excel export options for the individual subreports when we are exporting only a single report (containing subreports) ? And if we cant, then what is the workaround for this issue ?
LVL 2
mahmudaqAsked:
Who is Participating?
 
Brendt HessSenior DBACommented:
RomMod. frodoman, mahmudaq:

I agree in general that this question does not truely have an acceptable answer, and mahmudaq's last comment is a strong argument for deletion.  However, I would prefer that, in this case, the points be refunded and the question be PAQ'd.

Why?  Basically, because frodoman's answer was correct for CR9, but mahmudaq found a better answer in using CR10.  If someone else comes looking for a similar answer, I believe that it is better to have an answer on file that says "you can't do it this way....  try this other way that requires an upgrade" instead of losing that information.

bhess1
0
 
mlmccCommented:
I am not familiar with those options but there is an excellent paper from Crystal on how to format the report to avoid some of the problems in exporting to Excel.

http://support.businessobjects.com/communityCS/TechnicalPapers/scr_exportexcel.pdf.asp

mlmcc
0
 
frodomanCommented:
You cannot specify different export options for individual subreports - the export option applies to the entire report.

I don't know of any good workaround that will handle this situation (apart from the document mlmcc posted).  The best I can think of would be to create a parameter and conditionally suppress the subreports and export with the PageHeader type.  Then change the paramter to suppress the main report sections and export with the Detail type.  You'd still need to merge the resulting spreadsheets together though so this is a half-baked workaround at best.

Bottom line is that you've hit the limit of what Crystal can handle and AFAIK you aren't going to be able to get exactly what you want.

frodoman
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
mahmudaqAuthor Commented:
I looked up the paper that mlmcc referred to. its useful but its limited to changing the formatting of the reports themselves rather than the export options. In my case, I am forced to assume properly formatted reports ... so I cant touch the report itself. I have to find a way to alter the export options through C# code so that it would work for any report, regardless of the number of subreports.

I had also considered what frodoman suggested but the difficulty would then lie in merging the resulting spreadsheets... as you have pointed out yourself.

Let's c if any1 else has any other suggestions.
0
 
mahmudaqAuthor Commented:
Ive also noticed a member variable of type short by the name of ExcelAreaGroupNumber ( in the ExcelFormatOptions object), however MSDN doesnt explain what this variable is for. Is it related in any way to the above mentioned issue ?

Note that Im not extending the question, just including some additional info that may come in handy in finding the solution.
0
 
frodomanCommented:
ExcelAreaGroupNumber:  I've never used it but my understanding is that during export you can specify that Crystal uses the 2nd Group (for example) to format the Excel worksheet as far as column widths, etc.  This article explains one use for it if you're curious, but unfortunately it isn't going to be of any help with this problem: http://support.businessobjects.com/library/kbase/articles/c2014698.asp

The workaround I gave is admittedly feeble and you're free to wait for other people to chime in, but as I've said you cannot do this...

frodoman
0
 
mahmudaqAuthor Commented:
Well people, the latest on this question is that I decided to try Crystal Reports version 10 Evaluation to see if it had any more options for the AreaSectionKind enumeration... I found that it did, namely AreaSectionKind.WholeReport and a couple or so more. This should have been enough to solve the problem and since noone suggested that I try the latest version of CR, so I would have to split points between frodoman and mlmcc anyway - even though frodoman said it wasnt possible.

HOWEVER, since this wasnt enough to solve the issue, let's see if you can solve my next problem related to this issue - the existing code that used XML data to generate a report at runtime using the Crystal Decisions DLLs doesnt display any data when the new DLLs of CR v10 are used. Note that the code isnt altered whatsoever - only the references are changed in VS .NET 2003. There are no errors or warnings during compilation. But when the report displays, its blank as would be displayed if you had simply supplied a static RPT file as Report Source during design time.

Note to moderator: Since no1 was able to solve the original problem and I upgraded to CR10 on my own, I should be allowed to further my question so that some1 really earns the points :)
0
 
mlmccCommented:
Did you delete the viewer from the VB form and replace it with the CR10 viewer?

mlmcc
0
 
mahmudaqAuthor Commented:
Yes I tried that but it doesn't help. btw im using C# but I don't think that makes any difference.
0
 
mahmudaqAuthor Commented:
Ive also compared the execution times for both versions of CR. The Report.Load() function takes less than 2 seconds to execute on CR9 while the same function loading the same report takes about a minute using CR10...
0
 
mahmudaqAuthor Commented:
Since no1 has provided a solution and I myself have come up against a dead-end regarding the solution, I suggest you delete the question.
0
 
frodomanCommented:
To re-cap from mahmudaq's original posting:

"My question is this: How do we specify different Excel export options for the individual subreports when we are exporting only a single report (containing subreports) ? And if we cant, then what is the workaround for this issue ?"

My first posting answered correctly that what he wants can't be done and it also provided a workaround as requested.  

I believe I've earned the points on this one.

frodoman
0
 
mahmudaqAuthor Commented:
It really doesnt affect me much whether the question gets deleted or not, since my problem remains unsolved. And frodoman, if you read my comment

"...I decided to try Crystal Reports version 10 Evaluation to see if it had any more options for the AreaSectionKind enumeration... I found that it did, namely AreaSectionKind.WholeReport and a couple or so more. This should have been enough to solve the problem ..."

you would have noticed that it CAN in fact be done (using CR10 API), however, there are further issues in CR10 which need resolution. I suggested deletion of this question because I dont want to give a C grade. Dont take it personally - this was a tough nut to crack and I believe that Crystal Decisions is to blame since even their latest patch didnt solve the problem entirely.

This is my last comment on this question - I leave it up to the moderator to decide the fate of this question.
0
 
frodomanCommented:
RomMod,

The lastest post by mahmudaq is a reasonable explanation.  I withdraw my objection to deleting this question.

frodoman
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.