Introduction:
This article is a link given by Kanyuanzi the day before yesterday. I forgot who it belongs to. I only remember that the address http://www.codeproject.com/aspnet/ReportViewer.asp was posted, and then it was said that because For some reason it was not translated. I later took a look and it happened to be useful to me, because someone in the company was working on RS stuff, and the optoelectronics department downstairs also often had BS needs. I had known about this thing for a long time but had never had time to study it, so I happened to find this article. So I studied it carefully all afternoon, translated it first, and then added some insights.
During the translation process, I tried to localize it as much as possible, but retained the meaning of the original author. If there is any inaccuracy in the translation, please criticize and correct me.
There are a lot of service modes (Server Mode) of SqlServer RS on the Internet, and I really spent a long time studying the local mode (Local Mode), especially when there are parameter calls.
The reason why local mode is used instead of server mode is because every time the client in service mode requests a report, the server must organize the data into a report and then send it to the client. Although this is safer, the huge report will never be processed. Server-to-browser transmission reduces its performance.
So this article describes how to organize a report and read it using the ReportViewer control of ASP.NET2.0. Here we choose the local mode and use a stored procedure with parameters. What I use is ASP.NET2.0, VisualStudio2005, and SQLServer2005 plus Application Block. If you do not have the Microsoft Application Block tool, then please replace the code that calls the stored procedure through SQL Helper in the example with SQL Command.
Here we choose the Northwind database. Our example shows the user to select the product name from the drop-down list and then filter out the information in the report.
The first step is to create a stored procedureALTER PROCEDURE ShowProductByCategory(@CategoryName nvarchar(15) )
with parameters
AS
SELECT Categories.CategoryName, Products.ProductName,
Products.UnitPrice, Products.UnitsInStock
FROM Categories INNER JOIN Products ON
Categories.CategoryID = Products. CategoryID
WHERE CategoryName=@CategoryName
RETURN
The second step is to use the DataSet designer to create a DataTable under the DataSet.
In the solution manager interface, right-click the App_Code file and select "Add New Item". Select "DataSet" in the pop-up dialog box, give it a name, such as DataSetProducts.xsd, and then click the "Add" button. At this time, the TableAdapter configuration tool will automatically appear. If it does not appear or it is closed by mistake, right-click anywhere in the DataSet designer, select Add, and then select TableAdapter, and the tool will appear again. Create a DataTable according to the wizard, select "User existing stored procedures" as the command type in the interface that appears, and then specify "ShowProductByCategory" as the Select command.
The stored procedure created in the first step becomes a DataTable in the second step, and the report data is provided through this DataTable.
Figure 1: DataSetProducts.xsd containing a DataTable is the data source of the report
Step 3: Create a report file.
Right-click in the solution manager and select Add New Item, then select Report template. In this example, the default name Report.rdlc is used directly. RDL means report definition language, and c means client. In other words, rdl is a server report, and rdlc is a local report.
Drag the Table from the toolbox to the report design form. This "table" has three parts, header, content and tail. A Table is an area that displays data, and an area displays the data elements under the DataSet it is bound to. Although a report can have many areas, each area can only display the contents of one DataSet. Based on this, we can use stored procedures to combine data from multiple tables into a DataSet to populate reports.
Figure 2: Controls in the toolbar dedicated to report templates
Open the "Website Data Source" window and find the "DataSetProducts" data set, which is the one created in the second step. Expand until you see the DataTable called "ShowProductByCategory". This Table is called this name because we previously selected "Use existing stored procedure" in the TableAdapter configuration wizard, and the name of our stored procedure is ShowProductByCategory.
In the website data source window, drag ProductName, UnitPrice and UnitsInStock to the row showing details in the report designer, that is, the first, second and third columns of the middle row. And you can right-click any field in the display details row, and then find the Format tab in the property bar to define their display format for Unit Price and Unit In Stock.
Figure 3. The website data source form shows the DataSets defined in your program and the columns they have.
Step 4: Add the ReportViewer control to the ASP.NET2.0 page
. First drag the DropDownList control into the form, and then bind the CategoryName field in Categories by selecting the data source option. In other scenarios, users can enter parameters through other methods such as text boxes and then pass them to the stored procedure.
Then, drag the Report View control onto the form and set its Visible property to False. Another thing to note is that the Report Viewer of ASP.NET 2.0 provides Excel and PDF export methods. However, during the actual process, I found that there will always be a slight discrepancy between the printed report and the way you designed it.
Figure 4, set this page to the StartUp page
Next, call up the smart tag of Report Viewer and select the Report.rdlc file you just created.
Figure 5: Combine the report definition file into the Report Viewer control.
Step 5: Write code to allow users to select different names in the drop-down box and then display different data in the report.
Don’t forget to add the Microsoft.Reporting.WebForms namespace to your code-behind (or code file) file.
1<PRE lang=cs id=pre1 style="MARGIN-TOP: 0px">using System;
2using System.Data;
3using System.Data.SqlClient;
4using System.Configuration;
5using System.Collections;
6using System.Web;
7using System.Web.Security;
8using System.Web.UI;
9using System.Web.UI.WebControls;
10using System.Web.UI.WebControls.WebParts;
11using System.Web.UI.HtmlControls;
12using Microsoft.ApplicationBlocks.Data;
13using Microsoft.Reporting.WebForms;
14
15public partial class ReportViewerLocalMode : System.Web.UI.Page
16{
17 public string thisConnectionString =
18 ConfigurationManager.ConnectionStrings[
19 "NorthwindConnectionString"].ConnectionString;
20
21 /**//*I used the following statement to show if you have multiple
22 input parameters, declare the parameter with the number
23 of parameters in your application, ex. New SqlParameter[4]; */
twenty four
25 public SqlParameter[] SearchValue = new SqlParameter[1];
26
27 protected void RunReportButton_Click(object sender, EventArgs e)
28 {
29 //ReportViewer1.Visible is set to false in design mode
30 ReportViewer1.Visible = true;
31 SqlConnection thisConnection = new SqlConnection(thisConnectionString);
32 System.Data.DataSet thisDataSet = new System.Data.DataSet();
33 SearchValue[0] = new SqlParameter("@CategoryName",
34 DropDownList1.SelectedValue);
35
36 /**//* Put the stored procedure result into a dataset */
37 thisDataSet = SqlHelper.ExecuteDataset(thisConnection,
38 "ShowProductByCategory", SearchValue);
39
40 /**//*or thisDataSet = SqlHelper.ExecuteDataset(thisConnection,
41 "ShowProductByCategory", dropdownlist1.selectedvalue);
42 if you only have 1 input parameter */
43
44 /**//* Associate thisDataSet (now loaded with the stored
45 procedure result) with the ReportViewer datasource */
46 ReportDataSource datasource = new
47 ReportDataSource("DataSetProducts_ShowProductByCategory",
48 thisDataSet.Tables[0]);
49
50 ReportViewer1.LocalReport.DataSources.Clear();
51 ReportViewer1.LocalReport.DataSources.Add(datasource);
52 if (thisDataSet.Tables[0].Rows.Count == 0)
53 {
54 lblMessage.Text = "Sorry, no products under this category!";
55 }
56
57 ReportViewer1.LocalReport.Refresh();
58 }
59}</PRE>
Step 6: To run the report,
press the F5 key and click the "Run Report" button to run the report.
Figure 6, running report
Finally, make sure that ReportViewer is introduced into the website and configure the web.config file. It may be in the following format:
<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 deploying a website project with ReportViewer control to another server, you need to copy the file C:Program FilesMicrosoft Visual Studio 8SDKv2.0BootStrapperPackagesReportViewerReportViewer.exe. Then run it on the target server.
Translation experience: It seems that this is the simplest translation I have ever done, but it may be because the author said it is relatively concise and clear.
Learning experience: The author elaborated on two reporting methods acceptable to ReportViewer and made a simple analysis. However, in actual projects, more people may use Server Mode. Local Mode can still produce some reports that are not too large or too complex.
I know that there are people in the company who specialize in RS. It is said that it takes a week to do it. When running, it depends on the quality of the writing. Ten minutes is normal, and if it exceeds thirty minutes, you may need to consider changing the algorithm. It sounds scary, haha, but if it can be used for the reporting needs of the photovoltaic department downstairs, I want to be more comfortable than the previous GridView to Excel method.
If this can be researched and understood, I plan to make a special introductory video explanation to help more people understand this control.