Dynamically name report/file export in SSRS

  • 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

  • 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.

  • 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]

    Go

    If (Select OBJECT_ID('dbo.SQLInitiatedSubscriptionHistory')) Is Not Null

    Drop Table LAF_Reporting.dbo.SQLInitiatedSubscriptionHistory

    CREATE 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

  • 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?

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

  • 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.

  • I tried the suggestions but the email never came.

    If we change the Path in Catalog table to non existent user friendly report name path, are we not breaking the path for successful execution of subscription ?

    Sorry if I am missing something here

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply