IS "Microsoft Reports" good for us

SQL Server Reporting Services (SSRS)   “Microsoft Reports”

This is a report of the feasibility of using Microsoft Reports.

Executive Summary:

Presently we are using Data Dynamics VB6 reporting system. In the event of trying to get away from the VB technology we made an effort to convert to SAP Crystal reporting system. Our architecture presently consists of a report generator that is COM+ driven using ActiveX components that expose the component to other servers on the network. This component is then used with the  HMISellerTool  Classic ASP web application to create a contract in a .pdf file format and save it on the Hard drive. The contract is then exposed and delivered to the consumer for use.

Program Description:

Microsoft Reports a subset of the SSRS reporting system, is a report generation system that can generate the reports that we need. This tool is being considered in an effort to make a transition away from the VB6 component. There are two available tools that can be used to generate the forms we need:

·        Sql Server Report Builder

·        Visual Studio 

“Sql Server Report Builder”

Report builder is a standalone application that is a part of the Sequel Server group and resembles word as an development  IDE.  You can use Report Builder to create reports with multiple data regions (such as tables and charts), as well as data from multiple data sources within a single report. Report Builder also supports queries that run directly against relational data sources and multidimensional data sources, as well as user-friendly data access via published report models. 

Report Builder is a click-once application which allows you to run it without actually installing it.  When you install SQL Server Reporting Services (SSRS) 2008 R2, you must configure Reporting Services to run in native mode or SharePoint Integrated mode. 

Here is a good tutorial of Report Builder:

http://www.mssqltips.com/sqlservertutorial/2100/report-builder-30/ 

“Visual Studio”

Microsoft Visual Studio 2010 includes report design functionality and ReportViewer controls so that you can add full-featured reports in your Windows Form and ASP.NET AJAX applications. Report Designer allows you to create reports that contain tabular, aggregated, and multidimensional data. There are two versions of the control. The ReportViewer Web server control is an ASP.NET AJAX control used to host reports in ASP.NET AJAX projects. The ReportViewer Windows Forms control is used to host reports in Windows application projects. Both controls can process reports that have been deployed to a report server (remote processing mode) or have been copied to a computer where Microsoft SQL Server Reporting Services has not been installed (local processing mode). 

·        Local processing mode refers to report processing that is performed by the ReportViewer control in the client application. All report processing is performed in the local process using data that your application provides. This mode provides an alternative method for viewing and rendering reports when Reporting Services is not installed 

·        Remote processing mode refers to report processing that is performed by a SQL Server 2008 or above Reporting Services report server. In remote processing mode, the ReportViewer control is used as a viewer to render a report that is processed on a Reporting Services report server 

In Visual Studio, reports are saved as client report definition (.rdlc) files. These files are based on the same schema as report definition (.rdl) files published on SQL Server Reporting Services report servers, but they are stored and processed differently than .rdl files. At run time, the .rdlc files are processed locally, and the .rdl files are processed remotely. 

Deployment

You can freely distribute reports and the ReportViewer controls as part of your application. Deployment requirements vary widely depending on the control type and whether your report is configured for local or remote processing. You can deploy locally processed and remotely processed reports in the same application. The ReportViewer control redistributable is a self-extracting file named ReportViewer.exe that includes an .msi and other files. You can find ReportViewer.exe at the following location:

 %PROGRAMFILES%\Microsoft SDKs\Windows\v7.0A\Bootstrapper\Packages\ReportViewer\ReportViewer.exe

These are the files that are installed in the GAC

• Microsoft.ReportViewer.Common.dll

• Microsoft.ReportViewer.ProcessingObjectModel.dll

• Microsoft.ReportViewer.WebForms.dll

• Microsoft.ReportViewer.WinForms.dll

• Microsoft.ReportViewer.DataVisualization.dll 

http://msdn.microsoft.com/en-us/library/ms251723(v=vs.100).aspx

Deploying an ASP.NET application in a Web farm requires additional configuration to ensure that view state is maintained across the farm. If you are deploying the ReportViewer Web server control in a Web farm environment, you should specify the machineKey element in your application's Web.config file.

Reports that are processed locally can be stored in a network folder or compiled into the application executable. Reports that are processed on a remote server are typically stored on a SQL Server “Reporting Services report server” 

Report Servers

A report server is a computer that has SQL Server 2008 Reporting Services installed. It may also be a SharePoint site that is integrated with a report server. The report server stores published reports, shared data sources, shared schedules and subscriptions, and other resources. The report server system administrator can configure the report server to process report requests, maintain snapshot histories, and manage permissions for reports, data sources, and subscriptions. 

http://technet.microsoft.com/en-us/sqlserver/ff660783.aspx 

Considerations for installing Reporting Services

http://msdn.microsoft.com/en-us/library/ms143736(v=sql.105).aspx 

Pro’s and Con’s

I base the info on the following factors: scale of 1-5 with 5 being the best.

Category	Microsoft Reporting	Chrystal Reports
Ease of development	5	4
Ease of deployment	5	2
Ability to export data	5	5
Ease of support and find solutions on the web	5	3

 Both reporting tools use visual studio for the development of the Forms.

There is a problem with the deployment in our environment of the crystal report DLL’s. The Dill is called from the COM+ object so it has to be on the server to work. There is a conflict with installing the DLL’s due to application conflict with another application. We are not even sure that even if we were able to install Crystal reports on the component server the report would work across the network with the website application on a shared server.  

It seems that the SSRS Microsoft Report Services Dll is installed on the DB and the supporting files (listed above in the deployment section) would not be an issue on the environment we presently have. We use Sequel server 2005 presently.

Conclusion

This finding is based on the Local processing mode .Using Microsoft Reporting which is a part of the Sequel Server SSRS system is a good option to replace the system we have presently in place. The report can be created using Visual Studio to give it dynamic functionality and preform the task at hand. The deployment should not be a problem because the required DLL are allready on the server. The .Net Dll should convert to a COM+ component without a problem and share across the network.

References:

http://msdn.microsoft.com/en-us/library/bb885185(v=vs.100).aspx

http://technet.microsoft.com/en-us/library/dd220460(v=sql.100).aspx 

Components to install

http://technet.microsoft.com/en-us/library/ms143238(v=sql.90).aspx 

SQL Server 2005 SSRS

http://msdn.microsoft.com/en-us/magazine/cc188691.aspx

 

09/27/2013 Smoot