Fun about SSRs Article 2-creating simple reports

The report service is a component of Microsoft sqlserver Business Intelligence. With the help of the report service, you can create a variety of reports, whether simple or complex, at the same time, the system provides the subscription function to facilitate users to subscribe to reports. In addition, developers can easily integrate an SSRs report into their own winform or webformProgram.

This article describes how to create a simple report.

 

First, open SQL data tools, that is, the shell of Visual Studio 2010. Projects related to business intelligence, such as integrated service projects and analytical service projects, need to be used.

Create a project, select the report service under business intelligence in the list on the left, and select the Report Server Project in the Project template. After obtaining a name for the project and determining the storage location of the project file, click OK.

In the Solution Explorer on the report service development interface, you can see that the project structure is very simple. By default, it is divided into three folders, sharing data sources and sharing data set report folders.

Right-click shared data sources and choose add new data source to create a data source connection.

On the data source properties page, confirm that the type is Microsoft SQL Server, and click Edit next to connection string... Button.

Connect to the adventure works DW database on the connection properties page, as shown in the following figure. Adventure works is an official example database of Microsoft. For details about how to obtain and deploy these sample databases, refer to my article.Article:

Http://www.cnblogs.com/aspnetx/archive/2013/01/30/2883831.html

After specifying the database, click OK.

Click OK again to create the shared data source.

Next, create a report, right-click reports, and choose add-> new item...

On the create a project page, select report. Click OK.

You can see that the report design interface still has some changes compared with SQL Server 2005, but basically it has no major changes with SQL Server 2008.

First, create a dataset for the report. First, you need to find the dataset tag on the left. If not, click View> report data on the menu bar to display the dataset.

You can see the report data content. Parameters, data sources, datasets, and other information are included.

Right-click the datasets folder and click Add dataset...

In the pop-up dataset properties, select use a dataset embedded in my report, that is, use the dataset in the report. Click the new button.

On the data source properties page, select the use shared data source reference, that is, use the shared data source. In the following list, select the data source connection established at the beginning of this article, and click OK.

Return to the dataset properties page, confirm that the query type is query, and enter the following SQL statement.

Select St.Englishproductsubcategoryname As Productcategory

, Left ([Shipdatekey],6) As[Shipdate]

,Sum([Productstandardcost]) As[Productstandardcost]

,Sum([Salesamount]) AsSalesamount

,Sum([Taxamt]) As[Taxamt]

From [Adventureworksdw2012].[DBO].[Factinternetsales] F

left join [DBO] . [dimproduct] P On F . productkey =< span style = "color: teal; "> P . productkey

Left Join [DBO].[Dimproductsubcategory] St On P.[Productsubcategorykey]=St.[Productsubcategorykey]

Where Left ([Shipdatekey],4) =@ Year

Group By St.Englishproductsubcategoryname, Left ([Shipdatekey],6)

After specifying an SQL statement, you can click field on the left to view the supported fields retrieved from the SQL statement.

At the same time, it is noted that the query defines a parameter @ year, which can be detected in parameters. At this point, we can see that the report project has not mapped the parameters in this query. This is skipped first, because when you click OK, the system will automatically create a parameter to map with the parameters in the query.

After clicking the OK button for the data source attribute, you can view the created dataset in the report data and map the parameters created for the dataset to the parameters in the query.

Switch to the Toolbox interface of the Toolbox and drag the table control to the report design interface.

You can see the default style of the Table Control in the report design interface.

Return to the report data interface and drag productstandardcost, salesamount, and taxamt from the dataset list you just created to the table control position on the report design interface in sequence as follows.

Finally, click the preview tab on the report design page to view the report.

 

So far, how to create a report project and create a simple report first come here. Through this article, I believe you have a simple understanding of the report project. The next article will add some functions to the report, this section describes how to publish a report to a server.