Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Conferences
Free Learning
Arrow right icon
SQL Server 2016 Reporting Services Cookbook
SQL Server 2016 Reporting Services Cookbook

SQL Server 2016 Reporting Services Cookbook: Your one-stop guide to operational reporting and mobile dashboards using SSRS 2016

eBook
€8.99 €36.99
Paperback
€45.99
Subscription
Free Trial
Renews at €18.99p/m

What do you get with Print?

Product feature icon Instant access to your digital eBook copy whilst your Print order is Shipped
Product feature icon Paperback book shipped to your preferred address
Product feature icon Download this book in EPUB and PDF formats
Product feature icon Access this title in our online reader with advanced features
Product feature icon DRM FREE - Read whenever, wherever and however you want
OR
Modal Close icon
Payment Processing...
tick Completed

Shipping Address

Billing Address

Shipping Methods
Table of content icon View table of contents Preview book icon Preview Book

SQL Server 2016 Reporting Services Cookbook

Chapter 1. Getting It Ready – Configuring Reporting Services

In this chapter, we will cover the following recipes related to installation and configuration of Microsoft SQL Server Reporting Services:

  • Selecting the edition
  • Installing Reporting Services with default configurations
  • Installing Reporting Services-related tools
  • Accessing Reporting Services Configuration Manager
  • Configuring the service account of Reporting Services
  • Configuring Reporting Services URLs
  • Configuring the Reporting Services database
  • Executing post-installation tasks

Introduction

Stepping back to the year 2004, the release of Reporting Services as a post-release add-on to Microsoft SQL Server 2000 was a big in with the market as there were not many user-friendly reporting tools out there. Microsoft makes it easy and user-friendly; this worldwide-accepted catchphrase was expected by developers for the same and Microsoft kept the word again. Although it initially focused more on IT-driven reports, it became the most wanted and in-demand reporting tool for report designers and consumers.

Rosetta, the code name used for Reporting Services, was a perfect match. The Rosetta Stone found in 1799 explained the mystery of Egyptian hieroglyphics because it provided information needed to understand their history. Reporting Services does the same; it allows you to have the mystery or reasons for the success of your business revealed.

The first release of Reporting Services did not provide a wide range of data regions for addressing all Enterprise requirements, but it offered enough. However, the key was its architecture. It was designed to run under Internet Information Services as a middle-tier service, making it a server-based and web-based reporting platform. Reports designed with Reporting Services were able to be sourced by any type that has .NET managed provider, OLE DB provider, or ODBC provider. Since most Reporting Services processors and services were distributed, obtaining and processing data was efficient and extensible. The rich architecture used was supported with modern rendering extensions and delivered reports in HTML, PDF, and Excel. In addition to that, it allowed us to have our own extensions, making sure that reports were delivered as per the requirements and printing devices used.

Along with other Reporting Services releases, it was significantly improved and enhanced, making it the best reporting platform in the world. Before Microsoft SQL Server 2016, there were four more releases, with SQL Server 2005, 2008 R2, 2012, and 2014. Among all of these fantastic releases, 2005, 2008 R2, and 2016 are considered major releases of Reporting Services.

The latest release, Reporting Services 2016, is becoming the foremost reporting platform because of the features added, enhancements done on existing features, and the completeness in terms of modern reporting and BI platform. Since there were no major enhancements after 2008 R2, we thought that Reporting Services was about to retire, but the 2016 release showed us that it is still a part of the Microsoft BI ecosystem.

This chapter focuses on the installation and configuration of Reporting Services 2016. The recipes in this chapter explain typical installation and configurations as well as further and infrequent configurations that go beyond the traditional installation and configurations. You will be guided through the installation using recipes, and we will explain which sections are important, why they are important, and how they should be configured.

Selecting the edition

The edition of SQL Server 2016 should be selected based on your needs. SQL Server 2016 offers five downloadable editions, targeting specific business use cases for different prices and levels of capabilities. These five editions are Enterprise, Standard, Web, Developer, and Express. Generally, for an organization, you select one of the premium editions (which is Enterprise) or one of the core editions (which is Standard). There is another premium edition called Analytics Platform System (APS), formerly Parallel Data Warehouse (PDW); that will not be discussed in this book as it is beyond the scope. For an individual, it is always the Developer edition that has all functionality of the Enterprise edition. It can be downloaded and installed without paying Microsoft. Yes, it is free for developers and is licensed for use as a development or test system, not as a production server.

SQL Server 2012 and 2014 had an edition called Business Intelligence, which is no longer available with SQL Server 2016. You can try out Reporting Services with Enterprise Edition without purchasing, as it is available for a 180-day trial period. If you need to use the cheapest edition, which is Express, you need to have Express with Advanced Services.

Getting ready

Features supported by editions are different. For understanding, without discussing all features, we will see the features that are supported only by Enterprise edition.

The following features are supported only by Enterprise edition:

  • Mobile reports and KPIs
  • Data-driven report subscription
  • Scale out deployment
  • Alerting
  • Power view

Reporting Services maintain two databases: ReportServer database and ReportServerTempDB. It does not necessarily require the same edition for databases as you have used for Reporting Services. The following list shows you which editions of the Database engine can be used for specific editions of Reporting Services:

  • Enterprise edition of SSRS: Enterprise or Standard edition database engine
  • Standard edition of SSRS: Enterprise or Standard edition database engine
  • Web edition of SSRS: Web edition database engine (local only)
  • Express edition of SSRS: Express edition database engine (local only)
  • Developer edition of SSRS: Enterprise, Standard, or Developer edition database engine

How to do it...

  1. Select the edition based on the capabilities required for your application. If you need the aforementioned items that are supported only by the Enterprise edition, then you have no option but select the Enterprise edition. If they are not required, then the Standard edition is enough for your implementation. Cost and limited budget are some other factors for selecting the Standard edition.
  2. Select the Developer edition for development or testing, not as a production server.
  3. Try using Web or Express, which have very limited features. These two editions are good for small-scale implementations.

How it works...

