Problem
I am new to SQL Server Reporting Services Report Manager. I need information on customizations. I have also heard about the My Reports Feature. Can you please explain that as well? Check out this tip to learn a few SQL Server Reporting Services (SSRS) Tips and Tricks on Report Manager.
Solution
In this tip, we will take a look at the following Tips and Tricks:
- Customizing Report Manager Header/Title
- Uploading Custom File Types to Report Manager
- Enable My Reports Feature
Note: This tip assumes that you are familiar with basic functioning of Report Manager. If not, check out these resources – https://www.mssqltips.com/tutorial/report-manager/ and Reporting Services Tutorial.
Customizing Report Manager Header/Title
It is a good and well known practice to test the reports in all the different environments/configurations (SQL Server Edition/Service Pack, Operating Systems, Browsers etc.) to ensure that all the end user environment configurations are covered and the reports work fine on those. While testing the reports on different configurations, especially when there are a bunch of environments, it is a good idea to label Report Managers on each of the environments differently to avoid confusion. We can use the Report Manager Title property to label and identify different environments. By default the Report Manager Title is set to “SQL Server Reporting Services” as shown below.
Go to “Report Manager” >> “Site Settings” >> “General”. Set the “Name” property under “Properties” section. For the purpose of this demonstration, set the “Name” property to “SQL Server 2012 Developer (SP1) – 11.0.3000.0 (X64) – Dev Environment” and click on Apply.
Report Manager will look as shown below.
This tells us that, this Report Manager corresponds to 64-bit SQL Server 2012 Developer Edition with Service Pack 1 and is a Development Environment.
Uploading Custom File Types to Report Manager
Usually Report Manager is used to host various different Reporting Services artifacts like Reports, Data Sources, Report Parts, etc. However, Report Manager also allows hosting other types of files like Word, Excel, images, etc. This can be a very useful feature, especially in scenarios where reports are directly accessed from Report Manager and there is no front end portal for hosting reports, to host the project related artifacts like Help Documents, How-To Guides, Data Models, etc. For the purpose of this demonstration, let’s upload few sample files.
Click on Upload File and select the file to be uploaded in the Upload File section as shown below.
Below screenshot shows three different sample files have been uploaded to Report Manager – Word Document, Excel Workbook, and an Image.
Few files can be directly opened within the browser window like an image and other file types like Word, Excel, etc. need to be download to local file system to view them. The following diagram shows an image file opened directly in the browser window.
Enable My Reports Feature
The My Reports feature allows users to save reports to a private folder. This feature allocates/reserves space in the ReportServer database for each user. This feature is disabled by default.
Follow these steps to enable this feature:
- Connect to the Reporting Services instance in SQL Server Management Studio (SSMS)
- Right-click on the server and select “Properties” from the context menu, which will open the Server Properties window. Select the “Enable a My Reports folder for each user.” checkbox.
- Once this feature is enabled, every user gets to see a “My Reports” folder on the Report Manager. They can create/manage their reports in there.
- The Administrators get to see two new folders on the Report Manager – “My Reports” (My Reports folder for the Administrator) and “Users Folders” (this contains sub-folders, each of which is a “My Reports” folder for the individual users).
- We have chosen the “My Reports” role under the security settings for the My Reports folder in step 1. If you want to view or modify the specific settings of this role to include/exclude the tasks that the users can perform, you can do so. Connect to Reporting Services instance in SSMS, go to “Security” >> “Roles” >> “My Reports”. Right click on “My Reports” role to view/modify the properties of this role.
Note that you can assign a role of your choice to the My Reports folder, which is set to “My Reports” role by default. You can choose one of the built-in roles or you can create a custom role and assign it.
Here are few highlights of My Reports feature:
- This feature can be enabled/disabled either for all users or none of the users. It does not allow enabling/disabling for specific users/groups.
- Any changes to the security settings applies to all the users and security settings cannot be set differently for different users/groups.
- When this feature is disabled, users will no longer see the My Reports folder for their login. However, the folders stored on the Report Server need to be manually removed/deleted.
- While this feature is active, “My Reports” becomes a reserved name.
- This feature can be really useful in scenarios like the reports authored by various users need to be reviewed.
- When the number of users increases, it might be difficult to maintain them.
All the tips and tricks demonstrated above have been verified on SQL Server 2012. However, these tips and tricks work on SQL Server 2008 and above.
Next Steps
- Try the tips and tricks demonstrated above and explore more possibilities.
- Check out the following tips:
- Check out my previous tips
