OP Reports Tutorials: How do I export OP Report results to Excel?Module 1, Lesson 2
How Do I Export an OP Report to Excel?
(You must have a current version of Excel on your computer, of course.)
Open your OP Report in the Print Preview screen, then click the Printer icon in the upper left corner.
Tick the Print to File checkbox.
Set the Type: dropdown to XLS Report (not XLS Data — keep scrolling down to find XLS Report.) Set the Where: box to the file name and path where you want your Excel file saved. (Tip: if you don’t set a path, your file will automatically go to your C:\OP folder.)
Lost Data Errors in Conversion
Sometimes when exporting an OP Report to Excel, you will get an error message that looks like this:
…and when you click OK to open the report, the rows are misaligned, like this:
These errors make reports difficult to use because there are annoying blank lines.
These “blank lines” get inserted when the fields within the OP Report are not perfectly aligned. Notice how in this report the DATE1 field is somewhat below the PATNO field, which is below the Variable1 field:
During the conversion to Excel, the computer tries to be “helpful” and inserts blank spaces, so as to auto-mimic the misalignment within the OP Report.
To line up the data elements in each row, you can use the Align tool. After clicking on the Select arrow (highlighted yellow), drag a box around all the misaligned elements in the row that you want to align.
Then click on the Align Bottom tool (highlighted green). (If the Alignment tools are hidden, show them by going to View > Toolbars > Align or Space.)
Now your data elements should line up nicely:
Note that sometimes the misalignment in OP Reports is very subtle — it may just be 1-2 pixels. This can be enough to mess up the conversion to Excel and get the “lost data” error. Thus, you’ll want to use the Align Bottom tool, even if the row doesn’t’ look too bad. For more on the various Alignment tools, see this module.
Other Causes of “Lost Data” with OP Reports-to-Excel Conversion
Sometimes other kinds of asymmetries will cause messy results, even if you don’t get a “lost data” error. Here is a report that has two fields in the Detail band [top] and two fields in the Summary band [bottom]:
During the Excel conversion process, the computer isn’t sure how it should arrange the columns. Did you want 4 different columns of numbers or just 2 columns? If you use the Align Left tool on each pair of data fields, the computer will now make two very tidy columns:
Finally, using mixed font sizes/weights in a single band can also cause messy results. Notice how the two data fields on the right are bolder/larger font than on the left.
While you could use bold or large font for both data fields in the Summary band [bottom], mixing font sizes/boldnesses within a single horizontal band may cause unexpected row and column shifts after exporting to Excel.
Tip: you can vary color among data elements with a horizontal band, if you want certain figures to stand out in your report. This does not interfere with the export process.
Removing Automatic Cell Merging from OP Reports
Let’s say you’re exporting an OP Report to Excel, as described above. Some of the fields in Excel look a little wider than you’d expect (see yellow highlights):
And in fact, when you try to sort the report, you get an error message that some of the cells have been merged together:
From within Excel, you can “unmerge” the merged cells quickly by selecting the whole spreadsheet (Ctrl-A), then selecting the Home tab. Click the small down arrow next to Merge & Center and select Unmerge Cells. The cells now look to be the correct width all the way down…
and you can sort the data without getting an error message:
But let’s say you are going to be exporting this particular report a lot, and you don’t want to Unmerge Cells every time. You can turn off automatic cell merging in OP Reports. In the Design mode, in the Report Tree, click on Report:
Then, in Output – File, under XLSSettings, find the checked MergeAdjacent field, and uncheck it. The next time you export this OP report to Excel, the adjacent cells won’t automatically merge, and it will be easier to manipulate/sort your data.
Removing Excess Blank Rows
You’ll notice in the above example that each row of data has about two blank rows between it and the next true data row:
Also, you’ll notice that where the page breaks were in the OP report, there are a lot more empty rows (shown with the yellow squiggle), some with very small heights (outlined in the blue circle):
From within Excel, you can get rid of the blank rows quickly by selecting your entire spreadsheet (Ctrl-A) then Sorting on any column that contains data in some rows. This will make the blank rows drop to the bottom of the spreadsheet, out of your way. You can also resize all of the small-height rows to normal by selecting the entire spreadsheet (Ctrl-A), then on the Home tab, select Format (Cells) then Autofit Row Height.
But on the OP end, you can reduce the number of blank rows produced by squishing the data bands vertically.
The data band in this report, GroupFooter: PATNO, has a lot of space in the band both above and below the data fields (illustrated by blue vertical lines.)
Excel thinks you did this on purpose, and you want blank rows both above and below. In order to reduce the blank rows, you can shrink the size of the data band, by moving the data fields up to the top of the band, then collapsing the band by dragging the bottom up:
There are three strategies for reducing the number of blank rows at the end of a page. Any of the following will work:
- In Design mode, in the Report Tree, select Report. Then in Output – File, under XLSSettings, be sure that IgnorePageHeight is checked. This will stop inserting many blank rows to make up a page.
- You can shrink the Header and Footer until they are collapsed. To collapse the header, click and drag on the grey band labeled Header, then drag it upward, until there is no white space visible in the header band.
- You can also hide the Header and Footer on the Design bands to prevent Excel from trying to make the blank white space into rows. To hide the Header or Footer, right-click on the blank space in the header or footer band (here, the blank space in the header band is marked with a blue X), then choose Visible so that it is no longer selected.