You will be able to create, deploy, and consume reports with functionalities as per the features offered by the selected edition. Not only that, but the usage of processors of the system and memory consumption are based on the edition you have selected. Processors are more relevant to database engine. The Enterprise edition will use operating system maximum, but Standard and Web can utilize only four sockets or 24 cores. If you have selected, Express edition, it is limited to one socket or four cores.

Reporting Services consume memory for various operations. If the edition is Enterprise, it can consume operating system maximum. However, if the edition is Standard or Web, it consumes only 64 GB and, Express edition consumes only 1 GB.

See also

For more information about feature comparison between editions, visit the following URL:  https://msdn.microsoft.com/en-us/library/cc645993.aspx

Installing Reporting Services with default configurations

Installing Reporting Services is not a complex process. It is a part of SQL Server installation; just like selecting any other component, Reporting Services can be selected as a required component. Reporting Services 2016 offers two modes: Native and SharePoint integrated. SharePoint integrated mode will be discussed in detail in Chapter 9 , SharePoint Integration. Hence, this recipe focuses on Native mode.

Native mode is the default Reporting Services mode. You can install Reporting Services by either launching the wizard or using the command line.

Getting ready

It is always better to check and see whether your system is ready for Reporting Services before installing it. This check-up can be done using a tool given with the installation, which is called System Configuration Checker.

You need to make sure that you have admin rights on the system that you intend to run the installation on. For that, you need to be a member of a local administrator group, or you need to have credentials of an account that has admin rights.

During the installation, you can decide whether the installation wizard should install files and configure Reporting Services with default configurations, or only install files. If you select Install only, then you need to do the configuration later using Reporting Services Configuration Manager. Selecting the Install and configure option allows you to use Reporting Services immediately after setup is finished. However, this option will not be available if Database Engine Services is not selected as part of the instance features or the database engine is not installed on the same instance.

How to do it...

Let's go through the specific steps and options you select during SQL Server installation when you do the installation for Reporting Services. Note that we will not go through every page in the installation wizard, but only the pages related to this recipe. The steps are as follows:

  1. Once the installation wizard is launched by executing setup.exe, it opens SQL Server Installation Center, which has multiple tabs. The default tab is opened, which is Planning, and the detail page has the link for System Configuration Checker. The following screenshot shows this:

    How to do it...

    Figure 1.01

  2. When you click on System Configuration Checker, the tool goes through a set of predefined rules and tells you whether the system is ready for installation of SQL Server or not. The following screenshot shows the result of it:

    How to do it...

    Figure 1.02

  3. If everything is okay, continue with the installation. When you see the Feature Selection page, make sure Database Engine Services and Reporting Services - Native are selected:

    How to do it...

    Figure 1.03

  4. When you see Reporting Services Configuration, select the Install and configure option to install Reporting Services with the default configurations:

    How to do it...

    Figure 1.04

Now you can continue with the other pages and complete the installation.

How it works...

Since the installation is done with the Install and configure option, it installs the following items/features for us:

  • Report Server service:
    • Report Server Web Service
    • Report Server Web Portal
    • Other background processing applications

  • Reporting Services Configuration Manager
  • Reporting Services command-line utilities

In addition to that, it configures following items too:

  • Service account for the Report Server service
  • Report Server Web Service URL
  • The Web Portal URL
  • Report Server databases
  • Service accounts for accessing Report Server databases

To verify the installation, you can access the Web Portal and see whether it works or not. The URL for the Web Portal is different based on the installation:

  • With a default instance: http://MachineName:80/Reports
  • With a named instance: http://MachineName:80/Reports_InstanceName

This image shows the Web Portal configured with a named instance:

How it works...

Figure 1.05

There's more...

If you open Reporting Services Configuration Manager (this will be discussed in detail in the Accessing Reporting Services Configuration Manager recipe), you will see that some of the services related to Reporting Services are not configured. Unattended Execution Account, Email Setting, Encryption Keys, Subscription Settings, Scale-out Deployment, and Power BI integration are not configured as part of the installation, and they need to be configured manually using Reporting Services Configuration Manager.

See also

The next recipe, Installing Reporting Services-related tools, discusses the other required installations for completing a Reporting Services installation.

Installing Reporting Services-related tools

Installing only Reporting Services does not make your environment ready for working with Reporting Services. In order to author reports, you need Report Designer, which provides an interface for designing reports with various data regions. Report Designer ships with SQL Server Data Tools (SSDT), which was previously called Business Intelligence Development Studio (BIDS) with SQL Server 2005, SQL Server Data Tools (SSDT) with SQL Server 2012, and Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio (SSDTBI) with SQL Server 2014. SSDT is based on Visual Studio and it provides not only Report Designer for building reporting solutions, but also many other interfaces for working with relational databases, Azure SQL databases, SSIS, and SSAS solutions.

Until SQL Server 2014, this tool was automatically installed with SQL Server, but now you need to download it separately and install it.

The other most important tool that is required is SQL Server Management Studio (SSMS). This is not just for Reporting Services; it's an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. This was part of the default installation with previous versions of SQL Server, but now it needs to be downloaded separately and installed as it is a standalone tool.

Getting ready

You can find links to download SSDT and SSMS using any search engine. But the Microsoft SQL Server team has given us an easier way of finding the relevant download page. These links are given with the SQL Server installation wizard, and this makes sure that you download the latest versions of SSDT and SSMS.

How to do it...

Note that the SQL Server installation page has a number of installations related to SQL Server, but we focus only on the tools related to this book. Here is the way of installing the related tools:

  1. Execute the SQL Server setup.exe to open SQL Server Installation Center. Click on the Installation tab; this shows the various types of installation that can be done with it:

    How to do it...

    Figure 1.06

  2. Click on Install SQL Server Management Tools to open the web page that provides links for downloading Management Studio. This page shows you the link for downloading the latest SSMS.

    Note

    Note that at the time of writing this book, the version of SSMS is 13.0.15000.23, and it is June 2016 generally available release. This version of SSMS is free and does not require any license to install and use.

  3. You need to click on Install SQL Server Data Tools to install SSDT.

    Note

    Note that at the time of writing this book, the version of SSDT is 14.0.60525.0. Microsoft recommends that you install Visual Studio 2015 prior to this, and this update will replace SSDT RTM in Visual Studio 2015. If Visual Studio 2015 is not installed, SSDT will install Visual Studio Integrated Shell.

