Another missing "feature" in SSRS has been the ability to change the name of a report upon email or file share delivery. After digging a bit further into the Report Server database, I've found a nifty way to get the job done.

For this method to make a bit more sense, if you're not familiar with the Data Driven Subscriptions method covered in my previous article, you may want to take a moment and look. It can be found here.

What's involved

This method utilizes the dbo.[Catalog] table in the report server database. Although one would expect the "name" field to be the value to change, it's not. The "path" field is actually our target. But first we need to strip off the actual path from the report name itself, because a standard listing will be similar to "/My reports/This Folder/My Reportname" and all we want to change is the "My Reportname" portion. Lastly we will need to change the path back to its original value or your subscription entry will be all mucked up.

• Add the parameter definition to the procedure described in the above article.

    ,@reportFileName NVARCHAR(255) = NULL

• Declare three new variables for string manipulation and retaining the original path value.

       ,@previousReportFileName NVARCHAR(255)
       ,@tmpFileName NVARCHAR(255)
       ,@reportID UNIQUEIDENTIFIER

• In the section where you obtain the subscriptionID and scheduleID, modify that code to read:

-- get the subscription ID
    SELECT
        @subscriptionID = rs.subscriptionID
       ,@scheduleID = rs.ScheduleID
       ,@reportID = rs.reportID 
    FROM
        ReportSchedule rs
            INNER JOIN subscriptions s
                ON rs.subscriptionID = s.subscriptionID
            INNER JOIN dbo.[catalog] c
                ON rs.reportID = c.itemID
    WHERE
        extensionSettings LIKE '%' + @scheduleName + '%'

• Before calling the SQL job, modify the path value to meet your needs.

/* get the existing path/filename */    
    SET @tmpFileName = REVERSE(@previousReportFileName)

    -- now we'll reverse it, take off the end and reverse it back
    -- so that "/My reports/This Folder/this Report" becomes "/My reports/This Folder/"
    -- then add the new report file name to the end
    SELECT @reportFileName = 
                CASE WHEN ISNULL(@reportFileName,'') <> '' THEN 
                    REVERSE(right(@tmpFileName, LEN(@tmpFileName)-CHARINDEX('/',@tmpFileName)+1))
                            + @reportFileName
                ELSE
                    @previousReportFileName
                END
/* update the report's name to the new file name */
    UPDATE c
    SET [path] = @reportFileName
    FROM
        dbo.[catalog] as c
    WHERE 
        c.itemID = @reportID

• After calling the job, set everything back to the way it was.

/* update the report's name back to the original name */
    UPDATE c
    SET [path] = @previousReportFileName
    FROM
        dbo.[catalog] as c
    WHERE 
        c.itemID = @reportID

And that should do it. Please note, that as with any code that modifies Microsofts' design, there are no warranties that this approach will continue to work in future versions of SQL Server or after any patches are applied.

Have Fun!

Special thanks to Kris Coone for bringing the requirement to the forefront.