At my first job as a database administrator , I was asked to look at a problem with some reports. The reports were created in MS Access and linked to a SQL Server database. Each manager had his or her own version of the reports and, even though the reports had started out the same at one time, they had been modified by the individual managers over the years. The managers were complaining that the numbers were not consistent, and could I fix the problem?
I worked to correct the discrepancies as best I could, but the individual copies of reports still existed. Shortly after this, I attended the 2003 PASS Summit and saw the announcement about SQL Server Reporting Services (SSRS). In 2004, Microsoft released SSRS as an add-in for SQL Server 2000. I didn’t wait for the release. I knew that SSRS was going to solve my MS Access report problem, and I installed SSRS as soon as it was available.
The advantage that SSRS brought, compared to the MS Access reports , was the centralized web site, Report Manager, where the reports were published. Instead of each manager having his or her own copy of reports, the managers would run the reports from a central location eliminating the discrepancies.
SQL Server Reporting Services is one of the core components of the Microsoft Business Intelligence stack . SSRS is a feature-rich reporting tool that now includes mobile reports as well as a modern on-premises web portal.
SSRS has a number of interactive features, visual elements such as charts and maps, security, and more. Reports can contain data displayed in tabular format or visually. You can also create attractive and informative dashboards.
To run reports, end users browse to the web portal and click the report name. In the background, SSRS requests the data from the source databases and builds the report. The report is then delivered to the end user. Figure 1-1 shows how this works.
Figure 1-1. Reporting steps
Understanding SSRS Architecture
An SSRS implementation consists of multiple components that can be configured in many different ways. At a minimum, everything can go on one computer, even on a laptop. This configuration is probably useful only for development, and it is what I recommend for following along with the examples in this book. The configuration consists of a SQL Server instance that includes Reporting Services as well as the source databases and SQL Server Data Tools (SSDT) running in Visual Studio.
Note
SSRS can also be installed in SharePoint integrated mode. The way you develop reports is identical to the default which is called native mode. This book will focus on native mode, but it does have a section in Chapter 8 on deploying reports to SharePoint.
Typically, in a production environment, a server is dedicated to running SSRS, and the source data is found on other servers throughout the network. Report developers will use SSDT on their local computers to develop the reports and then publish the reports to the production server or possibly to a server where the reports can be tested before going live.
Before learning how to get everything set up on your computer, you will learn more about the components of SSRS. First there must be a SQL Server instance in place to host the SSRS databases. The instance is often installed on the server where the SSRS service is installed, but it can be a different server. There are two databases that will be created when you install or initially configure SSRS: ReportServer and ReportServerTempDB. ReportServer is used to store report definitions, security, history, and everything else that is needed for the published reports. You can probably tell by the name ReportServerTempDB that this database is used as a temporary workspace.
When you install SSRS, it creates a web service that responds to report requests. In native mode, it provides a web portal where users can browse for and run reports. In previous versions of SSRS this was called Report Manager, but starting with 2016 this interface has been completely redesigned. It is now just called the web portal and resembles Figure 1-2.
Figure 1-2. The web portal
The source of data can be from just about anywhere. This book will show examples from SQL Server databases, but you could report against Oracle, Analysis Services cubes, XML documents, SharePoint lists, cloud databases, and more.
Installing SQL Server with SSRS
You can follow along with many of the examples in the book by installing the developer tools without installing SSRS. You could also work with an SSRS instance that is already in your company’s network. I do recommend that, if at all possible, you install SSRS on your development computer. That will allow you to learn how to do some administrative tasks as well as develop the reports.
SQL Server is available in several editions. Each edition has a specific set of features and price. For development and learning, you can download the free Developer Edition. Just search the web for “SQL Server Developer Edition download” to find the file. There is also an Express Edition that is free, but the features are very limited.
Note
At the time of this writing, the media is an iso file. My Windows 10 laptop can easily handle iso files, but your operating system may not. You can search for a utility to mount or extract iso files if needed.
From the media, you should see a setup icon shown in Figure 1-3.
Figure 1-3. The setup icon
Follow these instructions to install a SQL Server instance with SSRS:
Double-click setup to launch the SQL Server Installation Center.
Click Installation on the left.
Click New SQL Server stand-alone installation or add features to an existing installation at the top as shown in Figure 1-4.
Figure 1-4. The SQL Server Installation Center
An installation wizard will launch. On the initial information pages, click Next.
On the License Terms page, click I accept the License Terms and click Next.
Click Next on the Microsoft Update page.
After checking for updates, click Next on the Product Updates.
On the Install Rules page, click Next once it is done. If there are any Failed statuses, you will need to click the message to find out what is wrong and correct it.
On the Feature Selection page, select Database Engine Services and Reporting Services – Native as shown in Figure 1-5.
Figure 1-5. The feature selection
On the Instance Configuration page, you must decide whether to install a default instance with Instance ID MSSQLSERVER or a named instance. Each instance of SQL Server on a computer must be unique. If there are existing instances of SQL Server installed, you will see them listed. If no other default instance is installed, select Default Instance and click Next. Otherwise, select Named Instance and type in a name before clicking Next. Figure 1-6 shows this page.
Figure 1-6. The Instance Configuration page
On the Server Configuration page, accept the defaults and click Next.
On the Database Engine Configuration page, click Add Current User. This will make your account an administrator in SQL Server. Click Next.
On the Reporting Services Configuration page, make sure that you choose Install and configure as shown in in Figure 1-7 and click Next.
Figure 1-7. The Reporting Service Configuration page
On the Ready to Install page, click Install.
Restart the computer if requested to do so at the end of the installation.
It may take several minutes to install the SQL Server instance and SSRS. There are dozens of things that could prevent a successful installation, and it would be impossible for me to help you troubleshoot via a book. My advice is to navigate to C:Program FilesMicrosoft SQL Server130Setup BootstrapLog. There will be log files with the messages generated during the installation. You can search the Internet using any error messages that you find for help and advice if the installation fails. That said, you may need to be connected to the Internet during the installation, and you may need to run the setup as an administrator for a successful install.
Previous versions of SQL Server allowed you to install SQL Server Management Studio (SSMS) with your SQL Server instance install. Starting with SQL Server 2016, Microsoft plans to release frequent updates to this tool, and make it available only by downloading. To find the link, relaunch the SQL Server Installation Center if you have closed it. On the Installation page, click Install SQL Server Management Tools. Follow the instructions found on the download page.
Installing SQL Server Data Tools
The primary development tool for SSRS is SSDT, mentioned earlier, and it runs inside Visual Studio. Microsoft has changed the name and the source of the development tool over several versions of SQL Server. At one time, you could install Business Intelligence Development Studio, also known as BIDS, directly from the SQL Server installation media. At one point, Microsoft changed the name to SQL Server Data Tools – BI, and it was a separate download. To make things confusing, there was also another product called SSDT used for database projects, not BI projects like reports. Luckily, in 2016, Microsoft has combined the two products into one SSDT download.
You can find the link to download and install SSDT on the Installation page of the SQL Server Installation Server as shown in Figure 1-8.
Figure 1-8. The link for SQL Server Data Tools
At the time of this writing, you can download the SSDTSetup.exe file and install from that, or you can scroll down the page to download an iso file. If you download the iso file, then run SSDTSetup.exe from the media to get the install started. Follow these steps to install SSDT:
Running SSDTSetup.exe starts the wizard. On the first page, make sure that SQL Server Reporting Services is checked as shown in Figure 1-9. You can leave the others checked as well.
Figure 1-9. SQL Server Reporting Services is checked
Click Next .
On the License Terms page, check I agree to the license terms and conditions.
Click Install
Configuring SSRS
If you followed the installation instructions exactly in the section “Installing SQL Server with SSRS”, SSRS should be configured. If, instead, you added SSRS to an existing SQL Server instance or selected Install only on the Reporting Services Configuration page , you will need to configure it now. To configure SSRS, follow these steps:
Launch Reporting Services Configuration Manager.
When asked to connect to your SSRS instance, select the server and instance name if required and click Connect as shown in Figure 1-10.
Figure 1-10. Connect to the SSRS instance
Select the Database page and click Change Database as shown in Figure 1-11.
Figure 1-11. The Database page
This opens the Report Server Database Configuration Wizard. Select Create a new report server database as shown in Figure 1-12. Click Next.
Figure 1-12. Create a new report server database
On the Database Server page, make sure that your server name is filled in. If you have a named instance, be sure to include the instance name. Figure 1-13 shows this page .
Figure 1-13. Connect to the database server
Click Next to move to the Database page shown in Figure 1-14. Accept the defaults on this page. If your installation is a named instance, the instance name will be part of the database name.
Figure 1-14. The Database Name
Click Next to move to the Credentials page. You can change the SSRS service account on this page. Accept the defaults and click Next.
Click through the remaining pages in the wizard to create the SSRS databases.
Click Finish once the process is complete.
To create the Web Service URL, select the Web Service URL page as shown in Figure 1-15.
Figure 1-15. Web Service URL page
For your own SSRS installation, just accept the defaults and click Apply. This sets up the web service.
When the task has completed, select the Web Portal URL page. Once again, you can accept the defaults and click Apply. This will create the web portal.
When the web portal creation is done, click the Encryption Keys page as shown in Figure 1-16. Click Backup to save the encryption keys.
Figure 1-16. Back up the encryption keys
Supply a location and password that you will not forget. This step is especially important in a production environment. The encryption key is required for restoring or moving the database.
Click Exit to close the SSRS Configuration Manager.
SSRS should now be configured. In Chapter 8, you will learn how to publish your reports. At that time, you will return to this tool to determine the web service URL and web portal URL.
Configuring Local SSRS Settings
There is one very frustrating problem that you will encounter if you install the SSRS instance locally related to security. In order to launch the web portal or publish reports, you will need to run the web browser and SSDT as an administrator. This feature helps prevent applications from making changes to the operating system without your knowledge and permission.
To get around this issue, follow these steps:
Determine the web portal URL by launching Reporting Service Configuration Manager. Click the Web Portal URL page and note the link. Do not click it.
Launch your web browser using the Run as an Administrator option.
Navigate to the URL determined in step 1.
Open the security settings of the web browser and add the current site to the Trusted Sites.
Click OK and close the browser.
Launch the browser again using the Run as an Administrator option.
Navigate to the web portal URL once again.
Click Manage Folder as shown in Figure 1-17.
Figure 1-17. The Manage Folder link
Click Add group or user.
Type in your computer or domain plus the account as the Group or user.
Select Content Manager as the role. The dialog will look similar to that in Figure 1-18.
Figure 1-18. The security for Home
Click OK.
Click the gear icon found at the top right of the page and select Site Settings as shown in Figure 1-19.
Figure 1-19. The Site Settings link
Select the Security page.
Click Add group or user.
Enter your account name and click System Administrator. The dialog will look like that in Figure 1-20.
Figure 1-20. The site settings
Click OK.
You will learn more about these security settings in Chapter 9. If you have followed these instructions but still encounter security errors when launching the web portal or publishing reports, see the article found at the following site for more information: https://msdn.microsoft.com/en-us/library/bb630430.aspx .
Determining the SQL Server Name
To follow the examples in this book, you will need to connect to your SQL Server instance when creating data sources. A SQL Server name has two parts: a computer name and an instance name. For example, a SQL Server located on a server named MyServer and an instance name of Inst1 can be reached with MyServerInst1. Often, SQL Server will be installed as the default instance. In that case, you do not need to supply the instance name which is actually MSSQLSERVER; you can supply just the computer name.
In the section “Installing SQL Server with SSRS,” you were instructed to install SQL Server as the default instance. If you supplied an instance name instead, you will need that name to connect to the database. To find out the instance name, you will need to launch SQL Server Configuration Manager.
Note
If you are using a network SQL Server instead of a locally installed instance, ask the person who is responsible for that server for the correct computer and instance names.
Once Configuration Manager is running, click SQL Server Services. If you see MSSQLSERVER, you have a default instance. If you see anything else, that is your instance name. Figure 1-21 shows both a default instance and a named instance called SSRS.
Figure 1-21. The SQL Server Configuration Manager
The SQL Server Configuration Manager utility has many other uses that are beyond the scope of this book.
Restoring the AdventureWorks Database
There are quite a few pieces to get into place in order to follow the examples in this book. The last is the AdventureWorks database which is often used as a sample database for SQL Server. Because the download locations change from time to time, browse to www.codeplex.com and then search for Microsoft SQL Server Product Samples: Databases. On that page, you may see links for several different versions. Make sure you find the download for AdventureWorks2016.bak.
Note
At the release of SQL Server 2016, the CodePlex page had an AdventureWorks2016CTP3.bak file available but not an AdventureWorks2016.bak file. The CTP3 designation refers to Community Technology Preview 3, a Beta version of SQL Server. If the release version is not available, the CTP3 version will work, but you will need to change the database name during the restore process.
Follow these steps to restore the database:
Download the AdventureWorks2016.bak or AdventureWorks2016CTP3.bak file.
The downloaded file most likely ended up in your Download folder. In order to restore the file, you will need to move it. If C: emp doesn’t exist, create it. Move the file to the new folder.
Launch SQL Server Management Studio.
Use the server name you determined in the previous section when prompted to connect. If you installed SQL Server locally, you can use localhost, (local), or a period as shown in Figure 1-22.
Figure 1-22. Connect to Server dialog
Click the Connect button.
In the Object Explorer, right-click on Databases and select Restore Database as shown in Figure 1-23.
Figure 1-23. The Restore Database selection
On the Restore Databases dialog, select Device.
Click the ellipsis as shown in Figure 1-24.
Figure 1-24. The Restore Database dialog
On the Select backup devices dialog, click Add and then navigate to the file as shown in Figure 1-25.
Figure 1-25. The Select backup devices dialog
Click OK to accept the file.
If the CTP3 file is the only one available for download, change the Database property from AdventureWorks2016CTP3 to AdventureWorks2016.
Click OK to start the restore.
Once the restore is complete click OK to dismiss the restore utility.
Expand and refresh the Databases folder. You should be able to see the AdventureWorks2016 database in place as shown in Figure 1-26.
Figure 1-26. The new database
Exit SSMS.
In this case, the source of data and the SSRS databases are hosted on the same SQL Server instance. In most production environments, they would be hosted on separate servers.
Taking a Tour of SSDT
You will be spending quite a bit of time using SSDT throughout many of the chapters of this book. Take some time now to become familiar with it by following these steps:
Launch Visual Studio 2015.
Since this is the first time that it is launched, you must configure a couple of settings. Select Business Intelligence Settings.
Choose a theme that you prefer as shown in Figure 1-27.
Figure 1-27. Setting up the environment
Click Start Visual Studio
Once Visual Studio is running select File ➤ New ➤ Project as shown in Figure 1-28.
Figure 1-28. Create a new project
On the New Project dialog, select Report Server Project as shown in Figure 1-29.
Figure 1-29. Select Report Server Project
Click OK to create the project.
There are several windows that you will use while developing reports. You’ll learn all about them starting in Chapter 2. For now, if you are not familiar with Visual Studio, take some time to learn how the windows work. Each window can be repositioned, auto-hidden, or closed. Figure 1-30 shows the icons found at the top of each window.
Figure 1-30. The window icons
The Auto Hide feature that you can also enable by clicking the pin icon hides the window without actually closing it. You can see the title on the edge of the program, and, by clicking the title, the window opens just when you need it. That’s a nice feature to give you more room to work.
By clicking and dragging the window titles, you can move windows around. To see where they will end up, pay attention to the markers as you mouse over them and the areas that are highlighted as shown in Figure 1-31. The window will end up in the highlighted area when you drop the window.
Figure 1-31. Moving the windows
If you close a window, you can always get it back by looking in the View menu. If you decide to go back to the default configuration, click Window ➤ Reset Window Layout.
When you close out of Visual Studio , the next time you open it, you can easily open that last project by looking in File ➤ Recent Files and Solutions. You could also select File ➤ Open ➤ Project/Solution and browse to the project.
You may be wondering about the difference between a project and a solution. A solution is just a container that holds one or more projects. The projects can be of the same type, such as all SSRS projects. In some cases, the projects may be related to a topic area but may all be different technical types. For example, there could be a database project, an SSRS project, a SQL Server Integration Services Project, and a SQL Server Analysis Services project within the same solution. For example, the solution could be used to develop a data mart.
Depending on the value of a setting, the solution name will show up only if it contains more than one project. You can modify that setting if you choose by selecting Tools ➤ Options. In the Options dialog, expand Projects and Solutions and select the General page. If Always show solution is checked, the solution name will show up even if it contains one project. Figure 1-32 shows the dialog if you wish to make the settings change.
Figure 1-32. The Always show solution option
Summary
SQL Server Reporting Services is a wonderful feature of SQL Server that allows you to create reports that can be deployed for your organization’s use. SSRS 2016 boasts a brand-new user interface called the web portal with the traditional paginated reports, key performance indicators (KPIs) , and mobile reports.
To get your development environment set up, you will need to download and install several components. Luckily, they are all free downloads from Microsoft. By following the instructions in this chapter, you will be ready to learn how to develop and publish SSRS reports.
In Chapter 2, you will learn how to create your first reports by using a wizard.