Rendering MS Excel reports (XLSX) via SSRS issue

H Kanwar,


Thank you for your patience with us.

Unfortunately, we have yet received updates in reference to the ticket logged earlier as SSRS-40165. We have requested the concerned development team member to share the insight of the presented scenario, and most favorably, an estimated release schedule for the fix. As soon as some news comes in, we will post here for you kind reference.

Hi Kanwar,

Thanks for considering Aspose.Cells for Reporting Services.

We have checked and tested your provided report definition file. It is fine.

Please let us know your environment.

Our test environment is:

  1. Windows Server 2008 R2 x64, SQL Server 2008 R2 x64, Aspose.Cells.ReportingServices.DLL 2.1.0.1
  2. Visual Studio 2010, .NET Framework 2.0

Here is the test code for your reference.


private static string
REPORTING_SERVICE_URI = “ReportService2005.asmx”;

private static string REPORTING_EXEC_SERVICE_URI = "ReportExecution2005.asmx";

private ReportingService2005 m_ReportServices;

private ReportExecutionService m_ReportSerivcesExec;

public byte[] ExportReport(string reportname, RW.Utility.SSRS.Services.ReportExecutionService.ParameterValue[] parameters, string format)

{

string historyID = null;

string devInfo = @"False";

string encoding;

string mimeType;

string extension;

string[] streamIDs = null;

try

{

ExecutionInfo execInfo = new ExecutionInfo();

ExecutionHeader execHeader = new ExecutionHeader();

RW.Utility.SSRS.Services.ReportExecutionService.Warning[] warnings = null;

m_ReportSerivcesExec.ExecutionHeaderValue = execHeader;

execInfo = m_ReportSerivcesExec.LoadReport(reportname, historyID);

String SessionId = m_ReportSerivcesExec.ExecutionHeaderValue.ExecutionID;

if (parameters != null)

{

m_ReportSerivcesExec.SetExecutionParameters(parameters, "en-us");

}

byte[] result = m_ReportSerivcesExec.Render(format,

devInfo,

out extension,

out encoding,

out mimeType,

out warnings,

out streamIDs);

execInfo = m_ReportSerivcesExec.GetExecutionInfo();

return result;

}

catch (Exception e)

{

Console.WriteLine(e.Message);

throw e;

}

}


Still we are getting error. We have collapse/expand functionality on header click (you must have seen in RDL). So far we did some research and if we remove clickable headers the report doesn't give error. PFA code for reference.

try
{
Po_ReportingExecution.ReportExecutionService rs = new Po_ReportingExecution.ReportExecutionService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

// Render arguments
byte[] result = null;
string reportPath = @"/StandardSSRSExport/LoadFactorExport";

string ExcelFormat = Convert.ToString(ConfigurationManager.AppSettings["ExcelFormat"]).Split('/')[0];
string format;
if (ExcelFormat != null)
format = ExcelFormat;
else
format = "Excel";
//string format = "Excel";
string historyID = null;
string devInfo = @"False";

// Prepare report parameter.
Po_ReportingExecution.ParameterValue[] parameters = new Po_ReportingExecution.ParameterValue[28];
for (int i = 0; i < parameters.Length; i++)
{
parameters[i] = new Po_ReportingExecution.ParameterValue();
}
if (Session["ReportParametrs"] != null)
{
dt = (System.Data.DataTable)Session["ReportParametrs"];
parameters[0].Name = "BU";
parameters[0].Value = dt.Rows[0]["BU"].ToString();
parameters[1].Name = "Analyst";
parameters[1].Value = dt.Rows[0]["Analyst"].ToString();
parameters[2].Name = "Dep_No";
parameters[2].Value = dt.Rows[0]["Dep_No"].ToString();
parameters[3].Name = "Origin";
parameters[3].Value = dt.Rows[0]["Origin"].ToString();
parameters[4].Name = "Destin";
parameters[4].Value = dt.Rows[0]["Destin"].ToString();
parameters[5].Name = "Res";
parameters[5].Value = dt.Rows[0]["Rsrc"].ToString();
parameters[6].Name = "E_Code";
parameters[6].Value = dt.Rows[0]["E_Code"].ToString();
parameters[7].Name = "IsSubClassLevel";
parameters[7].Value = dt.Rows[0]["IsSubClassLevel"].ToString();
parameters[8].Name = "DAYOFWK";
parameters[8].Value = dt.Rows[0]["DAYOFWK"].ToString();
parameters[9].Name = "Capture_Date";
parameters[9].Value = dt.Rows[0]["Capture_Date"].ToString();
parameters[10].Name = "FromDeparture_OriginalDate";
parameters[10].Value = dt.Rows[0]["FromDeparture_OriginalDate"].ToString();
parameters[11].Name = "ToDeparture_OriginalDate";
parameters[11].Value = dt.Rows[0]["ToDeparture_OriginalDate"].ToString();
parameters[12].Name = "HisOption";
parameters[12].Value = dt.Rows[0]["HisOption"].ToString();
parameters[13].Name = "CapType";
parameters[13].Value = dt.Rows[0]["CapType"].ToString();
parameters[14].Name = "LEG_SEG_TYPE";
parameters[14].Value = dt.Rows[0]["LEG_SEG_TYPE"].ToString();
parameters[15].Name = "roundTrip";
parameters[15].Value = dt.Rows[0]["roundTrip"].ToString();

//parameters[16].Name = "G1";
//parameters[16].Value = "Collapsed";
//parameters[17].Name = "G2";
//parameters[17].Value = "Collapsed";
//parameters[18].Name = "G3";
//parameters[18].Value = "Collapsed";
//parameters[19].Name = "G4";
//parameters[19].Value = "Collapsed";
//parameters[20].Name = "G5";
//parameters[20].Value = "Collapsed";

parameters[16].Name = "G1";
parameters[16].Value = (dt.Rows[0]["temp_Drillthroughs"].ToString().Contains("G1") == true) ? "Expanded" : "Collapsed";
parameters[17].Name = "G2";
parameters[17].Value = (dt.Rows[0]["temp_Drillthroughs"].ToString().Contains("G2") == true) ? "Expanded" : "Collapsed";
parameters[18].Name = "G3";
parameters[18].Value = (dt.Rows[0]["temp_Drillthroughs"].ToString().Contains("G3") == true) ? "Expanded" : "Collapsed";
parameters[19].Name = "G4";
parameters[19].Value = (dt.Rows[0]["temp_Drillthroughs"].ToString().Contains("G4") == true) ? "Expanded" : "Collapsed";
parameters[20].Name = "G5";
parameters[20].Value = (dt.Rows[0]["temp_Drillthroughs"].ToString().Contains("G5") == true) ? "Expanded" : "Collapsed";

parameters[21].Name = "ShowEqp";
parameters[21].Value = dt.Rows[0]["ShowEqp"].ToString();
parameters[22].Name = "FareMixData";
parameters[22].Value = dt.Rows[0]["FareMixData"].ToString();
parameters[23].Name = "Filter1";
parameters[23].Value = dt.Rows[0]["Filter1"].ToString();
parameters[24].Name = "Filter2";
parameters[24].Value = dt.Rows[0]["Filter2"].ToString();
parameters[25].Name = "Filter3";
parameters[25].Value = dt.Rows[0]["Filter3"].ToString();
parameters[26].Name = "DateFormat";
parameters[26].Value = dt.Rows[0]["DateFormat"].ToString();
parameters[27].Name = "Filter4";
parameters[27].Value = dt.Rows[0]["Filter4"].ToString();
}

Po_ReportingExecution.DataSourceCredentials[] credentials = null;
string showHideToggle = null;
string encoding;
string mimeType;
string extension;
Po_ReportingExecution.Warning[] warnings = null;
Po_ReportingExecution.ParameterValue[] reportHistoryParameters = null;
string[] streamIDs = null;
rs.Timeout = 99999999;
Po_ReportingExecution.ExecutionInfo execInfo = new Po_ReportingExecution.ExecutionInfo();
Po_ReportingExecution.ExecutionHeader execHeader = new Po_ReportingExecution.ExecutionHeader();
rs.ExecutionHeaderValue = execHeader;
execInfo = rs.LoadReport(reportPath, historyID);
rs.SetExecutionParameters(parameters, "en-us");
result = rs.Render(format, devInfo, out extension, out mimeType, out encoding, out warnings, out streamIDs);
execInfo = rs.GetExecutionInfo();
Response.Buffer = true;
Response.Clear();
Response.ContentType = mimeType;
/*
//This header is for saving it as an Attachment and popup window should display to to offer save as or open a PDF file
Response.AddHeader("Content-Disposition", "attachment; filename=" + extension);
*/
// This header is use for open it in browser.
Response.AddHeader("content-disposition", "inline; filename=LoadFactor" + Convert.ToString(ConfigurationManager.AppSettings["ExcelFormat"]).Split('/')[1]);
Response.BinaryWrite(result);
Response.Flush();
Response.End();
}
catch (Exception ex)
{
oErrorHandler.createElmahLog(ex.Message, ex.StackTrace, ex.Source);
ScriptManager.RegisterStartupScript(this, GetType(), Guid.NewGuid().ToString(), "CloseReport();close();", true);

}

Hi Kanwar,

Thanks for your posting and considering Aspose.Cells for Reporting Services.

We will work on your query and get back to you asap.

Shakeel I am waiting for your answer. Meanwhile kindly answer to this question when i print a report with 0 records with SSRS’s excel extension, whatever message i give in NoRowsMessage (in RDL) it prints it perfectly fine.

But when I use Aspose to print a report with no records to XLSX format then it shows headers in report.

and shows no records. that is bit confusing for client.


Hi,

Thanks for using Aspose.Cells for Reporting Services.

We still could not find your provided issues.

Please check our exported excel file. Are they in line with your requirement? (see attachments)

Please try the latest version: Aspose.Cells for Reporting Services 2008R2 (ACRS2008R2.V2.1.0.1).

Please copy Aspose.Cells.ReportingServices.dll into following folder :

${SQL Server 2008 R2 Reporting Services}\Reporting Services\ReportServer\bin.

We will keep checking it.

Issues still persists.
1) For no records always header are shown while exporting to excel using Aspose.

2) Attached is my file. If you don’t have issue on your side I suppose this can be data issue but I am amazed that when I remove header click functionality then report is exported fine.
Can you please tell me if i can see the specific stack trace where the file is getting crashed ?? Or can you please explain me the trace of attached file ? I was usig 2.1.0.0.When I used your mentioned dll still giving error.

Regards
Kanwar

Hi Kanwar,

Thanks for your feedback and using Aspose.Cells for Reporting Services.

We have logged your comment in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

Hi,

Thanks for using Aspose.Cells for Reporting Services.

We have found the issues about not showing NoRowsMessage and fixed it.

Please try the fixed version: Aspose.Cells for Reporting Services (SSRS2008) V2.1.0.8.

Please copy Aspose.Cells.ReportingServices.dll into following folder:

${SQL Server 2008 Reporting Services}\Reporting Services\ReportServer\bin.

Please let us know run result.

And other issues, we could not find, please post debug information for us .

· Update the logger value to debug in Aspose.Cells.ReportingServices.xml.

====================================================================



<level value=“debug”/>

……


==================================================================

· Run export program.

· Check Aspose.Cells.ReportingServices.log in installation folder.

Thanks Shakeel,


No row message issue has been resolved now and I am attaching the log file and exported file with this reply for the other issue.

Thanks
Kanwar

Hi Kanwar,

Thanks for your feedback and using Aspose.Cells for Reporting Services.

Please also provide us your RDL file which is causing this exception. We will look into your issue and help you asap.

Please find teh attached RDL.


Regards
Kanwar

Hi Kanwar,

Thanks for providing us RDL file and using Aspose.Cells for Reporting Services.

We have logged this issue it in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as

  • SSRS-40179 - Exception occurring on export - Additional non-parsable characters are at the end of the string.

Hi,

Thanks for using Aspose.Cells for Reporting Services.

We have tested your provided report definition file and could not find any issue.

Please try the latest version 2.1.0.9 (attached)

If you still face the issues, please post some information here.

1. Report Parameter running values .

2. MS default exported excel file.

Hello MShakeel,

Have attached the parameter value and I am unable to understand what you mean by MS default exported file ? We have attached the export which we get after exporting if it suits your requirements.



Regards
Kanwar

Hi Kanwar,

Thanks for your posting and using Aspose.Cells for Reporting Services.

We actually need XLS export which is built-in and done by SSRS engine. It means, the XLS export by SSRS report engine.

Hi,

We have evaluated your issue further. We could not reproduce it, so we update some exception handling.

Please try the new version/fix : Aspose.Cells for Reporting Services (SSRS2008R2) V21.0.11 (attached).

Please let us know your run result.

Thank you.

Hi,

Please try our latest version/fix: Aspose.Cells for Reporting Services V2.1.0.16 (attached).

Please copy Aspose.Cells.ReportingServices.dll into following folder: ${SQL Server 2008 Reporting Services}\Reporting Services\ReportServer\bin.

Please let us know run result.

If you still face the issues, please post your report file here.

We will check it ASAP.

Hello Amjad,

We have bought the license of Aspose and we are using Aspose for SSRS 2005 and 2008.

I have attached the RDL for reference. We are running this RDL with three different scenarios ( for different value of HisOption parameter) . Previously two scenarios were not opening (when setting HisOption as 1 and 2) and giving error. Now they are opening but they are giving below errors :-

1) The header seems to be missing when setting HisOption as 2( attached HeaderMissing.xlsx for reference) .

2) You might be aware that if we pass rs:Format = "ACXLSX" as parameter in Go to a report then normally Report will be opened as Excel file(on header Click, have attached normal.xlsx for Reference). Rest all files are working as expected but after your rectification the scenario where we set HisOption as 2 then cell click is ignoring the rs:Format parameter and opening the hyperlink in a browser instead of Excel (have attached HyperLink.xlsx for reference).

Kindly look into the issues ASAP.

Regards
Kanwar

Hi,


Thanks for providing us sample RDL and output Excel files.

I have logged the following issues into our database, we will investigate your issues in details and figure them out soon:
SSRS-40200 - The header seems to be missing when setting HisOption as 2
SSRS-40201 - After setting HisOption as 2 then clicking the cell ignores the rs:Format parameter when opening the hyperlink into the browser

Once we have any update on any of the above mentioned issues, we will let you know here.

Thank you.