SSRS数据驱动查询用报表参数生成动态文件名报错求助

问题描述 #

在Reporting Services中使用数据驱动查询生成动态文件名，创建了接收3个参数的存储过程：

ALTER PROCEDURE [dbo].[sp_GenerateReportFilename]
    @ReportName NVARCHAR(100) = 'Report',
    @ReportExtension NVARCHAR(100) = '.xslx',
    @StartDate DATETIME
AS
BEGIN
    DECLARE @Year INT, @Month INT,  @Day INT,@ReportDate NVARCHAR(20), @Filename NVARCHAR(100)

    -- Extract year and month from the parameter
    SET @Year = YEAR(@StartDate)
    SET @Month = MONTH(@StartDate)
    SET @Day= DAY(@StartDate)

    -- Format year and month as "####-##"
    SET @ReportDate = FORMAT(@Year, '0000') + '-' + FORMAT(@Month, '00') + '-' + FORMAT(@Day, '00')

    -- Construct the filename
    SET @Filename =@ReportName + ' (' +  @ReportDate  + ')' + @ReportExtension

    -- Return the filename
    SELECT @Filename AS ReportFilename
END

直接执行EXEC sp_GenerateReportFilename 'MyReport', '.xslx', '2023-12-31';测试正常，但在SSRS数据驱动查询中使用报表参数@ReportDate调用EXEC sp_GenerateReportFilename 'MyReport', '.xslx', @ReportDate时，出现如下错误：

An error has occurred.
The dataset cannot be generated. An error occurred while connecting to a data source, or the query is not valid for the data source.

排查与解决步骤 #

• 校验参数类型匹配：存储过程的@StartDate为DATETIME类型，需确保SSRS报表参数@ReportDate的类型设置为DateTime，而非Text或其他类型，类型不匹配会导致参数传递失败。
• 确认参数映射正确性：在SSRS数据集的参数配置界面，将报表参数@ReportDate正确映射到存储过程的@StartDate参数，避免因参数名混淆（存储过程无@ReportDate参数）导致的调用错误。
• 检查存储过程执行权限：确保SSRS数据源使用的数据库账号拥有该存储过程的执行权限，可执行以下语句授权：

  GRANT EXECUTE ON [dbo].[sp_GenerateReportFilename] TO [你的SSRS数据源账号];
  

• 规范参数化查询写法：将SSRS数据集的查询语句改为显式参数传递格式，帮助SSRS正确识别参数：

  EXEC [dbo].[sp_GenerateReportFilename] 
      @ReportName = 'MyReport', 
      @ReportExtension = '.xslx', 
      @StartDate = @ReportDate;
  

• 验证参数实际传递值：在数据集临时添加测试查询SELECT @ReportDate AS TestDate，确认参数传递到数据库时的格式、值是否正常，排查空值或格式异常问题。
• 替换FORMAT函数（兼容性处理）：若SQL Server版本对FORMAT函数支持存在问题，改用CONCAT+RIGHT拼接日期字符串，避免隐式转换错误：

  SET @ReportDate = CONCAT(
      RIGHT('0000' + CAST(@Year AS NVARCHAR(4)), 4),
      '-',
      RIGHT('00' + CAST(@Month AS NVARCHAR(2)), 2),
      '-',
      RIGHT('00' + CAST(@Day AS NVARCHAR(2)), 2)
  )
  

内容的提问来源于stack exchange，提问作者Volkan von Klass