By: Rob Fisch | Comments (2) | Related: > Reporting Services Dynamic Reports
Problem
Using the Reporting Services report designer, there are several situations when it would advantageous to be able to hide reports from the user, while still allowing access to them. Several situations come to mind. One is where you want to provide a drillthrough report. Another is when you use a sub-report. (See this tip.) You will also want to hide objects like datasources and supporting graphics in the project.
If you have a hard coded front end to your reports (i.e. static web page) then you are all set. You don't have to do a thing except provide direct links to reports.
...NOT SO FAST!
This turns out to be much more work than bargained for. It means every time you add a new report or change it's name, you have to update a hard coded web page somewhere. Maybe even several pages if you are providing links from different pages.
Solution
Hiding selected reports and other objects can be done at the server level by maintaining some quick configurations. This can be done in either Reporting Services mode (Sharepoint Integrated or Native). The techniques are conceptually different. (For an illustrated overview of the different Reporting Services modes, see the Part 1 of this article series.)
Before I begin, I would like to point out that the techniques described are at the presentation level, not the security level. "Hiding" reports doesn't mean that knowledgeable and curious users can never find them. It just means you are putting order to what would otherwise be chaotic for the intended audience.
Reporting Services gives the report developer a choice about where to store datasources. They can be put in the same folder as the reports. Or they can stored in another folder. There are pros and cons to either approach. A discussion about that is outside the scope of this article. For the purposes of this article, the datasources and reports within a solution will be deployed to the same folder.
Reporting Services (Native Mode)
After deploying a report (and it's datasource) to a Native Mode (a server that is not in Sharepoint Integrated mode) Report Server, you can easily hide objects. You can hide datasource, report or other supporting objects (like graphics). The procedure below describes how to hide a datasource, but the procedure is the same for other objects (including folders!).
From the SQL Server 2005 Management Console:
- Click the Connect button in the Object Explorer.
- Select Reporting Services.
- Enter your Server Name and click Connect.
- Drill down to your target folder.
- Right-click on the datasource.
- Select Properties.
- Check the Hidden in list view checkbox.
- Click OK.
This does two things for you. If you access the Report Manager directly, users will not see the object in list view. Additionally, if you are using the Sharepoint (WSS2) Reporting Services Report Explorer as a front end, the object will also be hidden. If you run WSS2 from the Reporting Server or even another server. The Report Explorer is quite useful. One of it's advantages is that when you click on a report, the browser automatically pops up a new window.
This article from the Microsoft Books Online talks about Reporting Services web parts for Sharepoint WSS2. I use the Report Explorer, but not the Report Viewer. That's because the web part screen size limits the size of the report and makes for a lot of scrolling with tabular reports.
Reporting Services in Sharepoint Integrated Mode
In order to manage object visibility using Reporting Services in Sharepoint Integrated mode, you need to use the Sharepoint (WSS3/MOSS2007) toolset for creating columns and filtered views. In Sharepoint, we'll take the opposite approach from what we did above. Instead of hiding objects, we'll proactively show objects using a combination of adding a column to a Sharepoint document library and adding a filtered view in order to display what we want.
To hide Reporting Services objects (datasources, reports, graphics, etc) in a document library follow these steps:
Create a new column called "Show-Hide"
- In the document library where you deployed your report and datasource, click on the Settings link and then select Document Library Settings.
.
- Scrolling down a screen or two, click Create column.
- Name it Show-Hide.
- Scrolling down a bit more, add Show and Hide for the choices
.
- Scrolling down again, enter Hide as the default choice.
- Then click OK.
Now we'll create filtered view to selectively display the objects we want.
- Back at the Document Library Customize screen, scroll all the way to the bottom and add a new view by clicking on Create view.
- Choose Standard View.
- Give your new view a name that your users will associate with the one they are supposed to use (i.e. "HR Reports, Sales Reports"). I'll use a generic view name for the purposes of this demonstration. Mine will be called "Active Reports".
It is very important that you check the Make this the default view checkbox.
- Scroll down some more.
Uncheck the Show-Hide column (which you created earlier). Don't worry. Even though we don't want to display this column, we'll still use it for filtering. Read on. (You can optionally uncheck other undesirable columns like "Modified By", etc.)
- Scroll down and choose an appropriate sort order. (Probably by Name.)
- Scroll down again to the filter section. Choose Show items only when the following is true. Then fill in the fields as shown below.
.
This is the key right here. Don't miss this step!
- Click OK at the bottom of the page.
- This will return you to your Reporting Document Library where you should see no reports lists.
Don't gasp! This is what is supposed to happen. Hang on and I'll tell you how to fix it. First let's review what we did
First we added a new column called "Show-Hide" to the document library. Then we created a new view, made the new view the default one, and added a filter to the view not to show anything unless the object is set to Show the item. That's why we can't see anything yet.
- From the View dropdown of the library, click the dropdown menu by clicking the black triangle, then select the All Documents view. This is the view YOU will use to manage your report library objects. Don't forget about it. You will need it every time you upload a new report, or no one will see the marvelous reports you create.
- Once you can see all your report objects (phew!), hover the mouse over an object you want users to see. After a second or two, you will see a dropdown arrow on the right.
- Click the dropdown arrow and select Edit Properties.
- Then select Show in the Show-Hide field and click OK.
- Then change your view back to the default view you created earlier. Mine is called "Active Reports". You show see the Report you decided to display to your users. To check everything, close all web browser windows and then re-navigate to the desired Report Document Library. You should be able to see only the reports you want to "Show" to your users.
- Edit the Properties of any other report you want to show.
Now you know how to hide undesired Reporting Services objects in both Native and Sharepoint Integrated modes.
Happy portal building!
Next Steps
- If you missed it, read Part 1 of this Portal Management series on "Security and Design Implicates"
- Stay tuned for Part 3 of this Portal Management series. The topic will be "Building a Dashboard" in Sharepoint.
- Take a look at these other Reporting Services tips.
About the author
Rob Fisch has worked with SQL Server since version 6.5 as a dba, developer, report writer and data warehouse designer.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
