使用查询作为输入参数创建动态SSRS报告

问题

当使用SQL Server Reporting Services(SSRS)从数据库中检索数据时,将创建一个报告。即使对于简单的SELECT语句,也会创建一个新的报表定义文件并针对特定的数据源运行。这种方法存在一个普遍的问题。随着业务需求增加以获取不同的数据集,报告的数量也随之增加。此外,有时用户需要查询报告数据,并且唯一可用的接口是SSRS。最终用户别无选择,只能创建一个新的报告定义并在SSRS服务器上运行它。

解决此问题的方法是创建一个报告,该报告允许用户将查询作为报告参数传递,并且查询输出以表格形式显示为报告。使用这种方法,报告的数量可以大大减少。可以针对单个报告创建多个订阅,以生成完全不同的数据集。使用SSRS自动电子邮件功能,可以从单个报告中创建不同的电子邮件警报。

在SQL Server Reporting Services中创建动态SQL报表

当针对一组表或视图运行SELECT查询或选择查询(例如CTE(公用表表达式查询))的组合时,此解决方案似乎运行良好。

该报告的主要组成部分是:

  1. 多行文本参数,接受查询作为输入
  2. 数据集中的动态SQL查询,该查询将查询的输出转换为仅三列,即RowId,ColumnName和Value
  3. 一个SSRS矩阵,可将数据从ColumnName和Value对组合转换为表格结构。

我们将通过逐步的方法来创建动态SQL报告。

创建多行文本参数

向您的空报告中添加一个新参数,如下所示:

将SQLTxt参数添加到报告中
SQLTxt参数的详细信息

在此示例中使用的报表参数名称为SQLTxt,其提示为“ SQL文本”,数据类型为文本,选中“允许多个值”复选框,并将“选择参数可见性”设置为可见。创建此参数后,它将显示在参数列表中,如下所示。

SQLTxt出现在报表参数中

为动态SQL查询创建数据集

这是驻留在数据集查询文本中的动态SQL查询的代码。

SET NOCOUNT ON

BEGIN TRY
   DECLARE @dbCursor CURSOR 
   DECLARE @sqlQuery NVARCHAR(MAX) =@SqlTxt
   DECLARE @tblColumnDetails TABLE (ordinal_position INT, column_name VARCHAR(255))
   DECLARE @column_name VARCHAR(255) , @ordinal_position INT , @column_characteristics_flags INT , @column_size INT , @data_type_sql INT , @column_precision INT , @column_scale INT , @order_position INT , @order_direction VARCHAR(255) , @hidden_column INT , @columnid INT , @objectid INT , @dbid INT , @dbname VARCHAR(255)
   DECLARE @queryDynamicColumnsDec NVARCHAR(MAX)=''
   DECLARE @queryDynamicColumnsLst NVARCHAR(MAX)=''
   DECLARE @queryDynamicColumnsLstDec NVARCHAR(MAX)=''
   DECLARE @queryDynamicTable NVARCHAR(MAX)=''

   SET @sqlQuery = 'DECLARE  query_cursor CURSOR FOR '+@sqlQuery
   EXEC sp_executesql @sqlQuery
   DECLARE @ResultSet CURSOR
   EXEC master.dbo.sp_describe_cursor_columns @ResultSet OUTPUT,@cursor_source=global,@cursor_identity=N'query_cursor'
 
   FETCH NEXT from @ResultSet INTO @column_name, @ordinal_position, @column_characteristics_flags, @column_size, @data_type_sql, @column_precision, @column_scale, @order_position, @order_direction, @hidden_column, @columnid, @objectid, @dbid, @dbname;

   WHILE (@@FETCH_STATUS <> -1)  
   BEGIN  
      INSERT INTO @tblColumnDetails(ordinal_position,column_name) VALUES(@ordinal_position,ISNULL(@column_name,'Col_'+CAST(@ordinal_position AS VARCHAR(6))))
      FETCH NEXT from @ResultSet INTO @column_name, @ordinal_position, @column_characteristics_flags, @column_size, @data_type_sql, @column_precision, @column_scale, @order_position, @order_direction, @hidden_column, @columnid, @objectid, @dbid, @dbname   
   END  
 
   SELECT @queryDynamicColumnsDec += '['+column_name+'] NVARCHAR(MAX),', @queryDynamicColumnsLstDec+='@var_'+CAST(ordinal_position AS VARCHAR(6))+' NVARCHAR(MAX),', @queryDynamicColumnsLst+='@var_'+CAST(ordinal_position AS VARCHAR(6))+','  FROM @tblColumnDetails ORDER BY ordinal_position
   SET @queryDynamicColumnsDec= LEFT(@queryDynamicColumnsDec,LEN(@queryDynamicColumnsDec)-1)
   SET @queryDynamicColumnsLst= LEFT(@queryDynamicColumnsLst,LEN(@queryDynamicColumnsLst)-1)
   SET @queryDynamicColumnsLstDec= LEFT(@queryDynamicColumnsLstDec,LEN(@queryDynamicColumnsLstDec)-1)
   SET @queryDynamicTable = 'DECLARE @dynamicTable TABLE('+REPLACE(@queryDynamicColumnsDec,'@','')+')'
 
   DECLARE @sql1 NVARCHAR(MAX) =
   'DECLARE '+@queryDynamicColumnsLstDec+CHAR(13)+
   @queryDynamicTable+CHAR(13)+
   'OPEN query_cursor
   
   FETCH NEXT FROM query_cursor INTO '+@queryDynamicColumnsLst+'
   WHILE(@@FETCH_STATUS<>-1)
   BEGIN
      INSERT INTO @dynamicTable('+REPLACE(@queryDynamicColumnsDec,'NVARCHAR(MAX)','')+') VALUES ('+@queryDynamicColumnsLst+')
      FETCH NEXT FROM query_cursor INTO '+@queryDynamicColumnsLst+'
   END

   SELECT RowId,ColumnName,Value FROM (
   SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1))[RowId],'+REPLACE(@queryDynamicColumnsDec,'NVARCHAR(MAX)','')+' FROM @dynamicTable
   )tblBase
   UNPIVOT
   (
   Value FOR ColumnName IN ('+REPLACE(@queryDynamicColumnsDec,'NVARCHAR(MAX)','')+')
   )tblUnPivot
   ORDER BY RowId
   '
   EXEC sp_executesql @sql1
 
   IF CURSOR_STATUS('global','query_cursor')>=-1
   BEGIN
      DEALLOCATE query_cursor
   END
 
END TRY

BEGIN CATCH
   SELECT 1 RowId,'Error'ColumnName,ERROR_MESSAGE()[Value]

   IF CURSOR_STATUS('global','query_cursor')>=-1
   BEGIN
      DEALLOCATE query_cursor
   END
END CATCH            

请注意,在上一步中创建的报告参数将作为参数传递给行号4的动态SQL查询。使用sp_executesql存储过程将报告参数的输入查询作为游标运行。通过使用存储过程master.dbo.sp_describe_cursor_columns获取此游标的列详细信息,然后将列详细信息存储在@tblColumnDetails表变量中。至此,已获得列的数据集和名称。当列名和值的信息可用时,sp_executesql再次与UNPIVOT运算符一起使用,将查询输出从多个列转换为仅三个列:RowId,ColumnName和Value。整个动态SQL代码包装在TRY CATCH块中,用于异常处理。

使用动态SQL代码创建新的数据集

添加新数据集的步骤之一

在数据集属性中,将查询类型设置为文本。将动态SQL查询复制并粘贴到数据集查询文本框中,如下所示:

数据集属性的“查询”选项卡

将超时值设置为合理的数字,例如600秒。

转到数据集属性的“字段”选项卡,以手动添加“字段名称”和“字段源”。如果这些字段自动出现,如下面的屏幕截图所示,则可以按建议创建,否则可以。

数据集属性的“字段”选项卡

此数据集中的另一个重要方面是控制参数,以便不会错误地创建它们。转到数据集属性的“参数”选项卡,并检查是否在数据集参数列表中显示@cursor_source和@cursor_identity。它们从动态SQL代码中出现,数据集参数不需要它们。如果出现,请确保删除@cursor_source和@cursor_identity。为此,选择每个参数,然后单击删除。

显示两个参数(如果要显示的话)需要删除。

删除@cursor_source和@cursor_identity参数后。数据集属性参数选项卡如下图所示。

@SqlTxt是数据集报告参数

现在我们需要控制参数值。在数据集属性的“参数”选项卡中,通过单击fx 按钮并将以下代码行添加到表达式来设置@SqlTxt参数的表达式。

=JOIN(Parameters!SQLTxt.Label,CHR(13))
该表达式会将不同的行组合成一个字符串

该表达式会将多值参数转换为其中包含多行的单个字符串。这就是我们将通过的查询,其中包含多行的单个SQL查询。

设置表达式后的屏幕截图

单击“确定”创建数据集。

需要注意的几个要点:

  • 如果在从数据集参数选项卡删除不需要的参数之前创建了数据集,则这些参数将自动添加到报告参数中。如果发生这种情况,请从数据集参数和报告参数中删除@cursor_source和@cursor_identity。
  • 要理解的另一个重要点是,在此阶段尚不知道将要通过此报告运行的查询,因此我们需要手动将RowId,ColumnName和Value字段添加到数据集属性。我们不能期望将自动创建数据集字段。
具有三列RowId,ColumnName,Value的数据集

创建一个SSRS矩阵

在工具箱中,在报告中添加一个新矩阵。一个新的矩阵将如下所示:

SSRS reprot设计中的空白矩阵。

从上一步中创建的数据集中,将每个字段从DataSet中拖放,如下所示:

三列数据集
SSRS矩阵显示从数据集中删除字段的位置。

拖放数据集字段后,矩阵如下图所示:

拖放字段后的SSRS矩阵

在Business Intelligence Development Studio的较早版本中,可能会出现带有聚合函数的值,例如SUM([Value])。为避免这种情况,请右键单击“值”文本框,并将表达式设置为= Fields!Value.Value。

价值表达屏幕截图

在此状态下,动态SQL报告在功能上已完成。保存工作并预览,它应针对不同的SQL查询显示输出。

校正列顺序

如果现在预览报表,您可能会注意到从左到右的列顺序是按字母顺序AZ而不是SELECT查询指定的顺序。这是由于矩阵中组的默认排序顺序所致。要更改此顺序,请选择矩阵并分组。您将看到两种类型的组:1)行组和2)列组。在列组中,转到ColumnName组的属性,然后选择“排序”选项卡。将排序依据选项更改为RowId。如下图所示。

更改组属性的步骤
排序改为改为RowId

更改SQL Server Reporting Services报表外观

从外观角度来看,我们将进行一些小的更改。首先,我们将隐藏RowId列,因为不需要显示它。为此,右键单击,选择“文本框属性”,然后选择“可见性”选项卡,然后在可见性选项中选择“隐藏”。

隐藏RowId列的屏幕截图

对RowId标头和RowId详细信息文本框重复此操作。

RowId列现在已隐藏,并且在运行报表时将不会显示。下一步是使RowId列足够小,以使其不占用空间。将矩阵移到左上角,然后单击以将RowId的宽度最小化,如下所示:

更改大小时的屏幕截图
减小RowId宽度的大小后

让我们更改矩阵中文本框的默认颜色和字体。右键单击文本框,选择属性,然后选择“字体”选项卡。在下面的示例中,以粗体样式选择了大小为11pt的Calibri字体。

更改文本框属性。

这将更改前景文本的外观。要更改背景颜色,请在同一“文本框属性”窗口中选择“填充”选项卡。如示例中所示,填充颜色为矢车菊蓝。

背景色被填满

选择颜色,然后单击确定按钮以关闭对话框。

该报告现已完成,可以进行测试了。

使用动态SQL Server Reporting Services报表

以下是在连接到Adventure Works 2016数据库的SQL Server Reporting Services 2016中使用动态SQL报表的一些示例。

查询1的例子
示例:查询1的输出
查询2的示例
示例:查询2的输出
posted @ 2020-07-13 08:45  Javi  阅读(1055)  评论(0编辑  收藏  举报