Exporting to Excel - Column Headings on Every Page
When I export a financial report to Excel, it prints the column headings multiple times.
It looks great when exporting to PDF because I really do want it to print the column headings on every page, but in Excel I just want them to print once.
Here is what my second page looks like in Acumatica:
But, when I export to Excel, I don't want these highlighted rows to appear:
Is there any way to prevent those column headings from printing a second, third, fourth, etc. time when exporting to Excel?
Unfortunately, I don't know of a way to prevent this when exporting to Excel only. But there is a way to prevent this for the whole report. Just note that it will suppress the second, third, fourth, etc. headers on the screen as well as when exporting to PDF, not just when exporting to Excel.
The key is to use the Column Group / Printing Group feature. Rather than go into how it works, I'll just show you an example for this scenario.
You would probably want to use a meaningful word like "HEADER", but, it can be any word as long as the word you use in the Column Group column of the Row Sets (CS206010) screen matches the word that you use in the Printing Group column of the Column Sets (CS206020) screen.
Just to prove that you can use any word, I'm using "OLYMPIC" in the screenshots below, with the Olympics fresh in my mind.
First, you add a row to the very top of the Row Set, set the Type to Header, and set the Column Group to "OLYMPIC":
Note: I dragged over Column Group for the screenshot. Your Column Group column is probably further to the right.
Then, in the Column Set, set the Printing Group to "OLYMPIC" for all of the header rows:
The result is that the Headings don't print on the second, third, fourth, etc. pages of the report:
And they also don't get exported to Excel:
Note: The problem with this is that the Column Headings still get printed on the screen and when you Export to PDF. So I created an idea that you can go vote on to make them only get suppressed when Exporting to Excel:
This is the case with the reports in Microsoft Dynamics GP as well. I usually filter on Column A and delete the rows other than the first one. But it's tedious to have to do this 'often'. If this needs to be done regularly a macro could solve the problem. In GP Integration Manager there was a way to insert into the SAME Excel workbook each time so a post-integration macro could be executed. From what I currently know about Acumatica there wouldn't be a way to do this.
But you 'could' export to a same-name macro and have a second Excel sheet the end-user has that has macros to auto-open the 'named' Excel workbook and execute a series of macros that strip out the junk and give the user a working Excel sheet.
Side note: The multiple-Header-Rows issue also breaks the ability to use Pivot Tables w/o manually stripping out the duplicate headers.