Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Reporting Services 2017 Setup



By:
Overview

SQL Server Reporting Services has evolved into a full-fledged reporting solution which maintains a strong presence in the current reporting, business intelligence, and reporting analytics market space.

Explanation

SQL Server Reporting Services or SSRS has been the go to reporting solution for SQL Server for almost a decade now. When it was first introduced, folks were used to working with Microsoft Access, and Crystal Reports was just storming into the reporting world.  Business Intelligence and its related processes were also beginning to shape the way data was presented. In many organizations, the ease of development, the web front end, and the security integration with Active Directory allowed SSRS to reign in as the reporting tool of choice. Still today it is a highly competent and able tool which has grown in feature availability and overall usage. For this tutorial we will focus on SQL Server 2017, but many of the features to be highlighted in this tutorial series apply to SQL Server 2016 and prior versions.

Installation and Configuration

Of course, the first steps that needs to be completed is installing SSRS. In the past SSRS was an integral part of the SQL Server installation process. For 2017, Report Services is now a separate, stand-alone install process. You first must download the SQL Server Reporting Services install file from https://www.microsoft.com/en-us/download/details.aspx?id=55252. The install process is like just about any other install process you complete; however, at the end of the install, either SSRS can be configured now or at a later time. We will not cover the individual installation screens as the they are covered in the installation instructions on the download site; we will however jump right into the configuration.

The configuration process requires that you already have a SQL Server in order to house the Reporting Services databases. The location of the reporting Services database is completely up to your organizationís requirements and needs; it can be on the same server as reporting-services although there will be contention issues. As an alternative you can place Reporting Services on one machine and then connect to another server to hold the databases.

To run the SSRS Configuration Manager, you will go to the Programs menu and select SQL Server Reporting Services Configuration Manager.  Initially, a connection to the SSRS service must be made.

Connect to SSRS - Description: Connect to SSRS

After connecting to the SSRS service, step 1 is instructing SSRS configuration manager which account to use for the SSRS service. This service account is actually set during the install, but can be changed to any local or Windows account (AD account).

SSRS Service Account - Description: SSRS Service Account

The report manager and the reports web portal URLís are assigned subsequently. In the below example, the virtual directory is set to ReportServer_SSRS and the default port for the website is set to port 80.

SET SSRS URL - Description: Set SSRS URL

Our next step is to actually set up connection to the SSRS databases. This link can be to existing databases or you can create two new databases. SSRS requires that you have both a regular SSRS database and a SSRS Temp database as shown below. These databases can be located on any server.

SMTP - Description: SMTP settings


Completing the SMTP email settings would be the ensuing process to complete. For this item, the sender address, the SMTP server, and the Authentication must be completed.

Email setup - Description: Email setup

Optionally, an Execution Account can be specified which allows for connectively to images on remote servers and connections to credential-less data sources.

Execution Account - Description: Execution Account

After configuring the execution account (for our killer app!), the next step is to backup (and when needed restore) the SSRS encryption keys. It is important that the key be backed up for both backup purposes and for the instance when SSRS is moved to a new server. This key preserves the security elements for the folders and reports on the SSRS website. We will discuss security in more detail later in this tutorial series.

Encryption Keys - Description: Encryption Keys

In a way similar to the Execution Account, a subscription account can also be defined to allow subscriptions to use a common account to access file shares. This account will be used by the subscription jobs to write files to the share specified in the subscription.

File Share - Description: File Share

If this server is included in an entire SSRS scale-out farm setup, then this instance can be added as part of a larger deployment process.

Scaleout - Description: Scaleout Deployment

The final and likely one of the newest steps in the configuration manager is to integrate SSRS with Power BI. Linking to Power BI allows users to publish (or Pin) reports to Power BI.

Power BI - Description: Link with Power BI.

This screen establishes the connection to your organizationís Power BI site and allows for the pinning of report to the Power BI site.

Power BI - Description: Power BI Link

Once SSRS is configured for use, the SSRS site is up and read to go!

SSRS UP - Description: SSRS UP and Running.

Development Tools

Now that the site is up and running, we need to explore the tools that are available to develop reports for the report server. You actually have two main choices when deciding which tools to develop reports. For light weight developers, the Report Builder tool can be used to develop reports whereas more cutting-edge developers generally use SQL Server Data Tools for Business Intelligence or SSDT-BI for short.

Both tools can be directly downloaded from Microsoft at:

Please note that these links change often as Microsoft releases updated editions of each of the tools.

As of this writing, the Report Builder is noted for SQL 2016, but in a testing environment, I have been able to connect to SQL 2017. It should also be noted that Report builder can also be downloaded directly from your SSRS web portal as shown below.

Report Builder Download

For SSDT-BI, it is generally recommended that you implement SSDT-BI as part of your Visual Studio installation. The Community Edition of Visual Studio 2017 can easily be used to house the SSDT-BI add-in. If you have a higher (i.e. paid) edition of Visual Studio, the add-in can certainly be supplemented as part of that edition. Once installed, several BI projects will be available in Visual Studio, as illustrated below.

Visual Studio - Description: Visual Studio Reporting Projects

Both of these tools are capable of developing reports for SSRS; most advanced developers will prefer to use SSDT-BI in Visual Studio. As such, in this series of tutorials we will use SSDT-BI. However, Report Builder is widely used by many data analysts to produce a quick report. Furthermore, a Report Builder developed report can be easily imported into a Visual Studio project for more advanced development, if necessary.

Installing Reporting Services and then subsequently the development tool, SSDT-BI are the first steps to working with SSRS. In the next phase in our tutorial series, we will use SSDT-BI to work through creating a new report and then move directly into reviewing the various options surrounding available data sources and their utilization in SSRS.

Additional Information

Last Update: 12/29/2017




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, February 21, 2018 - 3:24:17 AM - Cutting Edge Back To Top

Does the Report Builder not do 100% of the things SSDT-BI does?  If it does 100% of the things SSDT-BI does, then SSDT-BI has no "cutting-edge" advantage.


Learn more about SQL Server tools