For those of us that use the SSRS SOAP API to render reports (this is the endpoint that uses ReportExecution2005.asmx), just a quick heads up about a change that we were not aware of until recently.
While developing a SSRS report that was intended to be exported to Excel using the SOAP API, we started getting an error about the row count of one of the worksheets being more than 65536 rows. After hours of research and having found every article we could find saying that SSRS 2012 and higher should no longer be limited to 65536 rows, we could not figure out why it was still limiting us. We are running SSRS 2016 so we are definitely in a supported version.
Our custom application, which uses the endpoint listed above, is rendering the report and then automatically emailing it based on a user request through our custom application or through a scheduled service. We normally pass the report type as "EXCEL". The SSRS web application, which exported the report correctly, was of no help here as it only shows “EXCEL” as one of the various export options, so that is what we were using to pass into the API.
Upon investigation of the rsreportserver.config file in the SSRS report server folder (/Program Files/Microsoft SQL Server/MSRS13.MSSQLSERVER/ReportingServices/ReportServer), we found a section in the XML called <Render> which specified the various extensions that it supports.
Lo and behold, apparently, in order to get around the 65536 row issue, you need to pass "EXCELOPENXML" instead of "EXCEL". One thing I did not realize is that “EXCEL” creates .xls files where “EXCELOPENXML” creates .xlsx files. Once we made this change in our application, we were able to get well over 65536 rows in a single worksheet and our problems are now resolved.
Happy report rendering!