使用SQL Server Reporting Services基于现有存储过程创建报告

Basic extraction of the SQL Server database data is usually achieved by querying the databases and creating stored procedure to automate the extraction process. Unfortunately, extracting the information in this way will not yield high-end reports, and only basic table-shaped reports are available when extracting the information via SQL Server Management Studio or similar tools. In order to create high-end reports that will include additional projections of the data, such are graphs, lists, charts… SQL Server offers powerful reporting options within the SQL Server Reporting service.

SQL Server数据库数据的基本提取通常是通过查询数据库并创建存储过程以自动执行提取过程来实现的。不幸的是，以这种方式提取信息不会产生高端报告，并且在通过SQL Server Management Studio或类似工具提取信息时，只有基本的表格形报告可用。为了创建高端报告，其中将包括数据的其他投影，例如图形，列表，图表……SQL Server在SQL Server Reporting服务中提供了强大的报告选项。

To start with the report creation, we are going to use Microsoft Visual Studio in order to define data sources for the reports and configure reports architecture.

首先要创建报告，我们将使用Microsoft Visual Studio来为报告定义数据源并配置报告体系结构。

Start Microsoft Visual Studio and click on the ‘New project’

启动Microsoft Visual Studio，然后单击“新建项目”
In the newly opened “New project” dialog, expand the “Business Intelligence” node and select the “Reporting Services” in the project pane on the left side. Then, in the central pane select the “Report Server Project”. Finally, in the bottom part of the dialog, specify project and solution names and click on the “OK” button to complete this task

在新打开的“新项目”对话框中，展开“商业智能”节点，然后在左侧的项目窗格中选择“ Reporting Services”。然后，在*窗格中选择“ Report Server Project”。最后，在对话框的底部，指定项目和解决方案名称，然后单击“确定”按钮以完成此任务。
Now that we’ve created both the Solution and the Project within it, we can start creating reports. To do so, go to the “Solution Explorer”, bring up the context menu with right mouse click on the “Reports” and navigate to “Add” > “New Item…”

现在我们已经创建了解决方案和其中的项目，我们可以开始创建报告了。为此，请转到“解决方案资源管理器”，右键单击“报告”以打开上下文菜单，然后导航至“添加”>“新建项…”。
The “Add New Item” dialog will appear, and we should opt for the “Report” project and provide the desired name, and click “Add” to complete this task.

将出现“添加新项”对话框，我们应该选择“报告”项目并提供所需的名称，然后单击“添加”以完成此任务。
With this, our report will appear in the “Solution Explorer” pane, so we can access it by double clicking on it in order to continue with the report design

这样，我们的报告将出现在“解决方案资源管理器”窗格中，因此我们可以通过双击它来访问它以继续进行报告设计
First step that needs to be completed when designing a report is to specify the data source for the report. To do so, in the “Report Data” pane bring up the context menu and click on the “Add Data Source…” option.

设计报告时需要完成的第一步是指定报告的数据源。为此，请在“报告数据”窗格中打开上下文菜单，然后单击“添加数据源...”选项。
The “Data Source Properties” dialog will be brought up, and the user should specify the name for this data source, and click on the “Edit” SQL Server connection button

将显示“数据源属性”对话框，用户应指定该数据源的名称，然后单击“编辑” SQL Server连接按钮
In the “Connection Properties” dialog, choose the SQL Server, choose authentication method and provide valid credentials, and choose a database that holds the stored procedure(s) that will be used as the source for these reports

在“连接属性”对话框中，选择SQL Server，选择身份验证方法并提供有效的凭据，然后选择一个数据库，该数据库保存将用作这些报告源的存储过程。
Now that the data sources have been set, the next step in order is to specify the data sets. As was the case with the data source creation, bring up the context menu by clicking on the “Datasets” and select the “Add Dataset…” option. In the “Dataset Properties” dialog, provide the dataset name and opt for the “Use a dataset embedded in my report option” by clicking on the appropriate radio-button. Then, choose the data source we’ve just created, and opt for the “Stored Procedure” as the Query type. Finally, select the Stored Procedure from the drop-menu and click “OK” to complete this step. Note: to further fine-tune the dataset, configure the various options in the “Fields”, “Options” and other tabs of the “Dataset Properties” dialog.

既然已经设置了数据源，那么下一步就是指定数据集。与创建数据源的情况一样，通过单击“数据集”并选择“添加数据集…”选项来调出上下文菜单。在“数据集属性”对话框中，提供数据集名称，并通过单击相应的单选按钮选择“使用嵌入到我的报表中的数据集”。然后，选择我们刚刚创建的数据源，并选择“存储过程”作为查询类型。最后，从下拉菜单中选择“存储过程”，然后单击“确定”以完成此步骤。注意：要进一步微调数据集，请在“数据集属性”对话框的“字段”，“选项”和其他选项卡中配置各种选项。
If the stored procedure that is being used contains any parameters that will allow users to run the report with specific input (e.g. specific user, ID, time-frame…), the parameters also need to be configured in the report data. In the “Report data” pane, under the “Parameters” node, right click on the parameter that was discovered in the provided stored procedure and choose “Parameter Properties” option. In this dialog, we can use various options to configure specific parameters, but the most important step that needs to be performed here is to check the “Allow Nulls” and “Allow blank values” checkboxes so that the report can be run “open-ended”.

如果正在使用的存储过程包含任何允许用户使用特定输入（例如，特定用户，ID，时间范围等）运行报表的参数，则还需要在报表数据中配置这些参数。在“报告数据”窗格中的“参数”节点下，右键单击在提供的存储过程中发现的参数，然后选择“参数属性”选项。在此对话框中，我们可以使用各种选项来配置特定的参数，但是此处需要执行的最重要步骤是选中“允许空值”和“允许空白值”复选框，以便可以“打开-结束”。
With this, we have all resources required for the report creation, so we can now configure the report output. Now that we are back in the report designer, we can see that the report field is currently empty. First, let’s add the desired report item from the toolbox (accessed from the “View” pane in the main ribbon or called with Ctrl+Alt+X shortcut) – simply drag the desired report items from the toolbox to the report surface – e.g. table. Now, drag the data sets from the Datasets list to the table columns (user will need to add/remove columns to match the number of datasets). Same should be done for any other added items (charts, matrix, lists…)

这样，我们就拥有了创建报告所需的所有资源，因此我们现在可以配置报告输出。现在回到报表设计器中，我们可以看到报表字段当前为空。首先，让我们从工具箱中添加所需的报告项（从主功能区的“查看”窗格中访问或通过Ctrl + Alt + X快捷方式调用）–只需将所需的报告项从工具箱中拖到报告表面即可，例如表格。现在，将数据集从“数据集”列表拖到表列（用户将需要添加/删除列以匹配数据集的数量）。对于任何其他添加的项目（图表，矩阵，列表等）也应执行相同的操作
Now that we’ve added the values, the report can be previewed or exported to any of the available formats by clicking on the “Preview” or “Export” buttons. Of course, the only remaining task is to save the report (as .rdl file) which can be used to run the report whenever it is needed or to forward it to other users so they can quickly generate reports and deploy them to available reporting environment.

现在，我们已经添加了值，可以通过单击“预览”或“导出”按钮将报告预览或导出为任何可用格式。当然，剩下的唯一任务是保存报告（作为.rdl文件），该报告可用于在需要时运行报告或将其转发给其他用户，以便他们可以快速生成报告并将其部署到可用的报告环境中。