Showing posts with label SSRS. Show all posts
Showing posts with label SSRS. Show all posts

Tuesday, January 7, 2014

Render Report Asynchronously using ReportExecutionService in SSRS with Custom Authentication

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.

  • 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
 
  
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!

Friday, November 22, 2013

Duplicate Subscription in SSRS

If anyone experience duplicate subscription creation in SSRS while using SOAP API,Don't be panicked. It would be very difficult to troubleshoot this issue.There is a bug in SSRS. It might have caused the issue.

Subscriptions can be created using CreateSubscription() SOAP API . This API creates Subscription and returns SubscriptionId. If you want to update the Subscription, SetSubscriptinProperties() API is used. If you pass SubscriptionID and updated Subscription data, It will update specified Subscription.

If you create Subscription using CreateSubscription() SOAP API and  update the Subscription using the returned SubscriptionId, Intead of updating existing subscription, New Subscription will be created. You will end up in duplicate subscription.

The reason is that CreateSubscription() method returns SubscriptionId with alphabets in lower case. But Original Subscription in SSRS was created with Upper cased SubscriptionId. When You pass the lower cased  Subscription ID to update the existing Subscription, SSRS internally checks with out ignoring case. Since It doesn't find any subscription, It just Create a new one. Even though the MSDN document doesn't say anything about Subscription creation, new subscription is created. Since this API method returns null, You cannot get the SubscriptionID also!.

So always convert the SubscriptionId to upper case and then  pass this converted SubscriptionId to call SetSubscriptionProperties() method.



Wednesday, June 5, 2013

Reporting with SSRS in Multitenant Cloud Environment

As Increasing number of companies started to move towards cloud computing,  Most of the market leaders rushed to deliver cloud based software products to the market. Multitenancy is the key to become successful in the cloud based product business .

There are two areas become more of concern for the cloud based product providers and consumers . They are Reporting and Integration with other products.  Most of the existing reporting products are unusable in cloud or tied to their own cloud platforms. It is unwise to create reporting framework for each product from scratch. Generally challenges associated with Cloud Reporting are
  1. Database sits in remote location and connectivity will be the issue.
  2. ODBC and OLEDB based access from client network might have been restricted due to safety issues.
  3. Most of the applications are multitenant application. Often tenants share the same database server with other tenants. Each tenants should be restricted to view the data of other tenants.
  4. Need a 3-Tier or n-Tier architecture for Report Designing, Report Rendering and other functionalities such as scheduling.

Microsoft SQL Server Reporting server is one of the time tested product in Reporting.  It works well with .NET based products. Immediate choice for the Microsoft technology based companies would be SQL Server Reporting Server. There are several advantages with SQL Server Reporting Services.

  1. SSRS has full fledged reporting capabilities.(Tops in Gartner Report).
  2. Comes free with SQL Server.
  3. Three Tier Architecture.
  4. Customizable
  5. Load balance support
  6. SSL support.
  7. Provides C# based programmatic access.
  8. Microsoft technology - Works well with .NET and SQLServer.
  9. Regular feature addition and good support.
 In the upcoming articles, Let's see how we can overcome the challenges of cloud and Multitenancy with SSRS and discuss about various options available.


Monday, January 7, 2013

Microsoft ignored the Power of Report Model

When Microsoft announced the SQLServer 2012  features,  There was  a shock for   SSRS2008 or earlier version users. The bad news is that Microsoft is going to discontinue Report Model approach in SSRS 2012. There will be backward compatibility so that old report model will work fine. But you can't create new report model using SQL Server 2012 technologies. That means  Report Model feature will be discontinued in future.

 Report Model approach is  relatively least popular technology from SQL Server Reporting Services. Business users know what information they need and how their report should look, but they usually don’t have the technical skills necessary to create a rich report design, to understand real-world database schemas, or to use formal query languages such as SQL. In contrast, IT staffers understand report design, the technical aspects of the data source schema, and formal query languages, but they are not available on demand to analyze, design, and deploy urgent or one-time reports. Therefore, businesses need a reporting solution that enables information workers to understand and  create reports on demand in short term without the dependency of IT staff.

Report Model Approach creates  a metadata layer on top of the database schema and preparing it to present the most-frequently requested data in the most intuitive and accessible manner by using business taxonomy. The metadata layer will then translate report design, filtering, and parameters into the correct and optimized native queries against the underlying physical data source.

Since Report creation using Report model is well integrated into Report builder, This feature allows basic business user to create simple reports using report model and Experienced developer can create complex report using SQL from the same SSRS application.

Even though SSRS 2012 provided Web UI for SSAS Cube reporting, there is no user friendly solution for Report generation from RDBMS.

But real power of Report model technology in SSRS is from some other least known area. Let's see about it.

Report Creation in Cloud Environment

Now world is moving towards cloud . Application servers, Database servers etc, are moving to cloud. creating Report from cloud hosted Database Server is a major challenge  As SaaS is gaining popularity with multitenant architecture, Real world problem of report creation from multitenant cloud hosted database is unimaginable.

 In cloud hosted application, Reports needs to be created using some web app or through click once application. Fortunately SSRS provided Report Builder tool which is a click once application. All the access to SSRS happens through web services. It is possible to make the protocol as https.

One of the primary challenge is the access to the database from Report generation tool. Normally ODBC,OLEDB and other connectors connects SQLServer database using 1433 port.If the application is hosted in cloud and if it is shared by multiple applications/customers, It is not practically possible to open 1433 port . You cannot open the Database server to "Public". Most of the Reporting tools access the database using ODBC/OLEDB or other access through 1433.

As per Microsoft, SSRS provided 3 Tier architecture even for report creation. Client Application always contact SSRS App Server and SSRS Report server contact database. This is the architecture that we want. But It is not happening. SSRS issue in this architecture  is discussed well in this post.

Then how to make a non-1433 port access to design/create the report from Report builder. Here is the power of Report builder comes. All the calls made from Report builder to create Report Model approach requires http or https port alone. It doesn't require 1433 port access.

Even though Report Model creation needs some overhead in metadata creation and maintenance , At least this approach solves one major technical problem in cloud . Of course, There are lots of bugs and usability issues with Report model based Report creation , Because of the above advantage, lots of Cloud users tends to use Report model. With The release of SQLServer 2012, Microsoft  announced its intention to stop supporting Report Model without providing proper alternative for RDBMS based reporting.

It is the time for new vendor to come up with reporting product which can design/create report without 1433 port access. If someone comes with above feature with multi tenant RDBMS support, He will rule Reporting Products world in the future.

Sunday, December 16, 2012

Bug in Microsoft SSRS architecture

Microsoft SQL Server Reporting services (SSRS) is one of the best reporting solution available in market. Since it is coming as free with SQL Server, Most of the companies tries to use it for Reporting solutions. For intranet based appliation, it works well. Now world is moving towards cloud. As Databases are moved to cloud and web based application are common , We need a Reporting solution with perfect 3 tier architecture. As per Microsoft documentation, SSRS is multi-tiered, with an application layer, server layer, and data layer . Since it follows three tier architecture, Any front end application like report manager, report builder or third party application can access the SSRS application layer via Http/Https connection. When the application is hosted in cloud, port for http or https alone needs to be opened.
Architecture from MSDN
Since https port alone needs to be opened, We can think SSRS as perfect solution. You can create a web application by embedding reportviewer and link to the report builder in to your application. Report viewer should  help to view the report and Report builder will be helped to design report. If you create your web application as above architecture and test it locally, It will work fine. The problem will start if you host your application in cloud. When you open the report builder and try to create a dataset, you will end up in getting the following error.

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

 
If you open the 1433 port for sql server to public, Application will work fine. What does mean? Yes. You are correct. Report builder is not a perfect 3 Tier click once application. It connects database directly from client using SQL port. If you start search the web for this issue, you can find lots of reference about the issue. Someone even created a bug in Microsoft Connect and Microsoft accepted this as a limitation and try to fix in "unknown" version in the future.

It is totally impossible to open SQL port to the universe. Now you are in real trouble. The only way to overcome the issue is to keep specific machine to access Report builder and relax firewall rule to access SQL port from that specific machine.

When raised this issue to Microsoft, they are not ready to accept it as product bug. But they are calling this as a documentation bug. But still the documentation bug was not fixed till the post is published. So If you try to use SSRS in cloud, Be aware of this issue and plan accordingly!

How to use SSRS in cloud environment? Microsoft provided a very good solution earlier and now destroying this again. Let's see about that solution in another post.