By using this website, you agree to our Terms of Use (click here)
I created a report in Report Designer and I'm trying to export it to Excel. It exports ok, but the columns don't line up. Is there any way to fix this?
Hi Scooter,
The first thing you'll want to check is the Excel Mode field down in the Layout section of the Report Properties:
If it's set to Auto like in the screenshot above, then the export routine will try to match up the Report Designer columns to the Excel columns automatically.
But it doesn't always get it right. If that's the case, then switch the Excel Mode to Manual like this:
Once you are in Manual mode, the first thing to note is the Excel Columns field in the screenshot above.
If you click into the (Collection) field next to the Excel Columns field, you'll see a ... button. Click that.
Clicking the ... button will open a separate window like this:
The left-hand column signifies the number of Excel columns. You can use the Add/Remove buttons to add and remove Excel columns.
If you click on a column, you can then set the width that the column will have on the right side.
So now you have the number of Excel columns and their widths set. Now we just need to map Report Designer columns to them.
First, I recommend going to View -> Excel Grid on your menu and choosing Show Columns and Show Rows.
If you do this, then all of the fields in your report will get a little green icon on them like this:
Take the one highlighted in the screenshot above for example. This field will get exported to Excel in Row 2, Column 7.
Let's take a look at that field in the report when it runs inside of Acumatica.
Note that the field appears on what I would consider to be the third line in the report.
But now let's export the report to Excel and see where the field lands:
Hmmm, looks like it lands on the second, not the third line. Why is that? Because the little green icon told us it would land in line 2.
But what if we want it to export to the third line in Excel instead of the second line.
Back in Report Designer, go back to View -> Excel Grid and uncheck the two options.
Then, click on the field that we've been looking at and go down to the Layout -> Excel Cell section of the field properties. Note that it currently looks like this:
Change the Row value from 2 to 3 so it looks like this:
Save the report to the server, then run it and export it to Excel.
Notice that now the field is on the third row instead of the second row.
Of course, you can move columns around in the same way.
Sometimes you might notice a field not getting exporting to Excel. It might be sharing the same Row and Column values as another field so only one field is getting exported because only one can "win". Just make sure every field has its own Row and Column values.
Lastly, back in the Layout area of Report Designer for the field, note that you can set Excel Visible to False if you want to hide the field from getting exported to Excel.
My Excel Mode was set to Auto. I changed it to Manual and then changed the Row and Column values for some of the fields.
Now everything seems to be exporting correctly. Thank you for your help.
Why when I export a certain report to Excel, it changes the Column from words to numbers?
I have this open in another thread but here is what I have going on:
I run a report and all of the fields are showing correctly. When I Export to Excel, it changes the Address type from Residential to the number 500? It is doing this for all of those address types.
Residential shows as 500
Commercial shows as 100
Commercial needs lift gate shows as 200 and so on...
It shows up perfectly when exporting to PDF but it shows incorrectly when exporting to Excel.
Thanks.
Geremy,
Can you provide a little more information regarding the Address Type field you are referencing? On which screen is this maintained? (Customer Maintenance [AR303000], Customer Locations [AR303020], Business Account Maintenance [CR303000], etc. The challenge is this doesn't sound like a field I recognize from a standard data set. It is possible that what you are seeing being exported to Excel is the Primary key into the custom table and not the Foreign key or related description value. If we can identify the screen or table where address types are defined and maintained, we can review and see if we can find the correlation.
@shawn-slavin it is a custom field in the Customer records managed by the screen id AR303000
I believe you are right that it is a primary key vs the actual data. The problem is it comes through just fine in the PDF export as well as on screen. The only time it appears as a number is when we have Excel Mode set to manual and export it to excel.
If we switch the Excel Mode to Auto it comes through as the correct text but there are some serious issues with the report formatting then.
Hi Shawn.
It's actually a report. The report is SO620500. It's a Shipment Summary report we have under Sales Orders section. I can send you the .rpx if it would help.
When I run the report in Acumatica, under the "Address type" column it displays Residential or Commercial and a few others. When I export it to Excel, the description changes from Residential to 500, from Commercial to 100. See the attached for a couple of screen shots comparing the two.
Interesting. Seems like a bug with the Excel manual export then.
I have a coloured panel with white text in it, and I cannot use a section with a background as its just highlighting this text boxes within it.
So just a tip make the text fields within the panel have the same background colour as your panel, and the colour will go to Excel.
For those that may have missed it the first time, like me, the row settings are per section. With a new section the rows start back over at 1. I was wondering why I had so much whitespace until I realized this because I had 5:1 thinking it would be the 5th row down. Ended up being the the 5th row below my title rows and 4 rows of white space above it.