One of the most vital aspects of business intelligence is reporting. Organizations rely on reporting engines to deliver key information in a timely fashion. These engines need to allow users to create and deliver a variety of reports based on customer requirements. SQL Server 2012 Reporting Services (SSRS) provides a complete range of tools and services to meet those customer requirements.

If you’ve designed reports using any version of SSRS, you’ll probably be familiar with some of the topics. However, if you are new to SSRS 2012, we recommend you read the overview of SSRS beginning at http://msdn.microsoft.com/en-us/library/ms159106(v=sql.110).aspx.

What Will You Learn in This Chapter?

By the end of the chapter, you’ll learn about

• Architecture of Reporting Services
• Setting up the infrastructure for Reporting Services
• Data source management
• Authoring reports using reporting tools
• Publishing and subscribing to reports
• Integration with SharePoint
• Creating a Power View report
• Interacting with Power View reports

Software Prerequisites

To get the most out of this chapter, you’ll need the following software:

• SharePoint Server 2013 Enterprise Edition
• SQL Server 2012 SP1
• Adventure Works Database (AdventureWorksDW2012 Data File, AdventureWorks Multidimensional Models SQL Server 2012 and AdventureWorks Tabular Model SQL Server 2012 ) downloadable at http://msftdbprodsamples.codeplex.com/releases/view/55330
• SQL Server 2012 Data Tools
• Silverlight Browser Add-In (Required for Power View Reports)

Introduction to Reporting Services

Reporting Services is a server-based platform that facilitates reporting with ready-to-use tools to author, manage, deploy, and deliver reports using Web or Windows applications. Using Reporting Services, you can retrieve data from various data sources, publish reports, and view or export them in various formats. Reporting Services also includes a central management capability through which you can manage reports and report security.

Reporting Services delivers end-to-end processing and management of reports, with solutions that address a wide range of scenarios. You can take advantage of the out-of-the-box features using built-in tools or write custom code using the API. In this way, the product meets the needs of different audiences—report administrators, report authors, and business users. Although they all essentially share same tools, the Reporting Services API helps developers extend the features and functionality.

Here are some of the key features of Reporting Services:

• Builds reports from a range of data sources
• Creates a variety of reports, (including tabular, matrix, and charts) to meet different needs (such as column-based, with summarized data, with graphical data, and more) using built-in templates
• Supports ad hoc reporting with report templates using a ClickOnce application called Report Builder
• Lets users create reports, save them locally, publish them to a SharePoint site or Report Server, and subscribe to these reports
• Supports interactivity, drill-down, and linking to subreports
• Supports eight presentation formats—HTML, MHTML, PDF, XML, CSV, TIFF, Word, and Excel—to open or export a report
• Allows embedding custom or third-party controls with custom report extension(s) in reports
• Allows adding bookmarks and document maps in large reports
• Aggregates and summarizes data using controls and expressions
• Supports embedding images and external content

Now let’s take a look at the Reporting Services architecture.

Architecture

The Reporting Services architecture consists of three major components:

• Report Manager: This is the front end of Report Services, the UI that provides access to the Reporting Services web service, which in turn connects to the Report Server database.
• Web Service Components: These components handle and process all on-demand and interactive requests from report manager and the other tools. They act as a programmatic interface for any custom development and interact with background processing applications.
• Background Processing Applications: These are Windows service components that process reports triggered from a schedule and deliver them based on processing extensions. There are several processing extensions to support different operations:

• Security extensions handle authentication and authorization of users and groups. While the default security extension is Windows authentication, you can write a custom authentication model and replace the default.
• Data processing extensions are used to interact with different data sources. They perform query operations as they process query requests from the report processor component. While default data processing extensions are used to connect to many data source types, you can also write your own custom data processing extension.
• Rendering extensions are those that render data and the layout of the report from the report processor in one of the seven supported formats mentioned earlier. Report Server processes tables, charts, matrices, and more by default. When you need to add a custom feature to a report, you can design and create a custom report processing extension.
• Delivery extensions are used to deliver reports to specific destinations. For instance, an e-mail delivery extension delivers a scheduled report to specified e-mail address through SMTP.

All of these extensions allow users to create custom features in the product. For example, you can create and install a custom rendering extension to support a new output format. Given all these extensions, on a high level the Reporting Services service and component architecture looks like what’s shown in Figure 3-1.

Information about reports, report properties, and objects are stored in a SQL Server database, which also holds report models, published reports, and the folder hierarchy managed by Report Server. This database provides internal storage information for single or multiple report servers. This all arrives as an installation package with SQL Server 2012.

Setting Up the Infrastructure

