Solved

Crystal Excel Export Subreport problem

Posted on 2004-09-10
18
3,114 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 2
  • +1
18 Comments
 
LVL 101

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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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 101

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:
Brendt Hess 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

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…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

622 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