C H A P T E R  12

Delivering Business Intelligence with SSRS

Most companies accumulate business data that, if analyzed correctly, can provide insights into what direction the company should take to achieve ultimate success. The main intention of Business Intelligence (BI) is to provide data in such a way that it can be immediately utilized to make important decisions. Microsoft’s BI platform comprises many services and applications that work together to facilitate the analysis and delivery of critical business data. SQL Server is at the heart of the BI model, providing data storage, data transformation, notification, scheduling, analysis, and reporting services.

Having the right data available is just the first part of the challenge in building an effective BI system. The second part is to ensure that this data is delivered in an effective and accessible way to all the people who need to see it in order to make the right decisions. This is where SSRS comes into play. In our organizations, we found that by integrating SSRS with many of the other components of the BI platform, we were able to dramatically improve our overall business strategies by making necessary information available to our employees wherever they were and whenever they needed it.

In this chapter, you will examine the following applications that we have extended to include the SSRS reports for our software development company:

Analysis Services: Having the ability to “slice” through dimensions of data often renders unexpected and meaningful results. When OLAP Services was introduced with SQL Server 7.0, we were asked to build a data warehouse, transforming our OLTP data into an OLAP cube. We maintained this project using the version of Analysis Services that shipped with SQL Server 2000 and had some new functionality, such as data mining models. Microsoft has invested heavily in OLAP technologies ever since. One such enhancement is the VertiPaq engine that is used with SharePoint 2010, PowerPivot, and Excel Services. Using PowerPivot models and deploying them to a configured SharePoint 2010 server, an OLAP cube is spun up in memory to allow rapid slicing and dicing of hundreds of millions of records.

SharePoint Services: Part of Microsoft Office, SharePoint Services provides our company with an intranet portal that we have departmentalized. Any information relevant to individual departments or to the company as a whole is indexed and searchable. Integrating SSRS reports with SharePoint lets our employees easily find the data they require to do their jobs. I will show you how to add SSRS reports to SharePoint. Since SSRS builds on Windows SharePoint Services, the work you will do in this chapter will also be applicable to the Windows SharePoint Services included with Windows Server 2003. For a SharePoint 2010 and Reporting Services 2012 installation, you will need a 64-bit version of Windows Server 2008 SP2.

Most companies have similar applications to the ones we are describing. We are providing these examples to give you some ideas of how, with a modicum of effort, SSRS can easily enhance these types of business applications. Our purpose is not necessarily to provide a step-by-step guide, but to show how your company might use SSRS. If you use any of the technologies mentioned in this chapter, you can easily integrate this chapter’s ideas into your own environment.

Building SSRS Reports for SQL Analysis Services

When we began the journey of building an OLAP solution for our health-care application with SQL Server 2000 Analysis Services, we were eager to jump right in and start analyzing data the first morning. We quickly became the resident experts at developing SQL queries to interrogate our OLTP database. Since this was the source database from which we were going to build the warehouse, our team thought it would be a simple case of adding a few queries and processing the cube. It did not turn out to be quite that easy. In retrospect, however, the process of creating an OLAP cube from a known source of data was worthwhile, because we were able to apply the skills we learned to many other projects. With each new version of Analysis Services, more and more features were delivered. At first, this can seem overwhelming, simply because of the volume of enhancements and the time required to not only become familiar with the technology, but also master it. However, while adding new features, Microsoft also adds many new tools that simplify time-consuming tasks. In the case of Analysis Services 2005, these new tools included a graphical MDX query builder and a cube wizard that automates many of the steps that create the intricate parts of an Analysis Services solution. SQL Server Analysis Services 2008 and 2008 R2 went even further to add time-saving functionality, enhanced data analysis, and the capability to integrate with SharePoint. SQL Server 2012 Analysis Services brought on new additions like the VertiPaq Engine mentioned earlier and Tabular Models, which are based off of the new Business Intelligence Semantic Model (BISM).

In this section, we will show how to use a simple Analysis Services cube as the data source to build and deploy SSRS BI reports. The cube is based on a SQL Server database that serves as a data warehouse for the health-care application you have been using throughout the book. The cube is populated with data relevant to patient admissions for a health-care agency. Though we designed the report to analyze many aspects of patient admission history, such as patients with multiple recurrent admissions, changing diagnosis, and patient referral sources, we will show how to create a report that specifically delivers analytical information about the length of time between when a patient is referred to the agency and when he or she is actually admitted. Over time, the data that is collected can help assist decision makers isolate problem areas and improve the processes that may be causing inefficient patient referral times. First, let’s look at the database and cubes on which the report you create will be based.

The data warehouse database you will use as a source for the Analysis Services project is called HW_Analysis. It is a simple database containing only eight tables, and is populated with data using SQL Server Integration Services (SSIS). The typical process for preparing a data warehouse database with SSIS is to export data from the source OLTP database, transform the data to make it more conducive to analysis by SSAS, and finally load this transformed data into the data warehouse or data mart.

We have already built both the HW_Analysis database and the SSAS cube called Patient Referral, and we have included the required files for deploying these two key components and detailed installation instructions in the ReadMe.txt file included with code download for this book. The source code and related files are available in the Source Code/Download area for the book on the Apress Web site (http://www.apress.com). Once you have restored the HW_Analysis data warehouse database per the instructions in the ReadMe.txt file, you can open the Pro_SSRS project, which contains the Patient Referral cube. Figure 12-1 shows the simple Patient Referral cube structure in BIDS. The cube has six dimensions and two measures.

Figure 12-1. Patient Referral cube structure

Of the six dimensions, several of them should be familiar to you because you have worked with them in other chapters of the book. Dimensions contain textual attributes that describe the fact data. You can think of a dimension in a cube as pre-aggregated groups or categories of data that can be associated with one or more measures. Measures, such as Time to Admission and Fact Referral Count, are calculated values at each level in a dimension. Multidimensional data stored in a cube consists of both dimensions and measures that, when queried using MultiDimensional eXpressions (MDX), can be analyzed not just two-dimensionally like rows and columns of a T-SQL query, but also by being drilled into and sliced at many dimensions.

Picture a cube like the Rubik’s cube invented in 1974 that many of us played with as children. It contains three squares in height, three squares in width and three squares on depth. With that picture in your head, imagine if you will that the green side of the Rubik’s cube represents the Date of Admission dimension, the red side representing the Branch dimension and the yellow representing the Time to Admission measure. Much easier than solving a Rubik’s cube, you could create an MDX query that would return the intersection of these three points as shown in Figure 12-4.

The dimensions you will use in the Patient Referral cube are:

• County
• Referral Source
• Diagnosis
• Payer
• Branch
• Date of Admission

Since the starting point in this exercise is a prepopulated and processed cube, it is important to make sure you are getting the desired data from the cube before creating your SSRS reports. With the Pro_SSRS project open in BIDS, navigate to the Patient Referral SSAS project in the Solution Explorer, expand to Cubes, and double-click Patient Referral.cube. This will open the cube in design mode. Once the cube is open, select the Browser tab. Figure 12-2 shows the opened cube with the combined measures and dimensions ready to be dragged and dropped into the query pane. If no data appears in the Browser tab, and a message indicates that there is a permission problem or the database does not exist, this usually indicates that the cube needs to be processed or the SSAS Patient Referral database needs to be deployed. To deploy the Patient Referral OLAP database (the cube) in BIDS, right-click the Patient Referral project, and select Deploy. If the default permission settings are insufficient for your Analysis Services instance, you may have to open up the Data Source Designer for the HW Analysis data source and change the Impersonation Information to use a specific user name and password. For instance, my settings are set to use “SQLBIGEEKmcdonald” for the Windows user with appropriate permissions to deploy the cube. Once you have deployed the Patient Referral OLAP database successfully, you can return to the Browser tab, and click the available link to reconnect to the database.

Figure 12-2. Viewing the Patient Referral cube in BIDS

Perform the following steps to see the average Time to Admission:

1. You can begin by expanding Measures and then Fact Referral in the cube object window on the left of the cube design pane.
2. Next, drag the Time of Admission measure to the Drag Levels or Measures here to add to the query section of the query pane. Typically, when browsing the cube or creating reports, you will want to drag over the measures before you drag over your dimension attributes. You should see an average time of 14.095 days. This calculation was done at the time the cube was originally loaded and processed and represents a total average of time to admission for all dimensions combined. Figure 12-3 shows the Time to Admission measure in the query pane.
   

   Figure 12-3. Viewing the overall Time to Admission measure’s average for all dimensions

3. Now drag two dimension objects onto the data pane. Under the Branch object, drag and drop the Branch Name field to the left of our Time to Admission measure.
4. Next, under Date of Admission, drag and drop the Year - MonthName - admitdate hierarchy to the right of Branch Name in the data pane. The hierarchy looks like a pyramid made of six squares. When finished, the cube browser should look like Figure 12-4. You can now see the results for every single admitDate along with its corresponding Branch Name, Year and MonthName attributes.

In prior releases of the cube browser, you could drill down into hierarchies and have column groupings as we have seen in the Matrix-style reports. However, as of the current release of SQL Server 2012, the cube browser returns the data two-dimensionally much like the results of a T-SQL query. Some active members in the SQL Server user community has stated that this is a strange setback for Microsoft’s cube browser and I personally believe that they should add the functionality back. However, nothing really has changed from the reporting perspective. If you are seeing the results depicted in Figure 12-3 above, then you are indeed getting data from the cube! Now it is time to move on to SSRS and create the report you will deploy to SharePoint.

Figure 12-4. Cube browser with year and branch

Using an Analysis Service Cube with SSRS

Now that you have determined that the Patient Referral cube is working as it should, the next step is to create an SSRS report. In the Pro_SSRS project, you will find two completed reports, the Average Referral to Admission report and the Referral to Admission Chart report. Both reports are based on the Patient Referral cube. Since creating reports is the same, we will not go through the procedure of building these reports step by step. However, we will demonstrate the following through the Average Referral to Admission report:

• Setting up a data source to use Analysis Services
• Using the graphical MDX query builder that was added with SQL Server Reporting Services 2005

Setting Up the Analysis Services Data Source

With the Pro_SSRS project open, navigate to the Reports folder, and open the Average Referral to Admission report. Next, open the Report Data window. Right-click the Patient_Referral dataset and select the Query option from the submenu list. Notice that the query recognizes that you are using an OLAP data source with MDX and brings up the graphical MDX query designer. As you can see, it is very different from the T-SQL query window that we have used when accessing queries throughout the book. If you select filter expression and choose to filter on 2010, 2011, and 2012, you should see results similar to those shown in Figure 12-5. There are two datasets contained in this report, Patient_Referral and DateofAdmissionYear.

Figure 12-5. Viewing the Patient_Referral for the Average Referrals to Admission report

The type of data source for both datasets is Microsoft SQL Server Analysis Services. If your Analysis Services instance is not on the local machine that you are connected to, you will need to change the data source server from localhost, defaulted in the report, or the default to your SSAS server name. You can do that easily by double-clicking the Shared Data Source named PatRef_DS and editing the data source properties to match your environment, as shown in Figure 12-6.

With the datasets configured properly for your SSAS environment, you will next look at how you build the MDX queries.

Figure 12-6. Connection properties of the PatRef_DS data source

Working with the Graphical MDX Query Builder

One of the great new features introduced in SSRS for SQL Server 2005, and enhanced further in 2008, is the graphical MDX query builder. MDX is a fairly large, complex language that is prone to syntactical errors. You must innately understand it to truly deliver precision data from SSAS cubes. Having and using a graphical query tool to form the base MDX query, in much the same way that developers use the graphical query designer for T-SQL, reduces common syntax errors and speeds development of the query.

With the Average Referral to Admission report open to the Report Data Query window, notice that you have several dimensional and measure elements listed: BranchName, Year, MonthName, Time to Admission, and Fact Referral Count. By default the report will open in the graphical design mode, as shown in Figure 12-7, and not the generic query designer. The design modes are toggled with the Design Mode button on the right of the toolbar. In the graphical design mode, the dimensional elements are dragged and dropped directly in the window from the Metadata pane where the elements are listed. If the Auto Execution button is selected, which it is by default, whenever elements are dragged and dropped, the query executes, and the data results are displayed.

Figure 12-7. Output from graphical query builder

Like the multidimensional view of the data, as you saw when you queried the same cube in the Patient Referral cube browser, SSRS sees the data two-dimensionally, with rows and columns. An SSRS report can access data from a cube; however, it is necessary to arrange the data two-dimensionally because this is how the report will aggregate the data when it sums and totals the measured values, in this case Time to Admission and Fact Referral Count. We have also applied a filter to the query so that only the years from 2010 through 2012 will be selected.

As you may have noticed in the bottom left corner of Figure 12-7, you can use several buttons on the toolbar to modify the properties of the query, such as showing empty cells or adding calculated members. A calculated member is part of the MDX query that is created by combining one or more elements into a value that can be used independently as a new element. You could, for example, create calculated members to not only show the average time to admission for each dimensional element such as BranchName and Year, but also show the minimum time to admission, using the MIN function, or the maximum time to admission, using the MAX function. The calculated members would become part of the overall query that could be used as new measures with data values returned at each dimensional level. In other words, if you had to create a calculation at the report layer that wasn’t created as a calculation in the cube itself, you could create a Calculated Member at the dataset layer of the report. For those not skilled in writing MDX calculations, this is an easy way to create report level calculations.

With the graphical query built and working, let’s now look at the MDX that was created behind the scenes. You can do this by clicking the Design Mode toolbar button. Figure 12-8 shows the MDX query that returns the selected dimensional elements and measures for the report.

Figure 12-8. MDX query generated graphically

Figure 12-9. Warning message displayed when altering MDX query

Both reports, Average Time to Admission and Referral to Admission Chart, will be displayed in a compact Web Part in SharePoint, which we will show how to do in the next section. Because of this, we intentionally made the size of the reports small, with 8-point Arial font and narrow cells. Figure 12-10 shows the Design tab for the Average Time to Admission report.

Figure 12-10. Layout tab for Average Referral to Admission report

At this point, we could publish both reports to the reporting services instance and deliver the reports to users. These two reports combine a level of BI that is perfect for a view within a dashboard or business portal. To that end, we will take a final view of the second chart-based report in the design environment and then move on to a fully integrated Microsoft SharePoint deployment, where the reports can be viewed and managed within SharePoint itself. Figure 12-11 shows the Referral to Admission Chart report previewed in BIDS.

Figure 12-11. Previewing Referral to Admission Chart in the design environment

Incorporating SSRS with Microsoft SharePoint 2010

SSRS and SharePoint technologies have progressed and matured similarly. Both have enjoyed growing acceptance and support from businesses around the world. It was a natural progression, therefore, for the two technologies to one day come together to form components of the overall Business Intelligence platform that Microsoft has built over the past several years. Previous support for SSRS in SharePoint included SSRS Web Parts, which could be installed for SharePoint installations. These Web Parts provided a means to view published SSRS reports within SharePoint. However, this union was not as adhesive as many administrators and dashboard designers needed it to be. Management of reports still fell to the likes of DBAs or report content managers. SQL Server 2005 Service Pack 2 included support for a fully integrated SSRS and SharePoint infrastructure, whereby reports could be published, managed, and viewed all within SharePoint. SSRS 2008 and 2008 R2 continued this merger, and many organizations have adopted this methodology over a native-only SSRS that does not integrate directly with SharePoint.

This brings us to SharePoint 2010 and SQL Server 2012 Reporting Services. Although similar to previous versions in integrating Reporting Services with SharePoint, the installation and configuration process is very much different. In the latest release of Reporting Services, if you wish to run in SharePoint integrated mode, you will need to install SharePoint and then install Reporting Services in SharePoint integrated mode. This process essentially installs Reporting Services on top of SharePoint 2010. This process differs from prior editions, in that SSRS and SharePoint were separate entities and could be integrated using a configuration tool.

In this section, you will walk through a SharePoint and SSRS stand-alone installation and deploy to and view on the SharePoint site the two reports from the previous section, Average Time to Admission and Referral to Admission Chart:

1. First, you will install Microsoft SharePoint 2010 with Service Pack 1 and SSRS 2012 on the same server.
2. Next, you will deploy reports to the SSRS-integrated server with SharePoint 2010.
3. Finally, you will create a simple dashboard using SSRS Web Parts.

Installing SharePoint 2010 and SQL Server 2012 on a Stand-Alone Server

The installation routines for SQL Server 2012 have been greatly enhanced over the years. Specifically, with SSRS, you maintain the ability to install the core assemblies, but configure the SSRS instance later. However, in SSRS 2012, you can install SSRS in SharePoint integrated mode directly if you have planned to roll out SSRS and SharePoint together. For our installation, we chose to install both SSRS 2012 and SharePoint 2010 on the same server, known as a stand-alone installation. This is the recommended setup for anyone who wants to familiarize themselves with how the two technologies work together. Other deployment scenarios, such as multiple-server installations, are also possible and expected in larger environments, but we chose the stand-alone deployment initially solely for the sake of testing and training. We would recommend this path for anyone not already familiar with an integrated SSRS and SharePoint installation.

Assuming that all of the prerequisites, software and hardware requirements have been met then there are six major steps to complete a stand-alone installation of SSRS and SharePoint:

1. Install SharePoint 2010 with Service Pack 1.
2. Install SQL Server 2012 Database Engine and Reporting Services in SharePoint mode.
3. Configure SharePoint 2010.
4. Install and start the Reporting Services SharePoint Service.
5. Create a Reporting Services Service Application in SharePoint Central Administration.
6. Configure Reporting Services Integration with SharePoint.

Installing SharePoint 2010

We will start by walking you through the installation of SharePoint 2010 with Service Pack 1. Installing SharePoint 2010 is a straightforward process, with the exception of the final step, where the installation wizard asks you if you want to Run Configuration Wizard (Figure 12-12). This option button is selected by default, but in the case of Reporting Services in SharePoint integrated mode, you will configure it later. You’ll configure SharePoint after you have installed an instance of the SQL Server 2012 database engine. Remove the check from the checkbox and click Close to complete the SharePoint 2010 installation.

Figure 12-12. Installing SharePoint 2010

Installing SQL Server 2012 Reporting Services in SharePoint Mode

Now that SharePoint 2010 is installed, it is time to install the Database Engine and Reporting Services for SharePoint. The SQL Server 2012 installation with SSRS is a standard procedure that you may already be familiar with. Going through each step of the installation is outside of the scope of this book; however, the only difference between a typical install and one that includes support for integrated SharePoint can be seen on the Feature Selection and the Reporting Services Configuration screens. Go ahead and choose the options shown in Figure 12-13.

Since this is being done on a local VM, not on a traditional corporate network, I chose to install Analysis Services on this VM as well. That way, I can copy the Pro_SSRS project and deploy the Patient Referral Analysis Services database (the cube) to the same VM for the remaining examples in this chapter.

Figure 12-13. Installing SSRS for SharePoint

As shown in Figure 12-14, the only option that you have is Reporting Services SharePoint Integrated Mode – Install Only. In the note under the Install Only radio button, you can see a message stating that you will need to configure SharePoint using Central Administration to complete the installation. It also states that you’ll need to get the Reporting Services service started and create a Reporting Services service application (installation steps 4 and 5 in the previous list). Now, you will install the Reporting Services SharePoint Service.

Figure 12-14. Installing SSRS in SharePoint Integrated Mode

Configuring SharePoint 2010

Once the SSRS installation is complete, you need to perform the third major step in the installation process. Now let’s configure SharePoint using the SharePoint 2010 Products Configuration Wizard under the Microsoft SharePoint 2010 Products folder of the All Programs menu. Follow the below steps to configure your new server farm.

1. Choose the option to Create a new server farm and click Next to proceed.
2. Specify the Configuration Database Settings for the SharePoint site as shown in Figure 12-15. In this case, my server name is VMWINSVR2008SP2 and I specified the user credentials (SQLBIGEEKmcdonald) to use when connecting to the configuration database. Normally, you would specify a system account with a password that rarely changes. Click Next to proceed.
3. Specify a Passphrase to use for the Farm Security Settings. This is used when you need to perform operations like joining servers to the farm. The minimum requirement for this is like that of a secure Active Directory network. The password must have the following characteristics: a minimum length of eight characters, special characters, and a mixture of numerals and capital and lower-case letters. Click Next to proceed.
   

   Figure 12-15. Specify configuration database settings for SharePoint

4. Next, you’ll need to configure the SharePoint Central Administration Web Application. I like to change the default port number to something that is not commonly used, but a little easier to remember than the typical default. Feel free to keep the default port, but I chose to use port 2012, as shown in Figure 12-16. Click Next to proceed.
5. After completing the wizard, you will be presented with a summary. Click Next to begin the configuration process. After the configuration wizard is done working its magic, you will be prompted with a dialog box stating that the process has been completed. Upon clicking Finish, you are presented with the SharePoint Central Administration. If for some reason yours does not open up, you can find it by going to the Start Menu, All Programs, and then Microsoft SharePoint 2010 Products. Alternatively, you could fire up Internet Explorer and navigate to http://ServerName:PortNumber/. In my scenario, running on server name VMWINSVR2008SP2 on port 2012, my URL would be http://VMWINSVR2008SP2:2012/.

Figure 12-16. Specify SharePoint Central Administration port and security

Whew! You’re done with the configuration wizard. Now comes the fun part. I bet you didn’t think that you would be learning about PowerShell, did you? Well, you won’t be learning too much, so don’t be concerned. Now, you’ll perform the fourth major step in getting Reporting Services 2012 and SharePoint 2010 to work together.

Installing and Starting the Reporting Services SharePoint Service

In this step, you need to connect the installed SQL Server Reporting Services components to your new SharePoint farm. Perform the following steps to install and start the Reporting Services SharePoint Service.

Click on the Start button, All Programs, and then right-click and select Run as administrator on the SharePoint 2010 Management Shell under the Microsoft SharePoint 2010 Products folder. You have to run this as an administrator to perform the following steps. If not, you will receive the error displayed in Figure 12-17.

Figure 12-17. Error if SharePoint management shell not in administrator mode

Type in the following PowerShell command to install the SharePoint Reporting Service. Hit the Enter key after typing in the command to execute it.

Install-SPRSService

Type in the following PowerShell command to install the PowerShell service proxy. Hit the Enter key to execute the command.

Install-SPRSServiceProxy

Type in the following PowerShell command to start the service. After typing the command on one line, hit the Enter key to execute the command.

Get-spserviceinstance –all | where {$_.TypeName –like “SQL Server Reporting*”} | Start-SPServiceInstance

Figure 12-18 shows the result after completing the steps installing and starting the Reporting Services SharePoint Service. For the fifth step, you need to create a new Reporting Services Service Application.

Figure 12-18. SharePoint management shell and scripts in administrator mode

Creating a New Reporting Services Service Application

You are now on the cusp of having all of the setup steps required to install Reporting Services 2012 in SharePoint 2010 mode. This step will be performed in SharePoint 2010 Central Administration. Perform the below steps to create a new SQL Server Reporting Services Service Application. I know, it’s a mouthful, but we’ll get through this together.

You need to open up SharePoint 2010 Central Administration, which can be found under the Start menu, All Programs, and then Microsoft SharePoint 2010 Products.

From the Central Administration home screen, click on the Manage Service Applications link under the Application Management task group as pointed out in Figure 12-19.

Figure 12-19. SharePoint 2010 Central Administration manage service applications

Next, click the New button in the SharePoint menu and select SQL Server Reporting Services Service Application as shown in Figure 12-20.

Figure 12-20. Creating a new SSRS Service Application

Enter a name and Application Pool to be used for this Service Application. From the manageability perspective, it is a good idea to give them both the same name. As can be seen in Figure 12-21, mine is named Professional SSRS 2012 Service Application for both. Depending on your security needs, change appropriately. I used the default security settings for the SharePoint managed account. Scroll down and set the database server, name, and authentication details appropriately. Last but not least, choose the Web Application Association. In this case, there is only one web application to associate with. Click OK to save your settings. After a few minutes, you should see a screen stating that your new service application has completed successfully.

Figure 12-21. Creating a new SSRS Service Application

After the new SQL Server Reporting Services Service Application has been created, you will see it in your list of Service Applications as shown in Figure 12-22. I’m sure that you will be happy to hear that you only have one more step left in your installation and configuration of SQL Server 2012 Reporting Services in SharePoint 2012 mode.

In prior versions of Reporting Services, integration with SharePoint was managed using the Reporting Services Configuration Manager. As I mentioned earlier, in SQL Server 2012, if you choose to run in SharePoint integrated mode, Reporting Services is installed and configured completely within SharePoint 2010. So next, I’ll walk you through the steps to get the Reporting Services integrated into SharePoint.

Figure 12-22. List of service applications

Configuring Reporting Services Integration with SharePoint

Now that you have completed all of the required installation steps, it is time to begin the final configuration process. You know that SSRS is already configured for SharePoint integration. All that is required now is to configure SharePoint to activate the Reporting Services Integration and Administration Features.

Now that we are in SharePoint integrated mode using SharePoint 2010 and SQL Server 2012, the ReportServer database is managed completely by SharePoint. SharePoint will supply the storage for all of the security, reports, and data sources. All of the benefits of SharePoint, such as document publishing and delivery, will be maintained by SharePoint.

SharePoint’s main administration Web site, called Central Administration, is where you will perform the remaining SSRS configuration settings. Perform the following configuration steps:

1. Open the Central Administration Web site by clicking Start, All Programs, Microsoft SharePoint 2010 Products, and then SharePoint 2010 Central Administration.
2. Click on Site Actions and then Site Settings. When the Site Settings screen appears, click on the Site Collection Features link under Site Collection Administration. From there we need to ensure that the Reporting Services features are activated.
3. Click Activate next to the Report Server Central Administration and the Report Server Integration features as shown in Figure 12-23.

Figure 12-23. Activating site collection features

That’s all there is to it! You’re done with the installation and configuration of SharePoint 2010 and SQL Server 2012 Reporting Services in SharePoint mode. Before we move on, though, I want to show you how to get to the screen for administering your Reporting Services configuration settings. If you still have Central Administration open, click on Application Management. Next, click on Manage Service Applications under the Service Applications group. You should recognize the screen that opens, as it is the same one displayed in Figure 12-22 above. Drill into your Reporting Services Service Application, as I have done in Figure 12-24. Remember that mine is Professional SSRS 2012 Service Application.

Figure 12-24. Managing Reporting Services service system settings

Next, click on the System Settings link to view the Reporting Services Service settings. As you can see in Figure 12-25, this is where you can change various options like report history, timeouts, and even how long to save the Execution Log as we did in Chapter 10.

Figure 12-25. Setting Reporting Services Service Application server defaults

Now that you are done with all of your setup and configuration, it is time to move forward and show you how to deploy reports to a SharePoint site.

Deploying Reports in a SharePoint-Integrated SSRS Installation

Once the three management tasks have been completed, it is time to begin working within the SharePoint site itself, where you will work with deployed reports and dashboards to create a portal page for your users. In our organization’s case, we wanted a simple Admissions dashboard page where company executives and decision makers could review important BI reports concisely. SharePoint provides many templates and samples that you can use to aid you in designing your dashboard.

Let’s take a look at a default SharePoint page. You can see in Figure 12-26 that a base SharePoint site collection installation includes a Document Center. To centralize our deployment location, we created a Reports Library. We also created another site on our SharePoint system using a built-in SharePoint template called Business Intelligence Center, which will allow us to use web parts to create a sample dashboard.

Figure 12-26. SharePoint Document Center main page

Figure 12-27 displays the Business Intelligence Center for SharePoint 2010. The name Business Intelligence Center should not lead you to believe that this is where all of the SSRS reports will be deployed. SSRS reports can be deployed to any valid SharePoint path, as you will see shortly. The Business Intelligence Center is a location where you can create a dashboard or Microsoft Excel workbook as well as an area where reports can be deployed. It is in the Reports Library, which resides within the Document Center, where SSRS RDL files, data sources, and many other types of reports can be stored.

Figure 12-27. SharePoint Business Intelligence Center

As I mentioned, RDL reports can be deployed to any valid document library in SharePoint. In Figure 12-28, you can see project properties for your Pro_SSRS report solution. Since you will be deploying these reports to the SharePoint server, the target URLs for the server and report folder will be different from those for a native SSRS deployment. You can see that the server will be a URL to the main SharePoint site, in our case http://localhost/. The target report folder is a URL that points to a valid SharePoint document library; in this case, http://localhost/sites/Pro_SSRS/Reports, which is pointing to our Report Library contained in the Document Center.

Figure 12-28. Target URL properties for the report project

After you have made these changes to the target URLs for the report project, you can deploy reports directly to SharePoint. Right-click the PatRef_DS Shared Data Source and select Deploy. In order to deploy the report to the SharePoint site, you first must deploy the data source. After the PatRef_DS data source is deployed, right-click the Average Referral to Admission report in the Solution Explorer and select Deploy. Alternatively, you could have selected both the data source and the report by holding the Ctrl key down, then right-clicking and selecting Deploy. This would have deployed the data source first and then the report. Once the report has been successfully deployed, you will be able to see the report within the SharePoint site. The best part about deploying reports to SharePoint, as you can see in Figure 12-29, is that you get to take advantage of all of the SharePoint document features, as RDL files are viewed as documents as well. Features such as workflows, alerts, and document check-in and check-out are inherited.

Figure 12-29. Deployed report with SharePoint document features

Before running the report, let’s change the PatRef_DS data source to use Stored Credentials, like we did in Chapter 10 when configuring the data source for Subscriptions. We do this because the permissions between SharePoint and the data source may be different than Windows Authentication. If you are having permission-related issues when running a report, this is the first place that I would troubleshoot. Navigate to your deployed data source in SharePoint and alter your data source by clicking on the PatRef_DS link, or clicking the down arrow and selecting Edit Data Source Definition. Select the option to use Stored Credentials under the Credentials section. Enter a valid username and credentials as shown in Figure 12-30. If you are using Windows Credentials, be sure to select the option to Use as Windows Credentials. Click the Test Connection to make sure that you entered valid credentials. If the connection is created successfully, then click OK to save your settings.

Figure 12-30. Editing data source definition

Next, click the report Average Referral to Admissions. This will open the report in its own page and execute it, much the same way it would be executed via Report Manager. Figure 12-31 shows the report rendered in SharePoint.

Figure 12-31. Report rendered in SharePoint

You will follow the same deployment steps in BIDS that you did for the Average Referral to Admissions report to deploy the Referral to Admissions Chart report. The processes will be the same, and when complete, you will see the report in the SharePoint web site. Navigate to the report and click it to execute the report. With this report rendered, click on the Actions link button in the top left of the report as shown in Figure 12-32. You may have noticed these actions earlier; but notice the additional actions in the report toolbar, such as Subscribe, Open with Report Builder, and Export. Each of these actions are pretty self explanatory, it is important to realize here that since we are in SharePoint integrated mode, we still have the ability to take advantage of features such as subscriptions and ad-hoc report building. We will cover the Report Builder applications in the next chapter.

Figure 12-32. Actions available for a report rendered in SharePoint

Creating a Simple Dashboard to Display SSRS Reports

Now that you have successfully deployed and rendered reports in SharePoint and have seen many of the attributes and actions available, I’ll show you how to create a dashboard that can render the reports in a concise Web Part. The SQL Server 2012 Reporting Services Add-In that you installed previously included the SQL Server Reporting Services Report Viewer Web Part. Creating a dashboard is as simple as clicking the Dashboards link in the web site and then selecting the New Page option under the Site Actions link button. You will provide a name for the new dashboard link and the initial layout based on a template. For example, I chose to name the dashboard Admissions. Once the dashboard is created, you can navigate to it and edit its design directly in the browser. Click Web Part on the Insert tab while in edit mode and select the SQL Server Reporting Services Report Viewer from the list of available Web Parts, as shown in Figure 12-33. You will find it in the SQL Server Reporting category. After you have it selected, click the Add button.

Figure 12-33. Choosing the SQL Server Reporting Services Report Viewer Web Part in SharePoint

Once you add the Web Part to the Admissions dashboard, you will have the ability to edit it to point to a URL where the deployed reports reside. You can click the link to open the tool pane where the report URL will be entered. This links the Web Part directly to the report. You can see the Web Part and the report selection toolbar on the right. In this case, you will select the Referral to Admissions Chart report, which you can see in Figure 12-34. Click Apply to save the Web Part and render the report.

Figure 12-34. Selecting a report URL for the Web Part

Finally, you can view the rendered report in the Web Part that is contained within your dashboard. If you are not happy with the layout—there is too much white space, for example— you can resize the Web Part by navigating back to the Web Part toolbar and entering your desired size settings. Figure 12-35 shows the newly deployed Referral to Admissions Chart report rendered in the Web Part. This basic report can be combined with other SharePoint dashboard Web Parts, like the KPI and Excel Web Access Web Parts, to create an appealing and informative portal for your organization.

Figure 12-35. Report rendered in Web Part dashboard

Creating Data Alerts

SQL Server Reporting Services 2012 in SharePoint Integrated mode cannot be complete without discussing data alerting. This great new feature gives you the ability to receive alerts when the data contained in a report is changed or when it meets certain pre-defined criteria. The pre-defined criteria are specified when creating a rule within the New Data Alert window. Follow these steps to create a new data alert:

1. If you still have the Referral to Admission Chart report open, click on the Actions button. If not, run the Referral to Admission Chart report as shown in Figure 12-36.
   

   Figure 12-36. Creating a data alert

2. On the New Data Alert screen, configure the data alert by giving the alert a name. In this case, name it DA_Referral to Admission Chart_GT_8.9 as shown in Figure 12-37.
3. Next, click the Add rule… button to create a rule that states when the value of the Category Group1 is greater than 8.9. This will cause the SQL Server Agent to send out an alert when the value is over 8.9.
4. Set the schedule to run once a day. The scheduler is much like the one provided by Report Manager.
5. Enter the recipient to be alerted when the value reaches the desired threshold, the subject, and any description if desired.
6. Click the Save button to have SharePoint create the alert.

Figure 12-37. Configuring a data alert

Now, if the 8.9 threshold value is ever exceeded, you will be sent an email notification that the threshold has been met.

Summary

In this chapter, I showed you how to incorporate SSRS into a BI model, using the business model of a health-care agency deploying a custom BI portal. Understanding how to transform and analyze the data that drives your business will help you make important business decisions. Delivering that data to decision makers is a pivotal link in the BI chain. With SSRS, Microsoft has provided organizations with another tool that can easily tap into and extend the reach of crucial data. SSRS utilizes many types of data and can deliver that data in a variety of formats and mechanisms. Working with other applications and products in the Microsoft BI platform—such as SharePoint, Analysis Services, and Office—SSRS will prove to be an invaluable BI tool now and in the future.