SSRS and MOSS 2007: Deploying the Power

Last month, in “Information Integration: SSRS and MOSS 2007,” I introduced you to the rich information-integration environment available with SQL Server 2005 Reporting Services (SSRS) SP2 and Microsoft Office SharePoint Server (MOSS) 2007 Enterprise Edition. After looking at the technical architecture that takes reporting to new levels, I walked you through installing and configuring the components required to integrate SSRS and MOSS.

This month, in the second article in this two-part series, l show you how to perform familiar SSRS tasks—such as deployment, security setup, and property management—in the new MOSS environment. I also explain how to implement new MOSS-enabled reporting features such as versioning, workflow, alerts, and information management policies as well as how to use information-integration features. Taking advantage of SSRS and MOSS integration will not only let your information workers more easily find, use, and share information across the enterprise, but it will also simplify your report management and security implementation tasks.

Deploying Reports and Data Sources

To deploy resources from Business Intelligence Development Studio (BIDS), you must first install SP2 on each developer’s workstation to update the designer tools. These updated tools add the components that the design environment requires to connect to MOSS for report, model, and data source deployment. You’ll still be able to deploy items to a native-mode SSRS instance if you’re maintaining multiple report server instances.

You can deploy report definitions in MOSS integrated mode in two ways. First, you can use BIDS to deploy them directly to the MOSS libraries by changing your report server project’s properties to reflect the target document libraries, as Figure 1 shows. Notice that the folder references are different from nativemode deployment requirements. Even when deploying to the same server, you must specify the server name explicitly in the URLs instead of using localhost. Alternatively, you can deploy reports by opening the document library, clicking Upload, and selecting a report definition file to add to the library.

You can also deploy report data sources from BIDS, but you can’t upload them directly to the Data Connections library. If you choose not to use BIDS deployment, your only other option is to manually create the data source—a method some organizations require. For manual creation, navigate to the Data Connections library. On the New menu, select Report Data Source. On the Data Source Properties page, assign a name, provider type, connection string, and credentials. Windows Authentication (Integrated) credentials work only if you’ve enabled and configured Kerberos in your domain.

MOSS adds all new data sources in Pending mode, which means that only the author and users with Manage Lists permission can use the data source until it is approved. To change the status of the data source, point to the data source name, click the down arrow to open the context menu, and click Approve/Reject. On the Data Connections page, you can set the status as applicable. As long as the data source remains pending, users will get an rsAccessDenied message when executing a report linked to the data source.

To deploy a report model to a library, change the report model project properties, as Figure 2 shows. The report model is accessible for ad hoc reporting when a user with the appropriate permissions opens the applicable document library and selects Report Builder Report on the New menu. When Report Builder opens, it displays a list of report models in the current library, but the user can change to an alternative location to list other available models. Users can save Report Builder Reports to a document library as long as they have permission to do so.

Setting Up Security

When you’ve deployed your reports and data sources, you’re ready to address security. The most straightforward approach to defining security policies for your reports is to use the default SharePoint groups and permission levels. Table 1 describes the default security policies by group and permission level.

