SSRS Report Execution Service: export to xlsx without error

Question

Hello thanks for looking.

I have some .net code that uses the SSRS Report Execution Service to download a report for the user as an xlsx file.

It works & everything in the report is present & accounted for.

One annoying thing though. When opened in Excel 365 it gives a popup saying:

We found a problem with some content in "theReport.xlsx" Do you want us to try to recover as much as we can? If you trust the source of this workbook click yes

When I click yes then it indicates that the workbook was repaired and the report looks normal.

It does not give any indication what it repaired in the log file, just that it was repaired.

Please take a look at my code. Perhaps there is something small I can change to get rid of the error when the Excel sheet is opened.

 private void DownloadQuoteExport()
        {
            

            string reportName = "reportName";
            string fileName = "filename";

            

            //create web services instance
            ReportExecutionService rs = getService();

            //render report 1st parameter
            ParameterValue param1 = new ParameterValue();
            param1.Name = "QuoteID";
            param1.Value = quoteId.ToString();

            try
            {
                
                executeReport(reportName, new ParameterValue[] { param1 }, "EXCELOPENXML", fileName, rs);

            }
            catch (Exception ex)
            {
                Response.Write(ex.Message + "<br>" + ex.StackTrace.ToString());
            }
        }

 private void executeReport(String reportName, ParameterValue[] rptParams, String rptFormat, string strRptFileName, ReportExecutionService service)
        {
            string encoding;
            string mimeType;
            string extension;
            Warning[] warnings = null;
            string[] streamIDs = null;
            string historyID = null;

            ExecutionInfo execInfo = new ExecutionInfo();
            ExecutionHeader execHeader = new ExecutionHeader();

            service.ExecutionHeaderValue = execHeader;
            execInfo = service.LoadReport(reportName, historyID);

            service.SetExecutionParameters(rptParams, "en-us");
            String SessionId = service.ExecutionHeaderValue.ExecutionID;
            byte[] result = service.Render(rptFormat, null, out extension, out encoding, out mimeType, out warnings, out streamIDs);

            Response.ClearContent();

          
                if (rptFormat == "EXCELOPENXML")
                {
               
                Response.AppendHeader("content-disposition", "attachment; filename=" + strRptFileName + ".xlsx");
                
                Response.ContentType = "application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            }

            Response.BinaryWrite(result);
            Response.Flush();

        }

It's to do with the precision and/or scale of the numbers you are outputting. I had the same issue and ended up writing a stored procedure to 'fix' the data before it get sent to the report (at the end of the dataset query). My proc took anything that was > 14 precision or > 6 scale and set them to 14 and 6 respectively. These number may be a bit aggressive but it worked ok in my scenario. You shoudl see the same behaviour if you run the report from the SSRS web portal and then export it. If you need more help, let me know and I'll post a fuller explanation/answer — Alan Schofield, Aug 20 '21 at 18:15
Thank you so much for your reply. I think you are right that this sort of thing can cause this problem. I will run this by our dba. However I'm not convinced its the problem. Previously this was exporting .xls and we never had this issue even with same report. Also as you mentioned at the end of your comment if we run this report directly from SSRS it should give same behavior. But when my dba runs the report on SSRS directly and gets the .xlsx, when Excel opens the file it does not throw the error. Good advice though. — drzounds, Aug 20 '21 at 19:27

score 0 · Accepted Answer · answered Nov 09 '21 at 20:15

This is finally what worked.

private void executeReport(String reportName, ParameterValue[] rptParams, String rptFormat, string strRptFileName, ReportExecutionService service)
        {
            string encoding;
            string mimeType;
            string extension;
            Warning[] warnings = null;
            string[] streamIDs = null;
            string historyID = null;

            ExecutionInfo execInfo = new ExecutionInfo();
            ExecutionHeader execHeader = new ExecutionHeader();

            service.ExecutionHeaderValue = execHeader;
            execInfo = service.LoadReport(reportName, historyID);

            service.SetExecutionParameters(rptParams, "en-us");
            String SessionId = service.ExecutionHeaderValue.ExecutionID;
           
            byte[] result = service.Render(rptFormat, null, out extension, out encoding, out mimeType, out warnings, out streamIDs);

            Response.ClearContent();
            Response.Clear();
            
             
                if (rptFormat == "EXCELOPENXML")
                {
                 Response.AppendHeader("content-disposition", "attachment; filename=" + strRptFileName + ".xlsx");

                 Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
               
            }

            Response.BinaryWrite(result);
            Response.Flush();
            Response.SuppressContent = true;
            try
            { 
                HttpContext.Current.ApplicationInstance.CompleteRequest();
            }
            catch (ThreadAbortException ex)
            {
                //ignore
            }

        }

SSRS Report Execution Service: export to xlsx without error

1 Answers1