How it works...

Once both tools are installed, SSMS can be used to connect and manage Reporting Services instances and SSDT can be used to design and deploy reporting solutions. You can launch SSMS and connect with Reporting Services. We will talk more about this tool in Chapter 10, Administering and Managing Reporting Services.

You can create Reporting Services projects using SSDT. It allows you to create a new project, and select the Reporting Services template and Report Server Project type. You will see more on this in Chapter 2, Authoring Reports with SQL Server Data Tools.

There's more...

There is another useful tool called Report Builder, which is specifically available for power users. This does not mean that it cannot be used by developers, but as it is more of a Microsoft Office-like tool, it is more relevant to power users and business users. This will be discussed in detail in Chapter 4, Authoring Reports with Report Builder.

See also

A newly added tool called SQL Server Mobile Report Publisher is available with Reporting Services 2016. See Chapter 6, Authoring Mobile Reports with the Mobile Report Publisher, for more on it.

Accessing Reporting Services Configuration Manager

Reporting Services Configuration Manager is a tool that you can use to configure Reporting Services settings and related various components. It allows you to access the settings configured during the installation and modify them if required. In addition to that, you can use it to configure settings that are not available during installation.

Note

Note that you cannot use Reporting Services Configuration Manager to manage SharePoint integrated report servers. Prior to SQL Server 2012, SharePoint integrated reporting services instances were able to manage with it, but starting with SQL Server 2012, SharePoint integrated mode is managed and configured by using SharePoint Central Administration.

Unlike SQL Server Management Studio, Reporting Services Configuration Manager is version-specific. You cannot use a Configuration Manager installed for a specific version in your machine to manage another instance of Reporting Services that is not the same version, even if it is a lower version. As an example, if you have installed Reporting Services 2016 Configuration Manager, you cannot use it to manage Reporting Services 2014.

Most of the settings configured through Configuration Manager are stored in the rsreportserver.config file. Even though it is possible to make changes to the file directly without using Configuration Manager, you should not do it unless it is really required. If you have to, make sure you have copy of the file before making any changes.

Getting ready

Reporting Services Configuration Manager is installed with the default installation. It allows you to manage the following items:

  • Report Server service account: You can update the account that is already added or you can add a new account for the service
  • Web Service URL: You can modify or create new URLs for accessing Report Server Web Service
  • Database: You can create or configure the report server database for the Report Server. It also allows you to connect with a database that is already created and set with the required content
  • Web Portal URL: You can modify or create new URLs for accessing the Reporting Services Web Portal
  • Email settings: You can configure an SMTP server or gateway that Report Server can use to deliver reports to e-mail addresses
  • Execution account: You can configure an unattended account that report server can use for accessing various sources on scheduled operations, or when user credentials are not given for operations
  • Encryption keys: You can back up, restore, or change the symmetric key used to encrypt stored connections and credentials
  • Subscription settings: You can configure an account for subscriptions that need to access file shares
  • Scale-out Deployment: You can add multiple report servers to scale out your reporting solution
  • Power BI integration: You can use this section to register a Power BI account if you plan to integrate your reports with Power BI

In order to access either a local report server or remote report server using Configuration Manager, you need local system administrator permission of the server.

How to do it...

Let's see how we can open Configuration Manager and see the configuration settings. The steps are as follows:

  1. When you open the Configuration Manager, it prompts you with a dialog box for Server Name and Report Server Instance:

    How to do it...

    Figure 1.07

  2. Enter the server name and select the Report Server Instance for connecting with the required report server. If you have provided valid values and you have local administrator permission, it will open Configuration Manager for you:

    How to do it...

    Figure 1.08

  3. You can navigate through the tabs given in the left pane. Note that the Apply button is grayed out until you make a change. Once the changes are made, you need to click on the Apply button to save and apply your changes.

How it works...

The rsreportserver.config XML file holds most of the settings related to Configuration Manager. When you change any settings with Configuration Manager, the changes are applied to this file. You can see this file in the <installationpath>eport\Microsoft SQL Server\<instance name>\Reporting Services\ReportServer folder. You can open it and edit using any text editor or XML editor, but it is not recommended because your changes may prevent Report Server from functioning properly.

There's more...

There are some settings that are not available through Configuration Manager. For example, if you need to add another Authentication Type, it has to be manually added to the rsreportserver.config file, as Configuration Manager does not provide an interface for adding Authentication Types.

Some of the settings should only change with Configuration Manager even though it is possible to change them through rsreportserver.config. For example, encrypted values that are related to some nodes should not be changed through the rsreportserver.config file manually.

See also

In addition to the rsreportserver.config file, there are two more configuration files: rsreportserverservices.exe.config and rsreportdesigner.config. You can read more about them at https://msdn.microsoft.com/en-us/library/ms157273.aspx and https://msdn.microsoft.com/en-us/library/bb630448.aspx .

Configuring the service account of Reporting Services

The service account for Reporting Services is assigned during the installation, it is configured to run the Reporting Services service within the context of a specific Windows account. Installation allows you to select a local account, domain user account, or built-in account for the service. Once set, the installation makes sure that the account has the required permissions to access resources and run processes related to Reporting Services by adding the account to relevant security groups.

If you need to view or reconfigure the assigned account, Microsoft recommends that you use Reporting Services Configuration Manager without using Services Desktop Application. You might change the assigned account to a new account, or you might change the password set for the account if it has been changed at the Windows level. If you use Services Desktop Application to manage the service account, the additional settings that are linked with the assigned account will not be automatically changed. Specifically, it will not be added to relevant security groups. But Reporting Services Configuration Manager makes sure that all required settings are updated accordingly when a change is applied to the assigned account.

Getting ready

You can see the current assigned account using Reporting Services Configuration Manager. Once connected to the instance, you can click on the Service Account page to see the current account set or to set a new account for the service:

Getting ready

Figure 1.09

