Using the ASP.NET 2.0 ReportViewer in Local Mode

文章目錄

• Step 1: Create a parameterized stored procedure
• Step 2: Create a DataTable in a typed DataSet using the DataSet Designer
• Step 3: Create a report definition
• Step 4: Drag a ReportViewer web server control onto an .aspx form
• Step 5: Write source code for the “Run Report” button to generate the report based on user selections
• Step 6: Build and Run the Report

Introduction

There are a good amount of materials on the net about “SQL Reporting
Services in Server Mode” but it took me a while to research on using
“Local Mode”, especially when parameters are involved.

The reason to use “Local Mode” instead of “Server Mode” is that in
“Server Mode”, the client makes a report request to the server. The
server generates the report and then sends it to the client. While it
is more secure, a large report will degrade performance due to transit
time from server to browser. In “Local Mode”, reports are generated at
the client. No connection to the “SQL Server Reporting Services Server”
is needed for local mode. Large reports will not increase wait time.

So here is an article on how to generate reports using the ASP.NET 2.0 ReportViewer
web server control via Local Mode with a parameterized stored
procedure. I am using ASP.NET 2.0, Visual Studio 2005, and SQL Server
2005 with Application Block. If you are not using Microsoft Application
Block, just call the stored procedure via the SQL Command object without using the SQL Helper class in the example.

Using the Northwind database, our example will prompt the user for a
category from a dropdown list and display all the products under the
selected category.

Step 1: Create a parameterized stored procedure

<span class="vb-function">ALTER</span> <span class="vb-function">PROCEDURE</span> ShowProductByCategory(@CategoryName <span class="cpp-keyword">nvarchar</span>(<span class="vb-literal">15</span>) )<br><span class="vb-function">AS</span><br><span class="vb-function">SELECT</span> Categories.CategoryName, Products.ProductName, <br> Products.UnitPrice, Products.UnitsInStock<br><span class="vb-function">FROM</span> Categories <span class="vb-function">INNER</span> <span class="vb-function">JOIN</span> Products <span class="vb-function">ON</span> <br> Categories.CategoryID = Products.CategoryID<br><span class="vb-function">WHERE</span> CategoryName=@CategoryName<br><span class="vb-function">RETURN</span>

Step 2: Create a DataTable in a typed DataSet using the DataSet Designer

Under Solution Explorer, right-click on the App_Code folder. Select “Add New Item”. Select “DataSet”. Name your dataset, e.g., DataSetProducts.xsd,
and click Add. The TableAdapter Configuration Wizard should appear
automatically, if not, right click anywhere on the DataSet Designer
screen and select “Add” from the context menu. Select the
“TableAdapter” to bring up the wizard. Follow the wizard to create your
data table. I chose “Use existing stored procedures” as the command
type and specified “ShowProductByCategory” as the Select command. I
also highlighted “CategoryName” as the Select procedure parameter.

The results from the stored procedure created in step 1 will
eventually be placed into this data table created in step 2 (Fig. 1).
Report data is provided through a data table.

Fig. 1 DataSetProducts.xsd contains a DataTable to be used as a report data source.

Step 3: Create a report definition

Under Solution Explorer, right-click and select “Add New Item”. Select the “Report” template. I will use the default name Report.rdlc in this example. Click “Add” to add Report.rdlc to your project. “rdl” stands for Report Definition Language. The “c” stands for Client. Hence, the extension .rdl represents a server report. The extension .rdlc represents a local report.

Drag a “Table” from the Toolbox onto the report
designer screen (Fig.2). The Toolbox display here is specific to the
report template. It shows controls to be used in a report as opposed to
controls to be used in a web form. The “Table” has three bands, the
header, detail, and the footer bands.

A “Table” is a data region. A data region is used to
display data-bound report items from underlying datasets. Although a
report can have multiple data regions, each data region can display
data from only one DataSet. Therefore, use a stored procedure to link multiple tables into a single DataSet to feed the report.

Fig. 2 Toolbox contains controls specific to the report template.

Open up the “Website Data Sources” window (Fig.3). Locate the “DataSetProducts” DataSet (created in Step 2). Expand to see the columns in the DataTable “ShowProductByCategory”.
The table is named “ShowProductByCategory” because we chose “Use
existing stored procedure” in the TableAdapter Configuration Wizard.
And our procedure name is “ShowProductByCategory”.

Drag the column “ProductName” from the “Website Data Sources”
window, and drop it in the Detail row (middle row). Drag “UnitPrice”
into the middle row-second column and “UnitsInStock” into the last
column. The header is automatically displayed. You can right click on
any field in the detail row (e.g., right click on “Unit Price”) and
bring up the context menu. Select Properties from the context menu.
Select Format tab to format the “Unit Price” and “Units In Stock”
accordingly.

