ssrs 数据分页_如何在SSRS中使用JSON数据

ssrs 数据分页

In this article, we will explore the concept of using JSON data in SQL Server Reporting Services (SSRS). This usage concept will include a different approach than the usual methodologies because we will take advantage of SQL Server R service support.

在本文中，我们将探讨在SQL Server Reporting Services（SSRS）中使用JSON数据的概念。 此用法概念将包含与通常方法不同的方法，因为我们将利用SQL Server R服务支持。

什么是JSON？ (What is JSON?)

JSON is an abbreviation for JavaScriptObjectNotation. The main purpose of JSON data was to provide data transfer between server and web applications for JavaScript but today’s JSON is the most popular data interchange format. JSON offers two advantages to us. These are; light-weight text based format easily readable by humans. Currently, JSON is not only used in JavaScript applications, it is also used in all popular software programing languages like JavaScript, C++, C#, Perl, Java, Python, Php etc.

JSON为的J ava 小号 CRIPTöbjectÑ浮选的缩写。 JSON数据的主要目的是为JavaScript提供服务器和Web应用程序之间的数据传输，但是今天的JSON是最流行的数据交换格式。 JSON为我们提供了两个好处。 这些是; 基于轻量级文本的格式，易于人类阅读。 当前，JSON不仅用于JavaScript应用程序，还用于所有流行的软件编程语言，例如JavaScript，C ++，C＃，Perl，Java，Python，Php等。

什么是JSON数据结构？ ( What is the JSON data structure?)

The JSON object structure is based on a set of key-value pairs. A JSON object structure starts with left brace “{“and ends with right brace “}”. A Key defines the property of key-value pair and value stores the data of key-value pair. Keys and values are separated with colon sign “:”. Keys must be string and values can be any data type of JSON data type (string, number, object, array, boolean or null). Key-value pairs are separated with a comma. The following example shows a very simple form of JSON data.

JSON对象结构基于一组键值对。 JSON对象结构以左括号“ {”开始，以右括号“}”结束。 键定义键值对的属性，值存储键值对的数据。 键和值之间用冒号“：”分隔。 键必须是字符串，值可以是JSON数据类型的任何数据类型（字符串，数字，对象，数组，布尔值或null）。 键值对以逗号分隔。 以下示例显示了一种非常简单的JSON数据形式。

{
“Id”: “22”,
“first_name”: “Tom”,
“e_mail”: “tom@bymail.com”
}

{
“ Id”：“ 22”，
“ first_name”：“ Tom”，
“ e_mail”：“ tom@bymail.com”
}

如何使用SQL Server R服务获取JSON数据？ (How to get JSON data with the SQL Server R Service?)

SQL Server 2016 and higher versions are capable of storing and parsing JSON data. Also, another capability of SQL Server is to convert JSON data into tables. SQL Server offers the table-valued function OPENJSON, this function helps to parse and query JSON data types.

SQL Server 2016和更高版本能够存储和解析JSON数据。 另外，SQL Server的另一功能是将JSON数据转换为表。 SQL Server提供了表值函数OPENJSON ，此函数有助于解析和查询JSON数据类型。

After these brief explanations about JSON, let’s focus on our main topic. SSRS does not provide any organic JSON data source, so we need to find an alternative solution to get and use JSON data in SSRS. Generally, reporting development needs to source JSON data through a web site. The main solution of this issue is to use OLE Automation procedures. This article Consuming JSON Formatted API Data in 2016 provides all details about how to get JSON data over a web site using OLE Automation. An alternative of this solution is to use R language support of SQL Server. Now we will focus about this solution approach. If you have some interest in the R language you can imagine that it can handle this issue. We can find several packages for R script which helps us to get and parse JSON data. Jsonlite is the one of the package which helps to get and parse JSON data in R script. We need to complete the following steps for this alternative solution.

