使用sql语句生成报表_SQL Server报表服务：如何使用报表生成器处理常见的最终用户要求

使用sql语句生成报表

In this article, we will discuss the SQL Server Reporting Service Report Builder and look at how to handle common client, end user requests using Report Builder. Report Builder is a very powerful tool to create a report for the SQL Server Reporting Service.

在本文中，我们将讨论SQL Server Reporting Service报表生成器，并研究如何使用报表生成器处理常见的客户端，最终用户请求。 报表生成器是一个非常强大的工具，可以为SQL Server报表服务创建报表。

Advantages of the Report Builder is that it:

报表生成器的优点是：

• exists as a Stand-alone installation
  作为独立安装存在
• can use different data from a lot of data sources
  可以使用来自许多数据源的不同数据
• can easily deploy reports to SQL Server Reporting Service
  可以轻松地将报表部署到SQL Server报表服务
• supports full capabilities of SQL Server Reporting Service
  支持SQL Server Reporting Service的全部功能
• provides a productive report-authoring environment
  提供高效的报告编写环境

After that, we will demonstrate how to handle problems that may arise from the client or business.

之后，我们将演示如何处理客户或业务可能产生的问题。

Because when we create a report for clients, they can request very different features. Our scenario is that we have a client who sells the product to different countries, so we are developing a report for this client. We will find a solution for different needs of this client.

因为当我们为客户创建报告时，他们会要求非常不同的功能。 我们的情况是，我们有一个客户将产品销售到不同的国家，因此我们正在为此客户开发一份报告。 我们将为该客户的不同需求找到解决方案。

You can download Report Builder 2017 in this link and you can install it. At the startup, a screen comes up like below. In this screen, we will click blank report and create a new report.

您可以在此链接中下载Report Builder 2017并进行安装。 在启动时，出现如下屏幕。 在此屏幕中，我们将单击空白报告并创建一个新报告。

We will create sample sales data and retrieve data to report.

我们将创建样本销售数据并检索数据以进行报告。

CREATE TABLE SalesCurrency
(ID INT IDENTITY(1,1) PRIMARY KEY,
SalesAmount FLOAT ,
Cur VARCHAR(10),mail VARCHAR(50))
 
INSERT INTO SalesCurrency 
VALUES (20,'USD' ,'testQQtest.com') ,(10,'GBP','sample.sample.cox'),
(60,'EUR','invalidemail@h.cooom'),
('15','JPY','invalid@lll.coz'),('92','EUR','ppl@test.com')

Client Request: Could you show my different currency sales in USD and exchange the real-time currency rates?

客户要求：您能否以美元显示我的其他货币销售额并交换实时货币汇率？

Response: Yes, we can handle this problem in three steps on Reporting Builder!

响应 ：是的，我们可以通过Reporting Builder的三个步骤来解决此问题！

What to do?

该怎么办？

• We will create an XML datasource that retrieves the daily exchange rates
  我们将创建一个XML数据源来检索每日汇率
• We will create a dataset and connect this dataset to XML data source
  我们将创建一个数据集并将该数据集连接到XML数据源
• We will use lookup function and exchange all different currency sales to USD
  我们将使用查找功能，并将所有不同的货币销售额兑换为USD

First of all, we will add a datasource to our report.

首先，我们将数据源添加到我们的报告中。

Then we will select XML connection type and setup a connection string which returns exchange rates. This link will return USD exchange rates. You can find a lot of links which returns exchange rates in different currencies.

然后，我们将选择XML连接类型并设置一个返回汇率的连接字符串。 该链接将返回美元汇率。