Sort date names order in a matrix report based on SSAS Data Source

 

I created a report based on a simple SSAS cube with a fact table containing a value per day per division. The report has a year parameter and needs to show the sum per month in a pivot table, with 12 months in the columns and a row for each division.

The report is easy to generate with the report wizard, but it results in columns where months are sorted by name: April, August, December, February, …

Problem

The Report designer sorts the columns alphabetically by the name of the month.

Solution

The months are supplied by the SSAS MDX in correct order, it is the report definition that sorts the resulting columns alphabetically.

The solution is quite easy: remove the sort order from the Month Column Group.

in the Group properties of the Month column group, go to the Sorting options and remove the sort on the Month:

So that no sorting is set:

The report shows the months in chronological order:

Add administrators to reports site after installation of Reporting Services

Add administrators to reports site after installation of Reporting Services

Problem

After installation and setup of the reporting services, when you browse to the reports site, you receive this error:

“User 'Domainname\Username' does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.”

You cannot even assign access because the Site settings link on the top right hand side is not available.

Solution

2 steps need to be performed:

• Add a windows user or group to the System Role Assignments and grant System Administrator rights.
• Add a role for this user or group at the root folder of the Report Server and grant

Walkthrough

• Log on locally to Windows Server running the Reporting Services with an account that is member of the local admin group.
• From the start button, run IE as administrator
• 
• In the UAC dialog, click Yes
• 
• In the IE address bar, browse to http://localhost\reports
• This there is no error message and the Site Settings link is available
• 
  Click on the Site Settings link, Click on the Security tab in the left hand pane
• 
• Click New Role Assignment in the toolbar above the list of groups and Users.
• In the New System Role Assignment page, add a Windows Group or username and assign a role, check System Administrator and System User. Click OK.
• 
• The new role is listed under Group or User.
• 
• Browse to the Reporting Services Home page, by clicking the Home link at the top of the page.
• Now you are in the Root folder of the Reports. In the toolbar, click Folder Settings.
• 
• In the Folder settings, there is only one tab, namely ‘Security’. Click on New Role Assignment.
• 
• In the New Role Assignment page, type a group or username, check Content Manager. Click OK to create.
• 
• The new role is assigned and listed in the list of Group or User.
• 

With these 2 actions we granted a windows group or user administrative rights on the report server. From now on you can browse to the reports site on any client PC, without the need to run as administrator.

To add users that can only browse reports

The following steps are needed:

• Grant access to the root folder of the reports site.
• Grant access to the Folder with the reports that the user needs to consult.

Dates in Excel files rendered from reports are displayed as plain numbers

Problem description

A report subscription is setup to send an email message on regular schedule. The report is included in the email as an attachment file rendered in excel format.

When MS-Windows users receive the email, they can open the excel file and dates will be shown in a normal date format:

When iPad users receive the email, they can open the excel file, but the dates are shown as numbers:

 

Workaround

Standard solution for MS-Windows users

In the report design, the date field, e.g. ‘ReportDate’ is displayed as a regular field

The textbox has a custom date format

The report is displayed with this custom format

And the export to excel will show formatted date cells when opened with MS-Windows

However on the iPad the date in column H will be shown as a number.

 

Workaround for iPad users

In the report design do not use a custom format property; instead use an expression with the Format function to represent the date:
=Format(Fields!ReportDate.Value, "dd/MM/yyyy")

The result from the report subscription email attachment looks like this on the iPad:

 

Reminder

With the workaround, the MS-Windows user will see the dates visualized correctly, but the cells are treated as plain text, not as dates. The cells are also left-aligned like any standard text.

Repeat header rows on each page not working

The header rows for a simple tablix do not repeat on each page by default. Since I keep forgetting the solution, I posted the answer that I found on the internet here.

Posted by Stacia on http://stackoverflow.com

“It depends on the tablix structure you are using. In a table, for example, you do not have column groups, so Reporting Services does not recognize which textboxes are the column headers and setting RepeatColumnHeaders property to True doesn't work. Instead, you need to open Advanced Mode in the Groupings pane: click the arrow to the right of the Column Groups and select Advanced Mode. You'll see Static Groups appear in the Row Groups area. Clicking on a Static group highlights the corresponding textbox in the tablix. For the column headers that you want to repeat, select the Static group that highlights the leftmost column header. This is generally the first Static group listed. In the Properties window, set the RepeatOnNewPage property to True. Make sure that the KeepWithGroup property is set to After. The KeepWithGroup property specifies which group to which the static member needs to stick. If set to After then the static member sticks with the group af-ter, or below, it acting as a group header. If set to Before, then the static member sticks with the group before, or above it, acting as a group footer. If set to None, Reporting Services decides where to put the static member. Now when you view the report, the column headers repeat on each page of the tablix.”

Let Reporting Services use the User’s regional settings

US format by default

When designing a report currencies and dates use the US format, e.g. MM/dd/yyyy The user would like to read his report in his local format, e.g. dd/MM/yyyy.

Change the report for the user’s regional settings

In the report designer change the report property Language to Expression: =User!Language

A more elaborate explanation can be found at this link Reporting services tip about user regional settings

Report parameter default value to 1st and last day of previous month

Problem

For a report that selects data in a period with a start and end date, I needed to create a subscription that is sending an email the beginning of each month with the data from the previous month.

The report has 2 parameters @FromDate and @ToDate. The dataset of the report uses a select statement with a where condition: … WHERE Reportdate BETWEEN @FromDate and @ToDate.

The ReportDate is a DATE field (not a Datetime field)

when the user opens the report, he will be shown the 2 parameter fields with a Datetime picker to select a date from a calendar. But with a subscription the parameter fields need to have a default value. In this case the default value must be the 1st day and the last day of the previous month.

Solution with SQL Date field

The FromDate parameter has an expression as default value. The expression calculates the 1st day of the previous month:

=DateSerial(Year(DateAdd(DateInterval.Month, -1, Today())), Month(DateAdd(DateInterval.Month, -1, Today())), 1)

The ToDate paramet has a similar expression as default value. The expression calculates the last day of the previous month (no matter how many days). It does so by calculating the first day of the current month with DateSerial and then it subtracts one day with DateAdd.

=DateAdd(DateInterval.Day, -1, DateSerial(Year(Today()), Month(Today()), 1))

In case of SQL Datetime field

2 possibilities: either use the CAST function in the select statement, and keep the parameters and default expressions as they are for the Date field:

… WHERE CAST(Reportdate AS Date) BETWEEN @FromDate and @ToDate

Or leave the select statement as it is and change the default value of the ToDate parameter, by not subtracting one Day but the smallest necessary timeinterval. Howver the smallest interval in Reporting Services code is ‘Second’. If your datatime fields contain millisceconds you cannot give an absolute guarantee that it will catch all possible values. So only use this if you will never have values between 23:59:59.000 and 23:59:59.999

=DateAdd(DateInterval.Second, -1, DateSerial(Year(Today()), Month(Today()), 1))

Cannot create a Data-driven subscription on a report

For an existing report in SQL 2008 R2 I want to create a Data-driven subscription, but in the report management, under subscriptions there is a warning sign along the button to create a data-driven subscription

• The report credentials are stored to run the report
• It is not a linked report
• There are no user-defined parameter values

But I found a message in the reporting services forums stating that the report might contain expressions depending on user like User!UserID

When I looked in the footer of the report there was indeed a text box with the page and the text “printed by “ User!UserID

After removal of this expression, the option to create a data-driven subscription was available.

Retrieve report definition .rdl from a SQL 2005 report server

Problem

If you are a developer and confronted with a situation where there is no source for a report that is stored on a SQL 2005 report server, then you can download the report definition from the server itself. although it is not obvious.

Description

A report that was developed by someone else is run on a particular Sql 2005 report server. This report has to be modified and migrated to a new server. There is no source available. But luckily you do have the rights to administer the report site. Then there is a way to download this report definition as a file to your development pc where you can then add it as an existing report to your Visual Studio project.

Solution

To retrieve the .rdl file

• Open the SQL 2005 report site with Internet Explorer.
• Browse to the folder with the report.
• in this folder click on the ‘Show details’ button
  
• The reports are now listed line by line with an edit button in front of the name. Click on the ‘Edit’ button of the report that you need.
  
• The properties page for that report is opened. Under ‘Report Definition’ click on the ‘Edit’ link
  
• The ‘File Download’ dialog window opens. Click on the ‘Save’ button.
  
• In the ‘Save As’ dialog window select a location on your pc where you want to save the file. click on ‘Save’

Add the .rdl file to you reporting project

• Open the Business Intelligence project with Visual Studio.
• Right-click on the Reports folder and from the dropdown menu select ‘Add / Existing Item…’
  
• In the open file dialog browse to the location where you downloaded the .rdl file and select it.
• The report definition is now in your project ready for modification.

SSRS reporting with SharePoint integration log file location

Where are the SSRS log files when yu have SSRs integrated with SharePoint?
when integrated with SharePoint the SSRS service is running on the SharePoint App server. the log files will be on that server in :

C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles

Data driven report subscription send one email to all subscribers in comma separated list

Question

With an existing data driven report solution, a scheduled report is sent to several subscribers by email individually. I;e. if there are 4 users that subscribe to the report in the subscription database then 4 emails will be sent by reporting services. The manager would like to have the report sent once with all the subscribers in the TO section in a comma separated list. that way he can see who else received the email.

Current situation

The subscription database basically looks like this

The Subscription table holds the report parameters needed to subscribe to a report

The UserData table holds general information about the Windows users that can be subscribers.

For the many to many relation between subscriptions and users, the relation has been defined in the table ‘SubscriptionUser’ where users can be assigned to one ore more subscriptions.

There is also a lookup table ‘ReportFormat’ that holds the name of various export formats available in SSRS.

The original statement used by the data driven subscription in reporting services was:

SELECT a.SubscriptionName, d.EmailAddress AS [TO], 'true' AS IncludeReport, b.ReportFormatValue AS RenderFormat

    , a.MessageSubject, a.IncludeLinkToReportserver, a.IncludeFileInMessage, ISNULL(a.MessageBody,'') AS MessageBody

FROM dbo.Subscription AS a

INNER JOIN dbo.ReportFormat AS b ON a.ReportFormatID = b.ReportFormatID

INNER JOIN dbo.SubscriptionUser AS c ON a.SubscriptionId = c.SubscriptionId

INNER JOIN dbo.UserData AS d ON c.UserId = d.UserId

WHERE a.SubscriptionId = 3 AND d.IsActive = 1

This statement will yield a number of lines, in this example 4 lines

jack@abc.com    true    Excel    Weekly sales report    0    1    Dear Receiver, <br/><br/>Find enclosed the weekly sales report. <br/><br/>Regards <br/>
tom@abc.com    true    Excel    Weekly sales report    0    1    Dear Receiver, <br/><br/>Find enclosed the weekly sales report. <br/><br/>Regards <br/>
pete@abc.com    true    Excel    Weekly sales report    0    1    Dear Receiver, <br/><br/>Find enclosed the weekly sales report. <br/><br/>Regards <br/>
mike@abc.com    true    Excel    Weekly sales report    0    1    Dear Receiver, <br/><br/>Find enclosed the weekly sales report. <br/><br/>Regards <br/>

Solution

We need to have only one row returned from our data driven select statement. The Email addresses need to be concatenated in one semicolon (not comma!) separated string that will serve as the TO column.

• Select email addresses for the given report
  
  
  
  

  SELECT EmailAddress 

  FROM dbo.UserData AS dd

  INNER JOIN dbo.SubscriptionUser cc ON cc.UserId = dd.UserId

  WHERE cc.SubscriptionId = 3 AND dd.IsActive = 1

jack@abc.com
tom@abc.com

pete@abc.com

mike@abc.com




• This will yield 4 rows again, now these rows need to be concatenated into a single string. For this the T-SQL FOR XML PATH can be used
  
  SELECT  ';' + EmailAddress
  FROM
  (
      SELECT EmailAddress 
      FROM dbo.UserData AS dd
      INNER JOIN dbo.SubscriptionUser cc ON cc.UserId = dd.UserId
      WHERE cc.SubscriptionId = 3 AND dd.IsActive = 1
  ) AS e FOR XML PATH('')
  
  
  ;jack@abc.com;tom@abc.com;pete@abc.com;mike@abc.com
  




• Now the first semicolon needs to be trimmed from this string, this can be achieved with the STUFF function in T-SQL. The complete select statement result needs to b used as argument to the STUFF function.
  SELECT STUFF(
              (
                  SELECT  ';' + EmailAddress
                  FROM
                  (
                      SELECT EmailAddress 
                      FROM dbo.UserData AS dd
                      INNER JOIN dbo.SubscriptionUser cc ON cc.UserId = dd.UserId
                      WHERE cc.SubscriptionId = 3 AND dd.IsActive = 1
                  ) AS e FOR XML PATH('')
              ) ,1,1,'') AS EmailAddress
  
  
  
  jack@abc.com;tom@abc.com;pete@abc.com;mike@abc.com
  




