Export CR 2008 to Excel "blank" rows

[Former Member]

I am trying to export a CR 2008 report using the "Microsoft Excel (97-2003)" option.  When I export my CR report, I end up with a blank row between each row of data.  I've tried everything I can think of in my report set-up to get rid of the blanks but nothing is working.  Just a note, I cannot select the export of option of "Microsoft Excel (97-2003) Data-only".  Any suggestions on what I'm missing??

[Former Member · Accepted solution]

Annoying isn't it... But it is an easy fix.

Make your line of data very tight with no space above or below the fields. All the fields must be lighned up on a guide line and be the same height to do this.

[Former Member · Accepted solution]

Yes, this was extremely annoying!  The funny thing about the entire report is that it is so basic -- just a simple list.  Should have been something that took less than a half hour!!!!  I wasn't expecting to spend almost two days just trying to set-up the export.  Yikes!!!!

THANK YOU for your response!  I selected all the fields and then made them the same height, aligned everything at the top and then "scrunched" everything so there was no extra space anywhere... now it works. In looking at the screen, I couldn't tell which field was out of alignment, but something must have been slightly off. 

What a pain to fix!  Thank you again for your help!!!!

[Former Member · Accepted solution]

Debi, i have the exact same issue. I've tried your suggestion. In my subreport i have a page header(titles for data) and a details section. I made all the title/data fields the same size, and also tightened up the details section.

Still get the blank line.  I'm using cr11 and exporting to ms excel(97-2003) Data only.

are you meaning every title/data field need to be the same size ? or just the individual title/data field ?

thanks John

[DellSC · Accepted solution]

Do you have lines between the rows in your subreport?  That will also cause blank lines between the records - the lines don't export correctly.

The trick to getting everything to line up correctly in Excel exports is to make sure that the data under every column header is the same width as the column header and has the same "X" coordinate as the column header.  I usually place the first column, make it as wide as I need to, and then put a marker in the ruler above the report that lines up with the right side of the column (you should see little red lines at the edge of the objects it's "connected" to.)  Then place the second column, set its width, place a marker on it's right, etc. until all columns have been placed.  If you have to resize any of the columns, move the marker at the top to resize everything that's connected to it.  Then any header objects (like titles) above the column headings should line up with a marker on the left and right (they can span multiple columns though).  This process will prevent merged columns in the export.

-Dell

[Former Member · Accepted solution]

Thanks Dell, For every title/date field i selected both and then did align=lefts and size=same size and all have the same font.

No change, i still the get blank row. 

John

[DellSC · Accepted solution]

Set the "Y" position for each object to 0 and then move the bottom of the section up as far as it will go - it will stop at the bottom of the objects in the section.  If there are any markers in the ruler to the right, you will have to remove them to make this happen.  Also, make sure that all of the objects in a section are the same height.

-Dell

[Former Member · Accepted solution]

Done that.  Every title is same height and Y position = 0, and dragged section bar to bottom of objects.

Did the same in details section. Still getting the blank line.  Is there a way for me to send report with data for you to look at ?

Thanks for you help.   John

[DellSC · Accepted solution]

Yes.  Save the report with data.  Change the file extension from ".rpt" to ".txt".  Use the advanced editor and attach the file here.  I should be able to pick it up from there.

-Dell

[Former Member · Accepted solution]

Dell,  thanks. I've attached the .txt file .John

Phoenix Vtier.txt.zip

[Former Member · Accepted solution]

Dell, after attaching the file you have i noticed something, my report export works !!  But, heres the issue. When i attempted to attach the original file, i got an error stating it was too large(> 1mb) so i added a filter to the subreport to return fewer records 5970 instead on 283367.

So the report works and exports fine when less records are exported,  any idea why ?

Thanks John

[DellSC · Accepted solution]

I'm getting an error when I try to open the report, so I can't really look at it to see what the issue might be.  It looks like the file may have been corrupted.

-Dell

[Former Member · Accepted solution]

How do i change the file type extension ? from .rpt to .txt ? if i open the .rpt in notepad and save as .txt, that's what i did

Thanks

[Former Member · Accepted solution]

Dell, You may need the larger file, as that is failing, as i said when i filtered which reduced the nb records, the export worked(with all the changes you'd suggested in previous posts).

John

[DellSC · Accepted solution]

No.  You just need to rename the file.  Select the file in Windows Explorer, press F2 (or right-click and select Rename) and change the file extension from .rpt to .txt.

-Dell

[Former Member · Accepted solution]

OK in WIN 7 you have to Hide extensions for known file types,

The file is attached now.  This one works, as i have filtered the nb records, when the report returns ~283k records, the blank row is inserted on the export.

Thanks

Phoenix Vtier Test.TXT.zip

[DellSC · Accepted solution]

Got it this time!  Also, by default Hide Extensions is turned on, but you can turn it off.

I would do the following in the main report:

1.  Take the markers off the ruler at the top of the page.

2.  Right-click on the subreport and select "Format Subreport...".  Go the Border tab and set all of the borders to None.

3.  Right-click on the subreport and select "Size and Position...".  Set X and Y both to 0.

4.  Pull the bottom border of Report Footer b up to the bottom of the subreport.

In the subreport, you don't quite have it configured correctly for a good export to Excel. 

- When you place the markers at the top, you may need to do a slight resize on the fields below them to connect the edge of the field to the marker

- The fields need to have NO space between them.  Where one stops, then next should start.

- There should be no extra markers on the ruler - just the ones that are actually being used.

Here is an example of how it should look:

Notice where the red marks are - this indicates that the left or right edge of the field is linked to the marker above it.

Doing this type of formatting will prevent merged columns in the regular Excel export or blank columns in the Data Only Excel export.

-Dell

[Former Member · Accepted solution]

Thank you Dell, nearly there, i've attache d a new file.

How do i do this step ?  you wrote

>>>>>

In the subreport, you don't quite have it configured correctly for a good export to Excel. 

- When you place the markers at the top, you may need to do a slight resize on the fields below them to connect the edge of the field to the marker

- The fields need to have NO space between them.  Where one stops, then next should start.

- There should be no extra markers on the ruler - just the ones that are actually being used.

<<<

in the subreport, how do i move the fields so that they do not overlap/merge with each other when i'm removing the space between then ? is there a command/function if i select all objects in the section ? how are rulers showing in you example ?

Thanks John

Phoenix Vtier Test.txt.zip

[DellSC · Accepted solution]

1.  Start from the field on the left. 

2.  Extend the left side so that it matches the marker above it (it should "snap" to the marker as you get close to it.) 

3.  Extend the right side so that it matches the marker above it (this should also snap.)

4.  You should now have red lines on either side of the field.

5.  Delete the markers above the next field.

6.  Select the second field and move it close to the first field.  It may automatically snap to the marker at the end of the first field.  If it doesn't, just extend the left side until it snaps to the marker.

7.  Place a marker at the end of the second field, adjusting the field size, if necessary, to snap to the marker.

8.  You should now have red lines on both sides of this field.

9.  Repeat steps 5 through 8 for all of the fields/formulas in the report - lining up each with the field immediately to its left.

-Dell

[Former Member · Accepted solution]

Dell i really appreciate your help. 

I am missing something, as when i move any datafield to the left, to remove the spaces between the fileds, it does not "snap" it will merge/overlay the datafield i'm trying to move it next to.

Is there an option i'm missing ? 

[DellSC · Accepted solution]

I noticed that in your report when I was playing with it yesterday.  Try resizing the field - drag the left side of a field over, going slowly.  Put the marker above where you want the right side of the field and drag the right side over to it.

-Dell

[Former Member · Accepted solution]

Dell, i'm having a difficult time with this !! 

I have the markers in the ruler, when i try and move the 'Dev Size GB' datefield to the left, it never snaps, and will merge to the left into the "@New Tier + Shared List" datafield.

In this example the left edge of the ARRAYTYPE and ARRAYSERIALNUMBER are "red" ?

plus i have many many markers on the ruler bar, how do i removed them ?

Thanks John