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.
Collapseusing 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!