As you can see, there are two options for setting it. You can either select a built-in account or a user-defined (local or domain) account. If you have accepted the default during account selection, you should see the selected account as Virtual Service Account.

When selecting an account, either during installation or later using Configuration Manager, it is important to remember that the account you select has no excessive privileges. Not only that, but also you must make sure that it is a dedicated account for Reporting Services.

There are different types of account that you can select for Reporting Services:

  • Local system account: This account is a highly privileged account that should not be selected (or required) for Reporting Services. Generally, we should avoid highly privileged accounts for SQL Server services.
  • Network service account: This account has fewer privileges compared to the Local System account but has network log-on permissions. Therefore, it is not a bad practice to select this account as the Reporting Services service account. However, since this account is used by many applications, it might introduce a security threat, so it is better to avoid this account type as well.
  • Virtual service account: Virtual Service account was introduced with Windows Server 2008 R2, and SQL Server started supporting it from SQL Server 2012. It is simply a virtualized account based on a Network Service account but with its unique identifier. It is a local account, and you do not need to create or configure it. For SQL Server services, virtual accounts are automatically created during installation. Since it is a managed account, you do not need to worry about policies or password expiry issues, and this account type is recommended if you do not have a domain user account configured.
  • Local service account: This is a local account that is similar to an authenticated Windows user account in a local users group. This account accesses resources as a null session or without credentials. Therefore, this will not work as expected when report server needs to access external resources such as a remote database.
  • Local user account: There is a local, least-privileged Windows account configured for Reporting Services. This is recommended when Reporting Services is installed in a non-domain environment.
  • Domain user account: A least-privileged Windows domain account configured for Reporting Services. This is recommended when Reporting Services is installed in a domain environment.
  • Managed service account: This account is similar to a Virtual Service account, but this is a type of domain account. It is more secure than a domain user account and, unlike the Virtual service account, it needs to be created and configured by a domain administrator before it can be used.

How to do it...

In order to manage the assigned account, follow these steps:

  1. Start the Reporting Services Configuration Manager as shown in the preceding image.
  2. Make the necessary changes to the Report Server service account and click on Apply. You may change the account from Virtual service to Network service account for testing.
  3. The Apply button opens the SQL Server Connection Dialog window. It requires an account that has administrative privileges for applying grant rights script to a newly selected service account. If you have administrative permissions, select Current User - Integrated Security, or supply SQL Server credentials by selecting SQL Server Account:

    How to do it...

    Figure 1.10

  4. Click on OK to save the changes. Once completed, verify the messages in the results shown in the next image and see whether everything is successfully completed:

    How to do it...

    Figure 1.11

How it works...

Reporting Services Configuration Manager performs a few operations to apply your changes successfully:

  • A newly configured account is added to the report server group created on the local server.
  • The account is added to the necessary security roles in the SQL Server database engine instance that hosts the report server database, only if the connection configured to the report server database is set to service account. This account is not just a part of report server database; it gets added to the Master, Msdb, ReportServer, and ReportServerTempDB databases under different roles:
    • Master database: This account is added to RSExecRole
    • Msdb database: This account is added to RSExecRole, SQLAgentOperatorRole, SQLAgentReaderRole, and SQLAgentUserRole
    • ReportServer database: This account is added to db_owner and RSExecRole
    • ReportServerTempDB database: This account is added to db_owner and RSExecRole

  • The encryption key is updated, including the profile information of the account.

There's more...

Note that if you add a new service account to Reporting Services, the old account will not be removed from the database instance. See the next screenshot. It shows both accounts under the Users node; NT AUTHORITY\NETWORK SERVICE is the new account and NT SERVICE\ReportServer$SQL2016_M is the old account. Therefore, it should be manually removed if it is not required by the database engine:

There's more...

Figure 1.12

Configuring Reporting Services URLs

As you saw in the Installing Reporting Services with default configurations recipe in this chapter, once the installation is done, one way of verifying that the installation is successfully completed is by accessing one of the URLs that are configured automatically during the setup. If the installation is done with install only mode, then the URLs have to be configured using Reporting Services Configuration Manager, because only the install and configure option creates URLs for Reporting Services during the installation.

Installation with the default configuration reserves two URLs for Reporting Services, one for the Web Service application, and another for the Web Portal application. These URLs are defined as URL reservations in HTTP.SYS, which is an operating system component that listens to network requests and handles them. Even though it is possible to create and configure these URLs using different tools, it is recommended to use only Reporting Services Configuration Manager for creating and configuration. URL reservations are automatically created when you configure URLs using Reporting Services Configuration Manager.

URL reservations in HTTP.SYS define which URLs are configured for accessing Reporting Services applications. You can configure one or more URLs for accessing the Report Server Web Service and one or more URLs for accessing the report Web Portal. Although multiple URLs can be configured for web service and web portal, all URLs have to be connected to the web service virtual directory and the web portal virtual directory respectively. It is not possible to have multiple virtual directories.

Note

Note that Internet Information Services (IIS) is no longer used by Reporting Services to manage applications, and it is not required. All requests are completely handled by HTTP.SYS.

Getting ready

If you need to create or configure URLs using Reporting Services Configuration Manager, you need to understand how default URLs are created during the installation. This table explains how URLs are set by default:

Application

Instance type

Default URL

Reservation in HTTP.SYS

Web service

Default instance

http://<servername} reportserver<="" p="">

http://<servername}:80 reportserver<="" p="">

Web service

Named instance

http://<servername} reportserver_<instancename}<="" p="">

http://<servername}:80 reportserver_<instancename}<="" p="">

Web Portal

Default instance

http://<servername} reports<="" p="">

http://<servername}:80 reports<="" p="">

Web Portal

Named instance

http://<servername} reports_<instancename}<="" p="">

http://<servername}:80 reports_<instancename}<="" p="">

You can see this in the rsreportserver.config file located in the <installationpath}\Microsoft SQL Server\<instancename}\Reporting Services\ReportServer folder:

Getting ready

Figure 1.13

As mentioned before, you should not use any other tool to create or configure URLs, including the rsreportserver.config file.

In order to create or configure them, you need to make sure that you are a member of local administrator group of the server. In addition to that, if IIS is running in the same server, make sure that there are no virtual directories already configured with the names you have planned for Reporting Services virtual directories for applications.

How to do it...

Reporting Services Configuration Manager has two pages for managing URLs, one for web service applications, and another for web portal applications. It is shown in the following screenshot:

How to do it...

Figure 1.14

If you need to configure the Web Service URL, select the page and make the necessary changes:

  1. If you need to configure the Web Portal URL, you can select it to view and configure it. The preceding screenshot shows the settings related to the Web Service URL. As you see, the URL is formed by combining the machine name and virtual directory name.
  2. If you need to change the name of the virtual directory, you can simply change the text in the Virtual Directory input box. Remember that the name of the virtual directory is case sensitive, and only one virtual directory is allowed for the web service application.
  3. If you want to configure multiple URLs for the web service application, you can click on Advanced... and add more URLs.
  4. When you click on Advanced..., it opens a window that can be used to add both HTTP and HTTPS URLs. For testing purposes, let's allow users to connect with the web service using another port. Follow these steps to configure a URL with another port:
    1. Click on the Advanced... button to open the Advanced Multiple Web Site Configuration window.
    2. Click on Add in the HTTP section to add a new report server HTTP URL.
    3. Set the TCP Port as you want and click on OK to save it. Click on OK again to save the settings:

    How to do it...

    Figure 1.15

  5. Once the settings are saved, you should see two URLs appearing: one with port 80, which is the default, and another with port 50000. When you set a port, make sure that it is not reserved by another application:

    How to do it...

    Figure 1.16

  6. You can click on either URL to open the web service application. The configuration of Web Portal URL is the same as the Web Service URL. The Web portal URL page also has the Advanced... button for configuring additional URLs. Let's see how we can change the name of the virtual directory for web portal.
  7. For the Web Portal URL, the default virtual directory name is formed as Reports_<instancename>. As you can see in the following image, in our testing machine, it has been set as Reports_SQL2016_M and the URL has been formed as http://DINESH-LAP01:80/Reports_SQL2016_M:

    How to do it...

    Figure 1.17

  8. Let's change the name of the virtual directory to ProductionReports, as shown next. Once changed, the Apply button must be clicked on to save the changes. Note that you need to make sure that there is no virtual directory in IIS with the same name, if IIS is installed in the same server:

    How to do it...

    Figure 1.18

    You can click on the new URL and see whether it opens web portal or not.

How it works...

As we discussed before, Reporting Services Configuration Manager creates URL reservations in HTTP.SYS when changes are applied. If you open the rsreportserver.config file, you will see how changes are applied to the file.

As we set an additional port to the web service application, we can set additional IP addresses or hostnames for forming new URLs. One advantage with additional URLs is that you can provide different URLs for different audiences, as shown here:

  • For internal (or Intranet) users: http://server/reports
  • For external (or Internet) users: http://www.company.com/reports

How it works...

Figure 1.19

Note

If you have multiple instances installed on the same machine, you need to have a proper naming convention to avoid conflicts with URL reservations.

There's more...

When accessing either web service or web portal in the local server, you can use localhost instead of an IP address or server name. You can simply call applications using http://localhost/reportserver and http://localhost/reports . The http://localhost URL is interpreted as http://127.0.0.1 . However, if you have configured URLs to the computer name or a single IP address, this will not work unless you have added an additional reservation for 127.0.0.1.

Configuring the Reporting Services database

Reporting Services stores and maintains different types of information such as settings, metadata, and objects related to its applications. Some of this information is stored in configuration files and some of it, specifically metadata and objects, is stored in a SQL Server database. Reporting Services native mode uses two databases named ReportServer and ReportServerTempDB by default. The ReportServer database is the primary storage for holding all metadata and objects. ReportServerTempDB is for maintaining temporary information.

Note

Reporting Services uses three databases if the mode is SharePoint integrated mode. In addition to the ReportServer and ReportServerTempDB databases, it uses a third one to hold altering metadata.

If the Reporting Services instance is the default instance, the names of these databases are set as Report Server and ReportServerTempDB. If the instance is a named instance, then the default names become ReportServer$<instancename> and ReportServerTempDB$<instancename>.

The Reporting Services database is used to maintain report definitions, report models, shared data sources, schedules, security information, and snapshots. You should treat this database as a normal database; hence, it should be regularly backed up and should be a part of High Availability and Disaster Recovery strategies.

ReportServerTempdDB stores temporary data, such as session and cache data. Since it maintains temporary data, you do not need to have a backup strategy for this database. However, if this database gets corrupted or accidently deleted, you can create it using the CatalogTempDB.sql query located in the <installationpath>\Microsoft SQL Server\<instancename>\Reporting Services\ReportServer folder.

Getting ready

Similar to Reporting Services URLs discussed in the previous recipe, if you selected Install and Configure during the installation, these two databases are created automatically as a part of the installation. If you selected Install only, then you need to use Reporting Services Configuration Manager to create databases. You can host these databases either in a local database engine instance or in a remote database engine instance. Reporting Services supports the following database engine versions for hosting its databases:

  • SQL Server 2016
  • SQL Server 2014
  • SQL Server 2012
  • SQL Server 2008 R2
  • SQL Server 2008

However, you need to make sure that the Reporting Services database is hosted in the correct edition:

  • Enterprise edition: The database can be hosted in Enterprise or Standard edition
  • Standard edition: It can be hosted in Enterprise or Standard edition
  • Web edition: The database can be hosted in Web edition (local only)
  • Express edition: The database can be hosted in Express edition (local only)
  • Developer edition: It can be hosted in Enterprise, Standard, or developer edition

How to do it...

