Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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)
,@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.

Total article views: 11975 | Views in the last 30 days: 111
 
Related Articles
FORUM

Rebuild Catalog

Rebuild Full Text Catalog

FORUM

Full text Catalog Rebuild

Full text Catalog Rebuild

FORUM

Cannot Rebuild full text catalog in SQL SERVER 2000

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

FORUM

Changing the report manager color (SKIN) by style sheet

Changing the style sheet of report manager

FORUM

Sql Server Full-Text catalog/index is empty

Sql Server Full-Text catalog is not populating

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones