Thursday, December 25, 2008

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.

SSRS1

SSRS2

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();
    }

SSRSOP

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.

17 comments:

Unknown said...

Hi Vikram.
It was really a nice article.
But I am finding it difficult to set the reportPath.Report server url is set up and works fine but i am having errors while setting up the reportPath.Can you just explain whats the funda behind setting up the reportPath?
Thanks in advance.

Vikram Pendse said...

Hi punter,

You need to configure Report Server once you install SQL Server 2008, In Visual Studio 2008 environment, right now support is only provided for "Remote" mode, so you need to explicitly call that report from Report Server. You may see "Local Mode" but it is not working and no official explanation I find from Microsoft on the same.

Meanwhile many people "suffer" from setting up ReportServer and Path like you, I encourage you to visit following thread started by me which discuss some issues with other users :

http://bdotnet.in/forums/t/1281.aspx

Let me know if there is anything specific you want, I will try my best to help you out, though SQL is not my core domain :)

Vikram.

Unknown said...

Hi Vikram.
I set the path correctly this time and it works fine.
I am unable to pass parameters.
I passed a single parameter i.e Category_ID to the report.
The report deployed previously is excuted by the help of a simple storedproc (ie.select query).
While excuting my asp.net codes its saying me that "Category_ID" is not found in this context.
Please help !!!
Thanks in advance.

Unknown said...

Hi vikram.
hope I am not disturbing u much.I have successfully integrated the reporting services in my webpage.I used a sp to run the report.But it took the parameter by itself.Means While I am running the application automatically a textbox appears along with a "view report" button.But functionality is right.I was unable to pass the parameter from my webpage.
Waiting for ur reply.
thanks in advance.

Vikram Pendse said...

Hi Punter,

Good to know that you able to integrate report with ASP.NET, as far as your param issue is concern, basically we have option in Report Builder 2.0 to set-reset param.

The Code which I have written in article is upto the mark for sending param from ASP.NET to Report, so you can refer the same.

If that Textbox bothering you much [I think it must be showing you both TextBox, The one you created in ASP.NET and one with Report so must be clashing], You can put following line to hide the TextBox which comes with Report.

rptvw.ShowParameterPrompts = false;

This will hide/disable TextBox with Report which is next to View Report button as you mentioned, and now you can pass param smoothly to report with the code I have given.

feel free and let me know if you still have any queries, will try best.

Vikram.

Unknown said...

Hi Vikram.
Thanks for your concern.
I am forwarding my stored proc(i.e from which my report is running) and my button click code.But I am not able to pass the parameter from my webpage to the report.
Pls check..
stored proc..
-------------
ALTER PROCEDURE USP_GetReport
(
@Cat_ID varchar(10)
)
AS
BEGIN
if isnull(@Cat_ID,0)=0

select Category_ID,Category_Name,Description,Remarks from tab_test_Category

else

select Category_ID,Category_Name,Description,Remarks from tab_test_Category where Category_ID = @Cat_ID

END
GO

code...
----------

rptViewer.ProcessingMode = ProcessingMode.Remote;
rptViewer.ServerReport.ReportServerUrl = new Uri("http://pmapbbsr/SQLReportserver");
rptViewer.ServerReport.ReportPath = "/PrameterisedReport/paramRpt";
ReportParameter[] param = new ReportParameter[1];
param[0] = new ReportParameter("Category_ID", txtCatID.Text);
rptViewer.ServerReport.SetParameters(param);
rptViewer.ServerReport.Refresh();



its showing me Category_ID doesnt exist on th report..

Aviator said...

Hi Vikram,

The example you have shown is very nice and it helps me a lot.

I am using SSRS with asp.net using Report Viewer Control.Everything is working fine but I am not able to do paging. I have set the height of Report Viewer Control as 600px. Now if there are records the veritical scroll bar comes automatically and it it binds the whole report in a single page.
Is there any way to to the paging for certain number of rows say 14 rows per page.
I have tried rptvw.SizeToReportContent = false;
but does not work.
Is there any other control for integrating SSRS. It will be great if you resolve my issues.

Unknown said...

Hi Vikarm

It is very useful article..

Thank you.....

Chintan Shah said...

Vikram,

This is really nice article. We have similar situation in which we need to generate SSRS report on demand(button click). We have oracle database and SSRS 2005. Can you please suggest something?

Anonymous said...

Hi, Vikram.

Very good article.

Your code worked like a charm. Something of note, however, is that if your parameter has spaces in it, the builder removes those spaces in the parameter name. That was the only stumbling stone in implementing your code.

Bhaskara said...

Thakns a lot .This is an exact solution what i search from last two days

Sunil Bisht said...

hello Vikarm ,
I am very new to SSRS.. Before I used crystal reports but now I have keen intersent in SSRS.. but I have ODBC and Oracle option.. Can i use SSRS with them , if yes than how???
Plz Help me out from this..
Thanks in Advance

test said...

Hi
Please Help me on this...
i am not able integrate ssrs 2008 rs to asp.net - 2005

Unknown said...

Hi vikarm,

I am accessing my SSRS reports from my server in the same way to telling.
For first page there is no problem ,when going for report second page the reportviwer is continuously loading(on refresh button click also same problem).So loaded the report again and I got the second page but the tool bar is disabled .I tried to make it enable with coding but not working.

any help is appreciated....

Narasappa said...

Hi Vikram Ji im New to SSRS im trying to bind Stored Procedure which Accepts 3 parameters of different data types so i used Below code
protected void Page_Load(object sender, EventArgs e)
{
int month = 11;
int year = 2012;
string system = "DPO";
rptview.ServerReport.ReportServerUrl = new System.Uri("http://localhost/reportserver");
rptview.ServerReport.ReportPath = Server.MapPath("~/GetMonthlyProfit.rdlc");
rptview.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;
rptview.ShowParameterPrompts = false;
rptview.ShowPrintButton = true;
rptview.ToolBarItemBorderColor = System.Drawing.Color.PowderBlue;

rptview.ToolBarItemBorderStyle = BorderStyle.Double;
//
ReportParameter[] param = new ReportParameter[3];
param[0] = new ReportParameter("month",Convert.ToString(month));
param[1] = new ReportParameter("year",Convert.ToString(year));
param[2] = new ReportParameter("system", system);
rptview.ServerReport.SetParameters(param);

rptview.ServerReport.Refresh();

}
When i run the program its Giving Error Like
"The underlying connection was closed: The connection was closed unexpectedly." on rptview.ServerReport.SetParameters(param);...
can u suggest me where i went wrong and wat will be the solution

Unknown said...

Hi Vikram,

I'm working on Loading server rdlc report into web page. I want to send server credentials to get the rdlc report.

I have given the path properly. But how to set credentials.

I'm getting the error "The request failed with HTTP status 401: Unauthorized."


Thanks in advance

Anonymous said...

Excellent code and tutorial...ACC 561 Individual Assignment