Integrating SQL Server Reporting Services with ASP.NET 3.5

Today, I am going to discuss Basic and Simple demo on Integration of SSRS with ASP.NET 3.5, I am assuming that readers have already have installed Visual Studio 2008, SQL Server 2008 [with Reporting Features] and Report Builder 2.0

Just to have idea those who are new to SSRS, SQL Server Reporting Services 2008 are in a way unique due to their architecture, Now we have a separate block of Tablix, Tablix is combination of Table + Matrix [In short, Table + Matrix = Tablix].

Since Microsoft have great association with .NET Dundas visualization tool, so now you can implement Graphs, Gauges in your reports with ease and with much more Rich UI, which you can create using tools like Report Builder 2.0.

I am showing this example with Northwind Database. I assume you are comfortable with Report Builder 2.0 to design your report.

What I would like to discuss over here before we go for ASP.NET integration is how we can enhance performance of Report say for like getting 1 lac or more records, So We can Implement Cache for the same.

Step 1 : Cache the Report for better Performance

Start your Reporting Manager instance [ You can find URL of that in your Reporting Services Configuration], Once you add Report to your directory, Click on Report and locate “Properties” tab, Click on Properties and you will find Cache settings in “Execution” option on left hand side.

Note : You can only set Cache if and only if you provide valid credentials for “DataSources” option just above “Execution” option, else it will not allow you to implement Cache.

Once you apply Cache, Test report in Report Manager, Close the Report Manager instance and Start Visual Studio 2008

I tried from 60k to 1 lac records and it worked out very smoothly, so I personally find this feature as “Edge” over Crystal Reports.

Step 2: Integrate Report with ASP.NET 3.5 and Send parameters to Report from ASP.NET Web Application

You already have “MicrosoftReportViewer” Control in your Visual Studio 2008 under Reporting tab on Toolbox.

I am now taking one instance of ReportViewer with one TextBox to accept paramaters and one command button to submit that parameters to Report.

ASP.NET Code :

<div>
   <table>
       <tr>
           <td>Enter Customer ID: </td>
           <td><asp:TextBox Width="180" runat="server" ID="txtparam"/></td>
           <td><asp:Button Text="Show Report" ID="btnSubmit" runat="server" onclick="btnSubmit_Click" /></td>
       </tr>
   </table>  
   <rsweb:ReportViewer ID="rptvw" runat="server" Height="600px" Width="800px"></rsweb:ReportViewer>
   </div>

C# Code :

using Microsoft.Reporting.WebForms;

….

protected void btnSubmit_Click(object sender, EventArgs e)
    {
        rptvw.ProcessingMode = ProcessingMode.Remote;
        rptvw.ServerReport.ReportServerUrl = new Uri("http://localhost/reportserver");
        rptvw.ServerReport.ReportPath = "/Northwind/NewNorthwind";

        ReportParameter[] param = new ReportParameter[1];
        param[0] = new ReportParameter("CustomerID", txtparam.Text);
        rptvw.ServerReport.SetParameters(param);

        rptvw.ServerReport.Refresh();
    }

Explanation of Code :

Currently, I am sending only one parameter as “CustomerID” to my Report. SSRS basically supports two Processing Modes as “Remote” and “Local”, Since there is no support available for “Local” mode, Developers are suppose to fetch reports directly from Reporting Server, So I have set mode as “Remote” and have set those paths which you can see in code.

This is most simple way to integrate SSRS with ASP.NET, some other ways are using WebService [WSDL which RS exposes], but I personally find that time consuming and bit difficult compare to this one.

Hope this will give you idea about how you can enhance performance of your report with implementation of Cache and how easy is integration with ASP.NET 3.5 Web Application.

On ending note..

SSRS now give you power to export your reports in various formats like XML,PDF,CSV,TIFF and other Standard Office document formats, Also Report Builder 2.0 now implements most of the Office features like Zooming and Ribbon Controls for design and editing etc.

Vikram.