Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamically name report/file export in SSRS


Dynamically name report/file export in SSRS

Author
Message
Miles Neale
Miles Neale
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2760 Visits: 1694
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.:-)

Not all gray hairs are Dinosaurs!
Jason Selburg
Jason Selburg
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2981 Visits: 4106
Miles Neale (3/31/2010)
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.:-)


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.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
slmcweidman
slmcweidman
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 17
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".
gcbohmann
gcbohmann
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 68
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.
Stefan K
Stefan K
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 44
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.


Kindest Regards,

SK
dronee_456
dronee_456
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 524
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
klauskowarsch
klauskowarsch
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 24
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.
klauskowarsch
klauskowarsch
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 24
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
hkflight
hkflight
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 166
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?
lalita.ramani
lalita.ramani
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 26
where do we set the name of the file in Subscription?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search