Reporting Procedure reportview

 

People in the workplace will feel a bit of emotion when writing reports. A neat and beautiful report will add a lot of color in front of your boss, or even raise your salary-Everyone prefers to raise your salary, right? This article describes how to use MS Reporting Services 2005 to create a report and use a C # applet to generate it.

This article assumes that the reader has a preliminary understanding of Visual Studio 2005 IDE and can write code in C #. Understanding MS Reporting Services does not matter. Of course, similar reports have been written before, which will help you get started faster. Now, roll up your sleeves and get started!

See Figure 1. How complicated is this report? Guess how long it will take to complete? In terms of complexity, it is just a simple report extracted from NorthWind-> Products (SQL Server 2000). In terms of time, I believe it won't take you an entire hour.

 

Figure 1

Next, we start to create a report. First, we need to write a C # applet that generates the report.

Step 1: Create a Windows Application

Select the File menu, create-project, select C # From the project type, and select a Windows application from the right-side dialog box. In the Name Bar, it is best to use a name indicating the purpose of the program; in the location column, specify the directory you want to save. After that, there will be a Form1 in the project. Let's start with its Form Designer.

Modify the attributes of Form1 as follows. Of course, you can also modify other attributes based on your preferences:

Form1.Text = "MS Reporting Services 101 with Smart Client"
Form1.Size = 750,300

Step 2: Add a Report Viewer to a Form)

What is a report viewer? Just like a DVD player is required to watch a DVD, we need a report viewer to preview the report.
For the person who writes the report, the report viewer gives the report life. It can not only preview the output, but also help generate the report information in various formats (PDF or Excel ), print it out.

[Span] place the report viewer control on Form1 as follows:
Find the ToolBox-Data-Report viewer and drag it to Form1. This creates a new instance named reportViewer1.
By setting reportViewer1.Dock = Fill, the report viewer fills the entire area of the form to display the report.
After completing steps 1 and 2, the project looks like 2:

 

Figure 2

Step 3: Add a DataSet to the Project)

A dataset comes with the report viewer. It stores and provides raw data from the data source, so that we can process the raw data or output it in the C # program.

Follow these steps to add a dataset:
Select Add-new project-dataset from solution resource browser, change its name DataSet1 to dsProduct, and click Add.

Add a data table to the newly created dataset. A data table is actually used to load report data. When designing a report, it uses the relevant information in DataSet/DataTable.

Add the data table to the dataset (dsProduct) as follows:
Double-click dsProduct from the solution resource browser. The design view is displayed, right-click, and choose add-data table. Then, click the table header to change the name to dtProductList, 3:

 

Figure 3

Next, add a column for the data table (dtProductList). The design view should be 4. Right-click dtProductList and choose add -- Column ).

 

Figure 4

Repeat the preceding steps to add the following columns:

ProductName (String)
QuantityPerUnit (String)
UnitPrice (Double)
UnitsInStock (Double)
UnitValue (Double): A calculation Domain Based on UnitsInStock * UnitPrice

When adding a column, the default value is the String data type. After adding the column, go to the Properties window and modify the corresponding column as the Double type.
See Figure 5. The current data table looks like this. You can also view the Properties window to modify the data type.

 

Figure 5

Step 4: Add a report for the project

So far, we have created a project and added a report viewer and Dataset. Now, it is time to create a neat and beautiful report.

To generate a report (rptProductList. rdlc), follow these steps ):
Select Add-New Project-Report from solution resource browser, change Report1.rdlc to rptProductList. rdlc, and click Add.
The project may look like Figure 6. After adding a report, you can start designing with a DataSet.

 

Figure 6

Whether you are a beginner in designing reports for the first time, or experienced in the field, you must deal with the following three basic report areas: Header (or header), footer, and table body. In general, when designing a report, there should be a draft, such as the size of the paper, the layout, and so on. It is usually the size of the letter paper and the vertical layout. Of course, it would be better to draw a sketch on the paper before you can do it. Let's look back at 1. In the header, there is a report name and report date, and the table body contains a list of product-related information, while the footer contains the page number.

Let's start with the header. When a new report is added to a project, only the table body can be seen in the report designer by default. Right-click outside the table body of the report designer and select a header. This will add a header to the report. You can adjust the height of the header and table area as needed. See figure 7, figure 7 reduces the table height and increases the header height.

 

Figure 7

In the toolbox of the report designer, you can see various controls used to design reports. In this example, TextBox, Line, and Table controls are used. Now let's start designing the header. Drag two TextBox boxes and place them in the header area. TextBox displays both static and dynamic data, while the Line control separates the headers from the table area. After the control is placed, you can modify its properties to display the values we need. For example, you can specify a TextBox as the report title, while another TextBox displays the current date. After selecting TextBox, you can directly enter static text in it.

Modify the title TextBox as follows:

Value = "Product List"
Color = Purple

The date TextBox is changed:

Value = "Run Data:" & Today
Color = Purple

[Span] note that the Value attribute of the date TextBox starts with the "=" symbol, which means that it is not a simple static text, but an expression, this expression is the string "Run Date" and VB.. NET script.
In addition, you can specify any name you want for all objects in the Report. Generally, you can keep the default name for most controls, in this example, the Name (Name attribute) of the title TextBox is specified as "txtTitle ".

Refer to Figure 8. After the header is designed, it should look like this:

 

Figure 8

It is the table body. The table body is the detailed area of the bearer information and the most important part of the entire report. You can also see that when you add a report to a project, the table body has been automatically added, we only need to put some controls on it.

Traditionally, the table body area is used to display detailed data (in this example, it is the product information) and usually contains multiple rows of information, the table area will also expand, but in general, the report will only be designed as one page (letter or A4 size ).
Table, Matrix, and List are the three most commonly used controls in the Table area. In this example, the Table control is used. On the report design page, drag and place the Table control in the Table area. You will see that this will generate a Table with three rows and three columns, the middle column is marked as Header, Detail, and Footer.

When you know that the Table control is just a combination of a bunch of TextBox, isn't it a little surprised. By the way, each Cell in Talbe is the same as that in TextBox, that is, you can enter static text or specify a dynamic expression.
Before designing the table partition, remember to add two more columns (five columns in our report). It is very easy to add columns. follow these steps:

1. Select the Table control in the Table body area.
2. left-click the header of the rightmost column (assuming that you add a new column from the right ).
3. Right-click the header and select "Insert Column to the Right" (Insert Column on the Right ).

Now the report looks like Figure 9. You can adjust the column width based on the data in the column.

 

Figure 9

Most of you have used Excel or similar items. You can regard the Table control as a mini worksheet, where you can select the border type, change the font of any cell, and so on. Therefore, what needs to be done is to design tables in the format previously imagined.
We start from the first column, and then each column is the same. Click the cell at the top of the column and enter the following in sequence:

Header 1: "Product Name"
Header 2: "Packaging"
Header 3: "Unit Price"
Header 4: "Units in Stock"
Header 5: "Stock Value"

In the subsequent detailed data area (that is, the previous Detail), you need to input an expression, which is dsProduct. in dtProductInfo, You can manually enter an expression or drag and drop the column from the Data source toolbox (see figure 7 on the left.
If you want to manually enter the data, from the first column to the last column in the data area, click the cell and enter:

Detail 1: "= Fields! ProductName. Value"
Detail 2: "= Fields! QuantityPerUnit. Value"
Detail 3: "= Fields! UnitsInStock. Value"
Detail 4: "= Fields! UnitPrice. Value"
Detail 5: "= Fields! UnitsInStock. Value * Fields! UnitPrice. Value"

Pay Attention to Detail 5, which is an output after multiplying Units in Stock and Unit Value. In addition, if you drag a column to the data area of the Table control, if the header is empty, it will automatically add the header.
Finally, add a accumulative column to the footer of the Table control, select the footer cell of the 4th and 5th columns in the Table area, and enter:

Cell 4: "Total Value :"
Cell 5: "= SUM (Fields! UnitsInStock. Value * Fields! UnitPrice. Value )"

Click cell 5. A built-in SUM () function is used to obtain the accumulative result.

Only the footer area is left. before writing some C # code, you must complete the report footer. Just like adding a report header, in the open report designer, right-click and select the Footer, as shown in figure 7.
Drag a Line and TextBox Control to the footer area and enter the following expression in the TextBox:

Value: = "Page:" & Globals! PageNumber & "/" & Globals! TotalPages

We can see that PageNumber and TotalPages are used here, both of which are global variables.

Now, the report should be like Figure 10. Here, the color is changed, the right alignment of the numeric data is adjusted, and the Table is regarded as an Excel worksheet. You can adjust the appearance you want at will.

 

Figure 10

It's almost done! The rest is the expression generator. Expression generator is a very powerful tool in Reporting Services. As seen in 11, Stock Value is calculated using the SUM function. All data in DateSet can be calculated by "Fields !" Keyword access.

 

Figure 11

Step 5: Write C # code that gives the report life

In the solution resource browser, right-click Form1 and choose view code from the shortcut menu to add the following code to Form1_Load:

Using System. Data. SqlClient;
Using Microsoft. Reporting. WinForms;

Private void Form1_Load (object sender, EventArgs e)
{
// Declare the connection string
String cnString = @ "(local); Initial Catalog = northwind;" + "User Id = northwind; Password = northwind ";

// If the standard security attribute is used, change it to the following code:
// String cnString = @ "Data Source = (local); Initial Catalog = northwind; Integrated Security = SSPI ";

// Declare the connection, command object, and other related objects
SqlConnection conReport = new SqlConnection (cnString );
SqlCommand cmdReport = new SqlCommand ();
SqlDataReader drReport;
DataSet dsReport = new dsProduct ();

Try
{
// Open the connection
ConReport. Open ();

// Prepare a connection object to put the obtained data into the dataset

CmdReport. CommandType = CommandType. Text;
CmdReport. Connection = conReport;
CmdReport. CommandText = "Select TOP 5 * FROM Products Order By ProductName ";

// Read data from the command object
DrReport = cmdReport. ExecuteReader ();

// With ADO. NET, read data can be directly loaded into the data set.

DsReport. Tables [0]. Load (drReport );

// Close reading and Connection
DrReport. Close ();
ConReport. Close ();

// Provide local report data for the viewer
RpvAbraKaDabra. LocalReport. ReportEmbeddedResource =
"RsWin101.rptProductList. rdlc ";

// Prepare the report data source
ReportDataSource rds = new ReportDataSource ();
Rds. Name = "dsProduct_dtProductList ";
Rds. Value = dsReport. Tables [0];
RpvAbraKaDabra. LocalReport. CES. Add (rds );

// Load the report Viewer
RpvAbraKaDabra. RefreshReport ();
}
Catch (Exception ex)
{
// Display the error message
MessageBox. Show (ex. Message );
}
Finally
{
// Check whether the connection is still open. If yes, close it.
If (conReport. State = ConnectionState. Open)
{
ConReport. Close ();
}
}
}

As you can see, you may wonder why "TOP 5" is used in the select query statement, which is only for demonstration purposes and limits the output, get the cumulative result from 1. In addition, the Name attribute of ReportDataSource should always be "DataSet_DataTable ".

This article from the CSDN blog, reproduced please indicate the source: http://blog.csdn.net/wangdetian168/archive/2008/09/05/2884783.aspx