Click here to Skip to main content
15,861,168 members
Articles / Web Development / ASP.NET
Article

Integrate Reporting Services with VS.NET pages and VS.NET controls

Rate me:
Please Sign up or sign in to vote.
4.00/5 (11 votes)
31 Aug 20055 min read 156.2K   63   22
Integrate reporting services with VS.NET pages and VS.NET controls.

Introduction

I'm going very quickly and deeply into the integration of .aspx pages with reporting services reports. If you are new to reporting services this is not an article for you. I will show you how to integrate reports into .aspx pages and alter the content of the reports with web server controls such as drop down boxes and calendars. I take for granted you have a developer machine setup with SQL Server developer edition, Visual Studio .NET, and Reporting Services installed on your client with online samples and books installed as well. I will also not get into designing reports and placing them on your Report Server.

Get Microsoft report viewer

In the samples that are bunged with Microsoft reporting services there is a Report Viewer, this is supplied in VB as well as in C#. I will be concentrating on the C# version. The Report Viewer will allow you to display reports inline with other controls and HTML on a .aspx page.

The report viewer project can be found here:

C:\Program Files\Microsoft SQL Server\MSSQL\Reporting 
               Services\Samples\Applications\ReportViewer\

You can add this as a sub project to your solution or compile the project to a DLL and add that as a reference to your new reporting project. We will do the latter and add the compiled DLL to our new project.

  1. Open the folder ReportViewer in samples browse to cs (for C# version).
  2. Double click or open the ReportViewer.sln (i.e. open the ReportView solution in Visual Studio).
  3. Build the solution.
  4. Close the project.

Visual Studio has now created a few more directories including a bin directory with a debug version of ReportViewer.dll (you can change the build type to produce a release version if you want).

Configure VS to use Report Viewer

Now you can add ReportViewer.dll to this your web project and view reporting:

  1. Copy the ReportViewer.dll into your bin directory or your web project.
  2. In Solution Explorer: Right-Click references, Add a reference, Projects TAB, browse and select ReportViewer.dll in your bin directory.
  3. Now add a ReportViewer document to the toolbox: Right Click Toolbox, Add Remove Items, Browse to bin directory and select ReportViewer.dll.

You should have ReportViewer on your Toolbox in Visual Studio.

Image 1

Simple Report Viewer on web pages

This is the real easy bit, drag ReportViewer from the Toolbox onto your webpage:

Image 2

So, now we have to tell ReportView where to find a report. Right click to see the properties of the ReportViewer:

Image 3

As can be seen above, I've set ServerUrl and the ReportPath. This will show the report as shown on the default webpage for reporting server except that it's encapsulated into a ASP.NET web page. Unless you specify a report ServerURL and ReportPath the component does not let you alter the size and height of the report on the web page.

Useful parameters

  • ServerUrl: The website server of your MS Reporting Server is installed.
  • ReportPath: The path on the Reporting Server with the report name (Folder/report name).
  • ToolBar: Turn the toolbar on or off, good for exporting to Excel, PDF etc.
  • Parameters: Show parameter fields to the user (i.e. input fields that drive the report data).
  • Zoom: Handy for printing directly from the web pages.

Top Tip: Store the ServerURL in web.config application key so that it can be used throughout your application. System administrators have a habit of changing the backend servers.

web.config

XML
<appSettings>t;
    <add key="Key_ReportServer" 
         value="http://myServer/reportserver"/>
</appSettings>

C# code

C#
private void Page_Load(object sender, System.EventArgs e)
{
    // Put user code to initialize the page here

    ReportViewer1.ServerUrl  = 
      (ConfigurationSettings.AppSettings["Key_ReportServer"]);
}

Build a parameter report

Some reports take parameters and you detail what information is to be displayed by the start date, year, end date etc. I generally use SQL Server stored procedures to drive reports as I can change the backend SQL logic without the hassle of uploading and changing the report.

Below is a very simple stored procedure used to get the Order Qty (number of orders) over a set year. The SQL is included for reference:

SQL
CREATE PROCEDURE usp_SalesQty
@Year as int
AS
select * from Ord_Qty
Where YearInt = @Year
GO

I have put this in a simple report that displays a table by Month and Year with a Qty value. I also placed a chart on the report to show the quantity of sales by month. I have uploaded Report5.rdl to my Reporting Services server called in a test directory. Note that when the report is created by using the above stored procedure it will pull the parameter @Year as a report parameter automatically.

There are two ways I could view the report on the web page: Enter report details into parameters as shown above or use the code to tell the ReportViewer component where to find the report. Since we're all programmers I will use the code.

Change the report with the code

C#
ReportViewer1.ServerUrl = 
     (ConfigurationSettings.AppSettings["Key_ReportServer"]);
ReportViewer1.ReportPath = "/test/Report5";

Specify the Parameters of the Report component what the ServerUrl and the ReportPath are and the report will appear on the screen on build on browse. You still have to enter a year into the Parameter field just like going to reporting services directly; which is a bit crude for users, the toolbar is visible we will turn those off so that they are hidden from the user.

C#
ReportViewer1.Toolbar = 
  Microsoft.Samples.ReportingServices.ReportViewer.multiState.False;
ReportViewer1.Parameters = 
  Microsoft.Samples.ReportingServices.ReportViewer.multiState.False;

Top Tip: You have a single ReportViewer component that can be used to display more than one report.

Integrating web components

By placing a drop down list onto the web page and setting three variables (2002, 2003, 2004) we can use this to drive the Report parameter. You will have to ensure that autopostback is enabled on the drop list control.

C#
private void DropDownList1_SelectedIndexChanged(object sender, 
                                             System.EventArgs e)
{
   string param = "&Year=" + DropDownList1.SelectedValue;
   ReportViewer1.ReportPath = "/test/Report5" + param ;  
}

A more complex example is here pulling the date info from a calendar control and a drop down:

C#
string ReportPath = "/Reports/Invoice";
string param = "&Year=" + yearint + "&Month=" + monthint 
                              + "&Day=" + dayint + 
                              "&Cust=" Drop_Cust.SelectedValue;
ReportViewer1.ReportPath = ReportPath + param ;

Turning on toggle toolbar

You can also place a web control button on your page to toggle the toolbar on and off.

C#
private void Button1_Click(object sender, System.EventArgs e)
 {
    ReportViewer1.Toolbar = 
      Microsoft.Samples.ReportingServices.ReportViewer.multiState.False;
 }

Page view

Image 4

Complete code

C#
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Configuration;

namespace ReportWeb
{
    /// <summary>
    /// Summary description for WebForm1.
    /// </summary>
    public class WebForm1 : System.Web.UI.Page
    {
        protected System.Web.UI.WebControls.DropDownList DropDownList1;
        protected System.Web.UI.WebControls.Button Button1;
        protected Microsoft.Samples.ReportingServices.ReportViewer ReportViewer1;
        
        private void Page_Load(object sender, System.EventArgs e)
        {
            // Put user code to initialize the page here
            
            if (!IsPostBack)
            {
              string param = "&Year=" + DropDownList1.SelectedValue;
              ReportViewer1.ReportPath = "/test/Report5" + param ;
              ReportViewer1.ServerUrl = 
                 (ConfigurationSettings.AppSettings["Key_ReportServer"]);
              ReportViewer1.Toolbar = 
                Microsoft.Samples.ReportingServices.ReportViewer.multiState.False;
              ReportViewer1.Parameters = 
                Microsoft.Samples.ReportingServices.ReportViewer.multiState.False;
            }
        }
        
        #region Web Form Designer generated code
        override protected void OnInit(EventArgs e)
        {
            //
            // CODEGEN: This call is required by the ASP.NET Web Form Designer.
            //
            InitializeComponent();
            base.OnInit(e);
        }
        
        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        { 
            this.DropDownList1.SelectedIndexChanged += 
                new System.EventHandler(this.DropDownList1_SelectedIndexChanged);
            this.Button1.Click += new System.EventHandler(this.Button1_Click);
            this.Load += new System.EventHandler(this.Page_Load);
        }
        #endregion
        
        private void DropDownList1_SelectedIndexChanged(object sender, 
                                                           System.EventArgs e)
        {
            string param = "&Year=" + DropDownList1.SelectedValue;
            ReportViewer1.ReportPath = "/test/Report5" + param ;
        }
        
        private void Button1_Click(object sender, System.EventArgs e)
        {
            ReportViewer1.Toolbar = 
               Microsoft.Samples.ReportingServices.ReportViewer.multiState.False;
        }
    }
}

History

  • V1.0 - Initial release.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Software Developer (Senior)
United Kingdom United Kingdom
Frank Kerrigan

Currently developing Insurance systems with SQL Server, ASP.NET, C#, ADO for a company in Glasgow Scotland. Very keen on OOP and NUNIT testing. Been in IT forever (20 years) in mix of development and supporting applications / servers. Worked for companies big and small and enjoyed both.

Developed in (newest first) : C#, Progress 4GL, ASP.NET, SQL TSQL, HTML, VB.NET, ASP, VB, VBscript, JavaScript, Oracle PSQL, perl, Access v1-2000, sybase/informi, Pic Controllers, 6502 (ask your dad).

Msc .Net Development Evenings www.gcu.ac.uk
MCAD Passed
MCP C# ASP.NET Web Applications
MCP SQL Server 2000
HND Computing
OND / HNC Electrical Engineering,

Comments and Discussions

 
Questionmissing reportviewer project and dll [modified] Pin
jivangilad@yahoo.com28-Nov-06 22:56
jivangilad@yahoo.com28-Nov-06 22:56 
AnswerRe: missing reportviewer project and dll Pin
Frank Kerrigan30-Nov-06 8:39
Frank Kerrigan30-Nov-06 8:39 
GeneralThe stored procedure parameters Pin
sergio_m9911-Jul-06 7:21
sergio_m9911-Jul-06 7:21 
GeneralReportViewer component is disabled!!!!! Pin
ersinsivaz14-Jun-06 21:59
ersinsivaz14-Jun-06 21:59 
GeneralRe: ReportViewer component is disabled!!!!! Pin
Frank Kerrigan15-Jun-06 6:51
Frank Kerrigan15-Jun-06 6:51 
GeneralInserting Image in my Report (ReportViewer) Pin
anderslundsgard23-May-06 4:52
anderslundsgard23-May-06 4:52 
GeneralChart Pin
Tuaregue11-May-06 0:59
Tuaregue11-May-06 0:59 
GeneralRe: Chart Pin
Frank Kerrigan11-May-06 12:53
Frank Kerrigan11-May-06 12:53 
GeneralI dont have reportviewer.dll Pin
osmanayhan24-Apr-06 3:40
osmanayhan24-Apr-06 3:40 
GeneralRe: I dont have reportviewer.dll Pin
Frank Kerrigan24-Apr-06 7:56
Frank Kerrigan24-Apr-06 7:56 
GeneralRe: I dont have reportviewer.dll Pin
osmanayhan25-Apr-06 5:56
osmanayhan25-Apr-06 5:56 
GeneralRe: I dont have reportviewer.dll Pin
Andrew D. Bryan27-Apr-06 5:53
Andrew D. Bryan27-Apr-06 5:53 
GeneralRe: I dont have reportviewer.dll Pin
osmanayhan27-Apr-06 19:57
osmanayhan27-Apr-06 19:57 
GeneralRe: I dont have reportviewer.dll Pin
osmanayhan27-Apr-06 20:54
osmanayhan27-Apr-06 20:54 
GeneralIntegrate reporting services with VS.NET pages and VS.NET controls Pin
adb96hxo15-Mar-06 22:38
adb96hxo15-Mar-06 22:38 
Hi,

I have made a report and deploy it to report server on my local machine. And I can view the report in browser with http://localhost/reportserver or http://localhost/reports. But when I tried to access report from my VS web page, it gives error like the "mymachinename/ASPNET" has no permissions to do this operation. I have default settings in my report server (sevices credentials). And I set windows integrity security in my IIS for report server, repots and my web application. What shall I do if I want all user in our intranet can use the web page to access the report without any login and password.

Thanks for any help

Andrea

adb96hxo
GeneralRe: Integrate reporting services with VS.NET pages and VS.NET controls Pin
Frank Kerrigan16-Mar-06 12:22
Frank Kerrigan16-Mar-06 12:22 
GeneralRe: Integrate reporting services with VS.NET pages and VS.NET controls Pin
adb96hxo17-Mar-06 2:59
adb96hxo17-Mar-06 2:59 
GeneralRe: Integrate reporting services with VS.NET pages and VS.NET controls Pin
Frank Kerrigan20-Mar-06 23:37
Frank Kerrigan20-Mar-06 23:37 
GeneralRe: Integrate reporting services with VS.NET pages and VS.NET controls Pin
Arina200531-Mar-06 10:23
Arina200531-Mar-06 10:23 
GeneralLimitation of Reporting Services Pin
Gaurang Desai31-Aug-05 20:43
Gaurang Desai31-Aug-05 20:43 
GeneralRe: Limitation of Reporting Services Pin
Frank Kerrigan31-Aug-05 22:00
Frank Kerrigan31-Aug-05 22:00 
GeneralRe: Limitation of Reporting Services Pin
reklats7-Sep-05 3:58
reklats7-Sep-05 3:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.