After you’ve finished the basic steps for installing SQL Server, such as verification, entering the product key, accepting licensing terms, and installing setup support files, you can then choose features as shown in Figure 3-2. If you are installing SQL Server for the first time, you can select Reporting Services, along with Database Engine Services and others features. If SQL Server is already installed and you are setting up Reporting Services now, rerun the SQL Server setup and choose Reporting Services in the Features pane.

In the next several steps, you configure a number of items, including the installation rules, the SQL Server instance, and the service account for the server. When you’ve completed the basic installation, you then go through Database Engine Configuration, Analysis Services Configuration, and Reporting Services Configuration. For Reporting Services Configuration, you can choose among three options as shown in Figure 3-3.

Here are the details about the three options you have for on the Reporting Services Configuration screen:

• Install the native modedefault configuration: Select this option to install the report server in its native mode, irrespective of SharePoint. If you choose this option, though, you can technically use report server after installation, but you can’t run it in a SharePoint environment. However, the Report Viewer Web Part can load native-mode reports. Skip this option because you will be using the Reporting Services SharePoint mode.
• Install the SharePoint modedefault configuration: Choose this option to install report server in SharePoint mode. You will also need to install and configure the Reporting Services Add-in for SharePoint products. For this mode to be operational, SharePoint must be available on the same computer. If it is not, choose the next option. The SharePoint mode report server is installed as a SharePoint Shared Service, and it allows Reporting Services to use the enterprise and administrative features of SharePoint.
• Install, but do not configure the report server: Choose this option to install but not configure Reporting Services. When you decide to configure, you can simply run the Reporting Services Configuration tool to set the various options.

Now that Reporting Services and the Reporting Services Add-in are installed, you can configure your SharePoint environment for Reporting Services by following the article at http://msdn.microsoft.com/en-us/library/jj219068.aspx.

To begin authoring reports, it is important to understand the features offered by the Reporting Services Add-in and to choose the right mode (Local vs. Connected) for your reports. It is more likely that you will use connected mode for enterprise reporting scenarios.

In previous versions of SQL Server, the Reporting Services Add-in was available as a standalone installer. But in SQL Server 2012, this is bundled as part of the standard installation package. Let’s take a look at the features offered by this add-in.

Data Source Management

To consume data or add it to your reports, you will have to create a connection to a data source. To create a data source connection, you need three elements: a data source type, connection information, and credential information. A data source type is a connection type, such as a Microsoft SQL Server, SharePoint List. Connection information contains details about the name and location of the data source. And credentials with permissions over the data source are required in order to access the data.

Data sources can be either shared or embedded. Shared data sources are necessary when a data source is used often by more than one report. Embedded data sources are part of the report itself and can’t be shared with others. Table 3-1 compares shared and embedded data sources.

Authoring Tools

You can author reports with data sources using Report Builder or Report Designer from SQL Server 2012 Data Tools (SSDT). The key difference between the two is that in Report Builder you use the ribbon to insert and configure report items, while in Report Designer you use the SSDT toolbox to perform those tasks. However, both tools generate reports in report definition language (rdl). Table 3-2 compares the tools.

Even a brief glance at the products, as in Table 3-2, suggests that SSDT provides many more features than Report Builder 3.0. To get more details about the features of both, visit http://msdn.microsoft.com/en-us/library/dd207010.aspx. Now, let’s author a basic report using SSDT.

Building and Publishing a Basic Report

When you’re authoring a report and publishing it to your SharePoint environment, it is important to choose the right tool. We will use SSDT Report Designer for the following example and later edit the published report using Report Builder.

http://yoursharepointsite/_layouts/15/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/Report%20Library/rptInternetSales.rdl&Source=http%3A%2F%2Fbimain%3A1111%2FReport%2520Library%2FForms%2FAllItems%2Easpx

Using Report Builder to Edit a Published Report

Now you’ve seen how to author and publish a report using the SSDT Report Designer. You can edit the same published report using Report Builder 3.0:

1. Click “Edit in Report Builder” from the ECB menu of the report item (Figure 3-12). You can also edit the report after you open it by clicking on “Open with Report Builder” on the Actions menu as shown in Figure 3-13.

   

   Figure 3-12. Edit in Report Builder from the ECB menu of the report

   

   Figure 3-13. Opening Report Builder from the Actions menu

2. Once you choose one of those options, the click-once application (Figure 3-14) will launch Report Builder and request user permission to run the application, as shown in Figure 3-15.

   

   Figure 3-14. Launching Report Builder

   

   Figure 3-15. Asking user permission

3. Click the Run button to download and run the Report Builder application. Once it loads, you‘ll see the rptInternetSales.rdl file open in design mode. Let’s add some additional functionality to this report now.
4. Select the Insert tab on the ribbon, and click on Chart from the Data Visualization section. Choose Chart Wizard from the drop-down menu.
5. In the New Chart Wizard window, select the option “Choose an existing dataset in this report or a shared dataset” and click on DataSet1. Click Next to continue.
6. Specify the chart type as Pie, and click Next to continue.
7. In the “Arrange chart fields” window, from the “Available fields” list, select Internet_Sales_Amount and drag it to the Sum Values section as shown in Figure 3-16. Then select Subcategory, and drag it to the Categories section and click on Next.

   

   Figure 3-16. Arrange the chart fields for a report using Report Builder

8. Select the Generic chart style in the “Choose a style” window, and click Finish. Once the chart is added to the report canvas, adjust it to fit under the table that already exists, as shown in Figure 3-17.

   

   Figure 3-17. Report Builder 3.0 with the report file loaded

9. Click the Home tab, and then click the Run button in the Views section to preview the report. To return back to the design mode, simply click on the Design button in the Views section of the Run tab.
10. From the File menu, click the Save button, and then click on Exit Report Builder.
11. Return to Report Library on your SharePoint site, and click on rptInternetSales item to view the output, as shown in Figure 3-18.

    

    Figure 3-18. Report rendered in the browser using the Reporting Services viewer

Although this report is loaded and rendered using the Reporting Services viewer page, some situations might require you to add the report to a specific custom page instead. In that case, you can load the report to any page using the Report Viewer Web Part, which is available when you install the Reporting Services Add-in to your server. The Report Viewer Web Part provides the same functionalities as the Reporting Services viewer page, such as opening with Report Builder, subscribing to the report, retrieving a report rss feed, and so forth. Let’s see how to load reports using the Report Viewer Web Part.

Loading Reports Using the Report Viewer Web Part

1. Click on the Settings button from the top right corner Add a Page provide a page name in the Add a page creation window.
2. Once the page is created, from Editing Tools Insert, click the Web Part button on the Ribbon menu.
3. From the Categories section, select SQL Server Reporting. In the Available Web Parts section, select the SQL Server Reporting Services Report Viewer Web Part and click Add.
4. After the Report Viewer Web Part is added to the page, as shown in Figure 3-19, you can configure the options.

   

   Figure 3-19. SSRS Report Viewer

5. Click the “Click here to open the tool pane” link, and in the editor window, click the Browse button next to the “Report input” text box.
6. Browse to the Report Library document library, choose the rptInternetSales.rdl file, and click OK.
7. Expand the View settings in the editor window, and choose the options you’d like for the report viewer. For now, let’s leave them at the default values.
8. Click OK.
9. Save and close the page. Notice that the report is loaded in the Report Viewer as shown in Figure 3-20.

   

   Figure 3-20. The SSRS Report Viewer with the Internet Sales report loaded

Is there a way to create a new report using Report Builder? Yes! And it’s quite simple, too. When you install Reporting Services in SharePoint integrated mode, you will have Reporting Services predefined content types automatically enabled on the site. Let’s see what different content types are available and how to use them on your SharePoint site.

Reporting Services Content Types

Reporting Services content types are enabled on SharePoint sites that are integrated with Reporting Services, which you can use to manage Report Builder reports (.rdl) and shared data sources (.rsds). You can enable these content types in a library so that you can create new documents of these types.

Before setting up these content types in a library, you will have to verify few things on the site. First, make sure you are a site collection administrator or have full permissions. Second, ensure that the Report Server Integration Feature is Active in the site collection feature of your site. This feature provides the necessary infrastructure, content types, and so forth for the site. This feature should have been activated when you configured your SharePoint site for Reporting Services earlier in this chapter (Figure 3-16).

You’ll find the content types available under Report Server Content Types. Because you already created a Report Library document library earlier, go ahead and enable these content types on it:

1. Open the Report Library document library. From the Library tools Library tab menu on the ribbon, click Library Settings.
2. Click the Advanced Settings link under General Settings, and ensure that “Yes” is selected for “Allow management of content types?” Click OK to return to the Document Library Settings window.
3. Click Add from existing site content types under Content Types.
4. From the Select Content Types section, in the “Select site content types” drop-down list, choose SQL Server Reporting Services Content Types.
5. Select Report Builder Model, Report Builder Report, and Report Data Source from the Available Site Content Types list box, and click Add.
6. Click OK to return to the Document Library Settings window. Browse back to the Report Library.
7. Click the FILES tab from the Ribbon Menu. Under New Document, click the New Document button to view the drop-down list that consists of links to create a Report Model, Report, or a Report Data Source.
8. Click on Report Builder Report to load Report Builder 3.0. You can create a new report and publish it to this document library.

In the examples you’ve seen so far, we’ve used an embedded data source, which means the data source is specific to this report only. And as we discussed earlier in this chapter, when you want a data source that can be used in other reports, you need to create a shared data source. The connection information file can be either a report shared data source (.rsds) file or an office data connection (.odc) file that can be hosted in a SharePoint document library. An .rsds file is the same as an .rds file, but it has a different schema. When you create a Report Data Source from a new file in a document library on SharePoint, an .rsds file is created. When you create an .rds file from the SSDT Report Designer and publish it to a SharePoint document library, it will be converted into an .rsds file.

Creating a Report Data Source

To create an .rsds file on your SharePoint site, open the document library where you used the Reporting Services content type earlier.

On the Documents tab of the Library Tools tab, click on Report Data Source on the New Document menu. In the Data Source Properties window, follow these steps:

1. Enter the Name of the shared data source (.rsds) file.
2. Select the data source type you’d like to use.
3. Under Connection String, enter the statement needed to connect with the selected data source type. For examples of connection strings, visit msdn.microsoft.com/en-us/library/ms156450.aspx#Common. For instance, if you’d like to connect to SQL Server and the Adventure Works database, your connection string would be data source="(local)";initial catalog=AdventureWorks.
4. Choose credentials for the report server to use to access the external data source.
   • a.  Use Windows authentication when you want to use users’ credentials to access data in the report; this works well when Kerberos is in effect, and it can’t be used when Forms Based Authentication (FBA) is active on the site. The Windows authentication model doesn’t fit when subscriptions are scheduled for the report, because the user typically doesn’t have exclusive permissions to the report data source.
   • b.  Choose Prompt for credentials when you want users to enter credentials every time they access the report.
   • c.  Choose Stored credentials for accessing data with a single set of credentials.
   • d.  Use Credentials are not required when you are specifying them in the connection string itself.
5. Click on Test Connection to ensure the credentials entered are accurate.
6. Under Availability, select “Enable this data source” to make the data source active.
7. Click OK to save the shared data source file.

The new data source can be accessed via a URL. Whenever you create a shared data source using SSDT Report Designer or Report Builder, you can enter the shared data source’s URL to access it. To know if there are any dependent items using this shared data source, you can click View Dependent Items from the ECB menu of the shared data source item in the document library. You can certainly edit or delete this shared data source, but be aware that will impact the reports using it.

Now, what if someone wants to subscribe to these reports and knows how the data is changing over a course of time? Or maybe you want to save these reports in a particular output format on a scheduled basis? Reporting Services provides a mechanism to fetch these reports either on-demand or on a subscription basis. In the next section, you’ll see how to subscribe to reports that are published to a SharePoint environment.

Subscription to Reports

When you publish reports to a SharePoint environment integrated with a report server, you have the option to subscribe to them as long as you have access to the reports. You can create subscriptions that can be delivered as follows:

1. To a SharePoint document library: Reports are delivered to a document library that is available on a SharePoint site integrated with report server running in SharePoint integrated mode. The delivered reports are static in this case.
2. To a shared folder on file system: Static reports can be delivered to a shared folder that is accessible from the network.
3. To e-mail: Reports are delivered as an e-mail attachment.
4. Null: You can preload ready-to-view parameterized reports into a cache for specific scenarios. They can be used by administrators with the data-driven subscription model.

Reports are delivered in one of the available formats you choose while subscribing, including xml, csv, tiff, pdf, mhtml, Excel, and Word. For best results, it is recommended that you export reports in preview mode to see if they emerge correctly. If not, you can change them in design mode to make them look better.

An important aspect to note here is that reports delivered by subscription are not the original report files (.rdl). They are static reports, and you can’t use them with the Report Viewer Web Part. Even if you change the extension back to .rdl, you will not be successful in loading them.

In the following example, let’s try to subscribe to the Internet Sales report and deliver it to a SharePoint document library in the web archive file format (.mhtml).

Integration with a SharePoint List

For this example, let’s use the Airport Flight Stats custom list you created in Chapter 2. We will also author a new report using the same Report Project Sample you created using SSDT earlier in this chapter.

Creating a Power View Report

Bulding an interactive Power View report is quick and easy. Unlike a traditional SSRS report, fully functional Power View reports can be built right within the browser. SQL Server Data Tools is not required for building Power View Reports. Let’s build our first Power View report now.