You can use Reporting Services Configuration Manager to create and configure the report server database. Or you can use it to view or modify settings related to the database that is already configured. Use the following steps to create or modify the report server database:

  1. Open Reporting Services Configuration Manager and go to the Database page.
  2. You should see two sections: Current Report Server Database and Current Report Server Database Credential. Click on the Change Database button:

    How to do it...

    Figure 1.20

  3. Change Database starts a wizard. You need to select a task from first page; select Create a new report server database to create a new one or select Choose an existing report server database to add an existing one. Select the first option for standalone report server.
  4. The next page allows you to select the hosting server and credentials to connect with it. Note that the credential required in this page is for creating the database only. It will not be used by Reporting Services later to connect with the database:

    How to do it...

    Figure 1.21

  5. Select the hosting server and click on Next after setting the credentials. The next page allows you to set Database Name and Language.
  6. There is no restriction on the database name. You can enter the name of the database as you want. However, it is always better to have a proper naming convention and name it accordingly. As you can see, the name of the temp database is automatically set using the database name. Click on Next to continue:

    How to do it...

    Figure 1.22

  7. The next page is for setting credentials to connect to the database by Reporting Services. You have three options: Windows credentials, SQL Server credentials, or Service credentials. If the database hosting server is a remote server, you should select either Windows or SQL Server credentials; otherwise, you can select one of these as per your requirements:

    How to do it...

    Figure 1.23

  8. Everything required is set. Click on Next to continue. You should see a summary page with all your input. Continue with the wizard to complete the database configuration.
  9. If you go back to the second section of the Database page in Reporting Services Configuration Manager, you will see that there is a button named Change Credentials. This is for changing the credentials configured for Reporting Services to connect with the report server database. Although this gets configured either during the installation or while configuring the report database, you may use this option for changing credentials in the following situation:
    • The password set to the local or domain account is changed
    • The authentication type needs to be changed

How it works...

The wizard creates both ReportServer and ReportServerTempDB in the selected SQL Server instance. It uses codes stored in the <installationpath>\Microsoft SQL Server\<instance name>\Reporting Services\ReportServer folder to create these databases. Once the report server databases are created, report server uses the credentials stored for accessing report databases. During the configuration, it creates the encryption keys required and adds the necessary permissions for accessing the database.

Note

Users who access reports do not require permissions for report database or login for report database server. Report server uses credentials stored for accessing the database when required.

Reporting Services stores the connection for report database in the rsreportserver.config file in an encrypted format. Depending on the authentication type selected, some of the nodes related to the connection may be empty. As an example, if the authentication type selected is Service Credentials, then LogonDomain, and LogonCred are empty:

How it works...

Figure 1.24

In order to verify the report database configuration, log in to the SQL Server instance that hosts the databases. You should see two databases configured with the names you used:

How it works...

Figure 1.25

There's more...

Remember, Microsoft does not recommend using these databases directly to query or modify data. It is an internal database for report server and should not be accessed and modified.

See also

As mentioned before, Reporting Services uses three databases when the mode is SharePoint integrated mode. Read more about SharePoint integrated mode in Chapter 9, SharePoint Integration.

Executing post-installation tasks

Although the installation and configurations are done and everything works fine as you expected, it does not mean that the process of installation and configuration is completed. You have to do a few more tasks to complete it. These tasks make sure that your Reporting Services is protected and maintained properly.

Getting ready

There are a few key tasks you should perform after installation and configuration:

  • Make sure that report database is a part of your backup strategy
  • Back up the rsreportserver.config file
  • Make sure you have backed up Encryption Keys
  • The startup type of the services related to Reporting Services and database engine are set as Automatic

How to do it...

The required tasks can be performed using standard techniques and available tools:

  1. Taking a backup of the ReportServer database is easy with Management Studio. Or it can be a part of your scheduled backup job. The main thing to consider is that it should be treated as a normal database and backed up regularly. You may implement a strategy that has one backup type, such as Full Database backup, or based on the complexity in terms of number of reports it holds, frequency of changes, or heavy user interaction. There can also be a strategy that has a combination of various backup types, such as Full, Differential, and Log.
  2. To back up the rsreportserver.config file, you can take a copy of it and keep it in a safe place. Since most settings related to report server are held with this, you need to make sure that you have a backup copy of it.
  3. You can use Reporting Services Configuration Manager to back up Encryption Keys. This should be done because this requires many instances when recovering after failure. We will discuss Encryption keys in more depth in Chapter 10, Administering and Managing Reporting Services.
  4. In order to make the startup type of related services automatic, use Services Desktop Application. Note that you should not use this application to change any other settings.

How it works...

The key thing to understand is how quickly you respond to an issue, troubleshoot an issue, or how you can recover your reporting environment in a disaster. There are a few standard recovery strategies for reporting environments, but these post installation tasks will definitely help you to recover in most situations. As an example, if you make a mistake while changing the rsreportserver.config file manually, you can simply restore it to the previous stage if you have a backup copy of it. If, for some reason, your ReportServer database becomes corrupted, you can get it recovered using the database backup. Therefore, post installation tasks should not be ignored and must be a part of the installation and configuration process.

There's more...

You can secure your reporting server more by configuring a High Availability and Disaster Recovery strategy. Considering the demand, requirements, and the budget, a proper HA and DR should be implemented.

Left arrow icon Right arrow icon
Download code icon Download Code

Key benefits

  • Get up to speed with the newly-introduced enhancements and the more advanced query and reporting features
  • Easily access your important data by creating visually appealing dashboards in the Power BI practical recipe
  • Create cross-browser and cross-platform reports using SQL Server 2016 Reporting Services

Description

Microsoft SQL Server 2016 Reporting Services comes with many new features. It offers different types of reporting such as Production, Ad-hoc, Dashboard, Mash-up, and Analytical. SQL Server 2016 also has a surfeit of new features including Mobile Reporting, and Power BI integration. This book contains recipes that explore the new and advanced features added to SQL Server 2016. The first few chapters cover recipes on configuring components and how to explore these new features. You’ll learn to build your own reporting solution with data tools and report builder, along with learning techniques to create visually appealing reports. This book also has recipes for enhanced mobile reporting solutions, accessing these solutions effectively, and delivering interactive business intelligence solutions. Towards the end of the book, you’ll get to grips with running reporting services in SharePoint integrated mode and be able to administer, monitor, and secure your reporting solution. This book covers about the new offerings of Microsoft SQL Server 2016 Reporting Services in comprehensive detail and uses examples of real-world problem-solving business scenarios.

Who is this book for?

This book is for software professionals who develop and implement reporting solutions using Microsoft SQL Server. It is especially relevant for professionals who are software engineers, software architects, DW/BI engineers, and DW/BI architects who perform simple to complex report authoring implementations. This book is also suitable for those who develop software solutions that integrate reporting solutions and are keen to learn about Microsoft SQL Server 2016’s features and capabilities.

What you will learn

  • Key capabilities, architecture, and components of Reporting Services
  • New features that have been added to Reporting Services
  • Design the architecture for reporting solutions
  • Design the architecture for BI solutions
  • Implement reporting solutions using Reporting Services
  • Improve the performance, availability, and scalability of the reporting solution
  • Enhance reporting solutions with custom programming and improved security
Estimated delivery fee Deliver to Malta

Premium delivery 7 - 10 business days

€32.95
(Includes tracking information)

Product Details

Country selected
Publication date, Length, Edition, Language, ISBN-13
Publication date : Nov 29, 2016
Length: 596 pages
Edition : 1st
Language : English
ISBN-13 : 9781786461810
Vendor :
Microsoft
Category :
Languages :
Tools :

What do you get with Print?

Product feature icon Instant access to your digital eBook copy whilst your Print order is Shipped
Product feature icon Paperback book shipped to your preferred address
Product feature icon Download this book in EPUB and PDF formats
Product feature icon Access this title in our online reader with advanced features
Product feature icon DRM FREE - Read whenever, wherever and however you want
OR
Modal Close icon
Payment Processing...
tick Completed

Shipping Address

Billing Address

Shipping Methods
Estimated delivery fee Deliver to Malta

Premium delivery 7 - 10 business days

€32.95
(Includes tracking information)

Product Details

Publication date : Nov 29, 2016
Length: 596 pages
Edition : 1st
Language : English
ISBN-13 : 9781786461810
Vendor :
Microsoft
Category :
Languages :
Tools :

Packt Subscriptions

See our plans and pricing
Modal Close icon
€18.99 billed monthly
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Simple pricing, no contract
€189.99 billed annually
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just €5 each
Feature tick icon Exclusive print discounts
€264.99 billed in 18 months
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just €5 each
Feature tick icon Exclusive print discounts

Frequently bought together


Stars icon
Total 141.97
MDX with Microsoft SQL Server 2016 Analysis Services Cookbook
€45.99
SQL Server 2016 Reporting Services Cookbook
€45.99
SQL Server 2017 Integration Services Cookbook
€49.99
Total 141.97 Stars icon
Banner background image

Table of Contents

12 Chapters
1. Getting It Ready – Configuring Reporting Services Chevron down icon Chevron up icon
2. Authoring Reports with SQL Server Data Tools Chevron down icon Chevron up icon
3. Advanced Report Authoring with SQL Server Data Tools Chevron down icon Chevron up icon
4. Authoring Reports with Report Builder Chevron down icon Chevron up icon
5. Improving User Experience – New Designing and Visualization Enhancements Chevron down icon Chevron up icon
6. Authoring Reports with the Mobile Report Publisher Chevron down icon Chevron up icon
7. Consuming Reports – Report Access Enhancement Chevron down icon Chevron up icon
8. Reporting Solutions for BI – Integration Chevron down icon Chevron up icon
9. SharePoint Integration Chevron down icon Chevron up icon
10. Administering and Managing Reporting Services Chevron down icon Chevron up icon
11. Securing Reports in Reporting Services Chevron down icon Chevron up icon
12. Custom Programming and Integration to .NET Applications Chevron down icon Chevron up icon

Customer reviews

Rating distribution
Full star icon Full star icon Full star icon Full star icon Empty star icon 4
(1 Ratings)
5 star 0%
4 star 100%
3 star 0%
2 star 0%
1 star 0%
Sushant.S.Rajpoot Oct 24, 2018
Full star icon Full star icon Full star icon Full star icon Empty star icon 4
Good Book. May refer.
Amazon Verified review Amazon
Get free access to Packt library with over 7500+ books and video courses for 7 days!
Start Free Trial

FAQs

What is the delivery time and cost of print book? Chevron down icon Chevron up icon

Shipping Details

USA:

'

Economy: Delivery to most addresses in the US within 10-15 business days

Premium: Trackable Delivery to most addresses in the US within 3-8 business days

UK:

Economy: Delivery to most addresses in the U.K. within 7-9 business days.
Shipments are not trackable

Premium: Trackable delivery to most addresses in the U.K. within 3-4 business days!
Add one extra business day for deliveries to Northern Ireland and Scottish Highlands and islands

EU:

Premium: Trackable delivery to most EU destinations within 4-9 business days.

Australia:

Economy: Can deliver to P. O. Boxes and private residences.
Trackable service with delivery to addresses in Australia only.
Delivery time ranges from 7-9 business days for VIC and 8-10 business days for Interstate metro
Delivery time is up to 15 business days for remote areas of WA, NT & QLD.

Premium: Delivery to addresses in Australia only
Trackable delivery to most P. O. Boxes and private residences in Australia within 4-5 days based on the distance to a destination following dispatch.

India:

Premium: Delivery to most Indian addresses within 5-6 business days

Rest of the World:

Premium: Countries in the American continent: Trackable delivery to most countries within 4-7 business days

Asia:

Premium: Delivery to most Asian addresses within 5-9 business days

Disclaimer:
All orders received before 5 PM U.K time would start printing from the next business day. So the estimated delivery times start from the next day as well. Orders received after 5 PM U.K time (in our internal systems) on a business day or anytime on the weekend will begin printing the second to next business day. For example, an order placed at 11 AM today will begin printing tomorrow, whereas an order placed at 9 PM tonight will begin printing the day after tomorrow.


Unfortunately, due to several restrictions, we are unable to ship to the following countries:

  1. Afghanistan
  2. American Samoa
  3. Belarus
  4. Brunei Darussalam
  5. Central African Republic
  6. The Democratic Republic of Congo
  7. Eritrea
  8. Guinea-bissau
  9. Iran
  10. Lebanon
  11. Libiya Arab Jamahriya
  12. Somalia
  13. Sudan
  14. Russian Federation
  15. Syrian Arab Republic
  16. Ukraine
  17. Venezuela
