Print Page | Close Window

Page Header, Footer while exporting to Excel

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=759
Printed Date: 05 May 2025 at 3:45am


Topic: Page Header, Footer while exporting to Excel
Posted By: AshishB
Subject: Page Header, Footer while exporting to Excel
Date Posted: 29 May 2007 at 6:07am

Hi All,
I am currently in the process of evaluation of Crystal Reports XI R2. I have customer having previous version of reporting module, which they are more-or-less satisfied with. These reports have been developed with Access and Excel.

I am trying to get everybody to agree to me that Crystal Reports, is a professional reporting tool and would be scalable. The issue here is:

1. Everybody wants to see reports in Excel so that they can add/remove information on their own. But I have observered the following issues with Excel export till date:

   1.1 lines are not exported to Excel, which make export to Excel more-or-less non-printable and presentable.

   1.2 Page settings (margins) differ in the exported Excel spread sheet, that means the page breaks on screen (for CR version) are different then they are in the exported Excel spreadsheets.
   1.3 Page headers are not exported.
   1.4 There is no print_area defined in the exported excel spread sheet.
   1.5 Watermark images are exported as sparate row instead of being a background image.
   1.6 Language changes for column and report headings is not through resource strings.
 
Also few questions:
 
2.1 Is there any way to programattically allow the user to add/remove some of the columns from the reports?
2.2 Can I insert some "tags" (non-printable chars) in the report design, which can be read using excel Macros to make borders?
2.3 Is there any way to programatically add back ground image for a report?
 
Please dont hesitate in answering only one or two issues/questions out of the whole lot I have put forth.
 
 
With Regards,
 
 
Ashish.


Replies:
Posted By: hilfy
Date Posted: 29 May 2007 at 1:46pm

There are two different ways to export to Excel from XI, the regular Excel export and Excel - Data Only. 

The regular export will get you something close to the original format of the report.  I'm not sure if it will show the lines or not.  It also has the problem that there are lots of merged cells and the gridlines are turned off.  It's the best option if you want the export to be close to a "picture perfect" image of the report, but it doesn't let the user easily sort or otherwise manipulate the spreadsheet.
 
The Data Only export exports the data in columns, there are no merged cells and it doesn't do much in the way of formatting.  This is the best option if your users want to "slice and dice" data by resorting, filtering, etc.
 
To answer question 2.1 - yes you can do this.  For each column that you want to "hide", set up a Boolean parameter.  Your users or your application will then set the parameter values.  In your report, put a suppress formula on each "optional" field based on the corresponding parameter.
 
Is there a specific reason why the reports have to be in Excel format?  Why not just print them from Crystal?
 
-Dell 


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: AshishB
Date Posted: 30 May 2007 at 2:10am

Thanks Dell,

You are a great help! The project I am working on has legacy existing since last 10-15 years and has a large implementation base already.

These implementations have happened in lots of different countries and thats why the reporting requirements are different for each country. Thats why my organization decided to present reports in MS Office formats, so that once the user gets a valid export then he/she can add/remove details from it, customize it as per regional requirments.

At the same time, being a customer gives them rights to ask for picture perfect exports and thats the reason, using office product makes sense.

Your idea for setting up the boolean parameters is great and I would implement it today itself.

If you need any furhter clarifications or you can give me some more inputs, It would be just great.

I have already tried line object export, that doesnt works, gridlines are very much available, but watermark images export is not good at all.
 
Please feel free to provide any tips, pointers, solutions and/or directions.
 
With warm regards,

 

Ashish Bakshi.

Posted By: hilfy
Date Posted: 30 May 2007 at 9:37am

Have you tried exporting to Word or RTF instead of to Excel?  You might have better results.

-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Atheeque
Date Posted: 31 May 2007 at 7:19am
Hi Dell,
 
I have an issuse while exporting the crystal report to excel  data only format.

The Header  section of my report has two header rows (lines). when i  export the data the bottom header row information is exported and the top Header rows is not displayed in my export. but the Detail Data information is exported correctly.

Thank You For your help in advance.




-------------
Atheeque

Posted By: AshishB
Date Posted: 31 May 2007 at 7:27am

Did you check the supperess option for the same? I have reports where there are 2 rows and I can export them to Excel.

Posted By: hilfy
Date Posted: 31 May 2007 at 10:37am
There have long been quirks and issues with Crystal's export to Excel.  Unfortunately, there really aren't any answers for a lot of them and the response from tech support amounts to "that's the way it is, deal with it."
 
Do you have two sections in the header or two lines in your data?  If it's two lines in the data, does making the row in Excel taller solve the problem?  (i.e., is the data there, just not visible?)
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: easybelep
Date Posted: 07 Jun 2007 at 4:07am
Hi,
 
I had the same problem when using XI R2.
Managed to get around it by adding a text object as wide as the report and then formating it specifying the bottom border as a single line instead of the default None.
 
Regards, Miki
 
 

Posted By: sanjay_usa1
Date Posted: 07 Jun 2007 at 6:20am
Hello! Dell,
 
My issue is little different when i export the report with data only excel options i get all the header and footer but i only get one group name displayed for whole report whereas i have more groups name which is missing for dislay.
 
I would really appreciate you help in this concern
 
Sam

Posted By: hilfy
Date Posted: 07 Jun 2007 at 10:01am
That's one of the nice little quirks to the excel data only export and there's not much you can do about it.  To get around this, I would include the group data in the data instead/in addition to in the group header.  That way it's in the exported data.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: hilfy
Date Posted: 07 Jun 2007 at 10:12am
I just had a thought...
 
Depending on the complexity of the report on the number of groups you have, you might be able to put the group header in a separate details section at the top of the details.  You would then put a suppess formula on that section:
 
{group field} = previous({group field})
 
This way the "header" will only appear at the beginning of each group like it should and, since it's in the details line, should also appear in the excel export.
 
I haven't tested this, though, so I'm not absolutely certain it will work.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Atheeque
Date Posted: 12 Jun 2007 at 11:30am
hi guys
Thanks for your reply. 
I am able to get the header row information by spliting the header section into two sections and keeping each row on the seperate sections.
I dont know whether the process is correct or not. but when exported the data comes in the write format as i need them.



-------------
Atheeque


Print Page | Close Window