<

SQL Server 2008 Report Subscription Dynamic File Names

Published on
11,383 Points
5,283 Views
1 Endorsement
Last Modified:
Approved
After much searching across the internet I have found that you could not set the name of the file you were attaching to dynamic report subscriptons in Microsoft Reporting Services. I did manage to find one article showing you how your could make a stored procedure to actually execute the report from, providing the parameters. However this only worked for one parameter, and was kind of a pain for a long term solution.

I found the core of this method was using the path in the catalog table. That is what the file name is based off of. So I have created the stored procedure below to be run every 15 minutes (you can change the timing) the change the path of the reports that were going to be generated in the next 15 minutes and to change back the path of any reports that were previously generated.
 
The only limitation of this is that you cannot access that report directly in the report server during that 15 minutes. Howver, you can alter the script to change this timeframe if you would like to limit that window.

This has only been tested on Data-driven subscriptions on shared report schedules. This may not work in other scenarios as the date fields I use are specific to shared report schedules.

The code I have written works well for my scenario but you may want to change it for yours. The main point is you need to change the path in the catalog table before the emails goes to what you want the filename to be, and you need to change it back afterwards so you can access the report via Report Manager. Other ways I thought of doing this was to try and hunt down the procedure called to run the report and change it before and after that code in that procedure. If you end up doing this please post so other can see how.

Cheers,
Mark Terry


/****** 
SCRIPT: UpdatePaths
Created: May 25, 2010
Created By: Mark Terry
Updated: June 18, 2010

PURPOSE: The purpose of this procedure is to replace the Path in the catalog table for reports which are about to be distributed by email. 
It does this so that the filename of the attached report will have the date in the file name. 
In order for this to work it must be executed by a SQL Job every 15 minutes. 
During the 15 minutes that the path has been changed, you cannot access from report server directly as the path has been changed.

REQUIREMENTS: You must setup a SQL Job on the same server as the ReportServer database to run every 15 minutes (unless timing changed) for this to work correctly. 

Tweaks: You can change the line 
Set Path = Path + ' ' +  Replace(Replace(CONVERT(Char(11), getdate(), 106),'201', '1'),' ','') to Set Path = Path + ' ' +  Whatever you want as long as you update the condition in the first update statement to match
You can change the timing by changing the between 0 and 15 ranges at the end of the procedure.

Latest Updates: Changed the dates it is looking for as it didn't work over midnight runs.

  ******/
ALTER Procedure [dbo].[UpdatePaths] as
Begin
--This procedure updates paths for report subscriptions
--the report subscription typically needs to be on a shared subscription for this to work. Maybe changing the dates I am looking at will make it work
IF not EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='Catalog_Path') 
	CREATE TABLE [dbo].[Catalog_Path](
		[ItemID] [uniqueidentifier] NOT NULL,
		[PriorPath] [nvarchar](425) NULL
	) ON [PRIMARY]   

Insert Into Catalog_Path (ItemID, PriorPath)
Select ItemID, Path from Catalog Where Catalog.ItemID not in (Select ItemID from Catalog_Path)

Delete from Catalog_Path
Where ItemID not in (Select ItemID from Catalog)

UPDATE Catalog
Set Path = PriorPath
FROM  
(SELECT
	cp.ItemID as PastItemID,
	cp.PriorPath
FROM Catalog_Path cp
)Past
Where Catalog.ItemID = PastItemID
And Path like '%' +  replace(Cast(YEAR(GetDate()) as varchar), '201', '1')
And Catalog.ItemID in
(Select ItemID FROM        
	ReportSchedule rs            
INNER JOIN subscriptions s                
	ON rs.subscriptionID = s.subscriptionID            
INNER JOIN dbo.[catalog] c                
	ON rs.reportID = c.itemID    
Inner Join Schedule sc
	ON sc.ScheduleID = rs.ScheduleID
WHERE        
sc.LastRunTime < GETDATE() and not
(DATEDIFF(MINUTE,GETDATE(),NextRunTime) between 0 and 15
OR DATEDIFF(MINUTE,GETDATE(),StartDate) between 0 and 15))


UPDATE Catalog
Set Path = Path + ' ' +  Replace(Replace(CONVERT(Char(11), getdate(), 106),'201', '1'),' ','')
FROM  
(SELECT
	c.ItemID as FutureItemID
FROM        
	ReportSchedule rs            
INNER JOIN subscriptions s                
	ON rs.subscriptionID = s.subscriptionID            
INNER JOIN dbo.[catalog] c                
	ON rs.reportID = c.itemID    
Inner Join Schedule sc
	ON sc.ScheduleID = rs.ScheduleID
WHERE        
DATEDIFF(MINUTE,GETDATE(),NextRunTime) between 0 and 15
OR DATEDIFF(MINUTE,GETDATE(),StartDate) between 0 and 15)Future
Where Catalog.ItemID = FutureItemID
And Path not like '%' + Replace(Replace(CONVERT(Char(11), getdate(), 106),'201', '1'),' ','')
END

Open in new window

1
Comment
Author:markterry
0 Comments

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Join & Write a Comment

When you have multiple client accounts to manage, it often feels like there aren’t enough hours in the day. With too many applications to juggle, you can’t focus on your clients, much less your growing to-do list. But that doesn’t have to be the cas…
Wrapper-1-Query. Use an Excel function to calculate a column for an Access query. Part 1. Shows a query in Access that has a calculated column with the results of an Excel worksheet function. See how to call a wrapper function from a query, and …

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month