Click here to Skip to main content
15,861,172 members
Articles / Web Development / HTML
Article

Integrating Reporting Services 2005 Into a Web Application

Rate me:
Please Sign up or sign in to vote.
4.59/5 (25 votes)
4 Sep 20067 min read 373.4K   130   43
This article provides step-by-step instructions on how to integrate a MS Reporting Services report into an ASP.NET web application using the ReportViewer control. It also includes an HTML template than can be used for the layout and tackles a couple of known problems with the ReportViewer control.

Introduction

With release of Visual Studio 2005, Microsoft has made it much easier to integrate its SQL Reporting Services into ASP.NET web applications. Most desired functionality of Reporting Services can be achieved through its ReportViewer control, which is the focus of this article. For those of you who need very fine and detailed customization of embedding RS reports into applications, I would recommend that you seek out articles that discuss the RS object model and its soap interface. Otherwise, this article should provide you with a good tutorial on how to integrate Report Services into a web application using Visual Studio 2005.

Step-by-Step Instructions

Go ahead and start up Visual Studio 2005. Click on the “New \ New Website” item in the file menu. In the dialog box select the ASP.NET Website, your language preference (C# is used in this article), and where you would like your website to be built. Click on OK.

One of most difficult tasks in embedding the ReportViewer control is ensuring that it fills 100% of the space you want it to fill, no more, no less. You also want the ReportViewer to use its own scroll bars, not the browser’s.

The code I have written is basically a template that you can use which sets an area for a header, a custom control bar, and the report itself.

Since the ReportViewer has difficulty taking up 100% of the space in a table (probably not 100% XHTML), you are going need to remove any XHTML settings.

In the ASP.NET code, you will need to delete (or change) this line:

HTML
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

You may also probably need to change the validation from a non-XHTML setting to a setting such as HTML 4.01.

validation

If your target audience will only be using the IE browser, change the body tag so it looks like this:

HTML
<body style="vertical-align: bottom; height: 100%; margin-right: 0px; 
    margin-top: 0px; margin-bottom: 0px; margin-left: 0px; 
    padding-bottom: 0px; padding-top: 0px">

If any of the users will also using the Mozilla browser, change the body tag so that the height is 99.3%. This is to avoid a double vertical scroll bar effect where you see both the ReportViewer’s scroll bar and the browser’s scroll bar. There will be an almost unnoticeable space at the bottom of Mozilla browser. Take note, that the contents within the ReportViewer may not display correctly (nor will they in Report Manager). So develop reports for Mozilla at your own risk at this time.

HTML
<body style="vertical-align: bottom; height: 99.3%; margin-right: 0px; 
    margin-top: 0px; margin-bottom: 0px; margin-left: 0px; 
    padding-bottom: 0px; padding-top: 0px">

Next go ahead and replace any existing code between the <form> tags with the below code:

HTML
<table style="vertical-align: bottom; border-width: 0px; margin-top: 0px; 
   margin-bottom: 0px; width: 100%; height: 100%; padding: 0px,0px,0px,0px;" 
   cellspacing="0" cellpadding="0">
    <tr>
        <td>
            HEADER
        </td>
    </tr>
    <tr>
        <td>
            <div style="border-top: black 1px solid; 
                background-color: #ece9d8; border-bottom-width: 1px; 
                border-bottom-color: #d4d0c8; padding-bottom: 10px;">
                Custom Controls
            </div>
       </td>
    </tr>
    <tr>
        <td style="height: 100%;">
            <%-- This is where the ReportViewer control will go. --%>
        </td>
    </tr>
</table>

Go ahead and switch to the “Design” view since it is now time to add the ReportViewer control.

In the “Data” section within the Toolbox, drag the ReportViewer control onto the last row of your table, which should be taking up most of the page. Next you want to set the control’s properties so that it points to the right server and the right report. Under properties, go to the “Server Report” section. Right above the section you should see a property called “ProcessingMode”. In most cases you are going to want use an instance of a Report Server. So for the purposes of this tutorial, set this property to “Remote”, even if the Report Server instance is on the same computer as your web app. Next, you want to set the “ServerReport” settings.

First, type in address of the Report server in the “ReportSeverUrl” field.

The syntax is:

http:// NameOfServer / reportserver

i.e. http://ServerOne/reportserver

Next type in the location of the report in the “ReportPath” field.

i.e. /MyReports/TheReport

The location is easy to know since it is the same path structure that you see in the Report Manager. Also, always be sure the put a forward slash first before the actual path.

Properties

Unless you want the ReportViewer to be an absolute size, go ahead and set the height and width settings under the “Layout” section to be 100%. The ReportViewer will by default show controls to set the parameters for the report. These often look a little ugly, and I would recommend most developers to create their own parameter controls. This can be done in the control bar row of the template provided above. To get rid of the ReportViewer’s parameter controls set the “ShowParameterPrompts” in the properties underneath “Appearance” to false. In, the next section I’ll show how you can set the report parameters with your own code.

Those should be all the settings you need to make for the ReportViewer to display correctly, unless your version of Visual Studio has other default settings. Double check to make sure the ASP.NET looks similar to this code:

ASP.NET
<rsweb:ReportViewer ID="ReportViewer1" runat="server" Height="100%" 
    ProcessingMode="Remote" ShowParameterPrompts="False" Width="100%">
    <ServerReport ReportPath="/MyReports/TheReport" />
</rsweb:ReportViewer>

Now, the report is ready to be processed. You can go ahead and preview your website and the report should generate just fine.

Displaying a Report through the C#/VB Code

In many cases, you may need to pass parameters to a report or your code (or the end users) may need to have control over which report is generated. For example you may have a Drop-Down-List that lets the user select which report they wish to view. They can then click on a button that generates the report that accepts a parameter for the current time. (The Button and Drop-Down-List can be placed in the custom control bar section of the template.)

So the code inside the “Click-Button-Event-Handler” function is where we will need to put in our custom ReportViewer code. The code in this article is C#, but the VB code shouldn’t be too much different.

First, if you didn’t set the Report Server Url via the visual designer, you can do it here.

C#
this.ReportViewer1.ServerReport.ReportServerUrl = 
    new System.Uri("http://ServerOne/reportserver");

One problem/bug that is rather common in the ReportViewer control is when you change to a different report via the website itself coming from a drill-through report, an ASP.NET error can occur. This is because the ReportServer is thinking that the new report is still a drill-through when it is not. To get around this problem use the following code:

C#
while (this.ReportViewer1.ServerReport.IsDrillthroughReport)
{
    this.ReportViewer1.PerformBack();
}

This code will set whichever report you want to display next to be the parent.

Next you want to set the new report path:

C#
// Could also be set to the selection of a ListBox.
string strReport = "/MyReports/TheReport";
this.ReportViewer1.ServerReport.ReportPath = strReport;

If parameters are needed, you are going need to first declare an array of ReportParameter objects.

Microsoft.Reporting.WebForms.ReportParameter[] RptParameters = 
    new Microsoft.Reporting.WebForms.ReportParameter[1];

In this case, we only need one parameter.

Next, assign the first index (0) of the RptParameters array to a new ReportParameter object where the constructor function takes the syntax:

ReportParameter(ReportParameterName, Value);

i.e.

C#
string strTime = System.DateTime.Now.ToShortTimeString();

RptParameters[0] = 
    new Microsoft.Reporting.WebForms.ReportParameter("CurrentTime", strTime);

Then, you need set the RptParameters object to the ReportViewer control.

C#
this.ReportViewer1.ServerReport.SetParameters(RptParameters);

Finally, to put these changes into action, call the Refresh() function:

C#
this.ReportViewer1.ServerReport.Refresh();

So in its entirety, the code inside the button-clicked event-handler function should look like this:

C#
this.ReportViewer1.ServerReport.ReportServerUrl = 
    new System.Uri("http://serverone/reportserver");

while(this.ReportViewer1.ServerReport.IsDrillthroughReport)
{
    this.ReportViewer1.PerformBack();
}

// Could also be set to the selection of a ListBox.
string strReport = "/MyReports/TheReport";
this.ReportViewer1.ServerReport.ReportPath = strReport;
Microsoft.Reporting.WebForms.ReportParameter[] RptParameters = 
    new Microsoft.Reporting.WebForms.ReportParameter[1];

string strTime = System.DateTime.Now.ToShortTimeString();
RptParameters[0] = 
    new Microsoft.Reporting.WebForms.ReportParameter("CurrentTime", strTime);

this.ReportViewer1.ServerReport.SetParameters(RptParameters);
this.ReportViewer1.ServerReport.Refresh();

If you like you can delete the ReportPath from the properties in the visual designer. This will ensure that a report does not load until your own code loads a report itself. However, this will also remove the ReportViewer control bar, which you may wish to keep so that the website looks more structured. What I usually do is set the default ReportPath to point to a report that is basically a welcome screen. From there, the end user can choose parameters from my selection controls, and then when ready, the user can click on a view button that generates the desired report.

Conclusion

The ASP.NET code I have provided is code that I have used myself in different projects. Although I am personally more focused on software programming than web design, I do try to make my web pages 100% XHTML. Unfortunately, I was unable to completely stick to that standard because of the ReportViewer’s own HTML coding. If anyone has a 100% XHTML solution, which allows the DOCTYPE tag to be set to at least XHTML transitional, that can display RS reports sized to 100% inside a table, and allow the ReportViewer control to use its own scroll bars, I would be glad to revise this article. Otherwise, the template I have provided, should allow you to embed a nicely displayed RS report in a web page that can be process by the IE-based browsers .

Furthermore, this is only a solution for the IE browser. The layout template will work fine with Mozilla, but the ReportViewer's own HTML coding will not. This, of course, may change with new versions of the Mozilla browser.

If you wish deviate a little from this article in areas such as showing the ReportViewer parameters prompts, then the website may not show visually want you want. However, it should be easy enough to take my HTML code and tweak it (i.e. adding/removing border lines) so that it suits your needs.

As for the C# code, the only thing that may be confusing is the while loop to drill back to the parent report. Seemingly the Refresh() function should do this automatically, and yet, it will only give you an ASP.NET error. Hopefully, this will be fixed in later versions of the ReportViewer control, but for now the loop is a good go-around for this problem.

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
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Questionthank you Pin
DETROIT26266-Dec-12 8:29
DETROIT26266-Dec-12 8:29 
SuggestionThis method also worked for me Pin
mgoad9926-Jun-12 9:23
mgoad9926-Jun-12 9:23 
GeneralThanks! Pin
goldochsen26-Aug-10 8:54
goldochsen26-Aug-10 8:54 
GeneralMy vote of 5 Pin
Preetha Skaria27-Jul-10 6:57
Preetha Skaria27-Jul-10 6:57 
GeneralProblem ReportViewer Pin
foluis27-Apr-10 10:50
foluis27-Apr-10 10:50 
GeneralPerhaps a simpler solution Pin
danielcardenas755-Dec-09 3:21
danielcardenas755-Dec-09 3:21 
GeneralSSRS Daily Recurrence Pin
Sangmeshwar patil2-Sep-09 0:01
Sangmeshwar patil2-Sep-09 0:01 
GeneralRe: SSRS Daily Recurrence Pin
Divan van der Watt6-Mar-10 23:19
Divan van der Watt6-Mar-10 23:19 
GeneralThanks Man! Pin
bebandit18-Mar-09 9:24
bebandit18-Mar-09 9:24 
GeneralAsyncRendering property impact Pin
Sachin Dedhia10-Feb-09 21:37
Sachin Dedhia10-Feb-09 21:37 
QuestionHow to subscribe the server report through code? Pin
Prashant.IGT15-Dec-08 22:05
Prashant.IGT15-Dec-08 22:05 
GeneralUsing IFrame Pin
Noman Aftab14-Jul-08 18:53
Noman Aftab14-Jul-08 18:53 
GeneralRe: Using IFrame Pin
Member 209783428-Mar-09 1:53
Member 209783428-Mar-09 1:53 
Generalsame code in VB.net doesn't work Pin
lbearn10-Jul-08 8:01
lbearn10-Jul-08 8:01 
I tried the code and it works fine. When I translated it into VB.net, it doesn't work.
I got error message on this line: ReportViewer1.ServerReport.SetParameters(RptParameters)
The error message is: Value cannot be null.

Can anybody see anything wrong in the VB.net version?

This is the same code in VB.net:

ReportViewer1.ServerReport.ReportServerUrl = New System.Uri("http://myServer/ReportServer")

While (ReportViewer1.ServerReport.IsDrillthroughReport)
ReportViewer1.PerformBack()
End While

Dim strTerminal As String
strTerminal = DropDownList1.SelectedValue
' Could also be set to the selection of a ListBox.

Dim strReport As String
strReport = "/TIMStest/mainReport"
ReportViewer1.ServerReport.ReportPath = strReport

Dim RptParameters(1) As Microsoft.Reporting.WebForms.ReportParameter
RptParameters(0) = New Microsoft.Reporting.WebForms.ReportParameter("terminal", strTerminal)

ReportViewer1.ServerReport.SetParameters(RptParameters)
ReportViewer1.ServerReport.Refresh()
GeneralRe: same code in VB.net doesn't work Pin
Dominic Fitzpatrick22-Oct-09 4:12
Dominic Fitzpatrick22-Oct-09 4:12 
QuestionEvent Handlers for Server Reports? Pin
Larry Whipple11-Apr-08 11:35
Larry Whipple11-Apr-08 11:35 
GeneralSql Server 2005 Reporting Services in ASP.NET with C# Pin
Shankar Naspuri19-Mar-08 20:12
Shankar Naspuri19-Mar-08 20:12 
GeneralDOCTYPE Tag Pin
IT2666-Nov-07 16:13
IT2666-Nov-07 16:13 
GeneralError passing parameter Pin
BigBlueEye13-Jun-07 7:39
BigBlueEye13-Jun-07 7:39 
GeneralRe: Error passing parameter Pin
DDarren16-Jun-07 15:19
DDarren16-Jun-07 15:19 
QuestionReport with multiple perameters ....plz help me [modified] Pin
veeru.k4-Jun-07 19:34
veeru.k4-Jun-07 19:34 
GeneralIE 7.0 Pin
haskbask2-Jun-07 4:34
haskbask2-Jun-07 4:34 
GeneralRe: IE 7.0 Pin
DDarren2-Jun-07 10:25
DDarren2-Jun-07 10:25 
GeneralRe: IE 7.0 Pin
haskbask3-Jun-07 0:45
haskbask3-Jun-07 0:45 
Generalthe report is not showing Pin
Nomvula17-May-07 3:27
Nomvula17-May-07 3:27 

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.