SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Dynamically name report/file export in SSRS

By Jason Selburg,

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)
  • In the section where you obtain the subscriptionID and scheduleID, modify that code to read:
-- get the subscription ID
@subscriptionID = rs.subscriptionID
,@scheduleID = rs.ScheduleID
,@reportID = rs.reportID
ReportSchedule rs
INNER JOIN subscriptions s
ON rs.subscriptionID = s.subscriptionID
INNER JOIN dbo.[catalog] c
ON rs.reportID = c.itemID
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
END /* update the report's name to the new file name */
SET [path] = @reportFileName
dbo.[catalog] as c
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 */
SET [path] = @previousReportFileName
dbo.[catalog] as c
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.

Total article views: 14160 | Views in the last 30 days: 9
Related Articles

How to change Integration Services Catalog (SSISDB) database Master Key encryption password?

To change the Integration Services Catalog (SSISDB) database Master Key encryption password, run the...


Rebuild Catalog

Rebuild Full Text Catalog


Full text Catalog Rebuild

Full text Catalog Rebuild


Changing the report manager color (SKIN) by style sheet

Changing the style sheet of report manager


Cannot Rebuild full text catalog in SQL SERVER 2000

Catalog is on a different path/drive and need to be changed