ssrs 列表中插入图表_使用SSRS中的象限复制Excel的XY散布报告图表

ssrs 列表中插入图表

In this article, I’ll share some of the tricks and tips that I had to employ in order to successfully replicate Excel’ XY Scatter Report Chart.

在本文中，我将分享一些为了成功复制Excel的XY散布报告图表而必须使用的技巧。

In the article, Available options for generating heatmaps in an SSRS report, I covered some of the limitations of SQL Server Reporting Services (SSRS) specifically when it comes to native support for heatmaps compared to other Microsoft reporting tools and thus how such a limitation can make it difficult for BI developers to perform like-for-like migration of Excel reports (with heatmaps) into SSRS reports. In the same article, I went on to suggest 3 main workarounds for replicating Excel’s heatmap feature into an SSRS report. In this article, I continue to look at Excel-to-SSRS report migration but instead of heatmaps, I focus on demonstrating some of the tricks and tips that I’ve recently had to employ in order to successfully replicate Excel’s XY Scatter Report Chart (with Quadrants) into an SSRS platform.

在文章“ 在SSRS报表中生成热图的可用选项”中 ，我介绍了SQL Server Reporting Services （SSRS）的一些局限性，特别是与其他Microsoft报表工具相比，本机支持热图使得BI开发人员难以将Excel报告（带有热图）执行类似迁移到SSRS报告中。 在同一篇文章中，我继续提出了将Excel的热图功能复制到SSRS报告中的3种主要解决方法。 在本文中，我将继续研究从Excel到SSRS的报告迁移，而不是热图，而是着重于展示一些为了成功复制Excel的XY散点图而必须采用的技巧和技巧（象限）进入SSRS平台。

背景 (Background)

XY Scatter graphs with quadrants can easily improve readability of your report and be very effective in indicating where scattered data points fall within a given quadrant. Depending on the labels that you have assigned to your quadrants, an XY Scatter graph can give you a breakdown of your company’s Strengths, Weaknesses, Opportunities, and Threats (SWOT) against predefined measure.

带有象限的XY散点图可以轻松提高报告的可读性，并且在指示分散的数据点在给定象限内的位置方面非常有效。 根据分配给象限的标签，XY散布图可以根据预定义的度量对公司的优势，劣势，机会和威胁（SWOT）进行细分。

As the name implies, XY Scatter graphs require two sets of measures (X and Y) to successfully plot a single data point. For the purposes of this demo, my XY Scatter graph is based on data collected during the 2017/2018 UEFA Champions’ League season. I use this data to determine a football club’s performance (i.e. poor, average, good, excellent) with respect to points accumulated vs goal difference.

顾名思义，XY散点图需要两组度量（X和Y）才能成功绘制单个数据点。 就本演示而言，我的XY散布图基于2017/2018欧洲冠军联赛冠军联赛期间收集的数据。 我使用这些数据来确定足球俱乐部相对于积分与目标差的表现（即差，中，好，优）。

Using Microsoft SQL Server 2016 Report Builder, I have created a report dataset based on Table 1. I have also added an XY Scatter chart wherein X and Y values are mapped against Points and Goal Difference fields, respectively. A preview of this Report Builder SSRS report is shown in Figure 1.

使用Microsoft SQL Server 2016报表生成器 ，我基于表1创建了报表数据集。 我还添加了XY散点图，其中X和Y值分别对应于“ 点数”和“ 目标差”字段进行了映射。 此报告生成器SSRS报告的预览如图1所示。

Having successfully created an SSRS version of the XY Scatter graph, I will spend the rest of the article demonstrating how we can get the SSRS report shown in Figure 1 to include quadrants and related labels as per the Excel version of the report shown in Figure 2.

成功创建了XY散点图的SSRS版本后，我将在本文的其余部分中说明如何根据图2所示的Excel版本如何获得图1所示的SSRS报告，以包括象限和相关标签。 。

在SSRS XY散布图图表上创建一个象限 (Create a Quadrant over an SSRS XY Scatter Report Chart)

1. Manually Create Quadrants by Drawing Lines

   Perhaps the simplest way to splitting an SSRS XY Scatter report chart into 4 quadrants, is by manually drawing lines on the chart area using the Line report item type, as shown in Figure 3.

   

   Figure 3

   Using the Line report item type, I drew vertical and horizontal lines that interlace to form 4 quadrants on the SSRS XY Scatter report chart as shown in Figure 4.

   Figure 4

   One obvious disadvantage of creating a quadrant using the Line report item type is that the positioning of the lines, especially the vertical line is static. Imagine, for instance, that in subsequent seasons of the UEFA Champions League – the maximum points accumulated becomes 50, that will mean we will have to manually move the position of our vertical line from 10^th to the 25^th interval along the chart’s x-axis. Therefore, manually drawing quadrant lines is not an optimal option when dealing with a dynamic dataset.

   通过画线手动创建象限

   将SSRS XY散点图图表划分为4个象限的最简单的方法，也许是使用Line报告项类型在图表区域上手动绘制线， 如图3所示。

   

   图3

   使用Line报告项类型，我在SSRS XY Scatter报告图表上绘制了交织以形成4个象限的垂直和水平线， 如图4所示。

   图4

   使用“ 线”报告项类型创建象限的一个明显缺点是线的位置，尤其是垂直线是静态的。 想象一下，例如，在随后的欧洲冠军联赛中-累积的最高分变为50，这意味着我们将不得不沿着图表的x-轴手动将垂直线的位置从^第 10 ^个间隔移至第25 ^个间隔轴。 因此，在处理动态数据集时，手动绘制象限线不是最佳选择。

2. Dynamically Create Quadrants using StripLines

   One useful tip for dynamically creating quadrants over an SSRS XY Scatter report chart is by making use of StripLines. In order for StripLines to produce both horizontal and vertical lines, we need to configure the StripLines property in both vertical and horizontal Chart Axis. To access the StripLines property, you need to click on either the vertical or horizontal Chart Axis as shown in Figure 5.

   

   Figure 5

   Then, navigate to the property window – usually located on the right-hand side in Report Builder – and click on the ellipsis button next to StripLines property, as shown in Figure 6.

   Figure 6

   In the ChartStripLine Collection Editor, add a StripLine entry and ensure that Border-related properties are configured as shown in Figure 7.

   

   Figure 7

   When configuring the horizontal chart axis, ensure that the value for the IntervalOffset key is set to a calculated expression as shown in Figure 8. This will ensure that whenever values from the Points field change, the positioning of the strip line will be adjusted accordingly.

   Figure 8

   Once StripLines have been added in both vertical and horizontal Chart Axis, a quadrant should be formed over your XY Scatter chart during the next time you run your report, as shown in Figure 9.

   

   Figure 9

   使用 带状线 动态创建象限

   通过使用StripLines在SSRS XY散点图图表上动态创建象限的一个有用技巧是。 为了使StripLines产生水平和垂直线，我们需要在垂直和水平Chart Axis中配置StripLines属性。 要访问StripLines属性，您需要单击垂直或水平的Chart Axis， 如图5所示。

   图5

   然后，导航到属性窗口-通常位于Report Builder的右侧-并单击StripLines属性旁边的省略号按钮， 如图6所示。

   

   图6

   在ChartStripLine 集合编辑器中 ，添加StripLine条目并确保如图7所示配置了与Border相关的属性。

   图7

   配置水平图表轴时，请确保将IntervalOffset键的值设置为计算的表达式， 如图8所示。 这样可以确保只要Points字段中的值发生变化，带状线的位置就会相应地进行调整。

   

   图8

   在垂直和水平“图表轴”中都添加了StripLines之后 ，在下次运行报表时，应在XY散布图上形成一个象限， 如图9所示。

   图9

将标签分配给象限 (Assign labels to Quadrants)

Similarly, to drawing lines over charts, we begin with a simpler approach to adding labels to quadrants by making use of the Textbox report item.

同样，要在图表上绘制线条，我们首先使用一种简单的方法，即通过使用“ 文本框”报告项将标签添加到象限。

Using the Textbox report item, I have added Good, Excellent, Poor, and Average labels to my quadrants and adjusted the background color to mimic that of the labels shown in Figure 2.

使用“ 文本框”报告项，我向象限添加了“ 好” ，“ 优秀” ，“ 差 ”和“ 平均”标签，并调整了背景颜色以模仿图2所示的标签。

Unfortunately, simpler approaches often do not yield optimal results as the preview of Figure 11 shows that all labels (textboxes) get positioned outside of the chart area during a preview of the report, as shown in Figure 12. This means that we cannot reliably use textboxes as labels for quadrants over our SSRS XY Scatter charts.

不幸的是，更简单的方法通常无法产生最佳结果，因为图11的预览显示在报告的预览期间所有标签（文本框）都位于图表区域之外， 如图12所示。 这意味着我们无法可靠地将文本框用作SSRS XY散布图上象限的标签。

However, one trick that we can rely on involves making use of multiple chart titles as labels for our quadrants. To configure chart titles as labels, we first need to add them into our XY Scatter chart as illustrated in Figure 13.

但是，我们可以依靠的一个技巧是使用多个图表标题作为象限的标签。 要将图表标题配置为标签，我们首先需要将它们添加到XY散点图中， 如图13所示 。

Once all the chart titles have been added, we next move them around the chart area by enabling the CustomPosition property which is located at the bottom of each Chart Title property menu as shown in Figure 14.

添加完所有图表标题后，接下来我们通过启用位于每个“ 图表标题”属性菜单底部的CustomPosition属性，将它们在图表区域中移动， 如图14所示。

Having adjusted CustomPosition properties, you will notice that next time you preview the report, all 4 chart titles get correctly positioned as labels for quadrants in our SSRS XY Scatter report chart, as shown in Figure 15.

调整CustomPosition属性后，您会注意到，下次预览报表时，所有4个图表标题都将正确定位为SSRS XY散点图图表中象限的标签， 如图15所示。

摘要 (Summary)

In this article, we have looked at how an Excel’s XY Scatter Chart with Quadrants can be replicated into a SQL Server Reporting Services. It was demonstrated that the difficulty of this Excel-to-SSRS report replication often occurs when a quadrant with labels has to be created over the SSRS XY Scatter report chart. Yet by using a combination of StripLines and Chart Titles, we managed to successfully create a dynamically responsive quadrant with labels over the SSRS XY Scatter report chart.

在本文中，我们研究了如何将带有象限的Excel XY散点图复制到SQL Server Reporting Services中 。 事实证明，当必须在SSRS XY散点图图表上创建带有标签的象限时，通常会发生这种Excel到SSRS报告复制的困难。 但是，通过结合使用带状线和图表标题 ，我们成功地在SSRS XY散布报告图表上成功创建了带有标签的动态响应象限。

资料下载 (Downloads)

• XYScatterChart_StripLines
  XYScatterChart_StripLines
• XYScatterChart_ChartTitle
  XYScatterChart_ChartTitle
• XYScatterChart_Lines
  XYScatterChart_Lines
• XYScatterChart_TextBoxes
  XYScatterChart_TextBoxes

翻译自: https://www.sqlshack.com/replicating-excels-xy-scatter-report-chart-with-quadrants-in-ssrs/

ssrs 列表中插入图表