If you are using Reporting Services, the chances are sooner or later you’re going to need to invoke a Report from an ASP.Net Web application. In this article, we’re going to look at how to do this using URL Access. Fortunately for us, Microsoft has made this really easy and provided us a sample Report Viewer control. This is a Server Control which uses URL access to render reports within a web browser.
Both C# and VB versions of the source code are available, and if you used all the default installation options, they are installed under in C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\Samples\Applications\ReportViewer\vb and C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\Samples\Applications\ReportViewer \cs respectively. Open up the solution file ReportViewer.sln, (in your language of choice) in VS.Net and build the application. Now we are ready to create our web application using the ReportViewer Control. Create a new ASP.Net Web application project. This can be done in either VB or C#.
The next step after creating our project, is to add a reference to the ReportViewer.dll we created in the earlier step. To do this, click Tools --> Add/Remove Toolbox Items. In the Customize Toolbar dialog box click the .NET Framework Components tab, browse to the location where you created ReportViewer.dll and click open. The Report Viewer component has now been added to your toolbox and you can drag it into your web form just like you can any other control in the toolbox.
Having dragged the Report Viewer Control into our web form, you will change some of its properties. To do this, right on the control and click on the Properties tab. For the purpose of this article I am going to embed the Sample Reports provided by Microsoft within this web form. I have already opened the SampleReports solution, built it, and deployed it to my Report Server which in my case is on the same computer. I set the ReportPath property to my Report Server path ‘http://localhost/reportserver’ and the ReportUrl property to point to the Report I want to embed, in this case the Company Sales report ‘Samples/Company Sales’ It also makes sense to change the width and height properties of the Report Viewer control so that the report has sufficient real-estate in which to render. Compile and run the ASP.NET Web application to render the report in your Web browser.
Let’s take a look at what our ASPX page looks like
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<cc1:ReportViewer id="ReportViewer1"
runat="server" ReportPath="/SampleReports/Company Sales"
ServerUrl="http://localhost/ReportServer"
Width="95%" Height="95%">
</cc1:ReportViewer>
</form>
</body>
The Company Sales Report has no input parameters so we are going to change our code to render the FoodMart sales Report which does have parameters.
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<cc1:ReportViewer id="ReportViewer1"
style="Z-INDEX: 101; LEFT: 16px; POSITION: absolute; TOP: 56px"
runat="server" ReportPath="/SampleReports/FoodMart Sales"
ServerUrl="http://localhost/ReportServer"
Width="95%" Height="95%">
</cc1:ReportViewer>
</form>
</body>
If we leave our code as is, the report will render using the default parameter for ‘Product Family’ namely ‘Drink’. However we can also specify a different parameter
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<cc1:reportviewer id="ReportViewer1"
runat="server"
Height="95%" Width="95%"
ServerUrl="http://localhost/ReportServer"
ReportPath="/SampleReports/FoodMart Sales&
rs:Command=Render&
rc:Parameters=false&ProductFamily=Food">
</cc1:reportviewer>
</form>
</body>
Now if you run this application, you will see that it renders using ‘Food’ as the default parameter.
The next step is to how to specify this in code. Our web form, may display Reports based on User selection, or specify report parameters based on user access level. Lets look at another example
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<cc1:reportviewer id="ReportViewer1"
runat="server" Height="95%" Width="95%" >
</cc1:reportviewer>
</form>
</body>
In this case we have not specified the Report or the location of the Report Server in the ASPX page. Our code behind file looks something like this
protected Microsoft.Samples.ReportingServices.ReportViewer ReportViewer1;
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
}
We can put the following code, in our Page_Load method.
ReportViewer1.ServerUrl="http://localhost/ReportServer";
ReportViewer1.ReportPath="/SampleReports/Sales Order Detail";
Compile and run the application and the Sales Order Detail Report will render using the default Sales Order Number ‘SO8436’. Well the next obvious question is, what if we want to set another Sales Order Number in our code behind file.
Its worthwhile at this point to take examine and get atleast basic familiarity with the code in the ReportViewer sample. I am not going to go through it here, but I am going to add another method to ReportViewer Server Control – SetQueryParameter
public void SetQueryParameter(string ParamName, string ParamValue)
{
SetParameter(ParamName,ParamValue);
}
Rebuild ReportViewer.cs, and now we can set parameters in our web page. Add the following to the Page_Load method of your ASPX form.
ReportViewer1.SetQueryParameter("SalesOrderNumber","SO5018")
Now when your run the application the Sales Order Report will render using ‘SO5018’ as the Sales Order Number.
There are many other properties you can set using both the VS designer and through code, and you can check them out at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_urlaccess_959e.asp. Hope this helps those of you who are starting of with reporting services understand the basics of how to embed a Report in a web application using URL access.
Update: Since there have been so many email requests we have recieved about this article - we have added another one detailing how to embed the Report Viewer in a Web form
by Poonam Lall