Dynamically create filename in SSRS 2008

I have been asked a couple times to change the file name dynamically in SSRS 2008. Example: ReportName_201101.RDL. The 2011开发者_开发百科01 represents the execution date. Can this be accomplished in SSRS 2008?

If you mean the filename when you export the report and also happen to be pulling it from the ASP.NET ReportViewer, you can set the name through the DisplayName property.

ReportViewerControl.ServerReport.DisplayName = "ReportName_201101";

or (if ProcessingMode is Local):

ReportViewerControl.LocalReport.DisplayName = "ReportName_201101";

For pretty much all other cases, Alison is right.

There is an MS Connect item open for this. It only has a few votes, so head over there and upvote it...

Another work around, is to rename the report before it runs automatically. This is a diamond in the rough. This may only work for reports that are subscriptions and not ones that users link back to. Create a table on the ReportServer database that contains a listing of all reports that you want to rename before they execute. Table Report_Rename_Listing RenameID int ItemID uniqueidentifier OriginalReportName nvarchar(350) DateType nvarchar(75) Format int DatePlusMinus real Create a stored procedure on the same server that goes out and changes all the reports in the above table.

Create Procedure [dbo].[RenameReports]
AS
SET NOCOUNT OFF ; 
  Update dbo.Catalog 
    Set Name = ISNULL(( Select OriginalReportName + '_' + 
               dbo.func_SetupRenameOfReports(DateType, Format, DatePlusMinus)
      From dbo.DDC_Report_Rename r
      Where r.ItemID = c.ItemID), Name)  
  From dbo.Catalog c
return (0)

Create a scalar function on same server that figures out just how you want to rename the report.

Create Function [dbo].[func_SetupRenameOfReports]
( @DateType nvarchar(75), @Format int, @PlusMinus real  )
RETURNS nvarchar(75)
AS
BEGIN
  Declare @FirstMonth datetime, @LastMonth datetime
  Declare @OutputFormat nvarchar(75)

  Set @FirstMonth = CONVERT(datetime, Convert(varchar(2), DateAdd(mm, @PlusMinus, GetDate()), 103) + '/1/' + CONVERT(varchar(4), DateAdd(mm, @PlusMinus, GetDate()), 102))
  Set @LastMonth = DATEADD(dd, -1, DateAdd(mm, 1, @FirstMonth))

  Set @OutputFormat = 
    Case When @DateType = 'CurrentDate' Then Convert(varchar(75), DateAdd(dd, @PlusMinus, GetDate()), @Format)
         When @DateType = 'CurrentDayName' Then CONVERT(varchar(75), DateName(dw, DateAdd(dd, @PlusMinus, GetDate())))
         When @DateType = 'CurrentMonthName' Then CONVERT(varchar(75), DateName(mm, DateAdd(mm, @PlusMinus, GetDate())))
         When @DateType = 'CurrentYear' Then CONVERT(varchar(75), DateAdd(yy, @PlusMinus, GetDate()))
         When @DateType = 'CurrentBeginEndMonth' Then CONVERT(varchar(10), @FirstMonth, @Format) + '-' + CONVERT(varchar(10), @LastMonth, @Format)
    End

  If @OutputFormat IS null 
    Begin 
      Set @OutputFormat = ''
    End
 Return @OutputFormat

END

Then setup this up to have the stored procedure automatically run daily on your server. I have it run just after midnight every day.

Unfortunately, no, it is not possible. It is another one of those features that is missing from SSRS that developers have been asking for.

Here is a correction to the above stored procedure so that it actual works.

ALTER PROCEDURE [dbo].[ddc_RenameReports]

AS
SET NOCOUNT OFF ; 

  Update dbo.Catalog 
    Set Name = ISNULL((Select OriginalReportName + '_' + 
               dbo.func_SetupRenameOfReports(DateType, Format, DatePlusMinus)
      From dbo.DDC_Report_Rename r
      Where r.ItemID = c.ItemID And r.Active = 1), Name) 
  From dbo.Catalog c
  Update c
    Set c.Path = ISNULL((Select c2.Path + '/' + OriginalReportName + '_' + 
               dbo.func_SetupRenameOfReports(DateType, Format, DatePlusMinus)
          From dbo.DDC_Report_Rename r2
          Where r2.ItemID = c.ItemID AND r2.Active = 1), c.Path)       
  From dbo.Catalog c
    inner join dbo.Catalog c2 on c2.ItemID = c.ParentID 
return (0)