Saturday, July 28, 2012

Access SSRS Report from Net

SQL Server Reporting Services with its  range of  inbuilt functionality is one of the best application which helps in reports management. Due to  easy integration with technology like Dynamics AX, SQL and sharepoint  it not only proved to be best in market but the exposed web service provides a cool way of handling the programmable features and extend existing functionality.

 Reporting Services comes with a  complete set of tools for you to create, manage, and deliver reports, and APIs that enables to integrate or extend data and report processing in custom applications.

Lets discuss on a requirement where DAX needs to access SSRS report and get a filestream or just dowload file to a specific folder.The best way is to access reporting services web service and get file stream which can be handled as per requirement.Check out how can Net access SSRS report.

  

SSRS Web Service


The SSRS webservice exposes all the extensible methods which can help developer code extensible solutions.
The web service can be found at directory path -

  • <Installation directory> \Reporting Services \ReportServer
Check if following WSDL file is browsable -





Create a Net Solution


 Lets create a net solution and add service refererence of above web service.


Change App.config file to authentication mode NTLM


Add following binding security for the binding used -



 This will allow our client side to talk to server using Ntlm authentication mode.

Creation of Client which can communicate to SSRS


 Our first goal is to create a client which can talk to SSRS report server using the authentication of Ntlm as specified in app.config of our appication.Since SSRS  mostly uses windows authentication and I have my SSRS configured to accept window authentication lets create a client for the same.

 


 Now we have a client which can communicate to our SSRS webservice. Give it a test run and modification may be required if authentication mode is different in SSRS.
For this client its using windows authentication and which can be impersonated.
Impersonation is frequently used by reporting services to access data sources like DAX , SQL service so credentials will be delegated to appropriate data source.

 

Prepare Report Parameters


SSRS report may require several parameters to be passed like StartDate,Name of Company etc.
This parametrs can be wrapped up into ParameterValue object which contains two properties Name and Value.The below example shows a parameter of report called AX_CompanyName which will be passed on to report.




Load Report


Now we have client ready and so is our parameter lets go ahead and load the report.Loading report gives us a lot of details about a report. It requires the trusted user details,Full path of report ,execution history.

This will help us to load the report from SSRS web service.Here the trusted user header is the same username which is used to create client in step 2.The report name mentioned as second parameter is the  full path of the report in order to extract this path go to the report manager click on the report and go to Manage. There it will show the full path of report which can be pasted here.

Manage Sessions


 SSRS manages the session for every load request. This session is stored as part of ExecutionID passed as object in last request. So our next request if demands for executionHeader should contain this the same execution ID.




Set Parameters

Lets set the parameters which need to passed to SSRS report. Notice the executionID is extracted from last ExecutionHeader.Rest of parameters are pretty simple and mostly explained in above steps.


  

 Render Report

This is last step which fetches the report from reporting server based on the parameters passed in last steps.



Most of the parameters passed are self-explanatory .

ExecutionHeader and TrustedUserHeader are already discussed above,

Format -can be any of the type mentioned in microsoft knowledgebase-

DeviceInfo -An XML string that contains the device-specific content that is required by the rendering extension specified in the format parameter. For more information about device information settings for specific output formats.

Extracting the Report


Yay! If code so far is running good we already have report ad Ob which is output bytes was our target so far and we can now use this as per requirement.Lets store it in some file for timebeing.






Common Error Messages

  •  "The HTTP request is unauthorized with client authentication scheme 'Ntlm'. The authentication header received from the server was 'NTLM'"

This error is thrown when SSRS is unable to impersonate. Check if the execution account used in creation of client can be impersonated and you have following code Impersonation enabled for proxy client.
client.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation; 

  • Error during processing of ‘AX_CompanyName’ report parameter. (rsReportParameterProcessingError) 

Check if the AX_CompanyName passed as parameter is correct and the proxy
account used to establish connection with the SSRS has access. IF the SSRS is
running on windows authentication make sure the proxy account have windows credentials set and it has proper access to SSRS/DAX business connector


2 comments:

  1. Nice article to start mate..keep going :)

    ReplyDelete
  2. Thanks, goog article.

    ReplyDelete