Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Dynamically name report/file export in SSRS Expand / Collapse
Author
Message
Posted Wednesday, March 31, 2010 12:07 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:38 AM
Points: 2,262, Visits: 1,309
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!
Post #894040
Posted Wednesday, March 31, 2010 1:28 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 7:41 PM
Points: 2,694, Visits: 3,750
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
Post #894154
Posted Wednesday, July 7, 2010 11:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 30, 2012 10:51 AM
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".
Post #948722
Posted Wednesday, October 6, 2010 9:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 8, 2012 9:54 PM
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.
Post #999546
Posted Wednesday, July 6, 2011 7:58 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 6, 2011 7:52 AM
Points: 80, 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
Post #1137301
Posted Thursday, September 1, 2011 5:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:05 PM
Points: 22, Visits: 387
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
Post #1168983
Posted Saturday, December 31, 2011 8:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 12:41 PM
Points: 4, Visits: 23
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.
Post #1228713
Posted Tuesday, January 10, 2012 12:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 17, 2013 12:41 PM
Points: 4, Visits: 23
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
Post #1233448
Posted Friday, February 3, 2012 11:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 18, 2013 4:57 PM
Points: 65, 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?
Post #1246659
Posted Thursday, October 25, 2012 8:36 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 29, 2012 7:51 AM
Points: 82, Visits: 22
where do we set the name of the file in Subscription?
Post #1377038
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse