Solved

Crystal Excel Export Subreport problem

Posted on 2004-09-10
18
3,090 Views
Last Modified: 2008-01-09
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 ?
0
Comment
Question by:mahmudaq
  • 7
  • 4
  • 2
  • +1
18 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 12026377
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
 
LVL 42

Expert Comment

by:frodoman
ID: 12026682
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
 
LVL 2

Author Comment

by:mahmudaq
ID: 12041267
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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 2

Author Comment

by:mahmudaq
ID: 12041391
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
 
LVL 42

Expert Comment

by:frodoman
ID: 12063621
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
 
LVL 2

Author Comment

by:mahmudaq
ID: 12121566
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 12129694
Did you delete the viewer from the VB form and replace it with the CR10 viewer?

mlmcc
0
 
LVL 2

Author Comment

by:mahmudaq
ID: 12130163
Yes I tried that but it doesn't help. btw im using C# but I don't think that makes any difference.
0
 
LVL 2

Author Comment

by:mahmudaq
ID: 12130337
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
 
LVL 2

Author Comment

by:mahmudaq
ID: 12335853
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
 
LVL 42

Expert Comment

by:frodoman
ID: 12337949
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
 
LVL 2

Author Comment

by:mahmudaq
ID: 12344021
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
 
LVL 42

Expert Comment

by:frodoman
ID: 12346815
RomMod,

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

frodoman
0
 
LVL 32

Accepted Solution

by:
bhess1 earned 0 total points
ID: 12349107
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

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now