SharePoint security starts at the top-level site, with libraries and library contents inheriting security policies for ease of maintenance. Within the site, you map Windows groups or users to a permission level for each library or library item—much like using the role-based security model in native mode. To override the inherited permissions for an entire selected library, open Internet Explorer, navigate to the Reports library on your MOSS site (e.g., http://your_server/ReportsLibrary), click Settings, and then click Document Library Settings. On the Customize Reports Library page, in the Permissions and Management section, click Permissions for this document library. On the Permissions page, you can add or remove users and change permissions. To add a new user, click Actions, click Edit Permissions, and click OK to confirm that you want to override site permissions and create unique permissions for the Reports library. Now, you can add new users by clicking New and providing user or group names. You then either associate the new user or group with an existing SharePoint group so that the user inherits the appropriate permission level, or you can explicitly assign a permission level. To remove a user, on the Permissions page, select the check box to the left of each user you want to remove, and then click Actions, click Remove User Permissions, and click OK to confirm. To edit permissions for a user or group, simply click the user or group name and select the applicable permissions on the Edit Permissions page.

If you need to manage security for each report separately, you can open a report’s context menu (by clicking the down arrow to the right of the report name), click Manage Permissions, and edit the permissions to apply item-level security. Simply click Site Actions in the upper right corner, point to Site Settings, and click People and Groups. Select the current name in the page title, select a different group from the list on the left side of the page, and then use the New menu to add new users or groups to the selected group. Of course, you can also create your own SharePoint groups and permission levels. On the Settings menu, click Set Up Groups to create a new group and assign a permission level. To create a custom permission level, click Site Permissions on the left of the Site Settings page, and then from the Settings menu, click Permission Levels. On this page, you can add a new permission level or edit existing permissions levels to apply very granular security policies.

If you’re using report models, you already know that you can define model item security in the Model Designer, but now you can also use SharePoint if your login is in a group that’s been assigned Full Control. From the report model’s context menu, click Manage Model Item Security. In the Model Item Security page, you explicitly add permission at the root node for all Windows groups or users authorized to use the model. Entities and attributes inherit this permission. Then, for any group or user not authorized to view a particular entity or attribute, you can select that item in the model tree and assign permissions to all other groups or users that have authorization. Note that you can’t deny access to a user; instead, you must explicitly grant permissions to a user or allow the user to inherit permissions from a parent item.

Managing Report Properties

After you switch the report server to integrated mode, you can no longer use Report Manager or SQL Server Management Studio (SSMS) to manage report properties. Reports in integrated mode have the same properties you use to manage reports in native mode. A report has a context menu, which Figure 3 shows, to let users with either Contribute or Full Control permission access all report properties. Users with only Read permission have a more limited set of options on this menu.

If you use shared schedules for report snapshot generation or subscription delivery, you need to set up and manage these schedules across the site. Just click Site Actions, point to Site Settings, click Modify All Site Settings, and click Manage Shared Schedules in the Reporting Services section. Here, you can also pause or resume existing shared schedules. You use a report’s Subscription Properties or Manage Processing Options page to manage custom schedules, but keep in mind that, unlike with shared schedules, there’s no central location for managing all custom schedules.

One of the first new capabilities you should consider implementing in integrated mode is report metadata. With native SSRS, you can store as metadata only the name and description fields. But in integrated mode, you can add more report metadata fields to help users find and use the reports they need. Simply open the Settings menu in the document library, and click Create Column. In the Create Column page, you can define a name, data type, and field length and specify whether the column is required. You can even provide a default value. Note that even if you specify a column as required, reports can be deployed and approved without satisfying this requirement. However, the next time you use the Edit Properties command on a report, you must provide a value for the required column to save any other property changes you make.

MOSS also includes auditing and expiration features. To learn more about implementing these features, see the Web-exclusive sidebar “Enabling Information - Management Policies.”

Managing Versions

MOSS integrated mode brings new version-management capabilities to SSRS. Versioning is managed at the library level, so open the applicable document library, and select Document Library Settings from the Settings menu. In the General Settings section, click Versioning Settings. In the Document Library Versioning Settings page, which Figure 4 shows, you can configure several options to control the status and visibility of new content, including whether a newly uploaded report must be approved before it’s visible to all users with access to the library. This feature is great for testing the appearance and behavior of a report on the server before making it available to everyone.

The process for approving a report is slightly different than approving a data source because the approval mechanism isn’t available from the report’s context menu. Instead, an authorized approver must open the context menu, click View Properties, and then change the status to Pending. Only then does the report’s context menu include the Approve/Reject command.

You can also specify whether report edits generate a new version and, if so, whether versions should be tracked as major versions or as major and minor versions. To prevent versioning history from spiraling out of control, you can limit the number of versions that can accumulate.

You can also require that a report be checked out before it can be edited. If you enable this requirement, edits become changes to the report’s properties that you perform by selecting Edit Properties on the report’s context menu. Using Check In and Check Out prevents two people from simultaneously making changes to the same item. Be aware that you can redeploy a report from BIDS without first checking it out, but each deployment of the report updates the versioning history.

You can’t render a previous version of a report in MOSS, but you do have access to the report definition language (RDL) for that version if you open the version’s context menu and click View. Just copy and paste the RDL into a report’s code page in BIDS, and then preview the report in that environment, as long as the data source and queries in the report are still valid. From the version history list, you can use the Restore command on the context menu to replace the current version with a previous version. However, you first need to check out the report before you can revert to a previous version.

Implementing Approval Workflow

Although you can use versioning to require approval of new or updated content, notifying users that approval is required doesn’t happen without some additional upfront configuration. First, open the Shared Services Administration page in SharePoint 3.0 Central Administration, add a new shared services provider (SSP) if you haven’t already, and restart IIS to implement the session state settings. Open your Reports document library, open the Document Library Settings from the Settings menu, and then in the Permissions and Management section, click Workflow Settings. MOSS provides an Approval workflow template that you can easily implement. Just assign a unique name to the workflow and select one or more triggers, such as the addition of a new report or a change to an existing report.

As part of the workflow configuration, you define whether users work on their assigned task in parallel or sequentially and whether they can reassign their task or request a change before completing the task. You also define which users are designated as approvers and the sequence in which they will receive notification of their task if you’ve set up a sequential workflow. As an optional setting, you can notify other users about the workflow assignment for informational purposes.

You can define a due date as a specific date if you created a parallel workflow or as a specific number of days or weeks if you created a sequential workflow. Either way, if you’ve enabled email on the server on the Outgoing E-mail Settings page of Central Administration, users will receive reminders if their assigned task isn’t complete by the due date.

When you’ve configured the workflow for a document library, any subsequent activity that triggers the workflow generates an email to all assigned users for a parallel workflow or to the first user in a sequential workflow. The email contains a link to the report to review and an Edit This Task button to open the task page in MOSS and let the user approve or reject the report.

The workflow is complete when all users have finished their assigned tasks, and you can have the workflow automatically update the status of the report to approved. When circumstances (such as rejection of the report or a change to the report after the workflow started) prevent users from completing their tasks, you can configure the workflow to cancel itself. And you can always manually terminate the workflow by opening the report’s context menu, selecting Workflows, and navigating to the workflow you want to cancel.

Workflow notifications let users collaborate on the quality assurance process before making reports available to everyone. But what about users who want to know when a new report is available or when a favorite report has changed? Fortunately, you don’t have to set up a notification system for this group of users because MOSS includes a self-service alert system, but you should try out the system so you can show others how it works. For information about setting up alerts, see the Web-exclusive sidebar “Creating Alerts.”

Using Dashboards to Integrate Information

The simplest way to get started with information integration is to add a new dashboard page to the Reports document library. You then configure the Web Parts provided by the dashboard template, delete any Web Parts you don’t want to use, and add new Web Parts to bring together information from disparate sources, such as SSRS reports, Microsoft Excel workbooks, and other related content and links. You can rearrange the Web Parts on the page to achieve just the right layout for your business information. Let’s look at some of the key information-integration features available with MOSS.

SQL Server Reporting Services Report Viewer Web Part. For your SSRS reports, use the SQL Server Reporting Services Report Viewer Web Part. This new Web Part, included in MOSS, features different capabilities than the Web Parts packaged with SSRS for use in previous versions of SharePoint. Like all Web Parts, the Report Viewer Web Part includes an Edit menu that you use to configure settings. Your first task is to select Modify Shared Web Part on the Edit menu to open the tool pane for the Web Part. In the Report Viewer tool pane, you provide the full URL for the report, such as http://your_ server/ReportsLibrary/Your Report.rdl if your report is in the standard Reports document library. You can use a Browse button to navigate through the document libraries in your site and locate the desired report.

You use the View section of the tool pane to control the appearance of SSRS features. For example, you can use the Toolbar drop-down list to display the full toolbar in the report, enable only the navigation features of the toolbar to support moving from page to page, or hide the toolbar altogether. The Prompt Area drop-down list lets you display parameters when the report opens, require the user to open the parameter area of the report, or hide the parameters completely. Similarly, you can use the Document Map drop-down list to display, collapse, or hide a document map for the report.

You can override the report’s parameter values in the Web Part by expanding the Parameters section of the tool pane. Click Load Parameters, select Override Report Default, and select a new parameter value. This has no effect on the report’s parameter settings in the document library.

The remaining sections of the tool pane are properties common to all Web Parts. In the Appearance section, you can give the Web Part a title and configure its size and chrome (the title and border). The Layout section features properties for changing the Web Part to a different zone on the page and a different sequence within a zone when multiple Web Parts occupy the same zone. Finally, in the Advanced section, you can limit what users can do with the Web Part, such as minimizing or hiding it.

Target Audience. You might also want to implement the target audience property. By using a target audience on Web Parts, you can build one dashboard page with many elements and then enable specific Web Parts only for members of the target audience. You can define the audience as a group in the Global Audience, a distribution/ security group, or a SharePoint group. If a user who has permission to view the page but is not a member of the target audience opens the dashboard, that user doesn’t see the targeted Web Part. The visible Web Parts are rearranged to occupy the space of the missing Web Part.

Other Web Parts. When you have one or more SSRS reports in the dashboard, you can start the first stage of information integration by adding related content in additional Web Parts. A popular Web Part is Excel Web Access, which displays selected sections of an Excel 2007 workbook. You can also use Key Performance Indicators (KPIs) to display graphical indicators based on data from SharePoint lists, Excel, Analysis Services cubes, or manual entry. If you plan to use PerformancePoint Server 2007 (PPS) for scorecards, you’ll be able to include a Web Part for displaying the scorecard in your dashboard. You might also want to implement a Business Data Catalog (BDC) application on your MOSS server and use it to search and display line-of-business (LOB) data alongside the summary information captured in reports and workbooks.

Filters. You start the next stage of information integration when you add Filter Web Parts to synchronize the data that selected Web Parts display. You can use filters only with some Web Parts: Excel Web Access, KPI List (for KPIs based on Analysis Services 2005 data only), BDC item or list, Report Viewer, and eventually Scorecard Viewer. You connect these Web Parts to a common Filter Web Part and define the source values for the filter. Depending on how you configure the filter, the user can type a value directly, which I don’t recommend, or select from a list of values that you configure. This list of values can be populated from a list that you build manually for the filter, a SharePoint list or profile, an Analysis Services query, a BDC, or a SQL query.

Let’s say you want to create a filter that connects to a Report Viewer Web Part and a PivotTable filter in Excel Web Access to change the information displayed to a different year. Both your report and the Excel workbook must have a parameter for year, although the parameters don’t need to share the same name. You can create a parameter in the workbook by naming the cell that displays the current report filter. Then, when publishing the workbook to Excel Services, use the Excel Services Options button to access a dialog box that lets you select the named cell as a parameter. After adding the workbook to an Excel Web Access Web Part in the dashboard, click Add a Filter in the Filter Zone on the far left side of the dashboard. Select the Filter Web Part, and then after the Web Part is added to the page, open its Edit menu, and click Modify Shared Web Part to set the values for the filter. Each Filter Web Part has a different way of configuring values for user selection, but it’s a very straightforward process to set up.

To connect the filter to other Web Parts, open the filter’s Edit menu, point to Connections, point to Send Filter Values To, and then click the Report Viewer Web Part. In the Configure Connection dialog box, select the applicable report parameter, and click Finish. Repeat these steps, but this time, select Excel Web Access. In the Choose Connection dialog box, in the Connection Type drop-down list, select Get Filter Values From, click Configure, confirm that the year parameter is selected, and click Finish. Be sure that the filter values you supply are consistent with the format of the corresponding values in the target Web Part or the filter will fail.

When you’re developing a dashboard, only you can view it. You can check it in to change its mode so that users authorized to view drafts can review your dashboard. When you’ve finished developing the dashboard, you can use the Submit for Approval button to start a workflow or use the Publish button to make it available to users with access to the library.

To verify the filter, open the dashboard, click the Browse button to the right of the filter value, make a new selection, and then click the Apply Filters button. The connected Web Parts receive the same filter value and filter the displayed information accordingly, as Web Figure 1 shows. Notice that a user can set a default parameter value for each Filter Web Part to personalize the page.

Why Wait?

With this guide to installing, configuring, and deploying the SSRS and MOSS integrated architecture and to implementing MOSS’s new information-integration features, you’re equipped to deliver a reporting environment that supports better information access and integration across your enterprise. Whether you use some or all of MOSS’s information-integration features, the valuable integration of SSRS and MOSS will help your business users find what they need, analyze results, and share their insights for better decision-making.