Fig 3. Website Data Sources window shows typed datasets in your app and its columns.

Step 4: Drag a ReportViewer web server control onto an .aspx form

Drag a DropDownList control onto a new web form (Fig. 4). Use the “Choose Data Source” option from the “DropDownList Task” to bind the CategoryName
field from the Category table. Remember to enable autopostback. Users
can then make their selection as an input to the stored procedure.
While I am using a DropDownList in this example, you can use textboxes and other controls to prompt users for additional input.

Drag a ReportViewer web server control onto the web form. Set its Visible property to false. Also notice, the ReportViewer
web server control in ASP.NET 2.0 provides exporting capability. You
can select between Excel format or PDF format. However, I find that
what you see on screen is not always what you get from the printer. You
will have to experiment with the output format further.

Fig. 4 Set this web page as the StartUp page.

Next, bring up the smart tag of the ReportViewer control (Fig. 5). Select “Report.rdlc” in the “Choose Report” dropdown list. “Report.rdlc” was created in Step 3. Local Reports have the extension .rdlc. Server Reports are labeled with .rdc.

Fig. 5 Associate the report definition file (.rdlc) to the ReportViewer control

Step 5: Write source code for the “Run Report” button to generate the report based on user selections

Don’t forget to include the “Microsoft.Reporting.WebForms” namespace in your code-behind file.

Collapse

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.ApplicationBlocks.Data;
using Microsoft.Reporting.WebForms;

public partial class ReportViewerLocalMode : System.Web.UI.Page
{
public string thisConnectionString = 
       ConfigurationManager.ConnectionStrings[
"NorthwindConnectionString"].ConnectionString;

/*I used the following statement to show if you have multiple 
      input parameters, declare the parameter with the number 
      of parameters in your application, ex. New SqlParameter[4]; */

public SqlParameter[] SearchValue = new SqlParameter[1];

protected void RunReportButton_Click(object sender, EventArgs e)
    {
//ReportViewer1.Visible is set to false in design mode
        ReportViewer1.Visible = true;
        SqlConnection thisConnection = new SqlConnection(thisConnectionString);
        System.Data.DataSet thisDataSet = new System.Data.DataSet();       
        SearchValue[0] = new SqlParameter("@CategoryName", 
                         DropDownList1.SelectedValue);

/* Put the stored procedure result into a dataset */
        thisDataSet = SqlHelper.ExecuteDataset(thisConnection, 
"ShowProductByCategory", SearchValue);

/*or   thisDataSet = SqlHelper.ExecuteDataset(thisConnection, 
               "ShowProductByCategory", dropdownlist1.selectedvalue); 
               if you only have 1 input parameter  */

/* Associate thisDataSet  (now loaded with the stored 
           procedure result) with the  ReportViewer datasource */
        ReportDataSource datasource = new 
          ReportDataSource("DataSetProducts_ShowProductByCategory", 
          thisDataSet.Tables[0]);

        ReportViewer1.LocalReport.DataSources.Clear();
        ReportViewer1.LocalReport.DataSources.Add(datasource);
if (thisDataSet.Tables[0].Rows.Count == 0)
        {
            lblMessage.Text = "Sorry, no products under this category!";
        }

        ReportViewer1.LocalReport.Refresh();
    }
}

Step 6: Build and Run the Report

Press F5 to run the .aspx. Click on the “Run Report” button to see the list of products based on the selected category from the dropdown list (Fig. 6).

Fig. 6 Click on the “Run Report” button to generate a local report

Be sure to add reference of the ReportViewer to your web app, and note that your ReportViewer web server control has registered an HTTP handler in the web.config file. Your web.config file should have the following string:

&lt;httpHandlers&gt;<br> &lt;add path="Reserved.ReportViewerWebControl.axd" verb="*" <br> type="Microsoft.Reporting.WebForms.HttpHandler, <br> Microsoft.ReportViewer.WebForms, <br> Version=8.0.0.0, Culture=neutral, <br> PublicKeyToken=?????????????"<br> validate="false" /&gt;<br>&lt;/httpHandlers&gt;

When you use the Visual Studio 2005 ReportViewer web server control in your website, you will need to copy the "C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\BootStrapper\Packages\ReportViewer\ReportViewer.exe" to your server and run it before you post those web pages with the ReportViewer control.

Well, there you have it. This is a simple example of creating a
report in local mode. I hope you find the example useful. Happy
computing!