• Finally the result has to be selected together with the other report subscripion parameters for the report with id 3.
  SELECT STUFF(
              (
                  SELECT  ', ' + EmailAddress
                  FROM
                  (
                      SELECT EmailAddress 
                      FROM dbo.UserData AS dd
                      INNER JOIN dbo.SubscriptionUser cc ON cc.UserId = dd.UserId
                      WHERE cc.SubscriptionId = 3 AND dd.IsActive = 1
                  ) AS e FOR XML PATH('')
              ) ,1,1,'') AS [TO], 'true' AS IncludeReport, b.ReportFormatValue AS RenderFormat
      , a.MessageSubject, a.IncludeLinkToReportserver, a.IncludeFileInMessage, ISNULL(a.MessageBody,'') AS MessageBody
  FROM dbo.Subscription AS a
  INNER JOIN dbo.ReportFormat AS b ON a.ReportFormatID = b.ReportFormatID
  WHERE a.SubscriptionId = 3
  
  



• 
  

  The result is one line and a semicolon separated [TO] column

  jack@abc.com;tom@abc.com;pete@abc.com;mike@abc.com    true    Excel    Weekly FER report    0    1    Dear Receiver, <br/><br/>Find enclosed the weekly FER report. <br/><br/>Regards <br/>

Incorporating field data from a related table from a 2nd database into a Reporting Services report

Problem

How does one build a report based on data from one database, and incorporate lookup data from a related table from another database?

The 1st database holds facts data, the other database has a dimension table. The report is grouped by a category code that is used in the facts table, but the descriptive name of the categories (dimension) is stored in another database. The 2nd database does not necessarily reside on the same SQL server as the 1st database. Both databases and reporting services are SQL 2008 R2 versions.

Solution

There is more than one solution to this problem. 

When both databases reside on the same server and will continue to do so, then you can design the report with a single Data source and single Dataset, the query specifies the database names.

If the 2^nd database resides on another server, you could add a linked server to the 1^st server and still use the same solution as mentioned above.

If the 2^nd database resides on another server and you do not have the rights to add a linked server on the 1^st server, although you would expect other developers have experienced the same problem and also felt the need for a linked server.

When an IIS server is available web services can be created to load data from both databases.

With no IIS server available and no admin rights on the SQL servers I opted for the solution with 2 datasets and 2 data sources. The report will use a lookup expression in one of the tablix cells to read the data from the related dimension table.

Walkthrough

• Create a report server project with VS 2008.
• Create 2 Shared Data sources
• 1^st datasource connects to facts tables and is called ‘dsrc_fact’
• 2^nd datasource connects to dim tables and is called ‘dsrc_dim’

 

• Add a new report
• Use the shared datasource ‘dsrc_fact’
• Create a query that selects the necessary fields from the fact table.
• SELECT DivisionId, CustomerNr, Surname, Firstname, Street, Pcode, City, Country FROM dbo.Customer
• DivisionId is the key to the Dimension table in the second database.
• Create a Tabular report, grouped by the DivisionId.
• Call the report ‘CustomerByDivision’

• In the Report Data we see one Data source and one Dataset.

• Because I used the report wizard, my dataset is called DataSet1, rename it to ‘dset_Customer’.
• In the Report Data pane, add a 2^nd Data source
• Use the shared data source ‘dsrc_Dim’ and give it the same name again.
• In the Report Data pane, add a 2^nd dataset
• Call the dataset: ‘dset_Division’
• Use a dataset embedded in the report
• Data source: dsrc_Dim
• Query type: text
• Query:SELECT DivisionId, DivisionName FROM  dbo.Division
• Now there are 2 data sources and 2 datasets in the Report Data pane.

•  In the report group header we can add an expression to lookup the corresponding Division name in the 2^nd datasource.
• Right-click on the group header cell next to the DivisionId cell, select ‘Expression…’ from the dropdown menu.

• In the expression dialog box, type the following expression
• =Lookup(Fields!DivisionId.Value, Fields!Id.Value, Fields!Name.Value, "dset_Division")
• Reminder: the lookup function can be found in the Category ‘Common Functions/Miscellaneous’, Item ‘Lookup’.  The first lookup argument refers to the key field from the 1^st dataset, the 2^nd lookup argument refers to the corresponding key field from the 2^nd dataset, the 3^rd argument is the field value from the 2^nd dataset that needs to be returned by the Lookup function, and the last argument is the name of the dataset where Lookup needs to look.
• In the preview of the report the result is shown. Alongside each Division Id we see the corresponding division name.

Remark

With this easy solution, sorting can only be done on the Division Id , not on the Division name.

Reporting services where to find the property to repeat a group header on every page

Since it was some time ago that I had to create a report with a group header that must be repeated on top of every page, I had to dig up an article that explains how to accomplish it. Compared to other reporting tools it is not that obvious in Reporting services. The clue is to open the advanced mode in the 'row groups' and 'column groups' panes.
The solution comes from an an article on MSDN:

To repeat rows with column headings for a table with row groups

1. In Design view, select the table. The Grouping pane displays the row groups.
2. On right side of the Grouping pane, click the down arrow, and then clickAdvanced. The Grouping pane displays static and dynamic tablix members for each group. You can only set properties on a static tablix member.
3. In the Row Groups pane, click the static tablix member for the row that you want to repeat. When you select a static tablix member, the corresponding cell on the design surface is selected, if there is one. The Properties pane displays the properties for the selected tablix member.
4. Set the KeepWithGroup property in the following way:
   
   • For a static row that is above a group, click After.
   • For a static row that is below a group, click Before.
5. Set the RepeatOnNewPage property to True.
6. Preview the report. If possible, the row repeats with the group on each vertical page that the row group spans.

View a SQL Server Reporting Services Report in LightSwitch

Sql server Reporting Services report

This article on MSDN gives a rudimentary but working solution on how to view a SQL Server Reporting Services Report in LightSwitch. The solution works for both Lightswitch apps: web app or desktop app.

Update

Yann Duran wrote an interesting article: Add a Web Page URL to LightSwitch's Navigation Menu

Use SSIS package data as source for Report in Reporting Services

It is possible to use the destination output of a Package in SSIS as the input source of a report in Reporting Services. In SSIS the package can send output to a datareader destination object, this in memory output can then be captured by Reporting Services.

Configuring Reporting Services to Use SSIS Package Data

There is an article on MSDN that describes the configuration and also warns about possible security risks.

Creating Report Datasets from SQL Server Integration Services

The follow-up article on MSDN explains how to create a package and a report

Report Server cannot load the TERADATA / SQLPDW extension

Problem

In the Windows Application Log of a Windows 2008 server that hosts SQL server 2008 R2 reporting service, the following Error messages appear daily:

“Report Server (MSSQLSERVER) cannot load the SQLPDW extension.”

“Report Server (MSSQLSERVER) cannot load the TERADATA extension.”

Cause

I’ve found the explanation in the SSRS forums on MSDN (link can be found here)

“This error occurs because the Teradata extension is registered in the Reporting Services configuration file by default, but the Teradata assemblies are not shipped with SQL Server 2008 or as part of the .NET Framework. If the error message does not bother you, you can ignore the error when it is logged.

However, if you want to avoid this error, do one of the following:

• Open the Reporting Services configuration file located in folder C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer, remove or comment out the Teradata extension. Do this only if you do not require functionality that the Teradata extension provides.

• Install the .NET Data Provider for Teradata. Do this only if you require functionality that the Teradata extension provides. You can obtain the provider from the Teradata Web site. Reporting Services requires that the provider be version 12 or later.”

Solution

With SQL server configuration Manager or with Reporting Services Configuration manager, stop the reporting service.

Edit the file As suggested in the above article, edit the file rsreportserver.config in <C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer>

Search for SQLPDW, comment out 2 entries:

<!--Extension Name="SQLPDW" Type="Microsoft.ReportingServices.DataExtensions.SqlDwConnectionWrapper,Microsoft.ReportingServices.DataExtensions"/—>

<!--Extension Name="SQLPDW" Type="Microsoft.ReportingServices.SemanticQueryEngine.Sql.MSSQLADW.MSSqlAdwSQCommand,Microsoft.ReportingServices.SemanticQueryEngine">
    <Configuration>
        <EnableMathOpCasting>False</EnableMathOpCasting>
    </Configuration>
</Extension-->

 

Search for TERADATA, comment out 3 entries:

<!--Extension Name="TERADATA" Type="Microsoft.ReportingServices.DataExtensions.TeradataConnectionWrapper,Microsoft.ReportingServices.DataExtensions"/—>

<!--Extension Name="TERADATA" Type="Microsoft.ReportingServices.SemanticQueryEngine.Sql.Teradata.TdSqlSQCommand,Microsoft.ReportingServices.SemanticQueryEngine">
    <Configuration>
        <EnableMathOpCasting>True</EnableMathOpCasting>
        <ReplaceFunctionName>oREPLACE</ReplaceFunctionName>
    </Configuration>
</Extension—>

<!--Extension Name="TERADATA" Type="Microsoft.ReportingServices.SemanticQueryEngine.Sql.Teradata.TdSqlModelGenerator,Microsoft.ReportingServices.SemanticQueryEngine"/-->

Start the reporting service.

No cubes can be found

Using VS2008 to create a new dataset in a reporting services project, based on a SSAS cube. The query designer responds with the error message: ‘No cubes can be found’

From this this article on Microsoft connect

“After connecting to SSAS in SQL Server Management Studio and attempting to browse a cube, the cube browser doesn't show any cubes. Attempting to open a dataset that uses an MDX query in Reporting Services results in the following error:
No cubes can be found“

“This was caused by the cube Visible property being set to False but what flips this property from True to False is unknown.“

Although the Visible property was set to true in Visual Studio 2008, I have had to explicitly reset it to True and then redeploy the cube. After that I could create a shared dataset based on this cube.

Linked dataset in report does not show new columns added in database

Using Visual Studio 2008 to develop the report, SQL server 2008 R2.to host the database and the reporting services.

Problem

The problem occurred in a report that uses a link to a shared dataset, which is based based on a stored procedure in SQL server. The stored procedure uses input parameters to select data from a view and returns the dataset. The table on which the view is based has been altered with some new columns. The view was altered to select those columns. The stored procedure used a ‘SELECT * FROM view’ and showed the new columns after testing in SQL server management studio. The shared dataset was updated with the Refresh Fields button to show the new fields. The fields did become available in the fields list of the shared dataset. But with the report in the designer of VS2008, and with after refreshing the fields of the linked dataset, no new fields were available.

Report –> linked dataset –> shared dataset –> stored procedure –> view –> table.

Solution

The select statement in the stored procedure was modified to select each column from the view by name.

Instead of using

SELECT * FROM view

The statement was altered to

SELECT field1, field2, field3, … FROM view

After saving the changes in the stored procedure, the refresh fields in the linked dataset design gave all new fields.

The report server cannot decrypt the symmetric key

On a newly installed sql server standard 2008 R2 with reporting services, I received the following error message, when I opened the http:\\<myserver>\Reports site

The report server cannot decrypt the symmetric key that is used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. (rsReportServerDisabled)

In the Microsoft connect site I found this problem described.

The answer by VenkatKokulla fixed my problem:

“Do you happen to have a backup of your encryption key? If not, follow these steps:
Open Reporting Services Configuration Manager
Connect to your RS server
Select 'Encryption Keys' from the left pane
Press the 'Delete' button to get rid of your encrypted content
After doing this, you will need to re-set the connection strings and stored credentials on your reports.
Also, I'd be sure to make a backup of the encryption key afterwards.
If you do have a backup, press the 'Restore' button instead of the 'Delete' button.”

How to copy SSRS 2005 reports from one folder to another

If you ever need to copy reports from one folder to another folder on SSRS 2005 server.

• on the same server
• it is copy not move
• you do not have the BIDS source project
• The report copies need to point to a different data source

This tedious task can be done as follows:

• With the web browser in the reporting services home folder, create the new folder
• to copy a report from the source folder to the destination folder
• with the web browser go to the source folder
• select ‘Show details’
• click on the Edit icon next to the report name
• in the edit page, click on the edit link, this will download a copy of the report, with rdl extension.
• Store the file on your pc.
• browse to the target folder you have created in the 1st step
• click on ‘upload file’
• Select the .rdl report file you have just downloaded.
• Upload the file
• to change the datasource of the reports in the new folder
• If the reports were using shared datasource then create a new Data source in the Data Sources folder.
• browse to the folder with the new report
• select ‘Show details’
• click on the Edit icon
• in the menu on the left click on ‘Data Sources’
• 
• Select a shared datasource, click on the Browse button.
• In the Location Home/Data Sources select the new datasource.
• now you should be able to open the report