Solved

Crystal Excel Export Subreport problem

Posted on 2004-09-10
18
3,084 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

759 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

19 Experts available now in Live!

Get 1:1 Help Now