What is custom duty/charge? Chevron down icon Chevron up icon

Customs duty are charges levied on goods when they cross international borders. It is a tax that is imposed on imported goods. These duties are charged by special authorities and bodies created by local governments and are meant to protect local industries, economies, and businesses.

Do I have to pay customs charges for the print book order? Chevron down icon Chevron up icon

The orders shipped to the countries that are listed under EU27 will not bear custom charges. They are paid by Packt as part of the order.

List of EU27 countries: www.gov.uk/eu-eea:

A custom duty or localized taxes may be applicable on the shipment and would be charged by the recipient country outside of the EU27 which should be paid by the customer and these duties are not included in the shipping charges been charged on the order.

How do I know my custom duty charges? Chevron down icon Chevron up icon

The amount of duty payable varies greatly depending on the imported goods, the country of origin and several other factors like the total invoice amount or dimensions like weight, and other such criteria applicable in your country.

For example:

  • If you live in Mexico, and the declared value of your ordered items is over $ 50, for you to receive a package, you will have to pay additional import tax of 19% which will be $ 9.50 to the courier service.
  • Whereas if you live in Turkey, and the declared value of your ordered items is over € 22, for you to receive a package, you will have to pay additional import tax of 18% which will be € 3.96 to the courier service.
How can I cancel my order? Chevron down icon Chevron up icon

Cancellation Policy for Published Printed Books:

You can cancel any order within 1 hour of placing the order. Simply contact customercare@packt.com with your order details or payment transaction id. If your order has already started the shipment process, we will do our best to stop it. However, if it is already on the way to you then when you receive it, you can contact us at customercare@packt.com using the returns and refund process.

Please understand that Packt Publishing cannot provide refunds or cancel any order except for the cases described in our Return Policy (i.e. Packt Publishing agrees to replace your printed book because it arrives damaged or material defect in book), Packt Publishing will not accept returns.

What is your returns and refunds policy? Chevron down icon Chevron up icon

Return Policy:

We want you to be happy with your purchase from Packtpub.com. We will not hassle you with returning print books to us. If the print book you receive from us is incorrect, damaged, doesn't work or is unacceptably late, please contact Customer Relations Team on customercare@packt.com with the order number and issue details as explained below:

  1. If you ordered (eBook, Video or Print Book) incorrectly or accidentally, please contact Customer Relations Team on customercare@packt.com within one hour of placing the order and we will replace/refund you the item cost.
  2. Sadly, if your eBook or Video file is faulty or a fault occurs during the eBook or Video being made available to you, i.e. during download then you should contact Customer Relations Team within 14 days of purchase on customercare@packt.com who will be able to resolve this issue for you.
  3. You will have a choice of replacement or refund of the problem items.(damaged, defective or incorrect)
  4. Once Customer Care Team confirms that you will be refunded, you should receive the refund within 10 to 12 working days.
  5. If you are only requesting a refund of one book from a multiple order, then we will refund you the appropriate single item.
  6. Where the items were shipped under a free shipping offer, there will be no shipping costs to refund.

On the off chance your printed book arrives damaged, with book material defect, contact our Customer Relation Team on customercare@packt.com within 14 days of receipt of the book with appropriate evidence of damage and we will work with you to secure a replacement copy, if necessary. Please note that each printed book you order from us is individually made by Packt's professional book-printing partner which is on a print-on-demand basis.

What tax is charged? Chevron down icon Chevron up icon

Currently, no tax is charged on the purchase of any print book (subject to change based on the laws and regulations). A localized VAT fee is charged only to our European and UK customers on eBooks, Video and subscriptions that they buy. GST is charged to Indian customers for eBooks and video purchases.

What payment methods can I use? Chevron down icon Chevron up icon

You can pay with the following card types:

  1. Visa Debit
  2. Visa Credit
  3. MasterCard
  4. PayPal
What is the delivery time and cost of print books? Chevron down icon Chevron up icon

Shipping Details

USA:

'

Economy: Delivery to most addresses in the US within 10-15 business days

Premium: Trackable Delivery to most addresses in the US within 3-8 business days

UK:

Economy: Delivery to most addresses in the U.K. within 7-9 business days.
Shipments are not trackable

Premium: Trackable delivery to most addresses in the U.K. within 3-4 business days!
Add one extra business day for deliveries to Northern Ireland and Scottish Highlands and islands

EU:

Premium: Trackable delivery to most EU destinations within 4-9 business days.

Australia:

Economy: Can deliver to P. O. Boxes and private residences.
Trackable service with delivery to addresses in Australia only.
Delivery time ranges from 7-9 business days for VIC and 8-10 business days for Interstate metro
Delivery time is up to 15 business days for remote areas of WA, NT & QLD.

Premium: Delivery to addresses in Australia only
Trackable delivery to most P. O. Boxes and private residences in Australia within 4-5 days based on the distance to a destination following dispatch.

India:

Premium: Delivery to most Indian addresses within 5-6 business days

Rest of the World:

Premium: Countries in the American continent: Trackable delivery to most countries within 4-7 business days

Asia:

Premium: Delivery to most Asian addresses within 5-9 business days

Disclaimer:
All orders received before 5 PM U.K time would start printing from the next business day. So the estimated delivery times start from the next day as well. Orders received after 5 PM U.K time (in our internal systems) on a business day or anytime on the weekend will begin printing the second to next business day. For example, an order placed at 11 AM today will begin printing tomorrow, whereas an order placed at 9 PM tonight will begin printing the day after tomorrow.


Unfortunately, due to several restrictions, we are unable to ship to the following countries:

  1. Afghanistan
  2. American Samoa
  3. Belarus
  4. Brunei Darussalam
  5. Central African Republic
  6. The Democratic Republic of Congo
  7. Eritrea
  8. Guinea-bissau
  9. Iran
  10. Lebanon
  11. Libiya Arab Jamahriya
  12. Somalia
  13. Sudan
  14. Russian Federation
  15. Syrian Arab Republic
  16. Ukraine
  17. Venezuela