SQLServerCentral / Discuss Content Posted by Jason Selburg / Article Discussions / Article Discussions by Author / Dynamically name report/file export in SSRS / Latest Posts

RE: Dynamically name report/file export in SSRS

sjplater — Fri, 01 Mar 2013 15:19:41 GMT

Using SSRS 2005.I've implemented everything in this article, the report name doesn't change on the attachment.I've checked everything, the path of the previous file name is accurate.

RE: Dynamically name report/file export in SSRS

lalita.ramani — Thu, 25 Oct 2012 08:36:24 GMT

where do we set the name of the file in Subscription?

RE: Dynamically name report/file export in SSRS

hkflight — Fri, 03 Feb 2012 11:00:25 GMT

Hey Josh,This works for email subscriptions, but for some reason, does not work for Windows File Share subscriptions. I am using SQL Server 2008 r2 Standard. Perhaps this is an enterprise feature? Does anyone know if this feature will be included in SQL 2012?

RE: Dynamically name report/file export in SSRS

klauskowarsch — Tue, 10 Jan 2012 12:07:57 GMT

I'm going to give the missing info on SSRS running in SharePoint integrated mode. Would love to see comments by anyone else who is able to do this!SOLUTION:If you are running a SharePoint integrated instance of SSRS then there are two other places that must be updated in order for you to successfully kick off the subscription via SQL. Remember that in an integrated mode the report is also a document in SharePoint.Both of these additional fields are in the WSS_Content_Reports database. The first is the LeafName field in the dbo.AllDocs table. The second is the tp_LeafName field in the dbo.AllUserData table. My solution was to stored all current values of necessary IDs and the report name and path in a table before updating the SSRS and SharePoint tables. Then update the fields in the SSRS and SharePoint tables. Make sure that you put it in a Begin Transaction Commit Transaction Block or the subscription may fire before the changes are committed, this actually happened in one of my tests. Kick off the subscription and once it is done. Change the values back to their original values.I managed to use my relationships in IT to get the necessary access to update the fields in the WSS_Content_Reports database long enough to build the solution and test that it works but now my roadblock is the internal overblown concerns of a few administrators. It is being portrayed as having the potential to cause Global Failure to SharePoint when that is simply not the case. The dangers are solely isolated to breaking the relationship of the SSRS report to the corresponding SharePoint Document as well as the corresponding permissions.If you change only the path field in the ReportServer.dbo.Catalog table. The error msg on the subscription will say that the item was not found. If you change the LeafName field in the WSS_Content_Reports.dbo.AllDocs table in addition to the path field in the ReportServer.dbo.Catalog table but WITHOUT also changing the tp_LeafName in the WSS_Content_Reports.dbo.AllUserData table you will get a subscription error saying that the user credentials you used to kick off the subscription in SQL don't have the necessary permissions. If you change all three before kicking off the subscription it will work.Here is the Create Table statement I used to create a history table to store the before and after values:USE [LAF_Reporting]GoIf (Select OBJECT_ID('dbo.SQLInitiatedSubscriptionHistory')) Is Not Null Drop Table LAF_Reporting.dbo.SQLInitiatedSubscriptionHistoryCREATE TABLE LAF_Reporting.dbo.SQLInitiatedSubscriptionHistory( [nDex] [int] IDENTITY(1,1) NOT NULL, [ReportID] [uniqueidentifier] NOT NULL, [Catalog_Path] [nvarchar](425) NULL, [Catalog_PathTemp] [nvarchar](425) NULL, [Catalog_Name] [nvarchar](425) NULL, [Catalog_NameTemp] [nvarchar](425) NULL, [SubscriptionID] [uniqueidentifier] NULL, [ScheduleID] [uniqueidentifier] NULL, [Subscriptions_Description] [nvarchar](512) COLLATE Latin1_General_CI_AS_KS_WS NULL, [Subscriptions_Parameters] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL, [Subscriptions_ParametersTemp] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL, [Subscriptions_ExtensionSettings] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL, [Subscriptions_ExtensionSettingsTemp] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL, [Subscriptions_DataSettings] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL, [Subscriptions_DataSettingsTemp] [ntext] COLLATE Latin1_General_CI_AS_KS_WS NULL, [ExecutedDate] [datetime] NULL, [ExecutionStatus] [nvarchar] (260) NULL, [Completed] [datetime] NULL, [ExecutionTime] AS (datediff(second,[ExecutedDate],[Completed])),CONSTRAINT [PK_SQLInitiatedSubscriptionHistory] PRIMARY KEY CLUSTERED ( [nDex] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]Here are my updated statements: Update c Set Path = sh.Catalog_Path, Name = sh.Catalog_Name From ReportServer.dbo.Catalog c Join LAF_Reporting.dbo.SQLInitiatedSubscriptionHistory sh On c.ItemID = sh.ReportID WHERE sh.ReportID = @ReportID And sh.SubscriptionID = @subscriptionID And sh.ScheduleID = @ScheduleID And sh.ExecutedDate = @ExecutedDatetime Update ad Set LeafName = sh.Catalog_Name From WSS_Content_Reports.dbo.AllDocs ad (NoLock) Join LAF_Reporting.dbo.SQLInitiatedSubscriptionHistory sh On ad.ID = sh.ReportID WHERE sh.ReportID = @ReportID And sh.SubscriptionID = @subscriptionID And sh.ScheduleID = @ScheduleID And sh.ExecutedDate = @ExecutedDatetime Update aud Set tp_LeafName = sh.Catalog_Name --Select aud.tp_LeafName, ad.LeafName, ad.* From WSS_Content_Reports.dbo.AllUserData aud (NoLock) Join WSS_Content_Reports.dbo.AllDocs ad (NoLock) On aud.tp_ID = ad.DoclibRowId And aud.tp_ListId = ad.ListId And aud.tp_SiteId = ad.SiteId Join LAF_Reporting.dbo.SQLInitiatedSubscriptionHistory sh On ad.ID = sh.ReportID WHERE sh.ReportID = @ReportID And sh.SubscriptionID = @subscriptionID And sh.ScheduleID = @ScheduleID And sh.ExecutedDate = @ExecutedDatetime

RE: Dynamically name report/file export in SSRS

klauskowarsch — Sat, 31 Dec 2011 20:46:06 GMT

Has anyone tried this in SSRS 2008 in SharePoint integrated mode? I've been trying to do this for several days and even though I can get the subscription to be kicked off from SQL and modify the ExtensionSettings and Parameters as soon as I update the path in the Catalog it no longer works. But it also doesn't generate any errors. As soon as I set the path back, I can get the subscription to be be kicked off from SQL again.

RE: Dynamically name report/file export in SSRS

dronee_456 — Thu, 01 Sep 2011 17:47:02 GMT

Finally got it to work, meaning the sp would send a report. However, I tried to change the name of the file by adding the date to the end of the file. No luck. Has anyone successfully changed the name of the file sent out? ...If not, does anyone have a method of changing the attachment name sent out? Cheers, Eric

RE: Dynamically name report/file export in SSRS

Stefan K — Wed, 06 Jul 2011 07:58:01 GMT

Hi, I have this problem, too. Changing the content in Reportserver Database does not seem to be a feasible solution to me. I do not want to think of what could happen if the subscription execution fails for any reason. If I understood it correctly, this solution will also only work if you want to set the reportname only once per execution of the subscription (and then back to origin). If you want to set it for every entry in the data driven subscription source query you will have to change the data during the execution of the subscription, which would be only possible using a stored proc as dummy datasource which does this any time the report is executed...@josh - There is no filename attribute which you could define in an email subscription.

RE: Dynamically name report/file export in SSRS

gcbohmann — Wed, 06 Oct 2010 09:54:01 GMT

Has anyone else had success with this method? I always receive an Item not found error when the subscription runs if the path is altered in anyway on SSRS 2008.

RE: Dynamically name report/file export in SSRS

slmcweidman — Wed, 07 Jul 2010 11:18:47 GMT

How exactly? When I change the filename in the subscription to Scorecard@Date, or even @ReportName@Date (@Date is a parameter) I get that name as the report name "@ReportName@Date" or "Scorecard@Date".

RE: Dynamically name report/file export in SSRS

Jason Selburg — Wed, 31 Mar 2010 13:28:32 GMT

[quote][b]Miles Neale (3/31/2010)[/b][hr]Hi Jason,First of all what you have done is interesting and will work and very creative. Job well done.There is however an alternative available that you might want to explore if you are interested in delivery extensions and know something about c#. You can write a custom delivery extension that manages the name anyway you please, and will also do whatever else you want. I have written a collection of extensions and once you understand the simple approach to code and figure out the configuration for the extensions in the report manager and report server it is a very useful approach to getting out of SSRS what you want. Also if you are working in code you can attach to the SSRS web services and render the report of your choice and send or put it where you want. Again not a difficult process and there are examples in various places. I have found this ability interesting when you want to send a report out to someone and keep a copy in a repository.Again what you are doing is great. There are other options available if you want to go there.:-)[/quote]Thanks Miles I appreciate the comment, and I'm sure others will get some info on the C# method from it. As always though, my intent is to only show "another" way of getting a task completed. I try not to presume that my method is best!... and seriously, I appreciate the comment.

RE: Dynamically name report/file export in SSRS

Miles Neale — Wed, 31 Mar 2010 12:07:18 GMT

Hi Jason,First of all what you have done is interesting and will work and very creative. Job well done.There is however an alternative available that you might want to explore if you are interested in delivery extensions and know something about c#. You can write a custom delivery extension that manages the name anyway you please, and will also do whatever else you want. I have written a collection of extensions and once you understand the simple approach to code and figure out the configuration for the extensions in the report manager and report server it is a very useful approach to getting out of SSRS what you want. Also if you are working in code you can attach to the SSRS web services and render the report of your choice and send or put it where you want. Again not a difficult process and there are examples in various places. I have found this ability interesting when you want to send a report out to someone and keep a copy in a repository.Again what you are doing is great. There are other options available if you want to go there.:-)

RE: Dynamically name report/file export in SSRS

josh.fraser — Tue, 23 Mar 2010 09:40:15 GMT

Jason, Did you know that in the subscription you could just set the filename to@ReportName@ParameterName and it will save the report ?

RE: Dynamically name report/file export in SSRS

Jason Selburg — Tue, 23 Mar 2010 06:31:37 GMT

[quote][b]SQL-Student (3/22/2010)[/b][hr]What about customer support?From what I know, Microsoft will not support you, if you make modifications to the SSRS Catalog manually.[/quote]*grin* Don't tell them

RE: Dynamically name report/file export in SSRS

SQL-Student — Mon, 22 Mar 2010 14:55:30 GMT

What about customer support?From what I know, Microsoft will not support you, if you make modifications to the SSRS Catalog manually.

RE: Dynamically name report/file export in SSRS

SQLRNNR — Mon, 22 Mar 2010 12:03:27 GMT

Nice article.

RE: Dynamically name report/file export in SSRS

Jason Selburg — Mon, 22 Mar 2010 08:33:21 GMT

[quote][b]Gaurav.Vinodkumar (3/22/2010)[/b][hr]nice article...but one question..if we have to do the same thing at run time (means when user export the rport to pdf or excel) from the report viewer..is there any way to do this?[/quote]Not that I know of.. :-(

RE: Dynamically name report/file export in SSRS

l543123 — Mon, 22 Mar 2010 08:03:03 GMT

nice tip.. I have played around updating the subscriptions table also.. because we needed to email the same report to 70 users, by just changing one user parameter on the report. I dint want to crate 70 subscriptions, so created 1 email subscriotion which created a record in subscriptions table and also a agent job. 1) created a table to store the 70 userid & email2) Then wrote a SP to loop and update the parameters & extensionsettings fields in the subscription table 3) Kick off the subscription using below statement after each updateReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='asdasd-12sad-dsfas-dzfad-dfasdfsdfsd'4) Wait a minute using "Waitfor Delay"5) Updated the Agent job, commented the "ReportServer.dbo.AddEvent" in the job step and called the SP in that step.So now the subscription throws an error when you try to edit from Report Manager, which is good because if somebody edits it, SSRS will update existing job with the addevent step. This works fine because if later I need to change email or add new users, I just need to add a record to the table.Do you know of any other better way to do this??

RE: Dynamically name report/file export in SSRS

Gaurav.Vinodkumar — Mon, 22 Mar 2010 07:55:05 GMT

nice article...but one question..if we have to do the same thing at run time (means when user export the rport to pdf or excel) from the report viewer..is there any way to do this?

RE: Dynamically name report/file export in SSRS

jezman — Mon, 22 Mar 2010 03:25:08 GMT

I haven't done it myself, but I guess you could also dynamically configure the name if executing reports with [url=http://msdn.microsoft.com/en-us/library/ms162839.aspx]rs.exe[/url]

RE: Dynamically name report/file export in SSRS

meiscooldude — Mon, 22 Mar 2010 00:11:10 GMT

I've been trying to figure out how to do this at work for ages!Thanks a ton for the tip!

Dynamically name report/file export in SSRS

Jason Selburg — Sun, 21 Mar 2010 23:31:08 GMT

Comments posted to this topic are about the item [B]Dynamically name report/file export in SSRS[/B]