在对JSON进行了这些简要说明之后，让我们集中讨论我们的主要主题。 SSRS不提供任何有机JSON数据源，因此我们需要找到一种替代解决方案来获取和使用SSRS中的JSON数据。 通常，报表开发需要通过网站来获取JSON数据。 此问题的主要解决方案是使用OLE自动化过程。 本文在2016年使用JSON格式的API数据提供了有关如何使用OLE Automation在网站上获取JSON数据的所有详细信息。 此解决方案的替代方法是使用SQL Server的R语言支持。 现在，我们将重点介绍这种解决方案方法。 如果您对R语言感兴趣，可以想象它可以解决此问题。 我们可以找到一些R脚本包，这些包可以帮助我们获取和解析JSON数据。 Jsonlite是帮助在R脚本中获取和解析JSON数据的软件包之一。 对于该替代解决方案，我们需要完成以下步骤。

• Enable external scripts in SQL Server.
  在SQL Server中启用外部脚本。
• Install Jsonlite package for SQL Server R Service.
  安装用于SQL Server R Service的Jsonlite软件包。
• Get JSON data from a website and transform this JSON data to table with help of R.
  从网站获取JSON数据，并在R的帮助下将此JSON数据转换为表。
• Use this transformed data in SSRS.
  在SSRS中使用此转换后的数据。

The following query will enable external scripts in SQL Server.

以下查询将在SQL Server中启用外部脚本。

 
  EXEC SP_CONFIGURE 'external scripts enabled',1
  GO
  RECONFIGURE
 

We will run the below query which helps to check configuration R script.

我们将运行以下查询，这有助于检查配置R脚本。

 
  EXEC sp_execute_external_script
      @language = N'R'
      ,@script = N'print("R script enable")'
 

Now we will check the installation of Jsonlite package. When you try to execute the following query and if you experience an error such as the image below, it means that you have to install Jsonlite package to SQL Server R service.

现在，我们将检查Jsonlite软件包的安装。 当您尝试执行以下查询时，如果遇到错误（如下图所示），则意味着必须将Jsonlite软件包安装到SQL Server R服务。

 
  EXEC sp_execute_external_script
        @language = N'R'
       , @script = N'	library(jsonlite)'
 

The following steps help us to install Jsonlite package.

以下步骤可帮助我们安装Jsonlite软件包。

• 
  
  
  You can find it in this folder
  您可以在此文件夹中找到它
  “C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES\bin”
  “ C：\ Program Files \ Microsoft SQL Server \ MSSQL13.MSSQLSERVER \ R_SERVICES \ bin”

• 
  
  
  
  
  

• 
  
  
  
  
  

  We need to install curl package because it is the general network (http/ftp/…) client interface.

  我们需要安装curl软件包，因为它是常规网络（http / ftp /…）客户端界面。

• 
  
  
  
  
  

Now, we will get JSON data from a website. In the following demonstration, we will use JSONPlaceholder website for JSON data samples. This website offers free and online JSON data for testing. We will use posts data and this JSON data structure includes “userId”, “id”, “title” and “body” keys.

现在，我们将从网站上获取JSON数据。 在下面的演示中，我们将使用JSONPlaceholder网站获取JSON数据样本。 该网站提供免费和在线JSON数据以进行测试。 我们将使用发布数据，并且此JSON数据结构包括“ userId”，“ id”，“ title”和“ body”键。

In this step, we will get JSON data from the website and convert it to table with help of R in SQL Server. We will explain the R code first line of code initialize the package of Jsonlite and in the second line use fromJSON function. This function helps us to get and parse JSON data and then we assign this data to mydata variable and this variable is output of our R script. Finally, we will parse the JSON data to table WITH RESULT SETS command.

在此步骤中，我们将从网站上获取JSON数据，并借助R在SQL Server中将其转换为表。 我们将在第一行代码中解释R代码，初始化Jsonlite的包，在第二行中使用fromJSON函数。 此函数帮助我们获取和解析JSON数据，然后将这些数据分配给mydata变量，并且此变量是R脚本的输出。 最后，我们将使用RESULT SETS命令将JSON数据解析到表中。

 
  EXEC sp_execute_external_script
        @language = N'R'
       ,@script = N'library(jsonlite) 
  mydata <- fromJSON("https://jsonplaceholder.typicode.com/posts" target="_blank" rel="nofollow")'
  , @output_data_1_name = N'mydata'
  WITH RESULT SETS ((userId int ,Id int , title varchar(max),body varchar(max)))
 

在SSRS中使用JSON数据 (Using JSON data in SSRS)

In this section, we will quickly use the R external script in SQL Server Report Builder and visualize the JSON data in a sample SSRS report.

在本节中，我们将快速使用SQL Server Report Builder中的R外部脚本，并在示例SSRS报告中可视化JSON数据。

• Launch the SSRS Report Builder
  启动SSRS报表生成器
• Data Sources and select 数据源，然后选择Add Data Source添加数据源
  
  
  
  
  

• Use a connection embedded in my report and then give a name to data source and then click 使用我的报表中嵌入的连接” ，然后为数据源命名，然后单击“ Build构建”。
  
  
  
  
  

• In the Connection Properties screen, fill the server name and select the database. In this demonstration we don’t need to get any data from any database for this reason we can select tempdb
  在“连接属性”屏幕中，填写服务器名称并选择数据库。 在此演示中，由于这个原因，我们不需要从任何数据库获取任何数据，因此我们可以选择tempdb。
• Test Connection and ensure your connection settings and then click 测试连接并确保您的连接设置，然后单击OK 确定。

• Add Dataset 添加数据集”
• Use a dataset embedded in my report and then select the data source which we created in the previous step使用嵌入在我的报告中的数据集 ，然后选择我们在上一步中创建的数据源
  
  
  
  
  

•  
    Use tempdb
    go
    CREATE or ALTER PROC GetJsonWeb
    as
    EXEC sp_execute_external_script
          @language = N'R'
         ,@script = N'library(jsonlite) 
          mydata <- fromJSON("https://jsonplaceholder.typicode.com/posts" target="_blank" rel="nofollow")'
         , @output_data_1_name = N'mydata'
    WITH RESULT SETS ((userId int ,Id int , title varchar(max),body varchar(max))) 
   
  

• (!) exclamation sign（！）感叹号。
  
  
  
  
  

• You can see the converted JSON data table columns under the Datasets folder
  您可以在Datasets文件夹下看到转换后的JSON数据表列
• Insert tab and select 插入”选项卡，然后选择“ Insert table and then drop the table component into the SSRS report design panel插入表” ，然后将表组件放入“ SSRS报表设计”面板中。
  
  
  
  
  

• Run button. Our SSRS report will look like the below image运行按钮。 我们的SSRS报告如下图所示
  
  
  
  
  

If you want, you can deploy your report to the SQL Server Report Server and you can use your report in Reporting Server web portal.

如果需要，可以将报表部署到SQL Server报表服务器，也可以在Reporting Server Web门户中使用报表。

结论： (Conclusion:)

In this article, we looked at how to get JSON data from any website with help of the SQL Server R Service. If your JSON is in basic format and you don’t want to do much effort, you can use this alternative approach. At the same time, you can use this method in SSRS.

在本文中，我们研究了如何借助SQL Server R Service从任何网站获取JSON数据。 如果您的JSON是基本格式，并且您不想做很多事情，则可以使用这种替代方法。 同时，您可以在SSRS中使用此方法。

常见问题 (FAQs)

Can we convert table data to JSON in R?

我们可以在R中将表格数据转换为JSON吗？

Yes, we can use the toJSON function in R

是的，我们可以在R中使用toJSON函数

What is the advantage of JSON over XML?

JSON比XML有什么优势？

JSON has light-weight format and this advantage make it faster than XML.

JSON具有轻量级格式，这种优势使其比XML更快。

Which data types are supported by JSON?

JSON支持哪些数据类型？

• Integer, float or double
  整数，浮点数或双精度
• Boolean
  布尔型
• Array
  数组
• Null
  空值

翻译自: https://www.sqlshack.com/how-to-use-json-data-in-ssrs/

ssrs 数据分页