FoldersIn This Chapter
Moving Reports and Supporting Files to the Report Server
Seek and Ye Shall Find: Search and Find Functions
Managing Reports on the Report Server
Key Performance Indicators in the Web Portal
Reporting Services in SharePoint Integrated Mode
In Part III of this book, we focused on report authoring. You learned fancy techniques for creating whiz-bang reports—both paginated reports and tabular reports. However, the fact is, even the whiz-bangiest of reports are not much good if you cannot easily share them with end users.
In this chapter, you learn how to do just that. We move from authoring to managing reports and delivering them to the end users. This is done through the report server and its web portal.
NOTE
If you a using SharePoint integration for hosting Reporting Services, these management tasks can be performed through the SharePoint user interface. See the section “Reporting Services in SharePoint Integrated Mode” later in this chapter.
We took a brief look at the report server and its web portal in Chapter 1. Now, we take a more detailed look. Much of our examination focuses on the web portal and how it is used to access and control the report server.
The first step is to move your report definitions and supporting files from the development environment to the Report Catalog. Recall that the Report Catalog is the SQL Server database where the report server keeps all its information, which includes the definitions of the reports it is managing. We look at several ways to accomplish this report deployment.
If you used Report Builder to create your reports, you have saved those reports and the shared data source directly to the web portal, so Report Builder users will not need to go through the process of deploying reports described in this chapter. For the most part, you can skip the section entitled “Moving Reports and Supporting Files to the Report Server.” You may want to take note of the technique for hiding items in the “Hiding an Item” subsection along with the “Uploading Supporting Materials” subsection. Be sure to look at the rest of the material in this chapter beginning with the “Managing Items in Folders” section to learn how to manage the reports you already have on the server.
Once your reports are available through the report server, you need to control how they are executed. We use the report server’s security features to control who can access each report, and we use the caching and report history to control how a report is executed each time it is requested by a user. Finally, we control all these report server features using the web portal.
In short, in this chapter, we take your reports from a single-user development environment to a secure, managed environment where they can be executed by a number of users.
Before you deploy reports to the report server, you need to have an understanding of the way the report server organizes reports in the Report Catalog. In the Report Catalog, reports are arranged into a system of folders similar to the Windows or Mac file system. Folders can contain reports, supporting files (such as external images and shared data sources), and even other folders. The easiest way to create, view, and maintain these folders is through the web portal.
Although the Report Catalog folders look and act like Windows file system folders, they are not actual file system folders. You cannot find them anywhere in the file system on the computer running the report server. Report Catalog folders are screen representations of records in the Report Catalog database.
Each folder is assigned a name. Folder names can include just about any character, including spaces. However, folder names cannot include any of the following characters:
Also, a folder name cannot consist exclusively of dots or spaces.
In addition to a name, folders can be assigned a description. The description can contain a long explanation of the contents of the folder. It also can help users determine what types of reports are in a folder without having to open that folder and look at the contents. Both the folder name and the description can be searched by a user to help them find a report.
The web portal provides a straightforward method for creating and navigating folders in the Report Catalog. When you initially install Reporting Services, the Home folder is created by default. This is the only folder that exists at first.
The default uniform resource locator (URL) for accessing the web portal site on the computer running Reporting Services is
In this case, ComputerName is the name of the computer where Reporting Services was installed. If you are using a secure connection to access the web portal, replace http: with https:. If you are on the same computer where Reporting Services is running, you can use the following URL:
No matter how you get there, when you initially access the web portal, it appears similar to Figure 11-1.
Figure 11-1 The Reporting Services web portal with no folders defined
Notice the URL shown in Figure 11-1 is a bit different from the URLs given previously. This is because the web portal redirects you to the browse mode. The browse mode is used to display folder contents.
NOTE
Figure 11-1 shows the web portal as it appears for a user with content manager privileges. If you do not see the same items in the toolbar, you do not have content manager privileges and will be unable to complete the exercises in this section of the chapter. If possible, log out and log in with a Windows login that has local administration privileges on the computer running the report server.
Starting with SQL Server 2016, the web portal follows the HTML5 standard. This should get you great compatibility across all modern browsers. So feel free to use any modern browser when working with the web portal. To use the web portal, you must have scripting enabled in your browser.
Let’s create a new folder into which we will deploy some of the Galactic Delivery Services reports from the previous chapters. Here are the steps to follow:
NOTE
Examples showing report deployment throughout this chapter assume the Galactic Delivery Services folder is created in the Home folder. If you already have other folders created in your Report Catalog, be sure you are in the Home folder when you complete the following steps.
1. Click the New item in the menu bar and select Folder as shown in Figure 11-2. The New Folder dialog box appears.
Figure 11-2 Creating a new folder using the web portal
2. Type Galactic Delivery Services for Name and click Create. You see an entry for your new folder with its name in the Home folder.
Now let’s add a description to the Galactic Delivery Services folder to provide additional information for the user. Follow these steps:
1. Click the ellipsis (…) button in the upper-right corner of the Galactic Delivery Services folder item. The item information pop-up appears as shown in Figure 11-3.
Figure 11-3 The item information pop-up
2. Click the MANAGE link in the lower-left corner of the item information pop-up. The Folder Properties page for the Galactic Delivery Services folder appears.
3. Type Reports created while learning to use Reporting Services for Description. See Figure 11-4.
Figure 11-4 The Folder Properties page
4. Click Apply to save the description.
5. Click the Home link in the breadcrumb trail to return to the contents of the Home folder as shown in Figure 11-5.
Figure 11-5 The Home link in the breadcrumb trail
6. If you click the ellipsis (…) button for the Galactic Delivery Services folder item, you will see the item information pop-up now includes the description.
7. Click the “X” in the upper-right corner of the item information pop-up to close it.
If you were observant, you noticed one item on the Folder Properties page we did not use. (If you missed it, look at Figure 11-4.) This is the Hide in tile view check box. When the Hide in tile view check box is checked, the item does not appear when you view the contents of the folder that item is in. This is useful when you want to make the reports in a folder available through a custom interface but not obviously available through the web portal. We discuss this in detail in Chapter 13. You can view hidden items by checking Show hidden items on the View menu as seen in Figure 11-6.
Figure 11-6 Show hidden items on the View menu
To view the contents of a folder, click the folder name. The name of the current folder appears in bold text near the top of the page. Below the name of the current folder is the path from the Home folder to the current folder. This is the breadcrumb path you can use to return to any folder you clicked through to get to the current location. To return to any folder in the current path, click that folder name in the breadcrumb path. You can return to the Home folder by clicking Home at the beginning of the breadcrumb path or by clicking main heading in the upper-left corner of the page. By default, the main heading is “SQL Server Reporting Services.” Later in this chapter, we’ll see how to customize this heading to put your own brand on the web portal.
Now that you know how to create folders, it is time to put some content in those folders. You do this by moving reports and their supporting files from the development environment to the report server. This can be done using a number of different methods. We look at two of those methods now: using the Report Designer and using the web portal.
NOTE
The following sections are for reports created with SSDT or Visual Studio. If you created your reports with Report Builder, they are already saved to the report server and do not need to be deployed. If you authored your reports using Report Builder, you can skip ahead to the section entitled “Hiding an Item.”
The most common method of moving reports to the report server is by using SSDT or Visual Studio. Once you are satisfied with a report you developed, you can make it available to your users without leaving the development environment. This capability to create, preview, and deploy a report from a single authoring tool is a real plus.
Let’s try deploying the report project from Chapter 7. To do so, follow these steps:
1. Start SSDT or Visual Studio, and open the Chapter07 project.
2. Select Project | Properties from the main menu. (The menu may say “Chapter07 Properties” depending on what you happen to have selected at the time.) The Chapter07 Property Pages dialog box appears.
3. Type Galactic Delivery Services/Shared Data Sources for TargetDataSourceFolder.
4. Type Galactic Delivery Services/Chapter07 for TargetReportFolder. This is the folder into which the report is going to be deployed.
5. Type http://ComputerName/ReportServer for TargetServerURL, where ComputerName is the name of the computer where the report server is installed. You should replace http: with https: if you are using a secure connection. You can use localhost in place of the computer name if the report server is installed on the same computer you are using to run Visual Studio (see Figure 11-7).
Figure 11-7 The Chapter07 Property Pages dialog box
NOTE
If your report server does not have the Reporting Services web service configured in its default location, you will need to modify the URL in Step 5 appropriately.
6. Click OK to exit the Chapter07 Property Pages dialog box.
7. Right-click the Chapter07 project entry in the Solution Explorer, and select Deploy from the context menu.
8. The authoring environment builds all the reports in the project and then deploys all the reports, along with their supporting files, to the report server. (During the build process, the authoring environment checks each report for any errors that would prevent it from executing properly on the report server.) The results of the build and deploy are shown in the Output window.
9. Open the web portal in your browser. Click the Galactic Delivery Services folder to view its contents. You see that the authoring environment created two new folders in the Galactic Delivery Services folder: one called Chapter07 and one called Shared Data Sources. (Refresh your browser if the new items are not visible.)
10. Click the Chapter07 folder to view its contents. All of the reports in the Chapter07 project were deployed.
11. Click the Earth US Deliveries report. You see the rendered report.
NOTE
You can also deploy the contents of a project by selecting Build | Deploy Project Name from the main menu.
When the authoring environment deploys reports, it works through the Reporting Services web service. The web portal provides a human interface to Reporting Services. The web service provides an interface for other programs to communicate with Reporting Services. Because the authoring environment falls into the latter of these two categories, it uses the web service to deploy reports.
The web service has a different URL from the web portal. You must enter the URL for the web service and not the web portal in the Properties Pages dialog box for the deployment to work properly. The default URL for the web service is shown in Step 5 in the previous section.
In Steps 2 through 6, you entered information into properties of the Chapter07 project. These values tell the authoring environment where to put the reports and supporting items when the project is deployed. In this case, you instructed the authoring environment to put our shared data source in the Shared Data Sources folder within the Galactic Delivery Services folder and our reports in the Chapter07 folder within the Galactic Delivery Services folder.
We created the Galactic Delivery Services folder in the previous section. We did not create the Shared Data Sources and Chapter07 folders. Instead, the authoring environment created those folders for us as it deployed the items in the project. In fact, the authoring environment creates folders for any path we specify.
In Step 7, you used the project’s context menu to deploy all the items in the project. Alternatively, you could have right-clicked a report and selected Deploy from the report’s context menu. However, this would have deployed only this report, not the entire project.
On some occasions, you might want to deploy a single report rather than the entire project. At times, one report is going to be completed and ready for deployment, while the other reports in the project are still under construction. At other times, one report will be revised after the entire project has already been deployed. In these situations, it is only necessary to redeploy the single revised report.
Even when a single report is deployed, any shared data sources used by that report are automatically deployed along with it. This only makes sense. A report that requires shared data sources does not do much if those shared data sources are not present.
If you look back at Figure 11-7, you notice an OverwriteDataSources item in the dialog box. This controls whether a shared data source that has been deployed to the report server is overwritten by subsequent deployments. In most cases, shared data sources do not change, so they do not need to be overwritten. For this reason, OverwriteDataSources is set to False, meaning do not overwrite existing data sources.
Aside from saving unnecessary effort, not overwriting data sources also helps out in another way. Consider the environment shown in Figure 11-8. In this environment, reports are developed in the authoring environment using a shared data source that points to a development database server. Once the first version of the report is completed, it is deployed to a production report server, as shown in Figure 11-9. As soon as the deployment is complete, the shared data source on the production report server needs to be changed to point to the production database server. This is shown in Figure 11-10.
Figure 11-8 A report and a shared data source ready to deploy
Figure 11-9 Deploying the report and the shared data source
Figure 11-10 Modifying the shared data source to point to the production database server
Now, as time has passed, a new version of the report (version 2) is created in the development environment. This time, when version 2 of the report is deployed to the production report server, the shared data source already exists there.
If OverwriteDataSources is set to True, the data source from the development environment would overwrite the data source in the production environment, and we would be back to the situation in Figure 11-9. With this setting, we would have to redirect the shared data source each time a report is deployed.
To avoid this, OverwriteDataSources is set to False. Now, when version 2 of the report (and subsequent versions) is deployed to the production report server, the shared data source is not overwritten. It remains pointing to the production database server. This is shown in Figure 11-11. We have saved a bit of extra effort with each deployment.
Figure 11-11 A subsequent deployment with OverwriteDataSources set to False
As you will see throughout this chapter, folders are used to organize reports on the report server and help manage security for those reports. If you are managing your report server properly, you can have reports deployed in a number of different folders. A number of these reports use the same database as the source for their data. Rather than having a number of shared data sources scattered throughout the folders on your report server, it makes more sense to have the reports all reference a single shared data source or a set of shared data sources stored in one central folder. This is accomplished through the use of the TargetDataSourceFolder.
Just as the TargetReportFolder property enables you to specify the path where a report is to be deployed, the TargetDataSourceFolder property lets you specify the path where the shared data source should be deployed or found, if it already exists on the report server. If no folder path is specified, the TargetDataSourceFolder defaults to the TargetReportFolder. The OverwriteDataSources flag applies, whether the shared data source is being deployed to the TargetReportFolder or the TargetDataSourceFolder.
If you look back at Figure 11-7, you can see a few additional items in the Property Pages dialog box that we have not discussed. We look at some of those items now.
Maintaining Multiple Configurations At the top of the dialog box is the Configuration drop-down list. This drop-down list enables you to maintain several different deployment configurations for the same project. Each configuration has its own values for TargetDataSourceFolder, TargetReportFolder, TargetServerURL, and the other settings in the dialog box.
This is useful if you need to deploy the reports in a project to more than one report server. Perhaps you have the report server loaded on your PC for your own testing, a staging report server where the report undergoes quality assurance testing, and a production report server where the report is to be made available to the end users. You can enter the properties for deploying to the report server on your PC in the DebugLocal configuration, the properties for deploying to the staging report server in the Debug configuration, and the properties for deploying to the production report server in the Release configuration.
You can then easily switch between deploying to each of these report servers as new versions of your reports go from your own testing to quality assurance testing and are then made available to the users. You can change the configuration you are using for deployment through the Solution Configuration drop-down list in the Report Designer toolbar, as shown in Figure 11-12.
Figure 11-12 The Solution Configuration drop-down list
NOTE
Active (Debug) in the Configuration drop-down list of the Property Pages dialog box simply refers to the Debug configuration that is currently the selected or active configuration.
Target Server Version The Project Property Pages dialog box also enables you to specify the version of the report server to which you are deploying the report. The reason for this is the Reporting Services web service interface varies slightly between different versions of SQL Server. This option will allow you to deploy reports to report servers running older versions of SQL Server, as well as those running the current version.
Running a Report Project The final item we will look at in the Project Property Pages dialog box is StartItem, which is used when running your report project. Use the StartItem drop-down list to select which report from your project should be executed when you run the project. The report selected as the start item is displayed in a browser window.
When you run a report project, you deploy all the reports, shared data sources, and other supporting information in the project to the target server and target folders in your active configuration. Once the deployment is complete, the report specified as the start item is executed in a browser window. You can then debug this report, making sure it looks correct and functions properly. You can run the project by clicking the Start button on the toolbar (to the right of the Solution Configuration drop-down list) or by selecting any of the following items from the Debug menu (or by pressing any of the shortcut keys that correspond to these menu items):
Start Debugging
Start Without Debugging
Step Over
There is no such thing as stepping over a report. These menu items pertain to other types of SQL Server Data Tools and Visual Studio projects. Here they simply run the project. The report selected as the start item is executed in a browser window from start to finish.
Another common method of moving a report to the report server is by using the web portal. This is known as uploading the report. Deploying reports from SSDT or Visual Studio can be thought of as pushing the reports from the authoring environment to the report server, whereas uploading reports using the web portal can be thought of as pulling the reports from the authoring environment to the report server.
You may need to use the web portal upload feature in situations where your report authors do not have rights to deploy reports on the report server. The report authors create their reports and test them within the authoring environment. When a report is completed, the report author can place the Report Definition Language (RDL) file for the report in a shared directory or send it as an e-mail attachment to the report server administrator. The report server administrator can upload the RDL file to a quality assurance report server and test the report for clarity, accuracy, and proper use of database resources. Once the report has passed this review, the report server administrator can upload the report to the production report server.
Let’s try uploading some of the reports from the Chapter06 report project.
1. Open the web portal in your browser. Click the Galactic Delivery Services folder to view its contents.
2. Create a new folder called Chapter06.
3. Select the new folder to view its contents.
4. Click the Upload File button in the web portal toolbar. The Open dialog box appears.
5. Navigate to the folder where you created your solution for Chapter 6. If this folder is in the default location, you can find it under the following path:
Documents\Visual Studio 2015\projects\MSSQLRS\Chapter06
6. Select the Nametags report (Nametags.rdl), and click Open to exit the dialog box. The Nametags report is uploaded to the Chapter06 folder.
7. Repeat steps 4 through 6 to upload the Placecards report (Placecards.rdl) and the DaysInMaint report (DaysInMaint.rdl).
8. Click the Nametags report to execute it. You see an error similar to the one in Figure 11-13. You received this because, unlike the deployment from the Report Designer, the upload in web portal did not bring the shared data source along with the report.
Figure 11-13 The Reporting Services error page
9. Click the link to the Chapter06 folder at the top of the page.
To get the Nametags report functioning, you need to provide it with a shared data source. One way to do this is to create a new shared data source using the web portal. Follow these steps:
1. Click New in the web portal toolbar and select Data Source. The New Data Source page appears, as shown in Figure 11-14.
Figure 11-14 The New Data Source page
2. Type Galactic for Name.
3. Type Connection to the Galactic Database for Description.
4. Make sure Microsoft SQL Server is selected in the Type drop-down list. Other options here include OLE DB, Microsoft SQL Server Analysis Services, Oracle, ODBC, and XML.
5. Type data source=(local); initial catalog=Galactic for Connection string. If the Galactic database is not on the report server but is on a different computer, put the name of that computer in place of (local) in the connection string.
NOTE
Do not include the parentheses if you use a computer name in place of (local).
6. Select the Using the following credentials option.
7. From the Type of credentials drop-down list, select Database user name and password.
8. Type GalacticReporting for User name.
9. Type G@l@ct1c for Password. (The third character of the password is a lowercase l. The seventh character of the password is the number 1.)
10. Click Test Connection. Fix any errors if the connection is not successful.
11. Click Create to save the data source and return to the Chapter06 folder.
12. Click the ellipsis (…) button in the upper-right corner of the Nametags report item. The item information pop-up appears to execute it. You receive the same error message page because we have not yet told the report to use our new data source.
13. Click MANAGE. The Properties page for the Nametags report appears.
14. Click the Data sources link on the left side of the screen. The Data sources page for the Nametags report appears.
15. The “A shared data source” radio button should be selected. Click the ellipsis (…) button. The Data source dialog box appears.
16. Click the item for the Galactic Delivery Services folder, and then click the item for the Chapter06 folder.
17. Click the Galactic shared data source. The path to the Galactic shared data source is filled in.
18. Click Save.
19. Click the Chapter06 link in the breadcrumbs.
20. Click the Nametags report to view it. The report now generates using the new shared data source.
21. Once the report has completed generating, click the Chapter06 link in the breadcrumbs.
22. Repeat steps 13 through 21 for the Placecards report and the DaysInMaint report.
Figure 11-15 shows the tile view of the Chapter06 folder. The Nametags report appears under the PAGINATED REPORTS heading. Reports have an icon showing a piece of paper with a column chart. The Galactic shared data source appears under the DATA SOURCES heading. Shared data sources have a cylinder icon.
Figure 11-15 The Chapter06 folder tile view
NOTE
If you used Report Builder to complete the exercises earlier in the book, your Chapter 6 folder looks very different. You can follow the same steps to hide the GDS.gif image
When users are browsing through folders to find a report, you may not want other items, such as shared data sources, cluttering things up. It makes more sense to have the shared data sources where the reports can use them, but out of sight of the users. Fortunately, the web portal provides a way to do just that.
1. Click the ellipsis (…) button in the upper-right corner of the Galactic data source item, and then click MANAGE. The Properties page appears.
2. Check the Hide in tile view check box.
3. Click Apply to save this change.
4. Click the Chapter06 link in the breadcrumbs.
The Galactic data source is no longer visible. However, the DATA SOURCES section is still there with the “(1)” following it to indicate there is one hidden data source in this folder.
If you do need to edit the Galactic data source, you can view it by using the Show Hidden item on the View menu. Follow these steps:
1. Click the View button in the toolbar and select Show hidden items. This will cause the Show hidden items check box to be checked.
2. Click the web page background to exit the View drop-down menu. The Galactic data source is now visible but ghosted, as shown in Figure 11-16.
Figure 11-16 The Galactic data source with Show hidden items on
You can use the ellipsis (…) button and the MANAGE link to modify the properties of the Galactic data source, even if the item is ghosted.
When you are accessing data from a server-based database, such as SQL Server or Oracle, you need to provide some type of credentials, usually a user name and password, to show you have rights to access the data. Keeping these credentials secure is an important concern. The shared data sources created on the report server provide several methods for specifying these credentials.
When entering the connection string into a shared data source, it is best not to include the credentials in the connection string itself. The connection string is displayed as plain text to anyone who views the Data Source Properties page. To better protect password information, always enter the credential information under one of the Credentials options described here.
Integrated Security If you are not comfortable storing credentials in the Report Catalog, but you do not want your users entering credentials every time a report is run, this is probably the best solution for you. The As the user viewing the report option does not require the user to enter credentials. Instead, it takes the Windows login credentials that let the user access the web portal and passes them along to the database server. This is also known as Windows Integrated security or NTLM authentication. Your database server, of course, needs to be set up to accept these credentials.
This type of security always works when the data source exists on the same server as the report server. It may run into problems, however, if the data source is on another server. The problems are caused by the way this type of security works between servers.
For a better understanding of the problem, let’s look at an example. The user logs in to their computer. This computer knows everything about this user because the original authentication occurred here.
When the user accesses the web portal, the user’s credentials are passed from the original computer to the computer hosting the report server. However, using standard Windows security, not everything about this login is passed to the report server computer—only enough information to authenticate the user. Some sensitive information does not make this hop across the network.
When the user runs a report with a data source using this security setting, the report server must pass on the credentials to the database server. However, the report server does not have the complete credentials to pass along. In fact, it does not know enough about the user to successfully authenticate them on the database server. The authentication on the database server fails. Using standard Windows security, this security setting only works across one hop, from the original authenticating computer to a second computer. In the case of the web portal, this is the hop from the user’s computer to the report server.
To get this security setting to work across more than one hop, your Windows domain must use a special kind of security known as Kerberos, which allows authentication across multiple hops. Using Kerberos security, this security setting works across any number of servers in the network.
Credentials Stored in the Report Server The next option, “Using the following credentials,” enables you to have the user name and password stored in the Report Catalog on the report server. The user name and password entered with this option are encrypted when they are stored in the Report Catalog. Also, the password is not displayed to the user on the Data Source Properties page.
This option is convenient for the user because they do not need to remember and enter credentials to run reports using this data source. It also provides the required security for most situations through the measures noted in the previous paragraph.
In addition to the user name and password, you must specify the type of credentials being entered. When “Windows user name and password” is selected, the user name and password stored in the Report Catalog are treated as a Windows login. When “Database user name and password” are selected, the user name and password are treated as a database login.
There is also a check box under this option labeled “Log in using these credentials, but then try to impersonate the user viewing the report.” If this check box is checked, the data source can attempt to impersonate the user running this report after making a connection with the stored credentials. This feature is supported by the SQL Server relational database engine and SQL Server Analysis Services.
Prompt the User The next option is to have the user enter the credentials required by the data source each time the report is run. This is the By prompting the user viewing the report for credentials option. You can specify the prompt to be presented each time the user must enter these credentials. As with the previous option, you need to specify the type of credentials being entered, Windows user name and password, or database user name and password.
Having the user enter the credentials each time the report is run is the most secure option. No login information is stored with the data source, but most users are not pleased with a system where they must enter login information each time they run a report. This option may be appropriate when your organization’s security policy forbids storing login information in any way. In most other cases, the other Credentials options provide a better solution.
Credentials Not Required The final Credentials option is for data sources that do not require any authentication. This is the Without any credentials option. This option would be used for connection to some Access databases, FoxPro databases, and others that do not require any login or password. This option could also be used if you insist, despite prior warnings here, on putting your credentials right in the connection string.
In addition to reports and shared data sources, other items can be uploaded to report server folders. External images needed as part of the reports can be uploaded, for example, as well as documentation and other supporting materials.
If you look closely at the Nametags report when it comes up in the web portal, you notice this report has a problem. The GDS logo that should appear in the lower-left corner of each nametag is missing. You may see the broken-link X symbol instead of the GDS logo.
This image was stored as an external image in the Chapter06 project. We need to upload this image to the report server. Once the image is uploaded into the same folder as the report, the report can find it. Here are the steps to do this:
1. Return to the Chapter06 folder in the web portal.
2. Click Upload in the toolbar. The Open dialog box appears.
3. Navigate to the folder containing the Chapter06 project. Select the GDS.gif file, and click Open to exit the dialog box and upload the file.
4. Click the Nametags report to execute it. If the image placeholder is still visible, click the Refresh button in the Report Viewer toolbar, as shown in Figure 11-17.
Figure 11-17 The Refresh button in the Report Viewer toolbar
NOTE
When you need to have the web portal refresh a report, always use the Refresh button in the Report Viewer toolbar. Do not use the browser’s Refresh button. The browser’s Refresh button causes the page to be refreshed, but it does not cause the report to be reexecuted.
5. Click the link for the Chapter06 folder.
6. The entry for the GDS.gif image shows under the RESOURCES heading in the Chapter06 folder. As with the Galactic shared data source, you probably don’t want the entry for this image file cluttering up the web portal.
7. Click the ellipsis (…) button in the upper-right corner of the GDS.gif item and select MANAGE. The Properties page for GDS.gif appears.
8. Check the Hide in tile view check box.
9. Click Apply.
10. Click the Chapter06 link in the breadcrumbs. The GDS.gif item appears ghosted, just like the Galactic shared data source.
11. Click View in the toolbar and select Show Hidden to uncheck this item.
12. Click anywhere on the web page background to exit the View drop-down menu. The ghost entries for the hidden items disappear.
In some cases, you need to provide your users with documentation on one or more reports in the form of either a text file or a Word or HTML document. Supporting materials may also be created in other applications. For example, you may have a PowerPoint presentation or a Visio diagram that aids in the interpretation and understanding of a set of reports. These materials can be uploaded as folder items just like report files.
A text file or an HTML document can be displayed right in the browser without any additional software. For other types of documents, if the appropriate application is installed on the user’s computer and the browser supports it, the documents can be viewed right in the browser as well. These documents can also be downloaded and saved to the user’s computer, if desired.
Now, we’ll create a simple text document and then upload it to the Chapter06 folder.
1. Open Notepad or another text editor.
2. Type the following in the text editor:
3. Save this as ReportReadMe.txt in a temporary location on your computer.
4. Return to your browser with the web portal viewing the Chapter06 folder. Click Upload in the toolbar. The Open dialog box appears.
5. Navigate to the ReportReadMe.txt file, select it, and click Open to exit the dialog box. The file is uploaded.
6. Select the ReportReadMe.txt entry in the Chapter06 folder. You see the contents of the text file displayed within the browser.
7. Click your browser’s Back button.
8. Let’s add a second line to our text file. Open the ReportReadMe.txt file in your text editor, and add the following as a second line:
9. Save the changes and close your text editor.
10. Return to your browser with the web portal viewing the Chapter06 folder. Click Upload in the toolbar. The Open dialog box appears.
11. Navigate to the ReportReadMe.txt file, select it, and click Open to exit the dialog box. The Overwrite or cancel dialog box appears.
12. Click Overwrite.
13. Select the ReportReadMe.txt entry in the Chapter06 folder. You see the new version of the text file.
14. Click your browser’s Back button.
15. Click the ellipsis (…) button in the upper-right corner of the ReportReadMe.txt item and select MANAGE. The Properties page for ReportReadMe.txt appears.
16. Type The purpose of these reports … for the description.
17. Click Apply to save your changes.
18. Click the link for the Chapter06 folder.
19. Click the ellipsis (…) button in the upper-right corner of the ReportReadMe.txt item. The description shows up in the pop-up information.
20. Let’s make another change to our text file and look at another way to overwrite an entry on the report server. Open the ReportReadMe.txt file in your text editor, and add the following as a third line:
21. Save the changes and close your text editor.
22. Return to your browser with the web portal viewing the Chapter06 folder. Click the ellipsis (…) button in the upper-right corner of the ReportReadMe.txt item and click MANAGE. The ReportReadMe.txt Properties page appears.
23. Click Replace. The Open dialog box appears.
24. Navigate to the ReportReadMe.txt file, select it, and click Open to exit the dialog box. The file is uploaded.
25. Click the link for the Chapter06 folder.
26. Click the link for ReportReadMe.txt. You see the latest version of the text file.
27. Click your browser’s Back button.
28. Delete the ReportReadMe.txt text file from your computer (but leave it in the report server folder).
In addition to uploading a report definition to the report server, it is possible to download a report definition, modify it, and send your modifications back to the report server as an update. You only need to do this if you do not have a copy of the RDL file for a report that is on the report server and needs to be modified. If you already have the report in a report project, you can edit that report using the Report Designer and then redeploy it.
NOTE
If you used Report Builder to create your reports, you don’t need to worry about downloading an RDL file in order to edit it. You can simply edit the report in Report Builder and make the desired changes. So, all of you Report Builder users can skip ahead to the “Managing Items in Folders” section.
For this example, imagine we do not have the RDL file for the Deliveries per Planet report and need to make a change to the report. The first task we need to complete is to download this report’s RDL file from the report server to our local computer. Follow these steps:
1. Open the web portal in your browser, and navigate to the Chapter07 folder.
2. Click the ellipsis (…) button in the upper-right corner of the entry for the Deliveries per Planet report and select MANAGE. The Deliveries per Planet Properties page appears.
3. Click Download. The File Download dialog box or the File Download bar appears, depending on your browser. (If the file automatically saves to your Downloads folder, skip to the next section.)
4. If the dialog box appears, click Save. If the bar appears across the bottom of the window, click the down arrow next to the Save button and select Save as. The Save As dialog box appears.
5. Browse to an appropriate temporary location on your computer. Leave the filename as “Deliveries per Planet.rdl.” Click Save to exit the Save As dialog box. The file is downloaded and saved in the specified location.
6. Close the Download dialog box or the Download bar when the download is complete.
NOTE
If you have logon credentials stored in one or more data source definitions in the report, for security purposes, these are not saved in the resulting report definition file.
We now have the report definition file for the Deliveries per Planet report copied from the report server to our local computer. However, an RDL file by itself is not useful. To edit it, we have to place it in a report project. Again, remember for this example that we are imagining we do not already have the Deliveries per Planet report in a report project. Here are the steps to follow:
1. Start SSDT or Visual Studio.
2. Create a new report project in the MSSQLRS folder called Edit Deliveries per Planet. (Do not use the Report Wizard.)
3. Right-click the Reports entry in the Solution Explorer, and select Add | Existing Item from the context menu. The Add Existing Item dialog box appears.
4. Navigate to the location where you stored the Deliveries per Planet.rdl file in the previous section. Select the Deliveries per Planet.rdl file, and click Add to exit the Add Existing Item dialog box.
5. Double-click the Deliveries per Planet report to open it for editing. (If you encounter an error while trying to edit this report, save the project, close the Report Designer, restart it again, and reopen the Edit Deliveries per Planet project.)
6. Change the title of the chart to GDS Deliveries per Planet.
7. Click Save All in the toolbar.
8. Close the authoring environment.
Now that the report definition changes are completed, we are ready to upload the modified report.
1. Return to the web portal. Go to the Properties page for the Deliveries per Planet report, if you are not already there.
2. Click Replace. The Open dialog box appears.
3. Navigate to the Edit Deliveries per Planet folder to find the updated version of the DeliveryAnalysis.rdl file.
NOTE
Do not select the copy of DeliveriesPerPlanet you originally downloaded. The modified version is in the folder with the Edit Deliveries per Planet report project.
4. Select Deliveries per Planet.rdl, and click Open to exit the dialog box. The report file is uploaded.
5. Click the Chapter07 link in the breadcrumbs.
6. Click the Deliveries per Planet item to view the report. You will see the chart with the new title.
You now know how to load items into folders on the report server. Of course, we live in a dynamic world, so things seldom stay where they are originally put. We need to be able to move items around as we come up with better ways of organizing them. We also need to be able to delete items as they are replaced by something better or are simply not needed anymore. Fortunately, the web portal provides ways for us to do this housekeeping in an efficient manner.
As an example, let’s create a more descriptive folder for our Nametags Report and its supporting items.
Here are the steps to follow to move an item:
1. Open the web portal in your browser, and navigate to the Galactic Delivery Services folder.
2. Click New in the toolbar and select Folder. The New Folder dialog box appears.
3. Type 2016 Conference for Name.
4. Click Create to create the new folder.
5. Click Chapter06 to view the contents of this folder.
6. Click the ellipsis (…) button in the upper-right corner of the Nametags report and select MANAGE. The Properties page for the Nametags report appears.
7. Click Move. The Select Folder dialog box appears.
8. Click the link for the Galactic Delivery Services folder. You will see entries for all of the folders in the Galactic Delivery Services folder.
9. Select the 2016 Conference folder.
10. Click Select to move the report to this folder.
11. Click the link for the 2016 Conference folder in the breadcrumbs to view the contents of this folder.
12. Click the entry for the Nametags to run the report.
Note the report is still properly linked to the shared data source, so it executes properly. However, the GDS logo is missing from the report. For the report to look the way it should, we would have to move the GDS.gif file to this new folder as well. Instead of doing that, let’s move the Nametags report back to the folder it came from. Complete the following:
1. Click 2016 Conference in the breadcrumbs.
2. Click the ellipsis (…) button in the upper-right corner of the Nametags report and select MANAGE. The Properties page for the Nametags report appears.
3. Click Move. The Select Folder dialog box appears.
4. Click the link for the Galactic Delivery Services folder. You will see entries for all of the folders in the Galactic Delivery Services folder.
5. Select the Chapter06 folder.
6. Click Select to move the report to this folder.
7. Click the link for the Chapter06 folder in the breadcrumbs to view the contents of this folder.
8. Click the entry for the Nametags to run the report. The GDS logo appears once again.
The 2016 Conference folder is now empty, so let’s delete it. Follow these steps:
1. Click the Galactic Delivery Services link in the breadcrumbs.
2. Click the ellipsis (…) button in the upper-right corner of the 2016 Conference folder item and select MANAGE. The Properties page for the 2016 Conference folder appears.
3. Click Delete. The Delete 2016 Conference folder confirmation dialog box appears.
4. Click Delete to confirm your deletion. The 2016 Conference folder is deleted.
Folders do not need to be emptied before they are deleted. If the 2016 Conference folder had contained reports, supporting items, or even other folders, these would have been deleted along with the folder.
In addition to moving and deleting items, we may want to rename items. Let’s give the Chapter06 folder a new name.
1. Click the ellipsis (…) button in the upper-right corner of the Chapter06 item and select MANAGE. The Chapter06 Properties page appears.
2. Replace the contents of Name by typing 2016 Conference. Then type The items in this folder are for the GDS Conference. for Description.
3. Click Apply.
4. Click the Galactic Delivery Services link in the breadcrumbs.
This same technique makes it just as easy to change the names and descriptions for reports and other items. However, just because it is easy to make these changes does not mean you should do it often. Once users become familiar with a folder name, a report name, or a report’s location within the folder structure, you should change it only if you have a good reason to do so.
The web portal provides two features to help users find information. The Search function helps the user locate a report within the report server folder structure, and the Find function enables the user to jump to a certain piece of information while viewing a report.
First, we look at the Search function. This function lets the user enter a portion of a word, a complete word, or a phrase. The web portal then searches the names and descriptions of items in the report server folder structure for occurrences of this text. The web portal does not search the contents of a report or supporting files.
For example, searching for “GDS Report” would find “The GDS Report” and “GDS Reporting.” It would not find “Report GDS Income” or “GDS Accounting Report.” This is strictly a search for the text exactly as it is entered—no Boolean logic, proximity searching, or other features you find in Internet search engines. Also, the search is not case sensitive.
Follow these steps to use the Search function:
1. Open the web portal in your browser, and navigate to the Home folder.
2. Type report in the Search box in the upper-right corner of the screen, and then click the magnifying glass. The Search page is displayed with the search results.
3. The web portal lists several items of various types. No weighting or relevance is assigned to each result. Click an item to see its contents.
4. Click your browser’s Back button to return to the search results.
Next, we look at the Find function. This function also enables the user to enter a portion of a word, a complete word, or a phrase. The web portal then searches the contents of the current report for occurrences of this text. Next, it highlights the first occurrence that it finds. The user can use the Next button to move to the next occurrence.
As with the Search function, Find locates text just as it is entered—no Boolean logic or proximity searching. Also, Find is not case sensitive. Find will, however, find occurrences of the string on pages beyond the page being viewed, all the way to the end of the report.
We use the PlaceCards report to demonstrate the Find function. Navigate to this report in the 2016 Conference folder, and run the report. Suppose we want to look at who will be attending the conference from manufacturing companies. Rather than skimming through the report, looking for what we are interested in, here is a better way:
1. Type mfg in the entry area to the left of the words “Find | Next” in the Report Viewer toolbar.
2. Click Find. The first manufacturing company is found: Bolimite, Mfg. The “Mfg” is highlighted.
3. Click Next. (Make sure you do not click Find. Clicking Find simply starts the find operation again from the top of the page.) The next manufacturing company is found: Phillips Mfg, Inc. You may need to scroll down to see this.
4. Click Next. Quincy, Mfg is found.
Up to this point, we have been viewing reports in the preview format in SSDT, Visual Studio, or Report Builder. In the web portal, we have viewed reports in HTML format in the browser. Both the report authoring environments and the web portal offer a number of other options for report output.
To export a report in another format from the web portal, use the Export drop-down list (the disk icon) as shown in Figure 11-18. Select your desired format from the menu. Then, follow the instructions in the resulting dialog box to either save the exported report or view it on the screen in the appropriate viewer.
Figure 11-18 The Export drop-down list
A number of formats can be presented to a user. These presentation rendering formats retain the layout, fonts, colors, and graphics of the report. A complete list of the presentation rendering formats enabled by default is as follows:
Preview
HTML
TIFF Image
Adobe PDF
MHTML (web archive)
Excel
PowerPoint (new in SQL Server 2016 Reporting Services!)
Word
Print
The presentation rendering formats can be further divided into three groups. The interactive presentation rendering formats—preview and Hypertext Markup Language (HTML)—support all of the interactive features of Reporting Services. The physical page presentation formats—TIFF Image, Adobe PDF, Word, and Print—are primarily concerned with fitting content on a specific page size that can become a printout on a physical piece of paper. The logical page presentation formats—MHTML (web archive), Excel, and PowerPoint—are primarily concerned with formatting the content for viewing on a screen.
NOTE
By default, the Excel export is the Open XML format compatible with Excel 2007 and Excel 2010. In the RSReportServer.config file, this is called the EXCELOPENXML rendering extension. There is also an EXCEL rendering extension, which creates files compatible with Excel 2003 and earlier. To use this EXCEL rendering format, you must set the Visible property to True for this rendering format in the RSReportServer.config file. (See Chapter 13 for information on locating and modifying the RSReportServer.config file.)
Reporting Services also lets you export your report to two additional formats, which are used primarily for rendering report data into a form that can be used by other computer programs. These data exchange rendering formats contain the data portion of the report, along with a minimal amount of formatting. Here are the data exchange rendering formats:
Comma-Separated Values (CSV)
Extensible Markup Language (XML)
Unlike the other export formats, Reporting Services includes several report properties that allow you to customize the output of the XML export format. By default, XML exports include the data contained within tablixes and charts in your report. All the data from the tablix or chart is included in the XML export, even if a column or a row is hidden. XML exports do not contain values from text boxes that are not within a tablix.
Because each item in the XML export is labeled with an XML tag, reports to be exported using the XML format can be more complex than those exported using the CSV format. Thus, reports to be exported using the XML format may have more than one tablix or chart.
The following is a section of an XML file that results from a report:
You can quickly see how the XML structure follows the report layout. The Report tag provides information about the report as a whole. After that tag is a series of tags containing the data in a tablix.
Customizing the XML Data Exchange Format You can customize the XML export to fit your needs. Each item on the report contains three properties that control whether or not the item appears in an XML export and, if it does, how it is formatted.
DataElementName The DataElementName property controls the element name used in the XML structure for a particular item. If this is not specified for an item, the name of the item in the report definition is used as the data element name in any XML export.
DataElementOutput The DataElementOutput property controls whether or not a particular item is included in any XML export. By default, only those items in a tablix or chart are included.
DataElementStyle The DataElementStyle property controls how an item’s output is formatted in any XML export. By default, an item’s data is presented as attributes. This property enables us to present an item’s data in an element format.
In addition to the two data exchange rendering formats, it is possible to make report content available for use by Power BI (or Power Pivot in Excel). Specifically, you can make the content of the data regions in a report available as an atom data feed. An atom data feed allows Power BI to execute the report using the Atom rendering format and then import the report data for further analysis.
The data feed rendering format is not enabled in the default configuration of the report server. If you wish to use the data feed rendering format, you will need to modify the report server configuration file to enable this format. You will need to restart the Reporting Services Windows service after you make this change in order for it to take effect.
NOTE
Always make a backup copy of the rsreportserver.config file before making any changes so you can return to the default configuration if there are any issues.
The report server configuration file is called rsreportserver.config. In a default installation of the report server, the configuration file is found in:
Make a backup copy of this file and then edit the configuration file with Notepad or another text editor.
In the “<Render>” section of the config file, the entry for the atom data feed rendering format will appear as follows:
Change the Visible attribute from “False” to “True” to activate this rendering format. Then restart the Reporting Services Windows server to make it reread the configuration file and implement this change. Once the service has restarted, you will see an entry for “Data Feed” in the Export drop-down menu.
The Data Feed rendering extension functions a bit differently from the other items in the Export menu. It does not output the content of the report as an atom data feed. Instead, it generates a file that tells Power BI how to obtain the content of the report as an atom data feed. This file is known as a data service document.
The data service document contains, among other things, the URL needed by Power BI to access the atom data feed. The data service document is sent to your browser, causing a File Download dialog box or the File Download bar to display. Use the Save or Save As button to save this file. This brings up a Save As dialog box, allowing you to save the data service document to a file with an .atomsvc extension.
When you use Power BI to open a data service document, it reads the URL from the document. It then uses the URL to execute the report, rendering it in the Atom format, and importing the resulting report data. Obviously, Power BI must have a path to the report server in order for the data service document to be utilized successfully.
No matter how convenient you make it for your users to access reports in a browser, and no matter how many interactive drilldown and drillthrough features you provide, your users always want to print their reports on paper. You can explain all the wonders of the multiple cascading parameters you have created until you are blue in the face, but some users just need to touch and feel the numbers on paper. They need to be able to put something in a briefcase (does anyone really use one of those anymore?) and take it home with them at night. It doesn’t matter that they could receive up-to-date numbers through their virtual private network (VPN) at home. They want ink on paper.
New in SQL Server 2016 Reporting Services, the web portal provides printing through Portable Document Format (PDF) rendering of the report. This printing functionality works across all browsers. In older versions of Reporting Services, the print button only worked on Windows PCs. Reports could be exported to a PDF document and then printed, but this was a multistep manual process.
When you click the print button in the toolbar (the printer icon), a dialog box appears enabling you to set the page size and orientation for the printout. This is shown in Figure 11-19. Note that the selections made in this dialog box need to work in consort with the page setup of your report in order for the report to print properly. These options do not override the report formatting selected when the report was created.
Figure 11-19 The Print dialog box
Once you make your selections and click Print, the web portal will render the report as a PDF document in a small preview window and activate the PDF print dialog box. From this point, proceed as you normally would when printing a PDF document. When printing is complete, use the “X” in the upper-right corner of the print preview to close this dialog box.
With reports in place on the report server, you may be thinking your job is about done, but it is just beginning. At this point you need to manage the reports and supporting materials to ensure the reports can be utilized properly by your users.
Two of the biggest concerns when it comes to managing reports are security and performance. Reports containing sensitive data must be secured so they are only accessible by the appropriate people. Reports must return information to users in a reasonable amount of time without putting undo stress on database resources. Fortunately, Reporting Services provides tools for managing both of these concerns. Security roles and item-level security give you extremely fine control over just who has access to each report and resource. Caching, snapshots, and history let you control how and when reports are executed.
In Reporting Services, security was designed with both flexibility and ease of management in mind. Flexibility is provided by the fact that individual access rights can be assigned to each folder and to each item within a folder. An item is either a report or a resource. You can specify exactly who has rights to each item and exactly what those rights are. Ease of management is provided by security inheritance, security roles, and integration with Windows security. We begin our discussion with the last entry in this list.
NOTE
Remember that although we are creating and maintaining these role assignments using the web portal, the security rights apply to report server as a whole. No matter how you access folders and items—through the web portal or through the web service—these security rights are enforced.
Reporting Services does not maintain its own list of users and passwords. Instead, in its default configuration, it depends entirely on integration with Windows security. When a user accesses either the web portal or the web service, that user must authenticate with the report server. In other words, the user must have either a valid domain user name and password or a local user name and password to log on to the report server. Both the web portal and the web service are set up requiring integrated Windows authentication to ensure this logon takes place.
NOTE
If it is impossible for each report user to have their own credentials on the report server, it is possible to configure Reporting Services to use forms-based security through a custom security extension.
Once this logon occurs, Reporting Services utilizes the user name and the user’s group memberships to determine what rights the user possesses. The user can access only those folders and items they have rights to. In the web portal, users do not even see the folders they cannot browse and reports they cannot run. There is no temptation for the user to try and figure out how to get into places they are not supposed to go, because they do not even know these places exist.
In most cases, rights must be explicitly assigned to folders and items. There is, however, one security assignment that is created by default. Any user who is a member of the local administrators group on the computer hosting the report server has content manager rights to all folders and all items. This is done through the BUILTIN\Administrators designation on the report server.
Let’s look at the Security page.
1. Open the web portal in your browser, and navigate to the Home folder.
2. Click the Manage folder toolbar button. You see the Security page for the Home folder, as shown in Figure 11-20.
Figure 11-20 The Security page for the Home folder
The report server maintains a Security page for each item in the Report Catalog—every folder, every report, and every supporting item. The Security page lists all the role assignments for an item. Each role assignment is made up of two things: a Windows user or group and a security role. The rights associated with the security role are assigned to the Windows user or group.
Initially, one role assignment is on the Security page for each item. This entry assigns the Content Manager security role to the BUILTIN\Administrators group. This entry is the default entry that grants any user who is a member of the local administrators group rights to manage the contents of this folder.
You can perform a number of tasks in Reporting Services, each with its corresponding right. For example, you can view reports. Therefore, a corresponding right exists to view reports. The tasks within Reporting Services are shown in Table 11-1.
Table 11-1 Security Tasks Within Reporting Services
You may not be familiar with some of these tasks. We discuss linked reports in the section “Linked Reports,” and we discuss report history snapshots and subscriptions in Chapter 12. For now, you simply need to know these are tasks with associated rights within Reporting Services.
In addition to the tasks listed in Table 11-1, there are system-wide tasks with associated rights. These system-wide tasks deal with the management and operation of Reporting Services as a whole. The system-wide tasks within Reporting Services are shown in Table 11-2.
Table 11-2 System-Wide Security Tasks Within Reporting Services
Again, you may not be familiar with all the tasks in this list. We discuss jobs and shared schedules in Chapter 12.
The rights to perform tasks are grouped together to create roles. Reporting Services includes several predefined roles to help you with security management. In addition, you can create your own custom roles, grouping together any combination of rights that you like. The predefined roles and their corresponding rights are discussed here.
The Browser Role The Browser role is the basic role assigned to users who are going to view reports but who are not going to create folders or upload new reports. The Browser role has rights to perform the following tasks:
Manage individual subscriptions
View folders
View reports
View resources
The Publisher Role The Publisher role is assigned to users who are going to create folders and upload reports. The Publisher role does not have rights to change security settings or manage subscriptions and report history. The Publisher role has rights to perform the following tasks:
Create linked reports
Manage data sources
Manage folders
Manage models
Manage reports
Manage resources
The My Reports Role The My Reports role is designed to be used only with a special folder called the My Reports folder. Within this folder, the My Reports role gives the user rights to do everything except change security settings. The My Reports role has rights to perform the following tasks:
Create linked reports
Manage data sources
Manage folders
Manage individual subscriptions
Manage report history
Manage reports
Manage resources
View data sources
View folders
View reports
View resources
The Content Manager Role The Content Manager role is assigned to users who are managing the folders, reports, and resources. The Content Manager role has rights to perform all tasks, excluding system-wide tasks.
The Report Builder Role The Report Builder role gives users the right to create and edit reports using Report Builder. The Report Builder role has rights to perform the following tasks:
Consume reports
Manage individual subscriptions
View folders
View models
View reports
View resources
The System User Role The system-wide security tasks have two predefined roles. The System User role has rights to perform the following system-wide tasks:
Execute report definitions
View report server properties
View shared schedules
NOTE
If a user is not a member of the System User role, they may not see the toolbar button to launch Report Builder.
The System Administrator Role The System Administrator role provides the user with rights to complete any of the tasks necessary to manage the report server. This role has rights to perform the following system-wide tasks:
Execute report definitions
Manage jobs
Manage report server properties
Manage report server security
Manage roles
Manage shared schedules
As stated previously, role assignments are created when a Windows user or a Windows group is assigned a role for a folder, a report, or a resource. Role assignments are created on the Security page for the folder, report, or resource. These role assignments control what the user can see within a folder and what tasks the user can perform on the folder, report, or resource.
Let’s try creating role assignments for some of our folders and reports.
NOTE
To complete the next set of procedures, you need a user who has rights to log on to the report server, but who is not a member of the local administrators group on that computer. You should know the password for this user so you can log on as that user and view the results of your security settings.
Creating a Role Assignment for a Folder
Let’s try creating a new role assignment for the Home folder.
1. Open the web portal in your browser. Navigate to the Home folder, if not already there.
2. Click Manage folder in the toolbar. You see the Security page for this folder.
3. Click Add group or user. The New Role Assignment page appears, as shown in Figure 11-21.
Figure 11-21 The New Role Assignment page
4. Type the name of a valid user for Group or User Name. If you are using a domain user or domain group, this must be in the format DomainName\UserName or DomainName\GroupName. If you are using a local user or local group, you can simply type in the user name or the group name.
5. Check the check box for the Browser role.
6. Click OK to save your role assignment and return to the Security page. Reporting Services makes sure you entered a valid user or group for the role assignment. If this is not a valid user or group, you receive an error message and your role assignment is not saved.
NOTE
A user needs to have at least viewing rights in the Home folder to view other folders and navigate to them.
Inherited Role Assignments By default, folders (other than the Home folder), reports, and resources inherit their role assignments from the folder that contains them. You can think of the nested folders as branches of a tree, with the reports and resources as the leaves. Inherited security means you can make security changes to one folder and have those changes take effect for all the branches and leaves further along the tree.
This makes managing security easy. You can maintain security for all the reports and resources within a folder simply by modifying the role assignments for the folder itself. You can maintain security for an entire branch of the tree structure by modifying the role assignments for the folder that forms the base of that branch. Let’s look at the security for the Galactic Delivery Services folder.
1. Click the Home link at the top of the page.
2. Select the Galactic Delivery Services folder to view its contents.
3. Click Manage folder. You see the Properties page for this folder.
4. Select Security on the left side of the page. You see the Security page for this folder.
The Galactic Delivery Services folder is inheriting its role assignments from the Home folder. You did not add a role assignment giving Browser rights to your user in this folder and, yet, there it is! As soon as you added the role assignment to the Home folder, it appeared for all the items within the Home folder.
You gave your user Browser rights in the Home folder so they could view the contents of the Home folder and then navigate into other folders to find the reports they need. You may want to give this user additional rights in folders further along in the tree. Perhaps the user can manage the content of certain folders that belong to their department, but can only browse when in the Home folder.
To accomplish this task, you must first break the inherited security for the Galactic Delivery Services folder.
1. Click Customize security. A dialog box with a confirmation message appears. The web portal is confirming you want to break that inheritance by creating your own role assignments for this folder.
2. Click OK to confirm you want to break the inherited security.
Now that you have broken the inherited security, you have new buttons on the toolbar for adding a group or user, deleting existing groups or users, and using the same security as the parent.
Now you can edit the role assignment for your user.
1. Click the Edit link next to the role assignment giving your user Browser rights. The Edit Role Assignment page appears.
2. Uncheck the check box for the Browser role.
3. Check the check box for the Content Manager role.
4. Click Apply to save the changes to your role assignment and return to the Security page. The user now has Content Manager rights in the Galactic Delivery Services folder.
5. Click the Home link at the top of the page, and then navigate to the Galactic Delivery Services folder.
6. Select the Chapter07 folder to view its contents.
7. Click Manage folder. You see the Properties page for this folder.
8. Select Security on the left side of the page. You see the Security page for this folder.
You can see the folder is inheriting its role assignments from the Galactic Delivery Services folder.
NOTE
Although we do not do so in these exercises, you can check more than one role when creating or editing a role assignment. The user’s rights are then the sum of the rights granted by each role.
Managing Role Assignments for Reports
Now, let’s try managing role assignments for reports.
1. Click the Galactic Delivery Services link in the breadcrumbs, and then navigate to the Chapter07 folder.
2. Click the ellipsis (…) button in the upper-right corner of the Deliveries Per Planet report item and select MANAGE. The Properties page for this report appears.
3. Select Security on the left side of the page. The Security page for this report appears.
Again, you can see this report is inheriting its role assignments from the folder that contains it. Because the user has Content Manager rights for the folder, the user also has Content Manager rights for the report. This means the user can change any and all properties of this report and even delete the report altogether.
To continue our security example, we are going to suppose it is all right for the user to have Content Manager rights for the Chapter07 folder but not for the Deliveries Per Planet report. We need to edit the role assignment for your user. However, before we can do this, we must break the inheritance, as explained in the following steps:
1. Click Customize security. The confirmation dialog box appears.
2. Click OK to confirm.
3. Click the Edit link next to the role assignment giving your user Content Manager rights. The Edit Role Assignment page appears.
4. Uncheck the check box for the Content Manager role.
5. Check the check box for the Browser role.
6. Click Apply to save the changes to your role assignment and return to the Security page.
Now we modify the rights granted to this user for the Earth US Deliveries Report. In our example, we assume the user should have limited rights to this report. In fact, they should only be able to view the report. In this case, the predefined Browser role has too many rights. We have to define our own custom role.
To do this, we need to use SQL Server Management Studio. Follow these steps:
1. Start SQL Server Management Studio. The Connect to Server dialog box will appear.
2. Select Reporting Services from the Server type drop-down list, as shown in Figure 11-22.
Figure 11-22 The SQL Server Management Studio Connect to Server dialog box
3. Enter the name of the report server for Server name.
4. Click Connect. The SQL Server Management Studio will connect to the report server.
5. Expand the Security entry in the Object Explorer window. Next, expand the Roles entry in the Object Explorer window. You will see the five default security roles, as shown in Figure 11-23.
Figure 11-23 The default Reporting Services security roles
6. Right-click the Roles entry in the Object Explorer window, and select New Role from the context menu. The New User Role dialog box appears.
7. Type View Report for Name.
8. Type View Report Only for Description.
9. Check View Reports. The New User Role dialog box should appear as shown in Figure 11-24.
Figure 11-24 The View Report role in the New User Role dialog box
10. Click OK to save this new role.
11. Exit the SQL Server Management Studio, and return to the web portal in your browser.
12. Navigate to the Chapter07 folder.
13. Click the ellipsis (…) button in the upper-right corner of the entry for the Earth US Deliveries report and select MANAGE.
14. Select Security from the left side of the page. The Security page for this report appears.
15. Click Customize security. Click OK to confirm.
16. Click the Edit link next to the role assignment giving your user Content Manager rights. The Edit Role Assignment page appears.
17. Uncheck the check box for the Content Manager role.
18. Check the check box for the View Report role.
19. Click Apply to save the changes to your role assignment and return to the Security page. The user has rights to view the Earth US Deliveries Report, but no other rights with that report.
We make one more change to test security. We remove all rights assigned to this user for the Employee Homes report.
1. Click the Chapter07 link in the breadcrumbs.
2. Click the ellipsis (…) button in the upper-right corner of the entry for the Employee Homes report and select MANAGE.
3. Select Security from the left side of the page. The Security page for this report appears.
4. Click Customize security. Click OK to confirm.
5. Check the check box next to the role assignment giving your user Content Manager rights.
6. Click Delete. The confirmation dialog box appears.
7. Click OK to confirm the deletion.
You can now close your browser, log out of Windows, and log on with the user name you have been using in the role assignments. Let’s test our security changes.
1. Open the web portal in your browser. You should be viewing the contents of the Home folder. Notice no buttons are in the toolbar for creating folders and data sources or uploading files, as shown in Figure 11-25. That is because the user you are now logged on as has only Browser rights in this folder.
Figure 11-25 Browser rights in the Home folder
2. Select the Galactic Delivery Services folder to view its contents. When you are in this folder, the Manage folder, New, and Upload buttons have returned, as shown in Figure 11-26. In this folder, your user has Content Manager rights.
Figure 11-26 Content Manager rights in the Galactic Delivery Services folder
3. Select the Chapter07 folder to view its contents.
4. Click the ellipsis (…) button in the upper-right corner of the entry for the Deliveries per Planet report and select MANAGE. The Properties page for this report appears. Note the Security tab doesn’t appear on the left side of the page, as shown in Figure 11-27. Your user has Browser rights to this report, so you can view the report and its history and create subscriptions, but you cannot change its security. (Don’t worry about what subscriptions are right now; we discuss them in Chapter 12.)
Figure 11-27 Browser rights for the Deliveries Per Planet Report
5. Click the link for the Chapter07 folder in the breadcrumbs.
6. Click the ellipsis (…) button in the upper-right corner of the entry for the Earth US Deliveries report and select MANAGE. The Properties page for this report appears. Now, the Subscriptions page is gone, as shown in Figure 11-28. Your user has the rights from our custom View Report role for this report. You can view the report and its history, but you cannot create subscriptions.
Figure 11-28 View Report rights for the Earth US Deliveries Report
7. Click the link for the Chapter07 folder in the breadcrumbs. Notice the Employee Homes report is nowhere to be seen because your user does not have any rights for this report, not even the rights to view it.
Giving users only the rights they need is important. This prevents users from viewing data they should not see or from making modifications or deletions they should not be allowed to make. On the other hand, providing users with enough rights is important so their reports function properly.
As mentioned previously, role assignments can be made to Windows users or to Windows groups. If you create your role assignments using Windows users, you need to create a new set of role assignments every time a new user needs to access Reporting Services. This can be extremely tedious if you have a complex set of role assignments for various folders, reports, and resources.
In most cases, creating role assignments using Windows groups is better. Then, as new users come along, you simply need to add them to the Windows group that has the appropriate rights in Reporting Services. This is much easier!
Also, whenever possible, make your security assignments to the Reporting Services folders rather than individual reports and resources. Then let the reports and resources in that folder inherit their permissions from the folder that contains them. This is much easier to maintain than having a different combination of permissions set for each individual item in a folder.
Security is managed for mobile reports the same way it is managed for paginated reports and their supporting resources. The same security roles with the same capabilities are used to grant access and enable management of mobile reports. However, not all of the tasks associated with the security roles apply to mobile reports. These are simply ignored when a security role is applied to a mobile report.
In many cases, the security set up within Reporting Services restricts the folders a user can access. The sales department may be allowed to access one set of folders. The personnel department may be allowed to access another set of folders. The personnel department doesn’t want to see sales reports and, certainly, some personnel reports should not be seen by everyone in the sales department.
This works well—a place for everything and everything in its place—until you come to the report that needs to be used by both the sales department and the personnel department. You could put a copy of the report in both places, but this gets to be a nightmare as new versions of reports need to be deployed to multiple locations on the report server. You could put the report in a third folder accessed by both the sales department and the personnel department, but that can make navigation in the web portal difficult and confusing.
Fortunately, Reporting Services provides a third alternative: the linked report. With a linked report, your report is deployed to one folder. It is then pointed to by links placed elsewhere within the Report Catalog, as shown in Figure 11-29. To the user, the links look just like a report. Because of these links, the report appears to be in many places. The sales department sees it in their folder. The personnel department sees it in their folder. The fact of the matter is the report is only deployed to one location, so it is easy to administer and maintain.
Figure 11-29 A linked report
To demonstrate a linked report, we are going to make use of the Invoice-Batch Number Report from Chapter 4. This report shows the invoice amounts for companies in various cities. Galactic Delivery Services has sales offices in each of these cities, and each sales office has its own folder within the GDS Report Catalog.
A sales office should be able to access the Invoice-Batch Number Report in their own folder and see the invoices for customers in their city.
We begin by deploying the report to a common folder. Here are the steps to follow:
1. Log in with a user name and password that has Content Manager rights in Reporting Services.
NOTE
If you created the reports for Chapter 4 using the Report Builder, then manually create the Shared Reports folder in the Galactic Delivery Services folder and move the Invoice-Batch Number Report into this new folder.
2. Start SSDT or Visual Studio, and open the Chapter04 project.
3. Modify the properties of the Chapter04 project as follows:
4. Replace ServerName with the appropriate server name or with localhost.
5. Deploy the Invoice-Batch Number Report.
6. Close the development environment.
Now that the report is in the Shared Reports folder, it is time to create our linked reports.
1. Open the web portal in your browser, and navigate to the Galactic Delivery Services folder.
2. Create a new folder. Type Axelburg for Name.
3. Create another new folder. Type Utonal for Name.
4. Navigate to the Shared Reports folder.
5. Click the ellipsis (…) button in the upper-right corner of the entry for the Invoice-Batch Number Report and select MANAGE. The Properties page for this report appears.
6. Click Create linked report. The New linked report page appears.
7. Type Invoice-Batch Number Report for Name and Axelburg invoices in each batch for Description.
8. Click the ellipsis (…) button next to the Location entry. The Select folder dialog box appears.
9. Select the Axelburg folder, and click Select to return to the New linked report page.
10. Click Create to create this linked report in the Axelburg folder.
11. Click the link for the Axelburg folder in the breadcrumbs. You can see the linked report we just created looks like a regular report. The only difference is the representation of two links of chain in the lower-right corner of the icon for the report.
12. Navigate back to the Shared Reports folder.
13. Click the ellipsis (…) button in the upper-right corner of the entry for the Invoice-Batch Number Report and select MANAGE. The Properties page for this report appears.
14. Click Create linked report. The New linked report page appears.
15. Type Invoice-Batch Number Report for Name and Utonal invoices in each batch for Description.
16. Click the ellipsis (…) button next to the Location entry. The Select folder dialog box appears.
17. Select the Utonal folder, and click Select to return to the New linked report page.
18. Click Create to create this linked report in the Utonal folder.
19. Click the link for the Utonal folder in the breadcrumbs. You see the linked report we just created.
We have now successfully created our two linked reports.
We have our linked reports, but we have not quite fulfilled all the business needs stated for these linked reports. The Axelburg sales office is supposed to be able to see only their own invoice data. The same is true for the Utonal sales office. We can meet these business needs by managing the report parameters right in the web portal. Here are the steps to follow:
1. Navigate to the Axelburg folder.
2. Click the ellipsis (…) button in the upper-right corner of the entry for the Invoice-Batch Number Report and select MANAGE. The Properties page for this report appears.
3. Click Parameters on the left side of the screen. The Parameter Management page appears. Note that the BillingCity parameter has a default of Axelburg. Because this is the Axelburg folder, we leave that default alone. What we modify is the user’s ability to change this default value.
4. In the Visibility column for the BillingCity row, select Internal from the drop-down list. The user is no longer prompted for a city. Instead, the report always uses the default value. As you may have guessed, you can have a default value, you can prompt the user for the value, or you can do both. You must do at least one of these.
5. Check the Has Default check box in the StartDate row. Type 01/01/2015 for the default value for this row.
6. Check the Has Default check box in the EndDate row. Type 12/31/2015 for the default value for this row.
7. Click Apply to save your changes.
8. Click the Axelburg folder link in the breadcrumbs.
9. Click the Invoice-Batch Number Report entry to run the report.
10. Notice you can no longer select a city. It is always Axelburg. Also, notice we now have default values for the date. Also worth noting is these default values are much easier to modify than the default values that are part of the report because we can make changes without having to redeploy the report.
11. Navigate to the Utonal folder.
12. Click the ellipsis (…) button in the upper-right corner of the entry for the Invoice-Batch Number Report and select MANAGE. The Properties page for this report appears.
13. Click Parameters on the left side of the screen. The Parameter Management page appears.
14. Change the Default Value for the BillingCity parameter to Utonal.
15. In the Visibility column for the BillingCity row, select Internal from the drop-down list.
16. Check the Has Default check box in the StartDate row. Type 01/01/2015 for the default value for this row.
17. Check the Has Default check box in the EndDate row. Type 12/31/2015 for the default value for this row.
18. Click Apply to save your changes.
19. Click the Utonal folder link in the breadcrumbs.
20. Click the Invoice-Batch Number Report entry to run the report.
Now we have the linked reports working just the way we need them. Not only did we simplify things by not deploying the report in multiple places, but we also were able to hardcode parameter values for each linked report.
Key performance indicators (KPIs) provide a quick snapshot of the health of a given aspect of our organizations. KPIs consist of a goal value for a particular measure along with the current value of that measure. KPIs usually include some type of graphic—a gauge or an indicator—to help us instantly interpret the meaning of the current value as it relates to the goal value. Finally, the KPI may include a trend indicator to show how a value has changed over a period of time. Multiple KPIs are often collected together in a single dashboard display to provide a comprehensive look at the current state of one aspect of the organization or perhaps the organization as a whole.
We have seen KPI-style output several times as we authored reports in earlier chapters of this book. In Chapter 6, we created the Digital Dashboard paginated report to show the state of deliveries, lost packages, and repairs. In Chapter 10, we created the Mobile Delivery Dashboard mobile report displaying the same three values.
Both of these reports were saved in or deployed to the report server and can be viewed using the web portal. A user can navigate to the location of one of these reports and execute it to see the state of these KPIs. However, if these are truly key indicators for our organization, it would be great to present these values to our users as soon as they open the web portal. In fact, we can do that by creating KPIs right in the web portal.
In many ways, creating a KPI in the web portal is similar to creating a mobile report using Mobile Report Publisher. Like mobile reports, web portal KPIs do not have their own database query. KPIs use shared datasets, just like mobile reports.
Unlike mobile reports, a web portal KPI provides a visualization for a single measure. We can show the goal for that measure along with the actual value for that measure. We can provide a status indicator for the measure, and we can show the trend of that measure over the recent past. However, that is the extent of it. If we want to show another measure, we need to create a second KPI.
Let’s go ahead and create a KPI to show information on deliveries. We will use the same shared dataset we used for the Mobile Delivery Dashboard report.
1. Open the web portal in your browser, and navigate to the Home folder.
2. Click New in the web portal toolbar and select KPI. The New KPI page appears.
3. Enter Deliveries for KPI name.
4. Enter Number of Packages Delivered for Description.
5. Select Dataset field from the Value drop-down list.
6. Click the ellipsis (…) button in the Pick dataset field entry area for Value. The Pick a Dataset dialog box appears.
7. Click the entry for the Galactic Delivery Services folder.
8. Click the entry for the Shared Datasets folder.
9. Click the entry for MobileDashboard. You see the Choose a field dialog box.
The data in the dataset is displayed in the Choose a field dialog box. The top of the dialog box allows you to select the aggregation to use with the data in this dataset. We select one of the following aggregations to create a single value for our KPI:
Average
First
Last
Max
Min
Sum
The lower part of the dialog box shows the columns in the dataset.
10. The MobileDashboard dataset returns a single row, so any of the aggregations will work for us. We’ll leave the First aggregation selected. Click the option button for the NumOfPickups column.
11. Click OK to exit the Choose a field dialog box.
12. Select Dataset field from the Goal drop-down list.
13. Click the ellipsis (…) button in the Pick dataset field entry area for Goal. The Choose a dataset dialog box appears.
14. Click the entry for the Galactic Delivery Services folder.
15. Click the entry for the Shared Datasets folder.
16. Click the entry for MobileDashboard. You see the Choose a field dialog box.
17. Click the DeliveryTarget column.
18. Click OK to exit the Choose a field dialog box.
19. Select Dataset field from the Status drop-down list.
20. Click the ellipsis (…) button in the Pick dataset field entry area for Status. The Choose a dataset dialog box appears.
21. Click the entry for the Galactic Delivery Services folder.
22. Click the entry for the Shared Datasets folder.
23. Click the entry for MobileDashboard. You see the Choose a field dialog box.
24. Click the DeliveryStatus column.
25. Click OK to exit the Choose a field dialog box.
26. Select Not set from the Trend set drop-down list. The New KPI page should appear as shown in Figure 11-30.
Figure 11-30 Creating the Deliveries KPI
27. Click Create to complete the Deliveries KPI.
The completed Deliveries KPI will appear as shown in Figure 11-31. The KPI shows the number of deliveries in the current month and indicates this is 68 percent below the target. This puts our value in the yellow status, indicating there is room for improvement.
Figure 11-31 The Deliveries KPI
Let’s create a KPI for repairs and get a bit fancier this time. For our second KPI, we will include a drillthrough link. This will allow the user to click on the KPI and be taken to more detailed information on transport repairs. We will also include a trend chart in this KPI. To do this, we will need to create a new shared dataset with data for the trend in repairs from the previous 12 months.
1. In the web portal, navigate to the Galactic Delivery Services folder and then to the Shared Dataset folder.
2. Click New in the web portal toolbar and select Dataset.
3. Report Builder will open with the New Report or Dataset dialog box displayed. You should see the Galactic shared data source displayed. If you do not, click the Browse other data sources link and navigate to the Shared Data Source folder where we created the Galactic shared data source. Click the Galactic shared data source to select it.
4. Click Create. The Query Builder appears.
5. In the ribbon, click the Edit as Text button.
6. From the Command type drop-down list in the ribbon, select StoredProcedure.
7. Type the following in the area immediately below the ribbon:
8. In the ribbon, click the Run button (red exclamation mark) to execute the stored procedure. You will see 12 months of repair numbers.
9. Click the Save button in the upper-left corner. The Save As Dataset dialog box appears.
10. Navigate to the Shared Datasets folder.
11. For Name, enter Repair Trend.
12. Click OK.
13. Close the Query Designer dialog box.
14. Click the link for the Home folder in the breadcrumbs.
15. Click New in the web portal toolbar and select KPI. The New KPI page appears.
16. Enter Repairs for KPI name.
17. Enter Number of Transport Repairs for Description.
18. Select Dataset field from the Value drop-down list.
19. Click the ellipsis (…) button in the Pick dataset field entry area for Value. The Choose a dataset dialog box appears.
20. Navigate to the Shared Datasets folder.
21. Click the entry for MobileDashboard. You see the Choose a field dialog box.
22. Click the NumRepairs column.
23. Click OK to exit the Choose a field dialog box.
24. Select Dataset field from the Goal drop-down list.
25. Click the ellipsis (…) button in the Pick dataset field entry area for Goal. The Choose a dataset dialog box appears.
26. Navigate to the Shared Datasets folder.
27. Click the entry for MobileDashboard. You see the Choose a field dialog box.
28. Click the RepairTarget column.
29. Click OK to exit the Choose a field dialog box.
30. Select Dataset field from the Status drop-down list.
31. Click the ellipsis (…) button in the Pick dataset field entry area for Status. The Choose a dataset dialog box appears.
32. Navigate to the Shared Datasets folder.
33. Click the entry for MobileDashboard. You see the Choose a field dialog box.
34. Click the RepairStatus column.
35. Click OK to exit the Choose a field dialog box.
36. Select Dataset trend from the Trend set drop-down list.
37. Click the ellipsis (…) button in the Pick dataset trend entry area for Status. The Choose a dataset dialog box appears.
38. Navigate to the Shared Datasets folder.
39. Click the entry for Repair Trend. You see the Choose a field dialog box.
40. Click the NumRepairs column.
41. Click OK to exit the Choose a field dialog box.
42. In the Visualization area, click the Step visualization.
43. Select Custom URL from the Related content drop-down list.
44. KPIs can drill through to mobile reports or to a custom URL. In this case, we’ll use a custom URL to drill through to a paginated report. Enter the following in the Enter a URL text box:
where {computer name} is replaced with the name of the computer serving as the report server. We will learn more about how this URL was constructed in Chapter 13. The New KPI page should appear as shown in Figure 11-32.
Figure 11-32 Creating the Repairs KPI
45. Click Create to complete the Repairs KPI.
The completed Repairs KPI will appear as shown in Figure 11-33. The stepped line on the right side of the KPI shows the trend for the number of repairs over the previous 12 months. Click the Repairs KPI, and you see a dialog box containing a larger version of the KPI that includes the KPI description. Click the Related content link at the bottom of the dialog box, and a new tab will open displaying the Days in Maint report to provide more detailed information on transport repairs.
Figure 11-33 The Repairs KPI
As we saw in Chapter 2, it is possible to install Reporting Services to run in SharePoint Integrated mode. When Reporting Services is installed in this manner, the SharePoint user interface replaces the web portal. This has the advantage of allowing reports to be managed by the same tool that manages documents in the organization. In fact, reports appear to the end user as if they are just another document type like a Word document or an Excel spreadsheet.
For organizations where SharePoint is already being used, this has the advantage of providing the user with a familiar interface for locating and viewing reports. Likewise, it provides the administrator with an interface they are familiar with for deploying and managing reports. SharePoint Integrated mode operation also allows many of the SharePoint document management features, such as version control and routing and approval, to be applied to our reports.
NOTE
SharePoint Integrated mode cannot be used to host mobile reports. Keep this in mind when choosing between native mode and SharePoint Integrated mode architecture.
After installing Reporting Services in SharePoint Integrated mode, as described in Chapter 2, we need to prepare SharePoint by creating a document library that knows how to deal with Reporting Services objects. We also need to use the appropriate URLs for deploying reports from SSDT and Visual Studio, if we use that report authoring environment.
One of the features of SharePoint is that it knows what applications to use for viewing and editing the different types of documents that are stored in its document libraries. When working with Reporting Services reports, we want SharePoint to have that same knowledge so our users can seamlessly run and modify reports. To facilitate this, we need to create a document library with knowledge of the Reporting Services document types.
Follow these steps to create a document library ready to work with Reporting Services reports:
1. Open the SharePoint site where Reporting Services was set up. (This is the regular SharePoint site, not the Central Administration site.)
2. Click the Site Actions drop-down menu in the upper-left corner of the page.
3. Select New Document Library from the drop-down menu.
4. The Create dialog box appears. Enter a name and a description, if desired.
5. Set the Navigation and Document Version History settings as desired.
6. If you are managing a number of different document types in this library, set the Document Template appropriately. If you are managing only Reporting Services reports in this document library, set Document Template to None. The completed Create dialog box should appear similar to Figure 11-34.
Figure 11-34 The document library Create dialog box
7. Click Create to exit the Create dialog box. After a bit of processing, you will be taken to your new document library.
8. On the right side of the document library ribbon, select Library Settings. The Document Library Settings page appears.
9. In the General Settings area, click Advanced settings. The Advanced Settings page appears.
10. In the Content Types area, select yes to allow the management of content types.
11. Scroll down and click OK. You will return to the Document Library Settings page.
12. Scroll down to the Content Types area, and click the Add from existing site content types link. The Add Content Types page appears.
13. In the Select site content types from: drop-down list, select SQL Server Reporting Services Content Types. Three content types are now available for you to select: Report Builder Model, Report Builder Report, and Report Data Source.
14. Double-click Report Builder Report to move it to the Content types to add: list.
15. Double-click Report Data Source to move it to the Content types to add: list. The Add Content Types page should appear as shown in Figure 11-35.
Figure 11-35 The Add Content Types page
16. Click OK. You will return to the Document Library Settings page.
17. Scroll down to the Content Types area, and click the Change new button order and default content type link. The Change New Button Order and Default Content Type page appears.
18. If you intend to manage only Reporting Services reports in this document library, uncheck the Document check box.
19. Set the Position from Top drop-down list for Report Builder Report to 1.
20. Set the Position from Top drop-down list for Report Data Source to 2. The Change New Button Order and Default Content Type page should appear as shown in Figure 11-36.
Figure 11-36 The Change New Button Order and Default Content Type page
21. Click OK. You will return to the Document Library Settings page.
22. Navigate to your new document library.
23. Go to the Documents tab of the ribbon.
24. Click the New Document drop-down menu in the ribbon. Note the two choices are Report Builder Report and Report Data Source, just as we configured. This is shown in Figure 11-37.
Figure 11-37 The New Document drop-down menu on the Documents tab of the ribbon
You can select Report Builder Report from the New Document drop-down menu to launch Report Builder and create a new report. You can select Report Data Source from the New Document drop-down menu to be taken to the page for defining a new data source. As an alternative to creating items from within SharePoint, we can also upload our data sources and reports from SSDT or Visual Studio. We look at this in the following section.
NOTE
To copy a report saved on a native mode report server to a SharePoint Integrated mode report server, use the web portal to download the report definition to a file as discussed earlier in this chapter. Then, use the upload document button in SharePoint to upload the document. Any shared data sources in the uploaded report will need to be pointed to shared data sources in the SharePoint library.
Reports can be uploaded to a SharePoint document library in a manner similar to the way they are uploaded to a report server running in native mode. The only difference is the URLs specified in the Project Properties dialog box.
When working with Reporting Services running in SharePoint Integrated mode, the TargetServerURL is set to the URL for the SharePoint site. The settings for the reports, data sources, and so on are the URL for the SharePoint site followed by the name of the document library within the SharePoint site where each type of item should be stored. The example in Figure 11-38 shows all item types going to the same SSRS Reports document library.
Figure 11-38 The properties dialog box for deploying to a SharePoint Integrated mode server
Once reports are in place in the SharePoint document library, they are managed in much the same manner as they would be in the web portal. To manage the various aspects of a report, hover over the report entry in the document library and click the drop-down button to display the list of options available for managing the report. This is shown in Figure 11-39.
Figure 11-39 The report management menu in SharePoint
As with other documents in SharePoint, you can control access to a report using the Manage Permissions option available on the report management menu. Users are assigned to a SharePoint group to gain rights to an item. SharePoint offers three built-in groups that function similarly to the built-in groups in Reporting Services native mode:
Site Owners have full control over reports and other items (similar to Content Manager rights in native mode). Site Owners can create, modify, and delete content, as well as control who has rights to that content.
Site Members can create reports and other items (similar to Publisher rights in native mode). Site Members can create, modify, and delete content, but cannot control the access rights of others.
Site Visitors can view reports (similar to Browser rights in native mode). Site Visitors cannot modify any site content.
Data Alerts were a new feature of SQL Server Reporting Services 2012 running in SharePoint Integrated mode. A data alert enables Reporting Services to generate an e-mail whenever the data in a report meets a certain set of criteria. This feature allows a user to be notified of a certain situation reflected in the data, rather than constantly monitoring a report to look for that situation to occur.
Data alert definitions are created within SharePoint using the Data Alert Designer. This definition includes one or more rules that must be met in order to trigger the data alert. Once created, these definitions are stored in the SQL Server alerting database. The SQL Server alerting database is automatically created when Reporting Services is installed in SharePoint Integrated mode.
A SQL Agent job is created for each alert definition. The recurrence pattern defined for the data alert determines the execution schedule for the SQL Agent job. The job causes the following steps to be executed each time it runs:
1. The report is executed to generate the data in the data feeds.
2. The rules are applied to the specified data feed.
3. If one or more rules are satisfied, the alert e-mail is sent.
If a data alert is being specified for a report that requires parameters, those parameter values must be provided at the time the data alert definition is created. These parameter values are stored with the data alert definition. They are used each time the report is subsequently executed to generate the data in the data feeds.
If an error should occur during the processing of a data alert definition, an e-mail detailing the error is sent to the normal recipients of the data alert.
A Reporting Services report must meet certain requirements before data alerts can be defined for that report. Those requirements are
The report must be deployed to a SharePoint Library with Reporting Services operating in SharePoint Integrated mode.
All of the data sources used by the report must use stored credentials or must require no credentials. Windows integrated security and the prompt for credentials data source security options are not supported by data alerts.
The report must contain at least one data region. The data regions create the data feeds for the report. It is the data feeds that make data alerts possible.
The data in the report must change over time, even when the same set of parameters is entered.
This last item is important to understand. If the report always returns the same data for a given set of parameters, then either an alert is always generated with each recurrence because the rules are always satisfied or an alert is never generated because the rules are never satisfied. Instead, we need a report whose data will change over time, even when the same parameters are passed to it. This type of report will yield interesting results when data alerts are applied.
Once you have a report meeting the specified requirements deployed to a SharePoint library, you can create a data alert definition using the following steps:
1. Click the report to execute it.
2. Provide values for the parameters, if there are any.
3. Once you have executed the report, select New Data Alert from the Actions menu in the upper-left corner as shown in Figure 11-40.
Figure 11-40 Creating a new data alert
The New Data Alert dialog box appears. The upper-left corner of the dialog box contains the Report data name drop-down list. This drop-down list enables you to select the data feed whose data will be used by the rules in this data alert definition. The data feeds are named for the data regions in the report that created each of them. This is one case where taking the time to give meaningful names to a data region and its constituent parts might be a good idea.
Below the Report data name drop-down list is a grid showing the data in the selected data feed. The first 100 rows in the data feed are displayed. This provides you with some context as you create the rules for the data alert definition.
1. Select the desired data feed using the Report data name drop-down list.
2. On the right side of the New Data Alert dialog box, specify a meaningful name for the alert in Alert name. This will be used to identify this alert as it is managed in the future.
3. You can have the data alert send an alert e-mail when any of the data in the data feed has met the rules or if no data in the data feed has met the rules. Click the Alert me if any data has item to toggle between these two options.
4. Click Add rule to begin adding rules to the data alert definition. A list of all of the columns in the data feed appears.
5. Select the desired column for this rule. (In the example shown in Figure 11-41, we are using the value being represented on a linear gauge on a dashboard.)
Figure 11-41 A completed data alert definition
6. Click the “is” comparison operator and select the comparison operator you wish to use for this rule.
7. To the right of the comparison operator, enter a constant value or select another column from the data feed to complete the rule. Depending on which comparison operator you are using, you can specify multiple values using the ellipsis (…) button.
8. Click Add rule to define additional rules for this data alert definition, if desired.
9. Use the Recurrence pattern area to define the frequency with which this data alert definition should be executed.
10. You can use the Advanced area to set a start and/or a stop date for the execution of this data alert definition. The Advanced area also lets you specify whether the alert e-mail should be sent every time the data alert is executed and the rules are met or only when the rules are met and the data has changed since the previous execution.
11. Use the Email settings area to specify the recipients of the alert e-mail, along with the subject and any content you would like to appear in the body of the e-mail.
12. Click Save when your data alert definition is complete.
To manage existing data alerts, hover over the report in the SharePoint library and select Manage Data Alerts from the drop-down menu.
In this chapter, you learned how to put the reports where your users could come and get them. Your users were set up to pull the reports off the report server. In the next chapter, you learn how to deliver the goods right to the users. In Chapter 12, the report server pushes the reports out to the users. The pull and push capabilities combine to give Reporting Services some powerful tools for putting information in the hands of the users, right where it needs to be.