Rendering Long running report from SSRS is always one of the nightmare for users. These reports can not be run by user in real time. It might take more time for the user to view the report. To overcome the issue user can
1. Schedule a long running report and view it from Mail/Shared Folder
2. Render a report in the background process using ReportExecutionService
This back ground execution can be used to export big chunk of data from the database as well. As Scheduler/ReportExecution service can transfer the data to CSV,PDF,Excel,XML,Tiff etc format, this can be used as Exporting tool. Normally Tablix type of report are used for these Export functionality.
Scheduling option is the best solution if you use SSRS directly. If you use SSRS using custom application, Report Execution Service provides more flexibility. If you want to invoke this service in ad-hoc manner for multiple reports, Later suits well.
As any other coding that are related to SSRS processing, Rendering Report also has it own challenge. In this post, We can see How Report can be rendered asynchronously using ReportExecutionService from WCF Service using Custom Authentication in SSRS.
This Post covers the following topics
1. How to Render Report from WCF service
2. How to use custom authentication in ReportExecutionService
3. How to invoke ReportExecutionService Asynchronously from WCF service
4. Various Timeouts Involved in SSRS
5. Exported data issues in XML and Excel
1.Make sure to increase various Timeout for the Rendering Report
a) DatabaseQueryTimeout element in RSReportServer.config – Specifies the number of seconds after which a connection to the report server database times out. Set the larger value
b) Set ReportTimeout value to higher value.
If you want to process very Large report which can take hours, this value should be a big number.
c) ReportExecutionService proxy's Timeout should be set to higher value. So that service call can wait until the long running report finishes its execution. We can see more about this later in the post.
This MSDN article gives more idea about various timeouts.
2.Get proxy using proxy generator instead of Web Reference
You can provide web reference using Adding Service Reference method. Most of the time, It doesn't create proper proxy class. You will end up getting API methods as class name. To avoid this, use WSDL tool create proxy. Then you can include the proxy file in your WCF service application.
3. Custom Authentication for Report Execution Service
To make use of SSRS Custom Authentication, Authentication Cookie needs to be passed with every request to SSRS. It has the following steps
a).Inherit ReportExecutionService Proxy class
b).Override GetWebRequest and GetWebResponse methods. So that you can send the authentication cookie along with the Request
c). Call LogonUser() SOAP API to receive authentication cookie
d) Use the Same proxy class for the Subsequent requests.
Lets assume that ReportExecutionService is the Proxy class name, Here is the inherited proxy code to inject authentication cookie
Next step is to create Object for proxy and call the Logonuser to get authentication cookie for further calls.
Next step is to implement the callback
Possible Export Issues
1.If You try to export data into XML , You may end up in missing tags for some rows. If null value are present in the database, SSRS renderer will ignore those column. To avoid this, Follow this link.
2.If you try to export the data to Excel, Sometimes your excel column width will be fixed and you may not able to resize it. If so, Remove all other element in the Tablix report and set Table cells "CanGrow" Property to true.
Happy Coding!
1. Schedule a long running report and view it from Mail/Shared Folder
2. Render a report in the background process using ReportExecutionService
This back ground execution can be used to export big chunk of data from the database as well. As Scheduler/ReportExecution service can transfer the data to CSV,PDF,Excel,XML,Tiff etc format, this can be used as Exporting tool. Normally Tablix type of report are used for these Export functionality.
Scheduling option is the best solution if you use SSRS directly. If you use SSRS using custom application, Report Execution Service provides more flexibility. If you want to invoke this service in ad-hoc manner for multiple reports, Later suits well.
As any other coding that are related to SSRS processing, Rendering Report also has it own challenge. In this post, We can see How Report can be rendered asynchronously using ReportExecutionService from WCF Service using Custom Authentication in SSRS.
This Post covers the following topics
1. How to Render Report from WCF service
2. How to use custom authentication in ReportExecutionService
3. How to invoke ReportExecutionService Asynchronously from WCF service
4. Various Timeouts Involved in SSRS
5. Exported data issues in XML and Excel
1.Make sure to increase various Timeout for the Rendering Report
a) DatabaseQueryTimeout element in RSReportServer.config – Specifies the number of seconds after which a connection to the report server database times out. Set the larger value
b) Set ReportTimeout value to higher value.
- This can be set from Report Manager =>Report=>Properties=>Processing Options.
- If use SOAP API, Use the SetProperties API
Property[] props = new Property[1];
Property timeoutProp = new Property();
timeoutProp .Name = "ReportTimeout";
timeoutProp .Value = 20000;
props[0] = timeoutProp ;
reportName = "Myreport";
rService.SetProperties(reportName, props);
If you want to process very Large report which can take hours, this value should be a big number.
c) ReportExecutionService proxy's Timeout should be set to higher value. So that service call can wait until the long running report finishes its execution. We can see more about this later in the post.
This MSDN article gives more idea about various timeouts.
2.Get proxy using proxy generator instead of Web Reference
You can provide web reference using Adding Service Reference method. Most of the time, It doesn't create proper proxy class. You will end up getting API methods as class name. To avoid this, use WSDL tool create proxy. Then you can include the proxy file in your WCF service application.
3. Custom Authentication for Report Execution Service
To make use of SSRS Custom Authentication, Authentication Cookie needs to be passed with every request to SSRS. It has the following steps
a).Inherit ReportExecutionService Proxy class
b).Override GetWebRequest and GetWebResponse methods. So that you can send the authentication cookie along with the Request
c). Call LogonUser() SOAP API to receive authentication cookie
d) Use the Same proxy class for the Subsequent requests.
Lets assume that ReportExecutionService is the Proxy class name, Here is the inherited proxy code to inject authentication cookie
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;
using Microsoft.SqlServer.ReportExecution;
class RESProxy:ReportExecutionService
{
public Cookie SSRSSSRSAuthCookie
{
get
{
return m_SSRSAuthCookie;
}
set
{
m_SSRSAuthCookie = value;
}
}
private Cookie m_SSRSAuthCookie = null;
protected override WebRequest GetWebRequest(Uri uri)
{
HttpWebRequest request;
request = (HttpWebRequest)HttpWebRequest.Create(uri);
CookieContainer cookieJar = new CookieContainer();
request.CookieContainer = cookieJar;
Cookie SSRSAuthCookie = SSRSAuthCookie;
// if the client already has an auth cookie
// place it in the request's cookie container
if (SSRSAuthCookie != null)
{
request.Headers.Add("RSAuthenticationHeader", SSRSAuthCookie.Name);
request.CookieContainer.Add(SSRSAuthCookie);
}
request.Timeout = -1;
return request;
}
protected override WebResponse GetWebResponse(WebRequest request)
{
WebResponse response = base.GetWebResponse(request);
string cookieName = response.Headers["RSAuthenticationHeader"];
// If the response contains an auth header, store the cookie
if (cookieName != null)
{
HttpWebResponse webResponse = (HttpWebResponse)response;
Cookie SSRSAuthCookie = webResponse.Cookies[cookieName];
// If the auth cookie is null, throw an exception
if (SSRSAuthCookie == null)
{
throw new Exception(
"Authorization ticket is not recieved");
}
// otherwise save it for this request
SSRSAuthCookie = SSRSAuthCookie;
}
return response;
}
}
Next step is to create Object for proxy and call the Logonuser to get authentication cookie for further calls.
var rs= new RESProxy();
rs.Url = @"http://localhost/reportserever/ReportExecution2005.asmx"
rs.LogonUser("userid", "password",null);
If you use Windows Authentication, WCF service account should have proper privilege in SSRS . You can create a proxy class from original referenced class and you don't need to create inherited proxy class .
var rs = new ReportExecutionService() rs.UseDefaultCredentials = true;
4.Asynchronously invoking the ReportExecutionService
Once the Proxy is ready with authentication cookie, Now call the webservice render method asynchronously. This MSDN article will give good idea about execution Lifecycle. It will be useful if you use cached reports in SSRS
Once the Proxy is ready with authentication cookie, Now call the webservice render method asynchronously. This MSDN article will give good idea about execution Lifecycle. It will be useful if you use cached reports in SSRS
var rs= new RESProxy();
rs.Url = @"http://localhost/reportserever/ReportExecution2005.asmx"
rs.LogonUser("userid", "password",null);
string reportPath = @"/RootFolder/MyReport";
string format = "CSV";
string historyID = null;
string devInfo = "";
string encoding;
string mimeType;
string extension;
Warning[] warnings = null;
string[] streamIDs = null;
ExecutionInfo execInfo = new ExecutionInfo();
ExecutionHeader execHeader = new ExecutionHeader();
rs.ExecutionHeaderValue = execHeader;
execInfo = rs.LoadReport(reportPath, historyID);
rs.Timeout = 1000000;
rs.RenderAsync(format, devInfo);
AsyncCallback cb = RenderCallback;
RenderService.BeginRender(format, devInfo, cb, rs);
Next step is to implement the callback
public void RenderCallback(IAsyncResult ar)
{
var logMessage = new StringBuilder();
var rs = (RESProxy)ar.AsyncState;
byte[] results = null;
string extension;
Warning[] warnings = null;
string[] streamIDs = null;
string mimeType;
string encoding;
results = rs.EndRender(ar, out extension, out encoding, out mimeType, out warnings,
out streamIDs);
File.WriteAllBytes(@"C:\ReportData.csv", result);
}
Possible Export Issues
1.If You try to export data into XML , You may end up in missing tags for some rows. If null value are present in the database, SSRS renderer will ignore those column. To avoid this, Follow this link.
2.If you try to export the data to Excel, Sometimes your excel column width will be fixed and you may not able to resize it. If so, Remove all other element in the Tablix report and set Table cells "CanGrow" Property to true.
Happy Coding!
