CODEWITHSUNDEEP
reporting-servicesssrs-2019
Volkan von Klass
Volkan von Klass

Reputation: 1

SQL Server Reporting Services: how to use a report parameter in the data-driven query?

I have a report in Reporting Services which should use a data-driven query to create a dynamic file name.

For that I created a stored procedure that accepts 3 parameters:

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

This works fine with the test:

EXEC sp_GenerateReportFilename  'MyReport', '.xslx', '2023-12-31';

But if I try use the SSRS report also called "ReportDate" in the data-driven query, I got an error.

Query:

EXEC sp_GenerateReportFilename  'MyReport', '.xslx', @ReportDate

Error:

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.

Any idea to fix this problem?

Thank you in advance Volkan

I tried google, co-pilot and several workarounds without success

Upvotes: 0

Views: 90

Answers (1)

Harry
Harry

Reputation: 2951

Your procedure states this:

ALTER PROCEDURE [dbo].[sp_GenerateReportFilename]
    @ReportName NVARCHAR(100) = 'Report',
    @ReportExtension NVARCHAR(100) = '.xslx',
    @StartDate DATETIME
AS

You are calling the stored procedure using this:

EXEC sp_GenerateReportFilename  'MyReport', '.xslx', @ReportDate

Yet, your stored procedure uses @Startdate

So the statement should be

EXEC sp_GenerateReportFilename  'MyReport', '.xslx', @StartDate

Upvotes: 0

Related Questions