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
ALTER PROCEDURE ShowProductByCategory(@CategoryName nvarchar(15) )
AS
SELECT Categories.CategoryName, Products.ProductName,
Products.UnitPrice, Products.UnitsInStock
FROM Categories INNER JOIN Products ON
Categories.CategoryID = Products.CategoryID
WHERE CategoryName=@CategoryName
RETURN
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.
![](http://www.codeproject.com/images/minus.gif)
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:
<httpHandlers>
<add path="Reserved.ReportViewerWebControl.axd" verb="*"
type="Microsoft.Reporting.WebForms.HttpHandler,
Microsoft.ReportViewer.WebForms,
Version=8.0.0.0, Culture=neutral,
PublicKeyToken=?????????????"
validate="false" />
</httpHandlers>
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!