python slicer_报表筛选：Excel Slicer与SQL Server Reporting Services（SSRS）参数
python slicer
This article explores the report filtering using Excel Slicer and SQL Server Reporting Services.
本文探讨了使用Excel Slicer和SQL Server Reporting Services进行报表筛选。
At the heart of interactive reporting is the ability for end-users to filter report datasets according to their preference. Therefore, a great data visualization tool is the one that caters for report filtering. Some of the popular data visualization tools used by some of my clients to consume my Business Intelligence solutions usually include SQL Server Reporting Services (SSRS) and Microsoft Excel. Although these tools share a similar publisher, Microsoft, they have several differences on their report usage and configuration. One difference they seem to share is in the way they are configured to enable report filtering, which is the focus of this article.
交互式报告的核心是最终用户能够根据其偏好过滤报告数据集的能力。 因此，一种出色的数据可视化工具是迎合报表筛选的工具。 一些客户用来使用我的商业智能解决方案的流行数据可视化工具通常包括SQL Server Reporting Services（SSRS）和Microsoft Excel。 尽管这些工具与Microsoft发行商相似，但是它们在报表使用和配置方面存在一些差异。 他们似乎共享的一个区别是它们配置为启用报表筛选的方式，这是本文的重点。
案例分析 (Case study)
Likewise, proper discussions surrounding topics relating to SQL Server are better explained through technical demonstrations. Consequently, the dataset that will be used to produce SSRS and Excel reports will be based on the dummy data that was imported as part of the demo in my article, Working with Ragged Right Formatted Files in SSIS. For the purposes of this article, I have further transformed this data and represented it in a form of a SQL Server View titled, FruitSales.
同样，可以通过技术演示更好地解释有关SQL Server主题的适当讨论。 因此，将用于生成SSRS和Excel报表的数据集将基于在我的文章《在SSIS中处理不正确的右格式的文件》中作为演示的一部分导入的虚拟数据。 出于本文的目的，我进一步转换了此数据，并以标题为FruitSalesSQL Server视图的形式表示了该数据。
The definition of the view can be downloaded here.
视图的定义可以在此处下载。
Figure 1 shows the execution results of a SELECT T-SQL command against the FruitSales view.
图1显示了针对FruitSales视图的SELECT T-SQL命令的执行结果。
A typical business case with such a dataset could be as follows:
具有此类数据集的典型业务案例如下：
- As a business user, I would like to see breakdown of customers by the total number of quantities of fruits they have purchased to date. 作为业务用户，我希望按客户迄今为止购买的水果总数对客户进行细分 。
- I would also like to filter the report by fruit names. 我还想按水果名称过滤报告 。
使用Excel Slicer进行报告过滤 (Report filtering using Excel slicer)
Figures 2 and 3 respectively show an SSRS and Excel reports that have been developed to address the aforementioned business case. As it can be seen, from the perspective of an end-user, the reports are almost similar with SSRS implementing reporting filtering through a multi-select Fruits parameter and Excel Pivot making use of Fruit Slicer.
图2和3分别显示了为解决上述业务案例而开发的SSRS和Excel报告。 可以看出，从最终用户的角度来看，这些报告几乎与SSRS相似，SSRS通过选择的Fruits参数和使用Fruit Slicer的Excel Pivot实现了报告过滤。
However, one advantage that Excel Pivot report has over SSRS is the ease at which end-users can add and remove slicers (or filters). Whilst an SSRS report requires development IDE like SSDT, BIDS, or Report Builder to add and remove report filters, in Excel – as a developer, you leave it to users to configure their own filters by going through the following few steps:
但是，Excel Pivot报表相对于SSRS的一个优势是，最终用户可以轻松添加和删除切片器（或筛选器）。 虽然SSRS报表需要开发者IDE（例如SSDT，BIDS或报表生成器）来添加和删除报表过滤器，但是在Excel中–作为开发人员，您可以通过以下几个步骤让用户配置自己的过滤器：
Step 1: Insert Excel Slicer
步骤1：插入Excel Slicer
Navigate to the INSERT menu and click on Slicer under the filters section of the tool bar as shown in Figure 4.
导航到INSERT菜单，然后单击工具栏的filter部分下的Slicer ， 如图4所示。
Upon clicking the Excel Slicer button, an Insert Slicers dialog box comes up and allows you to choose you slicers from a list of all available fields from your dataset as shown in Figure 5.
单击Excel Slicer按钮后，将显示一个Insert Slicers对话框，它使您可以从数据集中所有可用字段的列表中选择切片器， 如图5所示。
Furthermore, adding multiple slicers in Excel is just a matter of checking boxes next to the fields you want to filter on.
此外，在Excel中添加多个切片器只是您要过滤的字段旁边的复选框的问题。
使用参数的SSRS报告过滤 (SSRS Report filtering using parameters)
As mentioned in the previous section, whilst introducing slicers in Excel is the matter of checking boxes next to the fields you want to filter on, SSRS requires that several datasets (in addition to the main dataset) be added in order to have the additional filters appear in the report. You have an option in SSRS to populate parameters based on the main dataset but unlike in Excel, the values are duplicated as shown in Figure 6.
如上一节所述，虽然在Excel中引入切片器是要过滤的字段旁边的复选框的问题，但是SSRS要求添加几个数据集（除了主数据集之外）才能具有其他过滤器出现在报告中。 在SSRS中，您可以选择基于主数据集填充参数，但是与Excel中不同，这些值是重复的， 如图6所示。
Thus the best way to populate parameters is to set up a new dataset.
因此，填充参数的最佳方法是建立一个新的数据集。
The following is a list of steps that are required to setup report parameter in SSRS:
以下是在SSRS中设置报告参数所需的步骤列表：
Step 1: Setup Data Source in SSRS Report
步骤1：在SSRS报表中设置数据源
This is where you configure your connection string to SQL Server. You have an option to skip this step provided you have already configured a shared data source in your report.
在此配置与SQL Server的连接字符串。 如果您已经在报告中配置了共享数据源，则可以跳过此步骤。
Step 2: Setup Dataset in SSRS Report
步骤2：在SSRS报表中设置数据集
In the case of our Fruits report filter, a new dataset was setup with a text query type which ensured that we eliminate duplicates by retrieving a unique fruit names from our FruitSales view, as shown in Figure 7.
对于我们的Fruits报表过滤器，将使用文本查询类型设置一个新的数据集，以确保我们通过从FruitSales视图中检索唯一的水果名称来消除重复， 如图7所示。
Step 3: Add New Parameter in SSRS Report
步骤3：在SSRS报表中添加新参数
Once your dataset is setup, you have to navigate to the Parameters folder (which is found under the Report Data window) and click add new parameter button. When the SSRS Report Parameter Properties dialog comes up, configure Available Values and Default Values of the parameter to be populated by the new dataset, as shown in Figure 8.
设置数据集后，必须导航到“ 参数”文件夹（位于“ 报告数据”窗口下），然后单击“添加新参数”按钮。 当出现“ SSRS 报告参数属性”对话框时，配置要由新数据集填充的参数的“ 可用值”和“ 默认值 ”， 如图8所示。
Now, should business users later realize the need to further filter the SSRS report by – for instance, Method of Payment (MOP) – you will, unfortunately, have to repeat all steps 1-3 again.
现在，如果业务用户以后意识到有必要通过例如付款方式（MOP）进一步过滤SSRS报告，那么不幸的是，您将不得不再次重复所有步骤1-3。
性能成本 (The performance cost)
It’s not just the number of steps that you have to perform to set up additional filtering in SSRS Report, but there is a SQL Server performance angle to it too. Because every parameter has its own dataset, then whenever an SSRS report is executed, separate SQL Server database connection sessions are generated to retrieve and populate report parameter values.
不仅要在SSRS报表中设置其他筛选所必须执行的步骤数，而且还存在SQL Server性能方面的问题。 因为每个参数都有其自己的数据集，所以每执行一次SSRS报告，就会生成单独SQL Server数据库连接会话以检索和填充报告参数值。
To demonstrate this point, I set up a database trace prior to running both an SSRS report and Excel Pivot report. When we refresh the Excel Pivot report, only a single SQL:BatchCompleted event class is generated as shown in Figure 9. We can also see the T-SQL script that was executed as part of refreshing the report, which was the execution of the FruitSales view.
为了说明这一点，我在运行SSRS报告和Excel Pivot报告之前先设置了数据库跟踪。 当我们刷新Excel Pivot报表时，仅生成一个SQL：BatchCompleted事件类， 如图9所示。 我们还可以看到T-SQL脚本是刷新报告的一部分，该报告是FruitSales视图的执行。
The trace results of an SSRS report refresh indicate that in addition to executing the FruitSales view, another T-SQL script was executed to retrieve unique fruit names as shown in Figure 10. It is important to note that only two scripts were executed because we only have one parameter if, later, business requires additional parameters, then more T-SQL scripts will come up in the trace.
SSRS报告刷新的跟踪结果表明，除了执行FruitSales视图之外，还执行了另一个T-SQL脚本来检索唯一的水果名称， 如图10所示。 重要的是要注意，仅执行了两个脚本，因为如果以后业务需要附加参数，那么只有一个参数，那么跟踪中将出现更多的T-SQL脚本。
结论 (Conclusion)
The purpose of this discussion has not been to underestimate the value of SSRS Report instead to share a scenario (report filtering) whereby the use of Excel could be more convenient for report end-users and development team than SSRS. The convenience of filtering report using slicers in evident in the ability for users to select multi-slicers by checking boxes which reduces some dependencies on report developers.
讨论的目的并不是要低估SSRS报告的价值，而是要分享一个方案（报告过滤），从而使报告的最终用户和开发团队使用Excel比SSRS更方便。 使用切片器筛选报表的便利性明显体现在用户可以通过复选框选择多个切片器的能力，从而减少了对报表开发人员的依赖性。
参考： (Reference:)
- SQL:BatchCompleted Event Class SQL：BatchCompleted事件类别
- How to Use Excel Slicers 如何使用Excel Slicer
- SSRS Report Parameters SSRS报告参数
python slicer