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

问题

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

解

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

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

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

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

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

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

创建多行文本参数

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

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

为动态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参数后。数据集属性参数选项卡如下图所示。

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

=JOIN(Parameters!SQLTxt.Label,CHR(13))

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

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

需要注意的几个要点：

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

创建一个SSRS矩阵

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

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

拖放数据集字段后，矩阵如下图所示：

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

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

校正列顺序

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