SQL Server Reporting Services in ASP.NET

18 views

Skip to first unread message

Niranjan Singh

unread,

Mar 23, 2011, 7:21:45 AM3/23/11

to .netgroup

This article features the following:

Building of Business Intelligence Report.

Deploying the Reports on the Report Server

Reporting in ASP.NET.

1) Business Intelligence Report:

New project form Visual Studio 2005 go for Business Intelligence and Select Report Server Project Wizard consists of two separate folders Share Data Sources and Reports.

Shared data source is the data source, which consists of the connection string of the database. We can use the same data source for different reports, which are used to retrieve the data from the same database. We have to use different data sources for the different databases.

First we have to create the Data Source and make it as shared one.

dsReports is the Share Data Source. Once the shared data source has been created this can be used for all the reports, which are pointing to the same database.

Next step is of creating the reports. Select the report folder add new item select the report immediately the following screen is displayed with the shared data source (dsReports) selected. Here we can create the new data source as well.

Go with next, which will ask about the query or the stored procedure we have to use. The following screen is displayed where we have to provide the either query or stored procedure "depinfo".

Next step is the selecting the report type either Tabular or Matrix

Next step is about displaying the report with fields in our required format.

After this Report Wizard screen came into the picture, where we have to provide the proper name for the report and check the check box for previewing the report.

Click finish then, since we have used stored procedure for retrieving data form the database and that store procedure having the parameter it will ask the value for displaying the report.

After providing the parameter then click the View Report button then Report is generated as shown.

This report contains three tab's called Data, Layout and Preview. Some modification should be done before deploying the report on the report server.

Select the DATA tab and do the following changes, actually it will displays

Now it has to be changed like this and in the command type we have to select the stored procedure and the stored procedure has to be changed and just stored procedure name has to be specified.

Select the properties of the stored procedure where we have to provide the parameter.

Under Fields Tab you are able to see the fields that are to be displayed by the Stored Procedure.

Under the Parameters tab you have to provide the parameters.

Now comes to the Layout Tab where actual design of the report will be present. Change the report as per the requirements.

All the properties for the report can be set in the Layout tab.

Next Preview tab is mainly used for previewing the data after providing the parameters.

After the completion next step is the deployment of the report on the report server.

2) Deployment of the Report on the Report Server.

To deploy the report on the report server select the properties of the Project and in the properties specify the report server in the Target Server URL field

Then select the report and select the properties "Deploy" then the report is deployed.

If you have face any problem you can deploy the report in another way, but you need the rights for deploying the reports on the reports server.

To deploy the report and to use those reports on the report server you have to create the data source on the report server for maintain the connection string, this data source is also of type shared data source.

After creating the shared data source on the report server the next step is to upload the report and for the report, this data source is assigned and set the properties.

To upload the report go with "Upload File" select the report "DepartmentReprt.rdl".

Next step, select the report assign the data source and set the parameters on the report server

Select the data source and assign the data source, which created on the report server.

After assigning the data source for the report, view the report, which in turn requires the parameter should be passed, the report can be displayed as follows

These are the steps to be followed when the report is deployed on the report server.

3) Reporting in ASP.NET:

To make a call for the report which is deployed on the Report Server following steps to be followed
1. To access the report from the Report Server we have to pass the Network credentials.
2. We have to add the reference for accessing the reporting services the dll is "MicroSoft.ReportViewer.Webforms"

In the application the following code is to be written to call the report from the report server

protected void btnReport_Click(object sender, EventArgs e)

{

ReportParameter[] parm = new ReportParameter[1];

parm[0] =new ReportParameter("deptno",txtDeptno.Text);

ReportViewer1.ShowCredentialPrompts = false;

ReportViewer1.ServerReport.ReportServerCredentials = new ReportCredentials("Reportfolder Name", "Password of the folder", "");

ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;

ReportViewer1.ServerReport.ReportServerUrl = new System.Uri("http://ReportServer/ReportServer");

ReportViewer1.ServerReport.ReportPath = "/ReportFolder/ReportName";

ReportViewer1.ServerReport.SetParameters(parm);

ReportViewer1.ServerReport.Refresh();
}

In the above code ReportParameter are the report parameters used to display the report.

ReportFolderName is the folder where we have deployed the report on the Report server.

Password of the Folder this is the password for that folder on the report server.

Next step is about the class, which implements the Microsoft.Reporting.WebForms.IReportServerCredentials interface for accessing the reports.

public class ReportCredentials : Microsoft.Reporting.WebForms.IReportServerCredentials

{

string _userName, _password, _domain;

public ReportCredentials(string userName, string password, string domain)

{

_userName = userName;

_password = password;

_domain = domain;

}

public System.Security.Principal.WindowsIdentity ImpersonationUser

{

get

{

return null;

}

public System.Net.ICredentials NetworkCredentials

{

get

{

return new System.Net.NetworkCredential(_userName, _password, _domain);

}

public bool GetFormsCredentials(out System.Net.Cookie authCoki, out string userName, out string password, out string authority)

{

userName = _userName;

password = _password;

authority = _domain;

authCoki = new System.Net.Cookie(".ASPXAUTH", ".ASPXAUTH", "/", "Domain");

return true;

}

This class is mainly used for passing the report credentials to access the report.