46,79 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Sprache: Englisch
Create interactive cross-platform reports and dashboards using SQL Server 2016 Reporting Services
About This Book
- 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
Who This Book Is 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
In Detail
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.
Style and approach
This comprehensive cookbook follows a problem-solution approach to help you overcome any obstacle when creating interactive, visually-appealing reports using SQL Server 2016 Reporting Services. Each recipe focuses on a specific task and is written in a clear, solution-focused style.
Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 416
Veröffentlichungsjahr: 2016
Ähnliche
Table of Contents
SQL Server 2016 Reporting Services Cookbook
SQL Server 2016 Reporting Services Cookbook
Copyright © 2016 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the authors, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.
First published: November 2016
Production reference: 1211116
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham
B3 2PB, UK.
ISBN 978-1-78646-181-0
www.packtpub.com
Credits
Authors
Dinesh Priyankara
Robert C. Cain
Copy Editor
Vikrant Phadkay
Reviewers
Gogula Aryalingam
Dave Wentzel
Project Coordinator
Shweta H Birwatkar
Commissioning Editor
Amey Varangaonkar
Proofreader
Safis Editing
Acquisition Editor
Vinay Argekar
Indexer
Mariammal Chettiyar
Content Development Editor
Amrita Noronha
Graphics
Disha Haria
Technical Editor
Akash Patel
Production Coordinator
Arvindkumar Gupta
About the Authors
Dinesh Priyankara is an experienced professional and database enthusiast with skills in database management systems and business intelligence, especially on the Microsoft SQL Server product suite. Possessing over 16 years of experience on data related technologies, he does training, consulting, and is a top contributor to the local SQL Server community. In recognition of his contributions and expertise he has demonstrated, he was awarded the Microsoft Most Valuable Professional (MVP) award for SQL Server for 11 consecutive years since 2006.
Dinesh is the Founder/Principal Architect of dinesQL (Pvt) Ltd (http://dinesql.com), a company that provides services on database, business intelligence, and training. In his spare time, he runs a blog at http://dinesql.blogspot.com with a follower base of over 100,000 from around the world and is a co-organizer of SQL Server Sri Lanka User Group (http://www.meetup.com/ssslug/).
Even though he has written many articles on the database area, this is his first official book as a co-author.
I would like to especially thank my daughter, Minupama Dulnethmi, who always encouraged me to complete my book with her fullest support. And I would also like to thank all of my family members and friends who helped on this in many ways.
My sincere appreciation to the entire team at Packt Publishing, with special thanks to Amrita Noronha and Vinay Argekar.
Robert C. Cain (http://arcanecode.com) is a Microsoft MVP, MCTS Certified in BI, and is the owner of Arcane Training and Consulting, LLC. He is also a course author for Pluralsight, with over 11 courses to his credit. Robert is a team mate at Linchpin People, and co-author of four books.
A popular speaker, Robert has presented at events such as the SQL PASS Summit, IT/Dev Connections, TechEd, CodeStock, and numerous SQL Saturdays. Robert has over 25 years’ experience in the IT industry, working in a variety of fields including manufacturing, insurance, telecommunications and nuclear power.
I would like to thank my wife and daughters for their patience as I worked on this book. I also wish to thank my coauthor, Dinesh. Without his help, this book would not have been possible. My editor, Amrita, needs special thanks; this book would not be what it is without her patience and guidance.
I would also like to thank two long-time mentors. The first is Adam Curry. From his humble beginnings as a radio personality and MTV VJ, Adam became a pioneer in the early days of the Internet. In addition, his implementation of the podcasting platform opened a wealth of knowledge to both myself and many others.
I would also be remiss not to acknowledge John C. Dvorak. From his early columns in PC Magazine to his various TV shows and podcasts, John provided a wealth of information that guided my career.
Finally, I would like to thank the pair of them for their work on The No Agenda Show (http://noagendashow.com). I spent many late nights working on the demos for this volume listening to their entertaining and informative podcast.
About the Reviewers
Gogula Aryalingam is a database and business intelligence professional with more than 11 years of experience in the field. He is a Microsoft MVP for 9 years on the data platform and SQL Server, and PASS Regional Mentor for South Asia. His passion is in building business intelligence solutions, writing about the things he works with and talking about them. He is actively involved with the technical community; he co-runs the SQL Server user group in Colombo, Sri Lanka, speaks at local and international technical events and blogs his experiences. Gogula possesses an MSc and is a Microsoft Certified Solutions Expert, while also having played a part in developing exams for Microsoft certifications.
Dave Wentzel is a Data Solutions Architect at Microsoft with the Azure Enablement Team. He helps customers with their Digital Transformation, focused on all things data science, big data, and SQL Server. He knows how to converge big, fast, and unstructured data with traditional relational data to create actionable intelligence. He takes his customer experiences and feedback back to the product groups at Microsoft to make better solutions. Dave has been working with SQL Server for, let’s just say, a long time, and with SSRS since its infancy. Dave shares his experiences at http://davewentzel.com/. He’s always looking for new customers. Would you like to engage?
www.Packtpub.com
For support files and downloads related to your book, please visit www.PacktPub.com.
Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.PacktPub.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at [email protected] for more details.
At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a range of free newsletters and receive exclusive discounts and offers on Packt books and eBooks.
https://www.packtpub.com/mapt
Get the most in-demand software skills with Mapt. Mapt gives you full access to all Packt books and video courses, as well as industry-leading tools to help you plan your personal development and advance your career.
Why subscribe?
Preface
SQL Server 2016 Reporting Services is truly revolutionary. SQL Server Reporting Services saw few enhancements since the SQL Server 2008R2 release. With SQL Server 2016, all of that has changed.
Many longstanding complaints of users have been addressed. Reports now render in HTML 5, allowing compatibility across a multitude of platforms. The old Report Manager has received a major overhaul, and with that a new name, the Report Portal.
The Report Portal provides many new capabilities. It will, of course, host the traditional SSRS reports (now renamed Paginated Reports). Now, though, it can host a variety of other items, such as KPIs and PowerBI Desktop reports.
An exciting new addition is the Mobile Report Publisher, allowing you to create reports that can easily be viewed across computers, tablets, and phones.
The installation and configuration has been enhanced and streamlined to make setting up a new instance of Reporting Services even easier. Management tools have been updated as well, providing an easier to use experience for administrators.
In this book, we’ll explore all the new, exciting enhancements that come with SQL Server 2016 Reporting Services. The recipes have been designed to get you up to speed quickly, allowing you to jump right in to all the great features included in SSRS 2016.
What this book covers
Chapter 1, Getting It Ready – Configuring Reporting Services, will show you how to plan for and install Reporting Services. We’ll see how to configure SSRS for Native mode, as well as gain an understanding of backend databases.
Chapter 2, Authoring Reports with SQL Server Data Tools, will begin your authoring of reports by working with SQL Server Data Tools (SSDT). You’ll see how to create a variety of reports using tables, matrixes, as well as charts. Finally, you’ll see how to publish reports to the SSRS server from within SSDT.
Chapter 3, Advanced Report Authoring with SQL Server Data Tools, will continue your education on SSDT by moving into some advanced report topics. You’ll learn how to use multiple datasets in the same report, and create charts and tables with multiple axis. You’ll also get a view of SSRS behind the scenes, looking at the XML code used within an SSRS report file.
Chapter 4, Authoring Reports with Report Builder, will focus on the newly revamped Report Builder tool. You’ll see how an end user could leverage this tool to create a variety of objects. Not just reports, but data sources and data sets.
Chapter 5, Improving User Experience – New Designing and Visualization Elements, will focus on the new visualization and design elements introduced with SSRS. You’ll see the new Sunburst and Treemap charts, as well as learning how to integrate the R language into your reports.
Chapter 6, Authoring Reports with the Mobile Report Publisher, explains that Microsoft addresses the need for reporting on mobile devices, such as phones and tablets, through the addition of the Mobile Report Publisher. In this chapter, you’ll learn how to use this brand-new tool to create reports customized for a variety of platforms.
Chapter 7, Consuming Reports – Report Access Enhancement, will explore the new Report Portal, the redesigned Report Manager. You’ll see how to manage reports and data caching. You’ll also see how to create the new KPIs, as well how to customize the portal for your environment.
Chapter 8, Reporting Solutions for BI – Integration, shows that Reporting Services is not the only tool in a BI developer’s tool box. In this chapter, you’ll see how to integrate other tools, such as PowerBI and SQL Server Analysis Services, into your SSRS reports.
Chapter 9, SharePoint Integration, explains that, in addition to the Report Portal, SharePoint can also act as a host for your SSRS reports. In this chapter, you’ll see how to configure SharePoint and SSRS to work together.
Chapter 10, Administering and Managing Reporting Services, reveals that as good as Reporting Services is just out of the box, understanding how to manage key components will make the experience even better. In this chapter, you’ll learn how to manage data sources, history, caching, scheduling, and other key topics that will create a better experience for your users.
Chapter 11, Securing Reports in Reporting Services, highlights that it is critical to protect your data from unauthorized eyes. In this chapter, you’ll learn how to secure your server and its reports. In addition, you’ll find recipes explaining how to secure the data within your reports.
Chapter 12, Custom Programming and Integration to .NET Applications, explains that .NET developers often have need to integrate reporting into their application. In this chapter, we’ll see the various techniques developers can use leverage the power of SSRS within their programs.
What you need for this book
To work through the recipes in this book, you will, of course, need a copy of SQL Server 2016. If you don’t have one, Microsoft has now released the SQL Server 2016 Developer Edition as a free download, and it will work just fine for working through the recipes.
You can download the developer edition at:
https://www.microsoft.com/en-us/sql-server/sql-server-editions-developers
or use the shortcut:
http://bit.ly/sql2016dev
In addition, you will want a copy of the brand-new SQL Server 2016 sample database, WideWorldImporters. Specifically, the data warehouse version of the sample, WideWorldImportersDW, was used for the examples in this book. You can find the samples on GitHub, at:
https://github.com/Microsoft/sql-server-samples/tree/master/samples/databases/wide-world-importers
or use the shortcut:
http://bit.ly/wwisample
You also need AdventureWorks 2014 multi-dimensional and tabular databases since they were used with some examples. You can find the samples database at: https://msftdbprodsamples.codeplex.com/releases/view/125550
Who this book is for
A wide variety of users will find this book valuable. Naturally, IT Professionals whose chief duties are the development of reports will find these recipes useful.
In today’s world, many end users are also developing their own reports, using tools such as Report Builder and the Mobile Report Publisher. They will find the recipes quite useful as they continue to develop their own reports.
Additionally, system administrators who are responsible for installation, security, and maintaining Reporting Services will find valuable information herein to guide them as the manage SSRS.
Finally, we’ve included a whole chapter aimed at .NET developers, showing how to integrate SSRS directly into their applications.
Sections
In this book, you will find several headings that appear frequently (Getting ready, How to do it, How it works, There's more, and See also).
To give clear instructions on how to complete a recipe, we use these sections as follows:
Getting ready
This section tells you what to expect in the recipe, and describes how to set up any software or any preliminary settings required for the recipe.
How to do it…
This section contains the steps required to follow the recipe.
How it works…
This section usually consists of a detailed explanation of what happened in the previous section.
There's more…
This section consists of additional information about the recipe in order to make the reader more knowledgeable about the recipe.
See also
This section provides helpful links to other useful information for the recipe.
Conventions
In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.
Code words in text, database table names, folder names, filenames, file extensions, path names, dummy URLs, user input, and Twitter handles are shown as follows: "We can include other contexts through the use of the include directive. "
A block of code is set as follows:
[default] exten => s,1,Dial(Zap/1|30) exten => s,2,Voicemail(u100) exten => s,102,Voicemail(b100) exten => i,1,Voicemail(s0)When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:
[default] exten => s,1,Dial(Zap/1|30) exten => s,2,Voicemail(u100) exten => s,102,Voicemail(b100) exten => i,1,Voicemail(s0)Any command-line input or output is written as follows:
# cp /usr/src/asterisk-addons/configs/cdr_mysql.conf.sample /etc/asterisk/cdr_mysql.confNew terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: "clicking the Next button moves you to the next screen".
Note
Warnings or important notes appear in a box like this.
Tip
Tips and tricks appear like this.
Reader feedback
Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.
To send us general feedback, simply send an e-mail to [email protected], and mention the book title via the subject of your message.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on https://www.packtpub.com/books/info/packt/authors.
Customer support
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
Downloading the example code
You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.
You can download the code files by following these steps:
Once the file is downloaded, please make sure that you unzip or extract the folder using the latest version of:
The code bundle for the book is also hosted on GitHub at https://github.com/PacktPublishing/SQL-Server-2016-Reporting-Services-Cookbook. We also have other code bundles from our rich catalog of books and videos available at https://github.com/PacktPublishing/. Check them out!
If you are using Tableau Public, you’ll need to locate the workbooks that have been published to Tableau Public. These may be found at the following link: http://goo.gl/wJzfDO.
Downloading the color images of this book
We also provide you a PDF file that has color images of the screenshots/diagrams used in this book. The color images will help you better understand the changes in the output. You can download this file from: https://www.packtpub.com/sites/default/files/downloads/SQLServer2016ReportingServicesCookbook_ColorImages.pdf.
Errata
Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/submit-errata, selecting your book, clicking on the errata submission form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded on our website, or added to any list of existing errata, under the Errata section of that title. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.
Piracy
Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any form, on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.
Please contact us at [email protected] with a link to the suspected pirated material.
We appreciate your help in protecting our authors, and our ability to bring you valuable content.
Questions
You can contact us at [email protected] if you are having a problem with any aspect of the book, and we will do our best to address it.
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:
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:
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:
How to do it...
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:
Figure 1.01
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:Figure 1.02
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:Figure 1.03
When you see Reporting Services Configuration, select the Install and configure option to install Reporting Services with the default configurations: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:
In addition to that, it configures following items too:
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:
This image shows the Web Portal configured with a named instance:
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:
Figure 1.06
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.
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:
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:
Figure 1.07
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:Figure 1.08
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:
Figure 1.09
