Academia.edu no longer supports Internet Explorer.
To browse Academia.edu and the wider internet faster and more securely, please take a few seconds to upgrade your browser .
Enter the email address you signed up with and we'll email you a reset link.
- We're Hiring!
- Help Center
Microsoft sql server reporting services recipes
Take an active role online by participating in our P2P forums @
Related Papers
Le Quoc Thi
Christian Fermin
This book begins with you working along as Scott Guthrie builds a complete ASP.NET MVC reference application. He begins NerdDinner by using the File->New Project menu command within Visual Studio to create a new ASP.NET MVC Application. You'll then incrementally add functionality and features. Along the way you'll cover how to create a database, build a model layer with business rule validations, implement listing/details data browsing, provide CRUD (Create, Update, Delete) data form entry support, implement efficient data paging, reuse UI using master pages and partials, secure the application using authentication and authorization, use AJAX to deliver dynamic updates and interactive map support, and implement automated unit testing. From there, the bulk of the rest of the book begins with the basic concepts around the model view controller pattern, including the little history and the state of the MVC on the web today. We'll then go into the ways that MVC is different from ASP.NET Web Forms. We'll explore the structure of a standard MVC application and see what you get out of the box. Next we dig deep into routing and see the role URLs play in your application. We'll deep dive into controllers and views and see what role the Ajax plays in your applications. The last third of the book focuses entirely on advanced techniques and extending the framework.
Leopoldo Flores
Aditya Nambyar
anton zhong
fre pdf to study BI
Loading Preview
Sorry, preview is currently unavailable. You can download the paper by clicking the button above.
- We're Hiring!
- Help Center
- Find new research papers in:
- Health Sciences
- Earth Sciences
- Cognitive Science
- Mathematics
- Computer Science
- Academia ©2023
- Individuals
- Institutions & Businesses
- Wiley: Consumer
- Institutions and Businesses
- Search By Subject
- Browse Textbooks
- Knewton Alta
- Test Prep (View All)
- CPA Review Courses
- CFA® Program Courses
- CMA® Exam Courses
- CMT Review Courses
- Brands And Imprints (View All)
- Jossey Bass
- The Leadership Challenge
- Research Libraries
- Wiley Online Library
- Cochrane Library
- Open Access Journals
- Journal Collections (View All)
- Current Protocols
- Ernst and Sohn
- Hindawi Journals
- Special Collections
- Wiley Science Solutions
- Wiley Digital Archives
- Wiley Analytical Sciences
- Trending on Wiley
- Listen: This Study Shows Podcast
- An Article Free Future
- Research Supercharged by AI
- Introducing Research Exchange, Our New Submission Platform for Authors
- Publish My Research
- Author Services
- Find A Journal
- Open Access
- Publish My Book
- Wiley Editing Services
- Manuscripts
- Diversity is Not Enough
- What's Transparent Peer Review and How Can it Benefit You?
- A New Ecosystem of Scientific Sharing and What it Would Mean
- Preprints and Trust in Peer Review: A Q&A With Alberto Pepe of Authorea
- Classroom Resources
- Course Materials
- Learning Resources
- Wiley Efficient Learning
- Advancement Courses
- Degree Accelerator
- Re-Entering the Classroom in a Time of Trauma and Stress
- Cultivating an Inclusive Learning Experience
- Wiley "Stay the Course Grant" Winners Tell Their Stories
- 4 Things to Consider When Choosing an Online Platform That's Right for You
- Career Solutions
- Wiley Researcher Academy
- Diversity in Research Jobs
- Search by Subject
- Personal Career Development Books
- Teach Yourself Visually
- Determine Your Organization’s Digital Skills Level
- Limitless? What Can We Really Expect from 5G?
- The Need for Entrepreneurship in Sustainable Chemistry
- Art in the Anthropocene: What Do Art and Sustainability Have in Common?
- For Individuals
- For Institutions & Businesses
- Wiley Network
- About Wiley
- Corporate Responsibility
- Corporate Governance
- Leadership Team
- Cookie Preferences
Suggestions
Other results.
- United States
- Netherlands
- New Zeleand
- South Korea
- Switzerland
- United Arab Emirates
- United Kingdom
- Dummies Build your skills with trusted guides and expert how to's
- JK Lasser Easy-to-use tax guide
- Jossey Bass Improve student outcomes through meaningful teacher development
- The Leadership Challenge Leadership practices that lead to a more effective and engaged organization
- General & Introductory Computer Science
- Client/Server Technologies
Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports
Download Product Flyer
Description, about the author, permissions, table of contents.
Product not available for purchase
Paul Turley , Robert M. Bruckner
ISBN: 978-0-470-56311-3 April 2010 648 Pages
- Print Starting at just $49.99
While most users of SQL Server Reporting Services are now comfortable designing and building simple reports, business today demands increasingly complex reporting. In this book, top Reporting Services design experts have contributed step-by-step recipes for creating various types of reports.
Written by well-known SQL Server Reporting Services experts, this book gives you the tools to meet your clients' needs
Robert M. Bruckner is a technical lead with the Microsoft SQL Server product team. His core area of responsibility is the development of the report processing engine of Reporting Services. Robert frequently shares Reporting Services tips on his popular blog at http://blogs.msdn.com/robertbruckner.
Request permission to reuse content from this site
PREFACE xxv
INTRODUCTION xxvii
CHAPTER 1: BUSINESS REPORTING PARADIGMS 1
Process and Operational Support 2
Report Types 2
Sales Orders, Invoices, Manifests, and Inventory Forms 2
Template Forms 3
Tabular and List Reports 4
Activity Summaries 7
Status Reports 8
Analytical Reporting 8
Report Types 9
Dashboards and Scorecards 9
Pivot Table and Matrix Reports 9
Interactive Reports 15
Application Integration 17
Report Integration into Applications 18
Desktop Applications 18
Web Applications 19
Portal Content 19
ReportViewer Control 20
Installing the Reporting Services Samples and SQL Server
Sample Databases 21
Server Reports 22
HTML Viewer 23
Report Viewer Control 23
Reporting Services Processors and Extensions 23
Report Caching 24
The HTML Rendering Extension 25
The CSV-Rendering Extension 25
The XML-Rendering Extension 25
The Image-Rendering Extension 26
The PDF-Rendering Extension 26
The Excel-Rendering Extension 26
The Word-Rendering Extension 26
CHAPTER 2: BASIC REPORT DESIGN CONCEPTS 29
Using Report Builder 30
Formatting and Sample Values 31
Data Sources 31
Datasets and Queries 32
Designing Queries 33
Dataset Best Practices 36
Filtering Data 37
Using Stored Procedures 38
Reports and Report Objects 39
Report Body 39
Headers and Footers 40
Aggregate Functions and Totals 41
Adding Totals to a Table or Matrix Report 42
The Tablix 43
Static and Dynamic Columns and Rows 43
CHAPTER 3: REPORT DESIGN ESSENTIALS 45
Tabular and Matrix Reports 45
Defining Table Groups 45
Group Expressions and Options 46
Formatting Table Values 48
Matrix Reports 51
Sorting Options 51
Sorting in the Query 51
Sorting in a Group 52
Interactive Sort 53
Adding Headers and Footers 53
The Low Down on Drill-Down 54
Report Navigation Essentials 54
Reports with Multi-Level Groups and Drill-Down Actions 54
Standard Terminology 54
Drill-Down 55
Creating a Drill-Down Report 59
Drill-Through Reports 60
Navigating to a URL 62
Report Navigation Summary 62
Charting Basics 63
Series and Category Axes 63
Polar and Radar Charts 65
Shape Charts 65
Bar Charts 65
Pointers and Markers 67
Radial Gauges 68
Linear Gauges 68
Map Gallery 69
ESRI Shape Files 69
SQL Server Spatial Data 69
Using Parameters 69
Creating a Parameter List 70
Modifying and Formatting MDX Queries 72
Multi-Value Parameters 73
Cascading Parameters 74
Report Parameters 75
Expressions and Custom Code 75
Calculated Fields 76
Conditional Expressions 76
Using Custom Code 78
Using Custom Code in a Report 78
Using a Custom Assembly 79
Formatting Report Data 80
Introduction to Dynamic Formatting 81
Designing Multicolumn Reports 81
PART I: COLUMNAR AND GROUPED REPORTS
Green Bar Reports 85
Designing the Report 85
Alternate Row Colors in an SSRS 2000 or 2005 Matrix 90
Final Thoughts 92
Credits and Related References 92
Alternate Background Shading for Table Groups 93
Designing the Report 93
Designing the Report for Reporting Services 2005 98
Final Thoughts 98
Credits and Related References 99
Nested Group Green Bar Effect 100
Designing the Report 100
Final Thoughts 104
Credits and Related References 104
Creating Dynamic Groups 105
Designing the Report 105
Final Thoughts 114
Credits and Related References 114
Hiding and Showing Columns in a Table 115
Designing the Report 115
Showing and Hiding Group Headers 120
Final Thoughts 120
Horizontal Table 121
Designing the Report 121
Designing the Report for Reporting Services 2005 124
Final Thoughts 124
Credits and Related References 124
Resetting the Page Number Based on Groups 125
Designing the Report 125
Final Thoughts 127
PART II: BI DASHBOARDS AND ELEMENTS
Creating Sparklines 131
Designing the Report 131
Sales Trends 131
Final Thoughts 137
Credits and Related References 138
Cube Dynamic Rows 139
Designing the Report 140
Final Thoughts 147
Credits and Related References 147
Cube Metadata 148
Designing the Report 148
Adding MeasureGroups (for Cube/Perspective) 151
Adding Other Cube Metadata 153
Final Thoughts 161
Credits and References 162
Cube Browser 163
Anatomy of the Reports 165
Cube Browser 165
Cube Browser Metadata 166
Cube Browser Member 167
Behind the Scenes 167
Cube Browser 167
Report Body 173
Restricting Rows and Columns 174
Swap Actions 175
Footer Information 179
Final Thoughts 182
Credits and Related References 184
Australian Sparklines 185
Designing the Report 186
Preparing the Data and Adding Extra Controls 187
Building a Full-Sized Australian Sparkline 197
Adding the Australian Sparkline to a Table 201
Final Thoughts 203
Credits and Related References 203
Angry Koala Cube Browser 204
Anatomy of the Reports 205
r100 - Angry Koala Cube Browser 206
r101 - Angry Koala Graph 207
r102 - Angry Koala Driver 208
r103 - Angry Koala Member 210
Behind the Scenes 210
Angry Koala Cube Browser 210
Report Body 219
Restricting the Number of Rows and Columns 220
Swap Actions 220
Report Footer Info 220
Final Thoughts 221
Credits and Related References 221
Bullet Charts 222
Designing the Report 222
Final Thoughts 226
Credits and Related References 227
Synchronizing Groups, Charts, and Sparklines 228
Designing the Report 228
Final Thoughts 232
Credits and Related References 232
PART III: CHART AND GAUGE REPORTS
Chart Custom Color Palette 235
Designing the Report 236
Custom Legends 237
Final Thoughts 238
Credits and Related References 238
Chart Keywords 239
Designing the Report 239
Final Thoughts 241
Credits and Related References 242
Column Chart with Goal Threshold Line 244
Designing the Report 244
Adding Dynamic Color 249
Final Thoughts 249
Creating a Personal Report Card 250
Designing the Report 250
Final Thoughts 259
Customizing Gauges with Images 260
Designing the Report 260
Final Thoughts 263
Credits and Related References 263
Exception Highlighting with Gauges/Bullet Graphs 264
Designing the Report 264
Final Thoughts 267
Credits and Related References 267
Grouped Pie Chart Slices 268
Designing the Report 268
Final Thoughts 271
Growing Bar and Column Charts 272
Designing the Report 272
Final Thoughts 275
Credits and Related References 275
Histogram Chart 276
Designing the Report 276
Final Thoughts 278
Credits and Related References 278
Linear Regression Line 279
Designing the Report 279
Final Thoughts 285
Creating a Multi-Series Multi-Y Axis Chart 286
Designing the Report 286
Credits and Related References 292
Pareto Chart 293
Designing the Report 293
Final Thoughts 296
Credits and Related References 296
PART IV: INTERACTIVE REPORTING
Conditional Linking 299
Designing the Report 299
Final Thoughts 304
Credits and Related References 304
Drill-Through for a Multi-Level Matrix Report 305
Designing the Drill-Through Target Report 305
Designing the Drill-Through Source Report in 2005 309
Designing the Drill-Through Source Report in 2008 315
Final Thoughts 317
Credits and Related References 317
Drill-Through Report Link Breadcrumbs 318
Designing the Report 319
Final Thoughts 325
Dynamic Pivoting as a Matrix Replacement 326
Designing the Report 326
Final Thoughts 330
Using a Document Map Table for Navigation 331
Designing the Report 331
Final Thoughts 337
Credits and Related References 337
PART V: INTEGRATED REPORTING APPLICATIONS
Creating a Report Server Usage Report 341
Designing the Report 343
Final Thoughts 346
Rotating Report Dashboard 347
Designing the Report 347
Final Thoughts 351
Credits and Related References 351
Updating Data From a Report 352
Designing the Report 352
Final Thoughts 358
Offline Reporting Using the Report Viewer Control 359
Designing the Report 359
Computer Requirements and Prerequisites 362
Wiring Up the Report 363
Programming the Code-Behind 365
Final Thoughts 372
PART VI: ENHANCED REPORT CONTENT
Creating a Calendar Report 375
Designing the Report 375
Final Thoughts 382
Credits and Related References 382
Creating Mailing Labels 383
Designing the Report 383
Final Thoughts 390
Credits and Related References 390
Barcodes 391
Designing the Report 391
Custom Report Items (Barcode Components) 393
Final Thoughts 399
Credits and Related References 399
Currency Translation 400
Designing the Report 400
Final Thoughts 406
Custom Aggregation 407
Designing the Report 407
Designing the Median Report in SSRS 2005 407
Implementing the Report in SSRS 2008 409
Final Thoughts 414
Credits and Related References 414
Dynamic (Conditional) Page Breaks 415
Designing the Report 415
Designing the Report for Previous Versions of Reporting Services without the PageBreak.Disabled Property 418
Final Thoughts 419
Excel Worksheet Naming And Page Naming 420
Designing the Report 420
Final Thoughts 424
External Image Sources 425
Designing the Report 425
Creating the ASP.NET External Image Source 430
Final Thoughts 438
Language Localization 439
Multi-cultural Considerations 439
Designing the Report 440
Creating the External Resource Lookup with .NET 441
Final Thoughts 446
Credits and Related References 446
Page Running Total 447
Designing the Report 447
Final Thoughts 453
Renderer-Dependent Layout and Formatting 454
Designing the Report 454
Final Thoughts 459
Creating a Checkbox List to Show Existing Records 460
Designing the Report 460
Final Thoughts 463
Using a Checkbox List to Select and Deselect Records 464
Designing the Report 464
Using the Checkbox Report for Parameter Selection 472
Final Thoughts 473
Using the Map Wizard 474
Designing the Report 474
Final Thoughts 478
Credits and Related References 478
PART VII: FILTERING AND PARAMETERIZATION
Multiple Criterion Report Filtering 481
Designing the Report 481
Filtering in the Query 484
Using Code to Build the Query String 485
Filtering in the Dataset 487
Samples 488
Final Thoughts 488
Credits and Related References 489
Using Multi-Value Parameters with a Stored Procedure 490
Designing the Report 490
Final Thoughts 495
Using Multi-Value Parameters with a Subscription Report 496
Designing the Report 496
Final Thoughts 505
Parameterized Top Values Report 506
Designing the Report 506
Top Value Reports for Cubes 510
Final Thoughts 512
Cube Restricting Rows 513
Designing the Report 513
A Better Way to Interact With a Report Parameter 518
Final Thoughts 521
Creating Custom Sorting Reports 522
Parameterizing Custom Sorted Queries 523
Designing the Report 523
Parameterizing the Order By Clause 530
Custom Sorting in Tablix Groups 533
Using the Interactive Sort Feature 535
Creating a Custom Interactive Sort 536
Final Thoughts 542
Credits and Related References 543
Filtering User-Specific Report Data 544
Designing the Report 544
Final Thoughts 550
PART VIII: CUSTOM AND DYNAMIC DATA SOURCES
Using aWeb Service as a Data Source 553
Designing the Report 553
Final Thoughts 560
Credits and Related References 560
Reporting on SharePoint List Data 561
Preparing the Sample Data 562
Designing the Report 564
Designing the Report in 2008 R2 570
Final Thoughts 573
Credits and Related References 573
Dynamics AX Report Wizard 574
Designing the Report 574
Final Thoughts 581
PART IX: GAMES
Hangman Game 585
Reviewing the Report 585
Final Thoughts 589
Credits and Related References 590
Sea Battle Game 591
Reviewing the Report 592
How It Works 593
Final Thoughts 599
Credits and Related References 599
- No suggested jump to results
- Notifications
Code repository for SQL Server 2016 Reporting Services Cookbook by Packt
PacktPublishing/SQL-Server-2016-Reporting-Services-Cookbook
Name already in use.
Use Git or checkout with SVN using the web URL.
Work fast with our official CLI. Learn more .
- Open with GitHub Desktop
- Download ZIP
Sign In Required
Please sign in to use Codespaces.
Launching GitHub Desktop
If nothing happens, download GitHub Desktop and try again.
Launching Xcode
If nothing happens, download Xcode and try again.
Launching Visual Studio Code
Your codespace will open once ready.
There was a problem preparing your codespace, please try again.
Latest commit
Sql server 2016 reporting services cookbook.
This is the code repository for SQL Server 2016 Reporting Services Cookbook , published by Packt. It contains all the supporting project files necessary to work through the book from start to finish. ##Instructions and Navigations All of the code is organized into folders. Each folder starts with a number followed by the application name. For example, Chapter 2.
The code will look like the following:
Software requirements:
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
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
##Related Products
SQL Server Analysis Services 2012 Cube Development Cookbook
Microsoft SQL Server 2012 Integration Services: An Expert Cookbook
Microsoft SQL Server 2008 R2 Administration Cookbook
###Suggestions and Feedback Click here if you have any feedback or suggestions.
Download a free PDF
If you have already purchased a print or Kindle version of this book, you can get a DRM-free PDF version at no cost. Simply click on the link to claim your free PDF.
https://packt.link/free-ebook/9781786461810
Contributors 2
- HTML 100.0%
- Registrierung
SQL Server 2016 Reporting Services Cookbook (English Edition) 1786461811, 9781786461810
Create interactive cross-platform reports and dashboards using SQL Server 2016 Reporting Services About This Book Get up
710 244 20MB
English Pages 596 [591] Year 2016
Report DMCA / Copyright
DOWNLOAD FILE
Polecaj historie
534 97 46MB Read more
Microsoft SQL Server 2016 Reporting Services [5th edition] 9781259641510, 1259641511, 9781259641503, 1259641503
Up-to-Date Coverage of Microsoft® SQL Server® 2016 Reporting Services, Including the Mobile Report Publisher Build, publ
120 28 65MB Read more
Microsoft SQL Server 2016 reporting services [5th edition] 9781259641510, 1259641511, 9781259641503, 1259641503
2,061 353 61MB Read more
SQL Server 2016 Developer's Guide
660 166 5MB Read more
![Microsoft® SQL Server 2005 Reporting Services Step by Step [1st edition]](https://dokumen.pub/img/200x200/microsoft-sql-server-2005-reporting-services-step-by-step-1st-edition.jpg "microsoft sql server reporting services recipes pdf")
Microsoft® SQL Server 2005 Reporting Services Step by Step [1st edition]
Your hands-on guide to learning Microsoft SQL Server Reporting Services, Microsoft's customizable reporting solutio
478 88 3MB Read more
Microsoft SQL Server 2012 reporting services [4th edition] 0071760482, 9780071760478, 0071760474, 9780071760485
Including a detailed case study and sample reports; this practical reference covers the complete process of building and
843 138 46MB Read more
Professional Microsoft SQL Server 2012 Reporting Services [1., Auflage] 9781118101117, 9781118223796, 9781118237137, 9781118262108, 1118101111, 1118237137
A must-have guide for the latest updates to the new release of Reporting ServicesSQL Server Reporting Services allows yo
477 115 47MB Read more
Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports 0470563117, 9780470563113
Learn to design more effective and sophisticated business reports While most users of SQL Server Reporting Services are
1,255 123 13MB Read more
SQL Server 2016 Developer's Guide 2781403663
621 110 17MB Read more
Analytic SQL in SQL Server 2014/2016 1786304120, 9781786304124
Business Intelligence (BI) has emerged as a field which seeks to support managers in decision-making. It encompasses the
711 153 6MB Read more
- Author / Uploaded
- Dinesh Priyankara
- Robert C. Cain
Table of contents : Cover Copyright Credits About the Authors About the Reviewers www.Packtpub.com Table of Contents Preface Chapter 1: Getting It Ready – Configuring Reporting Services Introduction Selecting the edition Getting ready How to do it… How it works… See also Installing Reporting Services with default configurations Getting ready How to do it… How it works… There's more… See also Installing Reporting Services-related tools Getting ready How to do it… How it works… There's more… See also Accessing Reporting Services Configuration Manager Getting ready How to do it… How it works… There's more… See also Configuring the service account of Reporting Services Getting ready How to do it… How it works… There's more… Configuring Reporting Services URLs Getting ready How to do it… How it works… There's more… Configuring the Reporting Services database Getting ready How to do it… How it works… There's more… See also Executing post-installation tasks Getting ready How to do it… How it works… There's more… Chapter 2: Authoring Reports with SQL Server Data Tools Introduction The designer tool – exploring SSDT Getting ready How to do it… How it works… Visual Studio 2015 support Getting ready How to do it… How it works… Creating reports with a table data region Getting ready… How to do it… How it works… There's more… Creating reports with a matrix data region Getting ready How to do it… How it works… Creating reports with a chart data region Getting ready How to do it… How it works… There's more…. Managing and configuring parameters Getting ready How to do it… How it works… Chapter 3: Advanced Report Authoring with SQL Server Data Tools Introduction Creating reports with multiple axis charts Getting ready How to do it… How it works… Creating reports with multiple data regions Getting ready How to do it… How it works… Creating reports with nested data regions Getting ready How to do it… How it works… Creating reports with a map data region Getting ready How to do it… How it works… Creating reports with cascading parameters Getting ready How to do it…. How it works… Creating reports with dynamic datasets Getting ready How to do it… How it works… How to publish reports Getting ready How to do it… How it works… How to view the XML code related to reports Getting ready How to do it… How it works… Chapter 4: Authoring Reports with Report Builder Introduction Is this for a developer or business user? Getting ready… How to do it… How it works… Enhancements to Report Builder Getting ready… How to do it… How it works… Connecting Report Builder to Reporting Services Getting ready How to do it… How it works… How to design reports with the wizard Getting ready How to do it… How it works… There's more… How to design reports manually Getting ready… How to do it… How it works… Working with published data sources Getting ready How to do it… How it works… Working with published datasets Getting ready How to do it… How it works… How to publish reports to Reporting Services Getting ready How to do it… How it works… Working with published report parts Getting ready How to do it… How it works… Changing reports published in Reporting Services Getting ready How to do it… How it works… Chapter 5: Improving User Experience – New Designing and Visualization Enhancements Introduction Positioning parameters Getting ready How to do it… How it works… Visualizing data with modern styles Getting ready How to do it… How it works… There's more… Visualizing data with the TreeMap data region Getting ready How to do it… How it works… Visualizing data with the Sunburst data region Getting ready How to do it… How it works… There's more… Integrating R with Reporting Services Getting ready How to do it… How it works… There's more… See also Chapter 6: Authoring Reports with the Mobile Report Publisher Introduction Understanding the new Mobile Report Publisher Getting ready How to do it… How it works… How to create a mobile report Getting ready How to do it… How it works… Using simulated data Getting ready How to do it… How it works… Setting up data sources Getting ready How to do it…. How it works… How to use datasets for mobile reporting Getting ready How to do it… How it works… Understanding mobile report graphical elements Getting ready How to do it… How it works… Understanding the mobile report layout Getting ready How to do it… How it works… Configuring navigators Getting ready How to do it… How it works… Configuring parameters for drill-through reports Getting ready How to do it… How it works… Chapter 7: Consuming Reports – Report Access Enhancement Introduction Exploring the new Web Portal Getting ready How to do it… How it works… There's more… Managing reports with the Web Portal Getting ready How to do it… How it works… There's more… Viewing reports on any web browser Getting ready How to do it… How it works… There's more… Caching the report Getting ready How to do it… How it works… There's more… Refreshing the cache Getting ready How to do it… How it works… There's more… Creating a KPI with predefined values Getting ready How to do it… How it works… There's more… Creating a KPI with shared datasets Getting ready How to do it… How it works… There's more… Customizing the look-and-feel of the Web Portal Getting ready How to do it… How it works… There's more… Printing reports with the PDF rendering extension Getting ready How to do it… How it works… There's more… Embedding reports in other applications Getting ready How to do it… How it works… There's more… See also Chapter 8: Reporting Solutions for BI – Integration Introduction Making Reporting Services as a part of BI Getting ready How to do it… How it works… There's more… See also Creating a report using Multidimensional models Getting ready How to do it… How it works… There's more… Getting formatting from the Multidimensional model Getting ready How to do it… How it works… There's more… Getting aggregated values from the Multidimensional model Getting ready How to do it… How it works… There's more… Creating a report using Tabular models Getting ready How to do it… How it works… Connecting to the HDInsight cluster and getting unstructured data Getting ready How to do it… How it works… There's more… See also Chapter 9: SharePoint Integration Introduction Configuring SharePoint for Reporting Services Getting ready How to do it… How it works… Handling security with SharePoint Getting ready How to do it… Adding a user to your SharePoint site Altering permissions to a document library Altering permissions to a specific report item Removing a user's permissions from a report How it works… There's more… Creating dashboards using Reporting Services Getting ready How to do it… How it works… Configuring e-mail with SharePoint Getting ready How to do it… How it works… There's more… Configuring subscriptions Getting ready How to do it… How it works… Configuring data alerts Getting ready How to do it… How it works… Chapter 10: Administering and Managing Reporting Services Introduction Managing data sources Getting ready How to do it… How it works… There's more… Assigning configured data sources to published reports Getting ready How to do it… How it works… There's more… Caching reports Getting ready How to do it… How it works… There's more… Creating snapshots of reports Getting ready How to do it… How it works… Backing up Reporting Services Getting ready How to do it… How it works… There's more… Monitoring Reporting Services Getting ready How to do it… How it works… There's more… Chapter 11: Securing Reports in Reporting Services Introduction Authenticating users for Reporting Services Getting ready How to do it… How it works… Becoming a Report Portal System Administrator Getting ready How to do it… How it works… Authorizing users for accessing reports via SSRS roles Getting ready How to do it… How it works… There's more… Creating new roles in Reporting Services Getting ready How to do it… How it works… Editing existing roles in Reporting Services Getting ready How to do it… How it works… Filtering data for the current user Getting ready How to do it… How it works… There's more… Handling security on shared data sources Getting ready How to do it… How it works… There's more… Securing the channel using certificates Getting ready How to do it… How it works… Chapter 12: Custom Programming and Integration to .NET Applications Introduction Adding embedded codes to reports Getting ready How to do it… How it works… There's more… Referencing external .NET assemblies Getting ready How to do it… How it works… Opening reports using URL access Getting ready How to do it… How it works… There's more… Using ReportViewer control for accessing reports Getting ready How to do it… How it works… There's more… Index
Citation preview
SQL Server 2016 Reporting Services Cookbook Create interactive cross-platform reports and dashboards using SQL Server 2016 Reporting Services Dinesh Priyankara Robert C. Cain BIRMINGHAM - MUMBAI 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 Copy Editor Dinesh Priyankara Vikrant Phadkay Robert C. Cain Reviewers Project Coordinator Gogula Aryalingam Shweta H Birwatkar Dave Wentzel Commissioning Editor Proofreader Amey Varangaonkar Safis Editing Acquisition Editor Indexer Vinay Argekar Mariammal Chettiyar Content Development Editor Graphics Amrita Noronha Disha Haria Technical Editor Production Coordinator Akash Patel 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://noag endashow.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? Fully searchable across every book published by Packt Copy and paste, print, and bookmark content On demand and accessible via a web browser Table of Contents Preface Chapter 1: Getting It Ready – Configuring Reporting Services Introduction Selecting the edition Getting ready How to do it… How it works… See also Installing Reporting Services with default configurations Getting ready How to do it… How it works… There's more… See also Installing Reporting Services-related tools Getting ready How to do it… How it works… There's more… See also Accessing Reporting Services Configuration Manager Getting ready How to do it… How it works… There's more… See also Configuring the service account of Reporting Services Getting ready How to do it… How it works… There's more… Configuring Reporting Services URLs Getting ready How to do it… 1 9 9 11 11 12 12 13 13 13 14 18 19 19 20 20 21 22 22 23 23 24 25 27 27 27 28 28 31 33 34 34 35 37 How it works… There's more… Configuring the Reporting Services database Getting ready How to do it… How it works… There's more… See also Executing post-installation tasks Getting ready How to do it… How it works… There's more… Chapter 2: Authoring Reports with SQL Server Data Tools Introduction The designer tool – exploring SSDT Getting ready How to do it… How it works… Visual Studio 2015 support Getting ready How to do it… How it works… Creating reports with a table data region Getting ready… How to do it… How it works… There's more… Creating reports with a matrix data region Getting ready How to do it… How it works… Creating reports with a chart data region Getting ready How to do it… How it works… There's more…. Managing and configuring parameters Getting ready [ ii ] 43 44 44 45 46 50 52 53 53 53 53 54 54 55 55 56 56 57 62 62 63 63 64 64 64 65 87 90 94 94 95 99 99 100 100 116 117 118 118 How to do it… How it works… 119 126 Chapter 3: Advanced Report Authoring with SQL Server Data Tools Introduction Creating reports with multiple axis charts Getting ready How to do it… How it works… Creating reports with multiple data regions Getting ready How to do it… How it works… Creating reports with nested data regions Getting ready How to do it… How it works… Creating reports with a map data region Getting ready How to do it… How it works… Creating reports with cascading parameters Getting ready How to do it…. How it works… Creating reports with dynamic datasets Getting ready How to do it… How it works… How to publish reports Getting ready How to do it… How it works… How to view the XML code related to reports Getting ready How to do it… How it works… Chapter 4: Authoring Reports with Report Builder Introduction 128 128 129 129 130 135 136 136 137 144 145 145 146 150 151 151 152 160 162 162 163 164 164 164 165 167 170 170 173 178 178 178 179 181 182 182 [ iii ] Is this for a developer or business user? Getting ready… How to do it… How it works… Enhancements to Report Builder Getting ready… How to do it… How it works… Connecting Report Builder to Reporting Services Getting ready How to do it… How it works… How to design reports with the wizard Getting ready How to do it… How it works… There's more… How to design reports manually Getting ready… How to do it… How it works… Working with published data sources Getting ready How to do it… How it works… Working with published datasets Getting ready How to do it… How it works… How to publish reports to Reporting Services Getting ready How to do it… How it works… Working with published report parts Getting ready How to do it… How it works… Changing reports published in Reporting Services Getting ready [ iv ] 183 183 183 184 185 185 185 186 187 187 187 188 188 189 189 192 194 194 194 195 197 197 197 198 200 201 201 201 205 205 205 205 207 208 208 209 213 213 213 How to do it… How it works… 214 215 Chapter 5: Improving User Experience – New Designing and Visualization Enhancements Introduction Positioning parameters Getting ready How to do it… How it works… Visualizing data with modern styles Getting ready How to do it… How it works… There's more… Visualizing data with the TreeMap data region Getting ready How to do it… How it works… Visualizing data with the Sunburst data region Getting ready How to do it… How it works… There's more… Integrating R with Reporting Services Getting ready How to do it… How it works… There's more… See also Chapter 6: Authoring Reports with the Mobile Report Publisher Introduction Understanding the new Mobile Report Publisher Getting ready How to do it… How it works… How to create a mobile report Getting ready How to do it… [v] 216 216 218 219 220 222 222 223 224 225 225 225 226 227 236 236 238 239 244 245 246 247 248 255 255 255 256 257 258 258 259 261 262 262 262 How it works… Using simulated data Getting ready How to do it… How it works… Setting up data sources Getting ready How to do it…. How it works… How to use datasets for mobile reporting Getting ready How to do it… How it works… Understanding mobile report graphical elements Getting ready How to do it… How it works… Understanding the mobile report layout Getting ready How to do it… How it works… Configuring navigators Getting ready How to do it… How it works… Configuring parameters for drill-through reports Getting ready How to do it… How it works… Chapter 7: Consuming Reports – Report Access Enhancement Introduction Exploring the new Web Portal Getting ready How to do it… How it works… There's more… Managing reports with the Web Portal Getting ready How to do it… [ vi ] 264 264 264 265 267 267 268 268 270 271 271 271 280 280 280 281 284 285 285 285 290 290 290 291 294 295 296 296 301 302 303 304 305 306 311 312 312 312 313 How it works… There's more… Viewing reports on any web browser Getting ready How to do it… How it works… There's more… Caching the report Getting ready How to do it… How it works… There's more… Refreshing the cache Getting ready How to do it… How it works… There's more… Creating a KPI with predefined values Getting ready How to do it… How it works… There's more… Creating a KPI with shared datasets Getting ready How to do it… How it works… There's more… Customizing the look-and-feel of the Web Portal Getting ready How to do it… How it works… There's more… Printing reports with the PDF rendering extension Getting ready How to do it… How it works… There's more… Embedding reports in other applications Getting ready [ vii ] 317 317 318 318 318 321 322 322 323 324 327 329 330 330 331 336 337 338 338 339 341 342 342 342 343 348 349 350 351 351 355 356 356 357 357 362 362 362 363 How to do it… How it works… There's more… See also 364 369 372 373 Chapter 8: Reporting Solutions for BI – Integration Introduction Making Reporting Services as a part of BI Getting ready How to do it… How it works… There's more… See also Creating a report using Multidimensional models Getting ready How to do it… How it works… There's more… Getting formatting from the Multidimensional model Getting ready How to do it… How it works… There's more… Getting aggregated values from the Multidimensional model Getting ready How to do it… How it works… There's more… Creating a report using Tabular models Getting ready How to do it… How it works… Connecting to the HDInsight cluster and getting unstructured data Getting ready How to do it… How it works… There's more… See also Chapter 9: SharePoint Integration 374 375 376 376 377 377 378 378 378 379 379 385 385 385 386 387 391 392 392 392 395 396 396 396 397 397 401 401 402 409 417 418 418 419 [ viii ] Introduction Configuring SharePoint for Reporting Services Getting ready How to do it… How it works… Handling security with SharePoint Getting ready How to do it… Adding a user to your SharePoint site Altering permissions to a document library Altering permissions to a specific report item Removing a user's permissions from a report How it works… There's more… Creating dashboards using Reporting Services Getting ready How to do it… How it works… Configuring e-mail with SharePoint Getting ready How to do it… How it works… There's more… Configuring subscriptions Getting ready How to do it… How it works… Configuring data alerts Getting ready How to do it… How it works… Chapter 10: Administering and Managing Reporting Services Introduction Managing data sources Getting ready How to do it… How it works… There's more… Assigning configured data sources to published reports [ ix ] 419 420 420 420 425 426 427 427 427 428 430 432 432 434 434 434 435 443 444 444 445 446 446 446 447 447 451 451 451 452 454 455 456 456 457 458 462 462 462 Getting ready How to do it… How it works… There's more… Caching reports Getting ready How to do it… How it works… There's more… Creating snapshots of reports Getting ready How to do it… How it works… Backing up Reporting Services Getting ready How to do it… How it works… There's more… Monitoring Reporting Services Getting ready How to do it… How it works… There's more… 462 465 470 470 470 471 475 479 480 481 481 481 486 487 487 488 494 495 495 495 498 501 501 Chapter 11: Securing Reports in Reporting Services Introduction Authenticating users for Reporting Services Getting ready How to do it… How it works… Becoming a Report Portal System Administrator Getting ready How to do it… How it works… Authorizing users for accessing reports via SSRS roles Getting ready How to do it… How it works… There's more… Creating new roles in Reporting Services [x] 502 502 503 503 503 504 505 505 505 507 508 508 508 510 511 511 Getting ready How to do it… How it works… Editing existing roles in Reporting Services Getting ready How to do it… How it works… Filtering data for the current user Getting ready How to do it… How it works… There's more… Handling security on shared data sources Getting ready How to do it… How it works… There's more… Securing the channel using certificates Getting ready How to do it… How it works… Chapter 12: Custom Programming and Integration to .NET Applications Introduction Adding embedded codes to reports Getting ready How to do it… How it works… There's more… Referencing external .NET assemblies Getting ready How to do it… How it works… Opening reports using URL access Getting ready How to do it… How it works… There's more… Using ReportViewer control for accessing reports Getting ready [ xi ] 511 512 513 513 513 513 514 515 515 516 518 519 520 520 520 522 523 523 524 527 530 531 532 532 533 535 541 542 542 542 546 552 552 552 555 559 559 559 560 How to do it… How it works… There's more… 560 566 566 Index 567 [ xii ] 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. Preface 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 brandnew 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. [2] Preface 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/database s/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://msftdbprod samples.codeplex.com/releases/view/125550 [3] Preface 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. [4] Preface 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.conf [5] Preface New 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". Warnings or important notes appear in a box like this. 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/p ackt/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/supportand register to have the files e-mailed directly to you. [6] Preface You can download the code files by following these steps: 1. 2. 3. 4. 5. 6. 7. Log in or register to our website using your e-mail address and password. Hover the mouse pointer on the SUPPORT tab at the top. Click on Code Downloads & Errata. Enter the name of the book in the Search box. Select the book for which you're looking to download the code files. Choose from the drop-down menu where you purchased this book from. Click on Code Download. Once the file is downloaded, please make sure that you unzip or extract the folder using the latest version of: WinRAR / 7-Zip for Windows Zipeg / iZip / UnRarX for Mac 7-Zip / PeaZip for Linux The code bundle for the book is also hosted on GitHub at https://github.com/PacktPubl ishing/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/Packt Publishing/. 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/wJz fDO. 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/SQLServer2016Report ingServicesCookbook_ColorImages.pdf. [7] Preface 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://w ww.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. [8] 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 userfriendly 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. Getting It Ready – Configuring Reporting Services 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. [ 10 ] Getting It Ready – Configuring Reporting Services 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 [ 11 ] Getting It Ready – Configuring Reporting Services 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. [ 12 ] Getting It Ready – Configuring Reporting Services 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. [ 13 ] Getting It Ready – Configuring Reporting Services 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: Figure 1.01 [ 14 ] Getting It Ready – Configuring Reporting Services 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: Figure 1.02 [ 15 ] Getting It Ready – Configuring Reporting Services 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: Figure 1.03 [ 16 ] Getting It Ready – Configuring Reporting Services 4. 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. [ 17 ] Getting It Ready – Configuring Reporting Services 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 [ 18 ] Getting It Ready – Configuring Reporting Services 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. [ 19 ] Getting It Ready – Configuring Reporting Services 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. [ 20 ] Getting It Ready – Configuring Reporting Services 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: Figure 1.06 [ 21 ] Getting It Ready – Configuring Reporting Services 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 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 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. [ 22 ] Getting It Ready – Configuring Reporting Services 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 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. [ 23 ] Getting It Ready – Configuring Reporting Services 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. [ 24 ] Getting It Ready – Configuring Reporting Services 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: Figure 1.07 [ 25 ] Getting It Ready – Configuring Reporting Services 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: 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. [ 26 ] Getting It Ready – Configuring Reporting Services 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 eport\Microsoft SQL Server\\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 ht tps://msdn.microsoft.com/en-us/library/bb630448.aspx. [ 27 ] Getting It Ready – Configuring Reporting Services 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: [ 28 ] Getting It Ready – Configuring Reporting Services 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. [ 29 ] Getting It Ready – Configuring Reporting Services 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. [ 30 ] Getting It Ready – Configuring Reporting Services 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: Figure 1.10 [ 31 ] Getting It Ready – Configuring Reporting Services 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: Figure 1.11 [ 32 ] Getting It Ready – Configuring Reporting Services 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. [ 33 ] Getting It Ready – Configuring Reporting Services 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: 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. [ 34 ] Getting It Ready – Configuring Reporting Services 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 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 Default URL type Reservation in HTTP.SYS Web service Default http:// -1001 And CInt(dateString) Now.Year Or year Look at the written code once. It initially checks values like today and yesterday and sets the relevant dates. Then it addresses numeric values entered and set it as a date. If user has entered as value with the YYYY/MM/DD format, then it extracts year, month and date separately, and format the date. If an exception occurs or an invalid value is found, it sets for the date as 1900-01-01. [ 537 ] Custom Programming and Integration to .NET Applications 7. Click OK to save the settings: Figure 12.05 8. Next, we need to change the dataset query. Open the dataset and change the WHERE clause of the query as shown here: WHERE (s.[Invoice Date Key] = Convert(date, @Date)) 9. The preceding statement adds a parameter to the dataset. Open the Parameters page of Dataset properties and add the following expression for the @Date parameter: =Code.GetDate(Parameters!ReportParameterSalesDate.Value) [ 538 ] Custom Programming and Integration to .NET Applications 10. Click OK to save the Dataset properties: Figure 12.06 11. It is always better to show the date selected in the report. Not only that, it is better to notify the user that the date entered is invalid. That can be easily achieved by changing text box added that shows selected date as shown here: 12. Right-click on the text box to get the context menu and click on Expression menu. 13. Change the expression as shown in the following code: ="Selected date: " & Iif(format(Code.GetDate(Parameters!ReportParameterSalesDate.Value) ,"yyyy-MM-dd") = "1900-01-01" , "Invalid date entered", format(Code.GetDate(Parameters!ReportParameterSalesDate.Value), "yyyy-MM-dd")) 14. Click OK to save the expression. 15. Deploy the report and view it. You should see the report showing data for yesterday values (if exist only). [ 539 ] Custom Programming and Integration to .NET Applications 16. If you enter a value like -200, it sets the relevant date for the query and executes it: Figure 12.07 [ 540 ] Custom Programming and Integration to .NET Applications 17. If you enter an invalid value, you should see a message as we configured with the expression: Figure 12.08 How it works… Reporting Services allows us to use the functions available with .NET framework System.Math and System.Convert namespaces and Visual Basic run-time library functions. You do not need to add any references to the report or the project if you have not used other functions related to other namespaces. If you need to use other functions, you need to add references as full-qualified references such as System.Text.StringBuilder. When the report is deployed, the code added to the report is deployed. It does not get compiled during the deployment and Reporting Services executes the code when it is requested by a user. [ 541 ] Custom Programming and Integration to .NET Applications There's more… Adding various functions to each report may not be the best solution if functions have to be used with many more reports. If you have a set of functions that are required by many reports, it is always better to have all the function in a single .NET class, compile it into a single assembly and use it with all reports. That is what we are going to talk with our next recipe that is Referencing external .NET assemblies. Referencing external .NET assemblies You have already seen the way of adding custom code to reports and you may have already experienced the issues with it such as: It supports only Vb.NET The same function has to be added to each and every report With this recipe, we can overcome it by making an assembly that contains all required functions and use it with the report rather repeating it in every report. Getting ready In order to make an assembly with all required functions, you need to create a .NET class library using one of .NET languages. Yes, it is not limited to Vb.NET. Let's make one using C#.NET language. Follow these steps for creating a class library: 1. Open the Visual Studio and create a C#.NET Class Library project. Name it as StandardFunctions. 2. Create a public class called Functions and add a public static method named GetTextColor: using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace StandardFunctions { public class Functions { [ 542 ] Custom Programming and Integration to .NET Applications public static string GetTextColor(string messageType) { string color = "black"; switch (messageType) { case "Warning": color = "Yellow"; break; case "Error": color = "Red"; break; case "Success": color = "Green"; break; } return color; } } } 3. Open the AssemblyInfo.cs file in your project. Add System.Security namespace and make sure the AllowPartiallyTrustedCallers attribute is added: using System.Reflection; using System.Runtime.CompilerServices; using System.Runtime.InteropServices; using System.Security; [assembly: AssemblyTitle("StandardFunctions")] [assembly: AssemblyDescription("")] [assembly: AssemblyConfiguration("")] [assembly: AssemblyCompany("")] [assembly: AssemblyProduct("StandardFunctions")] [assembly: AssemblyCopyright("Copyright © 2016")] [assembly: AssemblyTrademark("")] [assembly: AssemblyCulture("")] [assembly: AllowPartiallyTrustedCallers()] [assembly: ComVisible(false)] [assembly: Guid("4d89a812-f315-48f3-af97-a7576cc4060b")] [assembly: AssemblyVersion("1.0.0.0")] [assembly: AssemblyFileVersion("1.0.0.0")] [ 543 ] Custom Programming and Integration to .NET Applications 4. Get the project properties and open Application page. Make sure that the Assembly name is set as StandardFunctions. 5. Open Signing* page of Project Properties and check Sign the assembly checkbox. This is for signing the assembly with a strong name as it is required when adding to GAC. Select from Choose a strong name key file drop down: Figure 12.09 [ 544 ] Custom Programming and Integration to .NET Applications 6. Set the Key file name as StandardFunctionsKey and uncheck Protect my key file with password. Click OK to create the file: Figure 12.10 [ 545 ] Custom Programming and Integration to .NET Applications 7. Save the project and build it. You should see the assembly created in the bin folder of the project: Figure 12.11 8. Now the assembly is ready. Let's see how it can be used with our reports. How to do it… The first thing we need to do is, make sure that the assembly can be accessed during development. This needs the assembly to be placed in the Reporting Services bin folder and add a reference adding it with Report Properties. [ 546 ] Custom Programming and Integration to .NET Applications Follow these steps for accessing the assembly: 1. Take a copy of StandardFunctions.dll and place it in bin folder of Reporting Services. The default path is C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\bin. 2. Open Chapter12 solution and get the Properties of the DailySales report. Note that you need make sure the DailySales report is modified as per the first recipe. 3. Go to References page and click on Add to add a reference (see Figure 12.13). Browse to the placed StandardFunction.dll and select it. Click OK to add it: Figure 12.12 [ 547 ] Custom Programming and Integration to .NET Applications 4. Once added, you should see the assembly in the grid. Click OK to save it: Figure 12.13 [ 548 ] Custom Programming and Integration to .NET Applications Let's use the function in the assembly with the text box we have to display the selected date: 1. Go to the Text Box Properties of the text box and open Font page. Click on expression icon to set the color through an expression: Figure 12.14 [ 549 ] Custom Programming and Integration to .NET Applications 2. Set the following expression. Note the way of referencing the method. It is AssemblyName.ClassName.MethodName. If the namespace is different, then you need to mention it as well: =Iif(format(Code.GetDate(Parameters!ReportParameterSalesDate .Value), "yyyy-MM-dd") = "1900-01-01" , StandardFunctions.Functions.GetTextColor("Error"), StandardFunctions.Functions.GetTextColor("")) 3. Click on OK to save the expression and click on OK again to save the text box properties: Remember, even though we could use the assembly during development, Reporting Services requires the assembly in the Global Assembly Cache (GAC) when running the report. Let's add the assembly to GAC. 4. Open Command Prompt with administrative privileges (as an Administrator) and change the path to following folder (your path may be different based on the version of Visual Studio installed): C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.6.1 Tools This folder has gacutil.exe and can be used for registering the assembly to GAC. Execute the following code to register it with GAC: gacutil.exe /i "\StandardFunctions.dll" 5. You should see a similar output with a success message: Figure 12.15 [ 550 ] Custom Programming and Integration to .NET Applications Everything required is done. Let's publish the report and see whether it works: 1. Go back to the project and deploy the report. 2. Open the report using Web Portal. If you input an invalid date, the message set with text box should be displayed in red color: Figure 12.16 [ 551 ] Custom Programming and Integration to .NET Applications How it works… Remember, if the Reporting Services is installed in a different server, all you need to do is, register the assembly in GAC. If it is not registered, Reporting Services cannot access the assembly as it uses the assembly in the GAC when running the report. The reference to the assembly is recorded in the .rdl file. If you open the .rdl, you will see a node that holds details of the assembly. In addition to this node, you will see an additional node if you have added Class Name and Instance Name with References page in Report Properties. This is not required for the code, we have written the function as a static method: StandardFunctions, Version=1.0.0.0, Culture=neutral, PublicKeyToken=5021460a45a99ff0 Opening reports using URL access A user's access reports are published using many techniques. Even though the Web Portal given with Reporting Services is heavily used for viewing reports, some solutions use different techniques for accessing reports. One way of accessing reports in Reporting Services is, URL Access that enables accessing all reports via Reporting Services Web Service. URL Access allows us to send necessary commands to the Report Server through the URL. There are various commands that can be used for passing instructions. These commands facilitate submitting name of the report, values for parameters, rendering format, and much more. Let us explore the URL Access along with some commands. Getting ready The first thing you need to do is, get the Web Service URL configured for the Reporting Services. Let's start it: 1. Open the Reporting Services Configuration Manager and connect with the instance of Reporting Services. [ 552 ] Custom Programming and Integration to .NET Applications 2. Click on Web Service URL for opening configurations of the web service. Note the URL under Report Server Web Service URLs. It is the URL, you have to use for accessing reports. Click on the URL: Figure 12.17 You may see multiple URLs if Reporting Services is configured with more than one URL. [ 553 ] Custom Programming and Integration to .NET Applications 3. When you click on the URL, it opens a web page that shows all high-level elements you have in Reporting Services. Click on the Chapter12 link for seeing all the elements published under Chapter12: Figure 12.18 4. When the Chapter12 folder is open, you should see DailySales report. Click on it. It opens the report as you have seen with Web Portal but notice the URL: Figure 12.19 [ 554 ] Custom Programming and Integration to .NET Applications As you can see, the URL is different from the URL we have seen with Web Portal: http://dinesh-lap01/ReportServer/Pages/ReportViewer.aspx?%2fChapter12%2 fDailySales&rs:Command=Render This is how the URL should be formed if you need to access report using URL Access. Let's see how we can access the DailySales report using different commands. How to do it… The syntax for URL Access is: ?[&prefix:param=value]..n] As you can see, the web service URL, report path must be separated using a question mark (?). After the path, multiple commands can be added with the appropriate prefix such as rc, rs or rv. Let's form a URL for DailySales report and view: 1. Get the Web Service URL and combine it with the DailySales report path. Your URL should be similar to; http://dinesh-lap01/ReportServer?/Chapter12/DailySales 2. Open a Web Browser and place the URL just formed. You should see the DailySales report opened: Figure 12.20 [ 555 ] Custom Programming and Integration to .NET Applications Notice the URL. You will see that the /Pages/ReportViewer.aspx part is automatically added. Let's check on the prefix: rs (Report Server Command). You have already noticed Render command. This is one of the Report Server Commands and it renders the report to the browser. Add it as &rs:Command=Render to the URL and view the report. Let's see how we can request for a different rendering format such as PDF: 1. Add &rs:Format=PDF to the URL and view the report. The URL should be similar to: http://dinesh-lap01/ReportServer?/Chapter12/DailySales&rs:Forma t=PDF: Figure 12.21 Let's check some rc (HTML Viewer commands). The Toolbar command allows us set the visibility of the toolbar: 1. Add &rc:Toolbar=false to the URL and run it in the browser. Your formed URL should be similar to http://dinesh-lap01/ReportServer?/Chapter12/DailySales&rc:Toolb ar=false [ 556 ] Custom Programming and Integration to .NET Applications Figure 12.22 2. If you want to hide only the parameter section while showing other items in the toolbar, you can use the &rc:Parameters command that accepts true, false, and Collapsed. The Collapsed value does not completely hide the parameter pane. It allows users to get it opened when required by toggling the pane. 3. Add &rc:Parameters=Collapsed to the URL and run it in the browser. Your URL should be similar to http://dinesh-lap01/ReportServer?/Chapter12/DailySales&rc:Param eters=Collapsed. Figure 12.23 [ 557 ] Custom Programming and Integration to .NET Applications 4. We did not check how to pass values for parameters. Multiple parameters can be submitted through the URL and the format of it is: &=value Note that the parameters are case-sensitive. You need to make sure they have been mentioned in the URL as they have been defined. In addition to that, if the parameter contains space characters, they are replaced with %20. This is automatically done by the browser. Hence you do not need to worry on it. Let's see how we can pass a value to parameters in the DailySales report. 5. Add &ReportParameterSalesDate=-200 to the URL and run it in the browser. Your formed URL should be similar to http://dinesh-lap01/ReportServer?/Chapter12/DailySales&ReportPa rameterSalesDate=-200: Figure 12.24 [ 558 ] Custom Programming and Integration to .NET Applications 6. There are many more commands that can be included the URL. You can read more in the following URLs: URL Access Parameter Reference: https://msdn.microsoft.com/en-u s/library/ms152835.aspx Pass a Report Parameter Within a URL: https://msdn.microsoft.com /en-us/library/ms155391.aspx How it works… This is completely based on the web service configured to Reporting Services. When the user sends a request using a URL, it accepts it and satisfies the request as per the command added to the URL. Generally, URL Access is used with applications and when the application needs to open the report outside the application. For an example, if a .NET application needs to open a report in a browser, report can be launched using URL Access through a .NET class like System.Diagnostics.Process. There's more… If you need to make sure that the requests are satisfied via a secured channel, you can configure one or more HTTPS Certificates to the web service. This can be done using Reporting Services Configuration Manager. As you see, this method is good for opening the report in a browser by other applications To open it within the application, refer to the Using ReportViewer control for accessing reports recipe. Using ReportViewer control for accessing reports It is always better to open functionalities related to reports within the solution rather directing users to different applications or tools. It makes the solution complete and user does not need to worry on other applications required for accessing reports. This recipe speaks about it, let's see how we can integrate Reporting Services reports with .NET applications. [ 559 ] Custom Programming and Integration to .NET Applications Getting ready Microsoft Visual Studio provides a control called ReportViewer that can be used for integrating Reporting Services reports with .NET applications. There are two controls: one for Windows applications and another for Web applications. Both work in same fashion and have same functionalities but design is specific to the application type. The ReportViewer control has property called ProcessingMode that decides the place of processing the report. It has two options: Local and Remote. Remote is the preferred option because it makes sure that processing is done at the Reporting Services server without burdening resources in application server. Not only that, if the Report Server is configured with multiple servers, requests can be distributed improving the overall performance of the solution. However, if the Reporting Services is not available, Local mode is preferred as an alternative way though it provides less functionalities when compared to Reporting Services. Let's create a Visual Studio project and see how we can integrate Reporting Services reports with it. How to do it… Here are the steps for integrating reports with the .NET Windows application: 1. Open Visual Studio 2013 or 2015 and create a new Visual C# Windows Forms Application: [ 560 ] Custom Programming and Integration to .NET Applications Figure 12.25 2. Visual Studio creates the default Windows Form. Rename it as MainForm. [ 561 ] Custom Programming and Integration to .NET Applications 3. Open the MainForm. Open the Toolbox and navigate to the Reporting section. Expand Reporting and double click on ReportViewer: Figure 12.26 4. Check the control in your Windows form. Locate a small right-arrow-icon on the top-right corner of the control and click on it. You should see the ReportViewer Tasks pane: Figure 12.27 [ 562 ] Custom Programming and Integration to .NET Applications 5. Enter the Report Server URL as http://localhost/reportserver. If the server name is different or it is a named instance, then replace the localhost with it. 6. Enter the report path as /Chapter12/DailySales for configuring the control to the DailySales report. 7. Open the Properties of ReportViewer and make sure ProcessingMode is set to Remote. 8. Run the application. You should see that report is opened within the application: Figure 12.28 9. The advantage of this integration is, we can control some of the elements and processing programmatically rather giving the full responsibility to Reporting Services. For an example, we can change the way of displaying parameters and have additional validations by adding our own input boxes to accept inputs of users on parameters. [ 563 ] Custom Programming and Integration to .NET Applications 10. Let's make the default toolbar and parameter pane invisible. Click on the ReportViewer control and get the properties of it. Make both ShowParameterPrompts and ShowToolbar as False: Figure 12.29 [ 564 ] Custom Programming and Integration to .NET Applications 11. Next add a label, a text box, and a button to the Windows form. Name the text box as textBoxSalesDate the and button as buttonViewReport. Set the text property of the label as Sales Date and the text property of the button as View Report: Figure 12.30 12. Double-click on the View Report button. This opens the code behind of the form and creates a method for handling the button-click event. Add the following code to button-click event and save it: private void buttonViewReport_Click(object sender, EventArgs e) { reportViewer1.ServerReport.SetParameters(new Microsoft.Reporting.WinForms.ReportParameter ("ReportParameterSalesDate", textBoxSalesDate.Text)); reportViewer1.RefreshReport(); } [ 565 ] Custom Programming and Integration to .NET Applications 13. Run the application. Enter -200 as the input value and hit the View Report button. You should see the output as shown in the following screenshot: Figure 12.31 How it works… If the Processing mode is set as Remote, the request is sent to Reporting Services and processing is done by the Reporting Services, including the rendering. The ReportViewer control is showing only the output of the processing. Since you can do a lot with .NET coding, you may take the entire validation to the .NET application. In addition to that, you can use .NET controls for handling report parameters in a better way. There's more… Remember, this control is available in .NET Web solutions as well. The implementation is the same and reports can be viewed as a part of the web solution. [ 566 ] Index . C .NET assemblies external .NET assemblies, referencing 542, 544, 546, 549, 550, 552 cache enabling, for reports 322, 323, 324, 325, 326, 327, 328, 329, 330 refreshing 330, 331, 333, 334, 335, 336, 337 Caller Data Record (CDR) 402 cascading parameters reports, creating with 162 certificates used, for securing channel 523, 524, 525, 526, 527, 528, 530 chart data region reports, creating with 99, 100, 101, 102, 103, 104, 106, 107, 108, 109, 111, 112, 113, 114, 115, 116, 117, 118 configured data sources assigning, to published reports 462, 463, 464, 465, 466, 467, 468, 469, 470 A Always On Availability Group reference link 378 Always On Failover Cluster 378 Analysis Services about 385 additive measure 392 non-additive measure 392 semi-additive measure 392 Analytical Reports 374 Analytics Platform System (APS) 11 authentication types Custom 504 RSWindowsBasic 504 RSWindowsKerberos 504 RSWindowsNegotiate 504 RSWindowsNTLM 504 Azure portal URL 403 Azure Storage Explorer URL 408 B BI solution with Reporting Services 376, 377, 378 Business Intelligence 11 Business Intelligence Development Studio (BIDS) 20 D Dashboard Reports 374 dashboard about 257 creating 434, 435, 437, 442, 444 Data Alerts configuring 451, 452, 454 data sources managing 456, 457, 458, 459, 460, 462 security, handling on shared data sources 520, 522, 523 setting up 267, 268, 269, 270, 271 data visualization with modern styles 222, 223, 224, 225 with Sunburst data region 236, 237, 239, 240, 241, 242, 243, 244, 245 with TreeMap data region 225, 226, 227, 228, K 230, 231, 232, 233, 234, 235 Data Warehouse (DW) 197 datasets using, for mobile report 271, 272, 273, 274, 275, 276, 277, 278, 279, 280 Distributed Architecture 376 drill-through functionality 349 drill-through reports parameters, configuring 295, 296, 297, 298, 299, 300, 301 dynamic datasets reports, creating with 164, 166, 167, 168, 169, 170 Key Performance Indicator (KPI) creating, with predefined values 338, 339, 340, 341, 342 creating, with shared datasets 342, 343, 344, 345, 346, 347, 348, 349, 350 M E email configuring, with SharePoint 444, 446 embedded codes adding 532, 534, 536, 538, 539, 541 encryption keys backing up 491 F Formal Reports 374 H Hadoop 401 HBase 401 HDInsight cluster connecting to 401, 403, 404, 405, 406, 407, 408, 411, 412, 413, 414, 415, 416, 417, 418 unstructured data, obtaining 401, 403, 404, 405, 406, 407, 408, 409, 411, 412, 413, 414, 415, 416, 417, 418 High Availability and Disaster Recovery 45, 54 Hive 401 Hive drivers URL 409 HTML Renderer 318 I Integrated Development Environment (IDE) 62 Intermediate Format 456 Internet Information Services (IIS) 35, 524 map data region reports, creating with 151, 152, 153, 154, 155, 157, 158, 159, 160, 161, 162 Mashup Reports 374 matrix data region reports, creating with 94, 95, 97, 98, 99 Mobile Report Publisher about 258 installing 258 launching 259, 260, 261 mobile report creating 262, 263, 264 datasets, using 271, 272, 273, 274, 275, 276, 277, 278, 279, 280 graphical elements, updating 280, 281, 282, 283, 284, 285 layout, creating 285, 286, 287, 288, 289, 290 modern styles for data visualization 222, 223, 224, 225 Multidimensional model aggregated values, obtaining 392, 393, 395, 396 for formatting 386, 387, 388, 389, 390, 391, 392 used, for creating reports 378, 379, 381, 382, 383, 384, 385 multiple axis charts reports, creating with 129, 131, 132, 133, 134, 135, 136 multiple data regions reports, creating with 136, 137, 138, 139, 140, 141, 142, 143, 144, 145 N navigators configuring 290, 291, 292, 293, 294, 295 [ 568 ] nested data regions reports, creating with 145, 146, 147, 148, 150 Network Load Balancing (NLB) 378 P Paginated Reports 374 Parallel Data Warehouse (PDW) 11 parameters configuring 118, 119, 120, 121, 122, 123, 124, 125, 126, 127 managing 118, 119, 120, 121, 122, 123, 124, 125, 126, 127 positioning 218, 219, 220, 221, 222 PDF rendering extension reports, printing 356, 358, 359, 360, 361, 362 Pig 401 Pinning to Power BI 362 Portable Document Format (PDF) 356 post-installation tasks executing 53, 54 Processing Mode Local mode 560 Remote mode 560 Production Reports 374 published data sources creating 197, 198, 199, 200 published datasets creating 201, 203, 204, 205 published reports configured data sources, assigning 462, 463, 464, 465, 466, 467, 468, 469, 470 parts, creating 208, 210, 211, 212, 213 R R Graphics Device about 255 URL 255 Recovery Model 488 Recovery Time Objective (RTO) 487 Report Builder about 183 connecting, to Reporting Services 187, 188 enhancements 185, 186 installing 183 using 183, 184 report embedding 362 Report Manager 302 Report Portal System Administrator authorizing 505, 507 Report Server Scale-Out reference link 378 Report Services Subscriptions 370 Reporting Services Configuration Manager about 456 accessing 23, 24, 25, 26, 27 Reporting Services Native Mode 304 Reporting Services backing up 487, 488, 490, 491, 492, 493, 494, 495 database, configuring 44, 45, 46, 47, 48, 49, 50, 51, 52, 53 edition, selecting 11, 12, 13 existing roles, editing 513, 514, 515 history 9, 10 installing, with default configurations 13, 14, 15, 16, 17, 18, 19 installing, with related tools 20, 21, 22 monitoring 495, 496, 497, 498, 500, 501 published reports, modifying 213, 215 R, integrating with 246, 247, 248, 249, 250, 251, 252, 253, 254, 255 reports, publishing 205, 206, 207 roles, creating 511, 513 service account, configuring 28, 30, 31, 32, 33, 34 URLs, configuring 34, 35, 36, 37, 38, 40, 41, 42, 43, 44 users, authenticating 503, 505 reports caching 470, 471, 472, 473, 474, 475, 477, 478, 479, 480 creating, with cascading parameters 162 creating, with chart data region 99, 100, 101, 102, 103, 104, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118 creating, with dynamic datasets 164, 166, 167, 168, 169, 170 creating, with map data region 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162 creating, with matrix data region 94, 95, 96, 97, [ 569 ] 98, 99 creating, with multiple axis charts 129, 131, 132, 133, 134, 135, 136 creating, with multiple data regions 136, 137, 138, 139, 140, 141, 142, 143, 144, 145 creating, with nested data regions 145, 146, 147, 148, 149, 150 creating, with table data region 64, 66, 67, 68, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 82, 83, 84, 85, 86, 87, 88, 89, 90, 92 creating, with wizard 188, 190, 191, 192, 193, 194 designing, manually 194, 195, 196, 197 embedding, in applications 362, 363, 364, 365, 366, 367, 368, 369, 370, 371, 372 publishing 170, 171, 172, 173, 174, 175, 177, 178 ReportServer database 44 ReportServerTempDB database 44 ReportViewer control about 560 used, for accessing reports 559, 561, 564, 566 S security handling, on shared data sources 520, 521, 522, 523 Server Analysis Services 64 service account configuring 28, 30, 31, 32, 33, 34 domain user account 30 local service account 30 local system account 30 local user account 30 managed service account 30 network service account 30 virtual service account 30 shared data sources 456 SharePoint configuring 420, 424, 425, 426 email, configuring 444, 446 permissions, adding to document library 428, 429 permissions, altering to specific report item 430 security, handling 426, 433, 434 user's permissions, removing from report 432 user, adding 427 simulated data using 264, 265, 266, 267 Single Server Architecture 376 snapshots creating, of reports 481, 482, 484, 485, 486, 487 SQL Server 2016 edition, selecting 11 SQL Server Agent 490 SQL Server Configuration Manager 456 SQL Server Data Tools (SSDT) about 20, 55, 493 exploring 56, 57 launching 57, 58, 59, 60, 61, 62 URL, for downloading 57 Visual Studio 2015 support 62, 63, 64 SQL Server Management Studio (SSMS) 20, 69 SQL Server Mobile Report Publisher 23 SQL Server Reporting Services (SSRS) 151, 375 SSDTBI 20 SSL references 530 SSRS roles users, authorizing for report access 508, 511 subscriptions configuring 446, 448, 450, 451 Sunburst data region for data visualization 236, 237, 238, 239, 240, 241, 242, 243, 244, 245 System Configuration Checker 13 T table data region reports, creating with 64, 66, 67, 68, 70, 71, 72, 73, 74, 75, 77, 78, 79, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 92 Tabular model report, creating 396, 397, 399, 400, 401 Transact SQL (T-SQL) 375 TreeMap data region for data visualization 225, 226, 227, 228, 229, 230, 231, 232, 233, 234, 235 [ 570 ] U W Universal Access Control (UAC) 305 URL access references 559 used, for opening reports 552, 554, 555, 556, 558, 559 users authenticating, for Reporting Services 503, 505 authorizing, for report access via SSRS roles 508, 511 data, filtering for current user 515, 516, 517, 518, 519 V web browsers reports, viewing 318, 319, 320, 321 Web Portal about 35, 302, 303 customizing 350, 351, 353, 354, 355, 356 exploring 304, 305, 306, 307, 308, 309, 310, 311, 312 interface 356 reports, managing 312, 313, 315, 316, 317 Web Service application 35 wizard reports, creating 188, 189, 190, 191, 192, 193, 194 Visual Studio 2015 support, for SSDT 62, 63, 64 X XML code viewing, related to reports 178, 179, 180, 181
Report "SQL Server 2016 Reporting Services Cookbook (English Edition) 1786461811, 9781786461810"
Enter the characters you see below
Sorry, we just need to make sure you're not a robot. For best results, please make sure your browser is accepting cookies.
Type the characters you see in this image:
Paul Turley's SQL Server BI Blog
sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009
Paginated Report Recipes eBook
Welcome to ssrs/paginated report recipes.
The first edition of this book, SQL Server Reporting Services Recipes , was published by Wiley Publishing/WROX Press in 2010 co-authored by Paul Turley and Robert Bruckner, with multiple contributing authors.
This new electronic book is a revision, in part, of the original recipes and new content modernized to work with Power BI Paginated Reports. These recipes can be used or adapted to work with SQL Server Reporting Services, Power BI Report Server or the Power BI Service in Premium Capacity (or Premium per User licensing).
2021 (c) Paul Turley, with contributions from multiple authors
Recipe Table Of Contents
- 01-Alternate Row Table “Green Bar” Reports
- 02: Alternate Row Shading in Matrix (with Column Groups)
- 03-Reusable Report Template
- 04-Drillthrough from Power BI to Paginated Report On-prem
- 05-Parsing Multi-value Parameters using OPENJSON
- 06-Sending Customized Paginated Reports to Multiple Recipients
- 07-Creating a Calendar Report
- 08-Horizontal Table Report
- 09-Customizing Gauges with Images
- 10-Histogram Chart
- 11-Dynamic Chart Sizing
- 12-Drill-Through for a Multi-Level Matrix Report
- 13-Column Chart with Goal Line
- 14-Checkbox List for Existing Records
- 15-Sparklines
- 16-Drillthrough Link Breadcrumbs (SSRS)
- 17-Heatmaps
- 18-Spatial Data Visualization
Additional recipes may be added in the future
How to Use this Book
All material contained in the pages of this electronic book are subject to copyright under terms of the Digital Millennium Copyright act. Content is for the benefit of the community and not for commercial gain, promotion or advertising; and cannot be copied or republished without permission from the author. Individual recipe authors are permitted to share their material at their discretion.
Each recipe is a self-contained set of instructions to achieve a specific report type or design pattern. Most recipes use a standard sample database than can be downloaded from this site according to instructions contained in this section. The sample database requires a local or hosted instance of SQL Server or Azure SQL. Recipes are offered “as-is” without any warranty.
The PaginatedReportData sample database can be downloaded along with the recipe resource files here: Paginated Report Book Downloads
Introducing Paginated Report Recipes
Late December back in 2003; what a very special time for me! SQL Server Reporting Services was due to be released to the market and I had been using the pre-released version to integrate reports into a custom web application. After using a variety of products like FoxPro, Access and Crystal Reports, and after mastering essential T-SQL queries and .NET programming, using SSRS was a dream. It was simple and elegant, programmable and powerful. I was so excited that I wanted to tell the world, so I recruited authors to write our first book about Reporting Services. Three editions later, I teamed up with Robert Bruckner, one of the lead developers on the SSRS product team at Microsoft, to write a different kind of book. Robert and I had both blogged quite a lot about report design techniques. Robert had done some crazy things in SSRS that no one knew was possible. He found very creative ways to develop games using SSRS such as tic-tac-toe, hangman and Battleship. Honestly, there are easier ways to do game programming, but he proved that almost anything was possible if you were willing to think outside the box and maybe even draw outside the lines a bit.
Is this the Right Book for You?
Let’s cut right to the chase and answer this important question. Report recipes will help you apply basic report design skills using SQL Server Reporting Services or Power BI Paginated Reports to apply specific reporting scenarios or solve a unique business problem. This book is not an introduction to basic design and you should already have some entry-level skills. If you know how to create a data source, a simple dataset query and basic report data regions (like a table or matrix), you’re good to go. If you have never created a report before, it would be good to get a little experience under your belt first.
Same Report Technology – Different Names
There are three different implementations of paginated reports from Microsoft. Each of these use reports in the same format, defined with an RDL file extension.
How does all this fit together?
Chances are that if you were to ask four people from different IT or business roles to describe the purpose and relationship between Power BI, Paginated Reports and Reporting Services; you would get four different answers. The ironic thing is that they all might all actually be right. The answer really comes down to understanding the history that brought us to where these products are today. You see, reporting tools are used in so many different ways that they evolved out of different product platforms at Microsoft, depicted in Figure 1-1. This means that many have, and continue to have different experiences working with reporting and BI tools from Microsoft.
Figure 1-1 – The Microsoft reporting puzzle
Power BI Paginated Reports and Reporting Services
For our purposes, “Paginated Reports” and “Reporting Services” mean the same thing. That was the simple answer but let’s get a little deeper and I’ll do my best to dispel some potential confusion. You might think that since you only work with either SQL Server Reporting Services, Power BI Report Server or Power BI Paginated Reports that you can just focus on the product that you or your organization have chosen to use and ignore the others. To a certain point, you can. You will most likely not be working with all three product implementations, but you are likely to encounter documentation and references to each and that’s where the confusion lies. Consider that the following terms are all different terms used to describe “operational” style reports created with Report Builder, Paginated Report Builder or the SSDT report designer:
- Paginated report
- Reporting Services report
- SSRS report
Throughout the book, these will be called “Paginated reports”. The design tools for this type of report produce a file with an “RDL” file extension.
Likewise, the following are all terms that might be used to describe “analytic” reports that you create with Power BI Desktop:
- Power BI interactive report
- Power BI report page
- “Dashboard”-style report
- PBIX report
For our purposes, we will refer to these as “Power BI reports”. The design tools for this type of report produce a file with an “PBIX” file extension.
To understand and appreciate why there are three different incarnations of Paginated reports, all with slightly different tool names and versions, you need to know a little about the history that brought us to this point. Since 2004 Microsoft have bundled Reporting Services with the editions of SQL Server that you can install on your own server. Of course, that product is called “SQL Server Reporting Services” (SSRS). After Power BI became a popular way to present highly-visual, interactive reports for analyzing business data; they created a special build of the Reporting Services report server capable of hosting Power BI reports in addition to standard Reporting Services reports on your on-premises report server. That product is called “Power BI Report Server” and aside from hosting Power BI reports, it is nearly identical to SQL Server Report Server. To differentiate between Power BI “interactive” reports, Microsoft began using the term “Paginated Reports” when referring to the more traditional SSRS-type reports. Finally, in 2019, Microsoft integrated Paginated Reports into the cloud-based Power BI service. At the time of this writing, Paginated reports are available to customers with Premium licensing, and may be available with other enhanced licensing options.
SQL Server Reporting Services (SSRS)
If you have been using SQL Server Reporting Services for years, as I have, and you deploy your reports to a Report Server installed within your network domain; the core product has only seen incremental improvements over the past few version. Reporting Services, commonly called SSRS, has pretty much the same feature set since 2010. The Report Portal, previously called the Report Manager, received a facelift and an overhaul.
There are two report development tools for Reporting Services: SQL Server Data Tools (SSDT) for Visual Studio and Report Builder. Examples used in this book will use Report Builder but the same design techniques apply to SSDT. Report Builder is typically installed over the Internet from a link on the downloads menu in the Report Portal. If you work in an environment where you are restricted from downloading and installing software, the latest Report Builder installer can be downloaded from the Microsoft Downloads site. Report Builder is typically updated every few months.
Power BI Report Server
The features of this enhanced edition of the on-premises report server are virtually identical to the SQL Server Reporting Services report server with additional support for Power BI interactive reports. Unlike the Power BI service that is updated very frequently, updates are available for Power BI Report Server and the associated development tools about every four months.
The recommended report design tool for PBRS is Report Builder. Installing or downloading Report Builder from the downloads menu in the Report Portal will ensure that you have a compatible version.
Figure 1-2 – Power BI Report Server Downloads menu
To design and publish Power BI reports for Power B Report Server (PBRS), you will need to use the version of Power BI Desktop that is specifically suited for PBRS rather than the Power BI cloud service. Microsoft manages a special version because the feature sets and release cadence between PBRS and the Power BI service are different. Things can get a little complicated if you also install Power BI Desktop for the service (from the Windows Store of from the Microsoft Downloads site), in which case you will have two versions of the application and you should make it a point to use open the appropriate application version.
Using the Programs menu from the Windows Start button, you will see that the Power BI Desktop version for PBRS has an icon with a black background, gold logo and is titled “Microsoft Power BI Desktop”. The standard version has a gold icon background, black logo and is titled “Power BI Desktop”. Figure 1-3 shows that you can change the default application version associated with files having the .PBIX file extension. In File Explorer, right-click a .PBIX file and then choose Open with from the menu.
Figure 1-3 – Changing the default application version
Operational Reports and Analytic Reports
We use the tools that we have at our disposal to create the solutions we need. If you were to ask me in 2010 how to design an interactive dashboard to analyze trends, I would have probably done that in SQL Server Reporting Services.
In my previous Reporting Services books, I have dedicated several chapters to techniques for designing interactive, visual, dashboard-style reports for Business Intelligence solutions. Today we think of Power BI as the tool for that kind of analytic reporting. Software and technology is an ever-changing journey and the best solution at one point in time may be quite different just a few years later. That is the reality of the technology-enabled world we live in.
If you need to create a report that allows users to interact with their data in summary, to perform calculations and to present results on a single page using interactive charts and other visuals; Power BI is probably your best bet.
The term “operational report” describes the purpose for reports used for day-to-day business processes. Paginated reports are most often used for operational processes, so the terms are often used together, just like “analytic” and “visual” report are closely related.
If you need to produce a long list of items with groups of details with totals and subtotals, shipping labels, invoices, or a multi-page contract; those are operational reports. Paginated reports may or may not entail multiple pages of content, but they typically consist of multiple fields formatted to fit precisely on one or more pages.
Use the Right Report Tool
Now that we have clarified the difference between these two categories of report types, here’s the complicated part: There can be a lot of overlap between the two and there isn’t always one clear choice. A summary report that allows you to drill-down to transactional details could be both analytical and operational.
Although each reporting tool may be optimal for one type or another, there are a variety of reports that could be created using either Paginated Report Builder or Power BI Desktop.
There is absolutely nothing wrong with creating operational reports with Reporting Services or Pagination Report Builder. Nor is there anything wrong with using Power BI to create certain types of operational reports. The key to effective report design is to work within the strengths and limitations of the design tool and platform, and to recognize when you are at risk of exceeding the boundaries of a reporting tool. Here are a few examples:
A multi-page list of items on an inventory list or shipping manifest, printed and attached to a clipboard to be manually checked-off when delivered – that is an operational report. This same process could be managed without being printed by checking off items on a hand-held table – still an operational report.
Multi-page contract or forms printed on legal size paper? – operational report
Mailing labels, name badges, place cards for guests at a fancy dinner – operational report
A collection of charts, tables and other visual report items on a single page that allow you to interactively explore data by filtering and cross-highlighting items by clicking or tapping different visuals – Power BI in the optimal tool for this style of reporting
A multi-level hierarchy of organization departments and cost centers that where you can expand rows to see more details, with columns representing different costs, forecasts and variances? – this is though one because both Power BI and Paginated reports have drill-down matrix visuals that allow you to navigate from summary to detail.
A summary and analytical report containing clickable links that navigate to a detail, operational report and filter the results based on items selected in the summary report? – This is where analytical and operational reports can be used together to create comprehensive, end-to-end solutions.
Setting Up the Book Samples
Two database backups are provided with the book download files. To follow-along and reproduce the recipe solutions, you will need to at least have the PaginatedReportData SQL Server database restored to your on-premises SQL Server instance or an Azure SQL instance in your Microsoft Azure tenant.
Nearly all the recipe instructions in the book include instructions to use the PaginatedReportData database. If you are using SQL Server on premises, you will need SQL Server 2016 or newer. You can install a local instance of SQL Server on Windows 10 using the free Developer license. The download files and instructions are available here: https://www.microsoft.com/en-us/sql-server/sql-server-downloads
Report Server
If you would like to setup a local report server instance, you can use the free Developer license for either Power BI Report Server or SQL Server Reporting Services. Power BI Report Server can be installed from here: https://www.microsoft.com/en-us/download/details.aspx?id=56722 .
SQL Server Reporting Services may be installed from here: https://www.microsoft.com/en-us/download/details.aspx?id=100122 . Do not install both report server products. If you are not sure which one to use, install Power BI Report Server. Just understand that these products are licensed only for development and not for production use. For production, the license for Power BI Report Server can be considerably more expensive than a conservatively licensed edition of SQL Server Reporting Services.
Make sure you add your Windows account to the Administrators group when installing SQL Server. If you will be working with SQL Server Analysis Services and you want to restore the provided book sample database, choose the option to install SSAS in Tabular storage mode and add your Windows user to the Administrators group during setup.
Note : Unlike earlier versions of SQL Server, Reporting Services is now a separate download and installation. This is because Reporting Services development is now managed by the Power BI product teams and is on a difference release cadence than SQL Server.
To manage your SQL Server instance and restore the sample database you will need SQL Server Management Studio (SSMS), which can be downloaded from here: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15
Note: The links provided are for the most recent versions of these products at the time of writing and there may be newer versions available in the future, and perhaps by the time you read this. Please research the current product version before using these links.
Install the SQL Server instance completely before installing SSMS and then the report server (either PBIRS or SSRS).
Open SSMS and connect to the local database instance.
Figure 1-4 – SQL Server Management Studio connection dialog
Figure 1-5 shows the two backup files provided with the book download files. Use the following steps to restore the SQL Server sample database.
Copy these files to a folder that is easy to browse to on your local computer storage drive (like C:\Temp). Don’t use your OneDrive or a network folder.
Figure 1-5 – Sample database backup files
In the Object Explorer, expand the relational database instance
Right-click on the Databases node
From the menu, chose Restore Database…
Figure 1-6 – Restore Database option
On the Restore Database dialog, shown in Figure 1-7, click the ellipsis button in the Source group to the right of the Device radio button.
Figure 1-7 – SQL Server Restore Database dialog
A file dialog opens which will allow you to browse to the location of the PaginatedReportData.bak database backup file.
Select the backup file and click OK.
Click OK on the Select backup devices dialog.
Back on the Restore Database dialog, if the same relative drive and folder paths can be created on your computer, the OK button will be enabled and no warnings or errors will be displayed. If a warning is visible, you may need to specify a different path for the database files that is compatible with your system.
Click the OK button to begin restoring the database backup.
Restoring the Analysis Services Tabular database backup is a similar process. After connecting to your SSAS instance, right-click the Databases node and choose Restore…
SSAS database backups can only be restored from trusted system drives. I find it best to copy the backup file to a folder off the root of my C: drive or other local storage drive.
Paginated Report Design Basics
This section will briefly review the essential building blocks of paginated report design that you will use as you apply different report recipes. Since we are not covering all of the beginner-level topics, the purpose of this high-level review is to help you be mindful of the terminology and remind you how these components work together. The following terms were established when the RDL (Report Definition Language) standard was first devised back in 2003. An important fact to keep in mind is that there are only so many words in the English language so many of these terms take on different meanings as they are applied to different products such as paginated reports and Power BI Reports. You want consistent object naming across different products? Nah, that’s just crazy talk!
Note : The best way to truly understand how the various report objects are identified is to open a report definition file in a text editor and review the nested XML elements. You will clearly see object collections and definitions with associated properties.
A thorough understanding of the RDL structure in detail is not necessary to develop a report but understanding the fundamental organization of these objects is important. Figure 1-8 shows a portion of the RDL file for a report alongside the same report in Report Builder. I’ve added arrows to the figure so you can see how these objects appear in the designer.
Figure 1-8 – RDL file and Paginated Report Builder properties
When you choose to create a new report from the File menu in Report Builder, you are presented with the New Report or Dataset dialog shown in Figure 1-9. My preference is to use the Blank Report option. For novice users who need to create very simple “out of the box” reports, one of the first three options can get them started but I am not a fan of the report wizards. Early Reporting Services was architected at a time when it was popular for all “mature” Windows applications to use wizards to step users through a setup or design process. Although SSRS conformed and includes report design wizards, I find it easier and faster to work without them. Just create a blank report and then explicitly add the needed objects. You can drag and drop fields directly into a table, matrix or chart.
Figure 1-9 – New Report or Dataset dialog
This is the highest-level object in the RDL file. Aside from serving as a container for all other objects, properties of the Report object include the printable page size and margins. The following advanced properties are used for adding custom internal or externally referenced programming code, which can be used to extend a report’s capabilities:
Code—Custom Visual Basic functions can be written and stored in the report and then called from expressions on any of the properties for this report. The language is a subset of Visual Basic.NET and shares some characteristics of Visual Basic for Applications (VBA).
References—Like custom functions stored in the Code element, external code libraries may be referenced and also used in property expressions. The advantage of this approach is that a single code library may be used by multiple reports.
Variables—Custom variables may be defined and used within the report to enable dynamic and advanced functionality. These variables are typically set and used within expressions or custom code functions in the report.
Page Properties—Page attributes and properties consist of the page scale units, page orientation, height, width, and margins.
Data Sources
A report has at least one data source object and a single data source may be shared by multiple datasets. A data source contains the connection information needed for a data provider to authenticate and connect to the source of report data.
Data source information can also be stored in a separate shared data source file. If a shared data source is used, the report still has an embedded data source object that references the shared data source object.
When considering best practices, there are two options: embedded and shared data sources. An embedded data source simply includes the connection information within the report RDL file with no external dependencies. If you have multiple reports that share sources of data in a business environment, using a shared data source simplifies deployment and saves time and effort with centralized management. In generally, I consider the use of shared data sources to be a best-practice for enterprise report development. The trade-off is that reports with shared data sources are not as portable. For this reason, all of our report samples use an embedded data source.
When you add a query to a report, the designer generates a dataset object which, in addition to the query, defines the collection of fields with associated data types, a collection of parameters, groups, filters and sort expressions. Novice report authors might think of a dataset as the query that returns a set of data. A dataset is often just a simple a bridge between a query that returns a set of data and the data regions and items in the report that consume and visualize the data.
In more advanced scenarios, a dataset has the potential to be a powerful tool that can add sophistication to a report. Consider that a data source like SQL Server can use complex T-SQL queries that support features like parameterized filtering, grouping and sorting. Yet, a less sophisticated data provider like, let’s say a JSON file or an XML data feed, don’t have such capabilities. In this case, parameterized filtering, grouping and sorting can all be performed within the report using properties of the dataset object.
As I mentioned, the dataset contains a collection of parameters. Report parameters used in a query are mapped to corresponding dataset parameters which can then be referenced in the dataset query. The query designer typically generates all the necessary expressions and property settings automatically with little effort for the report author.
Report Body
In the visual designer, the report body is the canvas that you drag and drop items into. The Body object contains just a few properties and serves as a container for all the data ranges and report items within the report. In the designer, the report body really is a blank canvas on which you place report items and data ranges. This is a unique approach when comparing Reporting Services with most other reporting products. This is a very flexible approach to report design that encourages free-form report formatting and unconstrained layout. Rather than being constrained to placing items at specific rows or columns, you have the freedom to place items anywhere within the report body. If you need to repeat items into report body, you use a data region such as a table, matrix or list. The List data region extends this pattern by repeating a free-form region for each record.
Report Items
The most common report item is a textbox because it does so much and is so useful. A textbox can be bound directly to a dataset field to display a single value or it can reside with the cells of a table, matrix or a list data region.
Both of these objects are The difference between a report item and a data region
Figure 1-10 – Report Builder Insert ribbon with Report Items
Data Regions
A data region is a report item that repeats rows, columns or a rectangular region for each grouped value or dataset row. There are three distinct types of data regions: Table, Matrix and List.
Figure 1-11 – Report Builder Insert ribbon with Report Regions
Technically, the all three data region types are implementations of the same core object, called a Tablix, that have certain properties preset to make them behave differently.
A table is a region with repeating rows. Optionally a table can have any number of row groups with corresponding headers and footers for totals and subtotals.
A matrix is a variation of the table with repeating column groups. This effectively creates a pivot table with an aggregated value at the intersecting cell for each row and column group value.
A list is a rectangular region that is repeated for each group or detail row in the dataset. The difference between a list and a table is that any number of embedded report items can be placed in the repeating region.
Filtering Data
Paginated reports have two different mechanisms for filtering data. The most common approach is to use the dataset query (such as the SQL WHERE clause) and filter records at the source.
When the query parser encounters these two query parameters as the query is parsed, corresponding report parameters are added and then these parameters can be fine-tuned in the Report Parameter Properties to provide a better user experience.
Using this filtering technique, if the user provides different parameter values to render several views of the same report within a session, the database will be queried repeatedly, perhaps resulting in longer overall wait times and much of the same data moving across the network multiple times. The dataset filtering feature, which is defined on the Filter page of the Dataset dialog, screens data in the report after it reaches the report server. This means that more data may be retrieved from the database than actually appears in the report. This may not be the most efficient way to filter data for a single report execution, but it can speed things up after the initial query runs and a user continues to interact with a report and provide different parameter values during a session. Because all the data is cached (held in memory), reports will render much faster after the report cache has been loaded. This technique can reduce the overall network traffic and rendering time. The report can also be configured so that the cached data is saved to disk, usually for a specific period of time.
Using Stored Procedures
There are advantages for using stored procedures rather than verbose T-SQL queries in a report. Complex queries can be stored as reusable database objects. Stored procedures can run more efficiently and provide greater security and control. The best way to query a data source will depend mainly on your requirements. Filtering techniques when processing parameters (on the database server, the client, or both) affects performance, efficiency, and the flexibility of your reporting solution. Handling parameters on the database server will almost always be more efficient, whereas processing parameters on the client will give you the flexibility of handling a wider range of records and query options without needing to go back to the database every time you need to render the report. As a general rule, it’s a good idea to filter data on the database server unless you have a good reason to do otherwise.
Using a parameterized stored procedure is typically going to provide the most efficient means for filtering relational data because it returns only the data matching your criteria. Stored procedures are compiled to native processor instructions on the database server. When any kind of query is processed, SQL Server creates an execution plan, which defines the specific instructions that the server uses to retrieve data. In the case of a stored procedure, the execution plan is prepared the first time it is executed, and then it is cached on the database server. In subsequent executions, results will be returned faster since some of the work has already been done.
Using Power BI Datasets in Paginated Reports
One of the fundamental differences between Power BI and Paginated reports is that the best use case for paginated reports is detail-level, operational reporting which typically consume transactional SQL queries. Whereas highly-visual Power BI reports typically provide summary views that are based on a sematic data model (cube or tabular model). One of the many data source types available to Paginated reports published in the Power BI service are Power BI datasets. This a convenient way to consume the same data used in Power BI reports within a paginated report published to the same tenant.
Note : The term “Dataset” has a different meaning in Power BI so don’t confuse this with a paginated report dataset. A Power BI dataset is the published in-memory semantic data model which contains tables, relationships and calculation objects written in DAX such as measures.
The advantage of using a Power BI dataset is that if the summary data needed for your report already exists in a published dataset that may have been created for Power BI interactive reports, you can simply use it without writing another query. A disadvantage might be that Power BI datasets and data models are typically designed for summary reporting and may not be optimal for an operational-style report. If you use parameters or apply filters or sorting in the paginated report, be mindful of the query efficiency and test the performance with a representation selection of data. We have found that in some cases, writing a separate TSQL query may be more performant that consuming a Power BI dataset.
Summary and Dataset Best Practices
When creating and managing datasets, consider the following:
Dataset names cannot contain spaces. Use mixed-case names. Concatenate multiple words and descriptive names, capitalizing the first letter of each word.
Try to avoid changing the name of a dataset after you use it. Data regions have a DatasetName property that must be manually updated if you change a dataset’s name.
Independent report items, like a textbox, may be bound directly to a dataset field. To do this, reference the dataset name in the second argument of the aggregate function used in the Value property to define the function scope. For example, if the CustomerName field is dragged from Dataset1 to the report body, it produces a textbox with the Value expression: =FIRST(Fields!CustomerName.Value, “Dataset1”)
You can use the T-SQL graphical query builder to design simple queries or write them by hand if you prefer. For more advanced queries, design them in the SQL Server Management Studio (SSMS) which has a richer set of code validation and debugging tools. Copy and paste the resulting query into the report designer query editor using text mode.
DAX and MDX queries used in a paginated reports can get complicated to write and to manage. In some cases Power BI may be a more ideal reporting tool for SSAS, AAS or Power BI dataset sources. For paginated report datasets using these data sources, you can use the graphical editor to generate the field metadata, parameters, and parameter drop-list datasets. If you modify or enhance a DAX or MDX query, make sure it conforms to the same metadata signature and the result returns the same fields in the same order. Always make a backup copy of the report before modifying the query.
Share this:
- Click to email a link to a friend (Opens in new window)
- Click to print (Opens in new window)
- Click to share on Twitter (Opens in new window)
- Click to share on LinkedIn (Opens in new window)
- Click to share on Facebook (Opens in new window)
- Click to share on Reddit (Opens in new window)
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Install SQL Server Reporting Services
- 4 minutes to read
- 13 contributors
SQL Server Reporting Services installation involves server components for storing report items, rendering reports, and processing of subscription and other report services.
Download SQL Server 2022 Reporting Services from the Microsoft Download Center.
Download SQL Server 2019 Reporting Services from the Microsoft Download Center.
Download SQL Server 2017 Reporting Services from the Microsoft Download Center.
Looking for Power BI Report Server? See Install Power BI Report Server .
Upgrading or migrating from a SQL Server 2016 or earlier version of Reporting Services? See Upgrade and Migrate Reporting Services .
Before you begin
Before you install Reporting Services, review the Hardware and software requirements for installing SQL Server .
Install your report server
Installing a report server is straightforward. There are only a few steps to install the files.
You don't need a SQL Server Database Engine server available at the time of install. You do need one to configure Reporting Services after install.
Find the location of SQLServerReportingServices.exe and launch the installer.
Select Install Reporting Services .
Choose an edition to install and then select Next .
For a free edition, choose either Evaluation or Developer from the drop-down.
Otherwise, enter a product key. Find the product key for SQL Server Reporting Services .
Read and agree to the license terms and conditions and then select Next .
You need to have a Database Engine available to store the report server database. Select Next to install the report server only.
Specify the install location for the report server. Select Install to continue.
The default path is C:\Program Files\Microsoft SQL Server Reporting Services.
After a successful setup, select Configure Report Server to launch the Report Server Configuration Manager.
Configure your report server
After you select Configure Report Server in the setup, you're' presented with Report Server Configuration Manager . For more information, see Report Server Configuration Manager .
You need to create a report server database to complete the initial configuration of Reporting Services. A SQL Server Database server is required to complete this step.
Creating a database on a different server
If you're creating the report server database on a database server on a different machine, you need to change the service account for the report server to a credential that is recognized on the database server.
By default, the report server uses the virtual service account. If you try to create a database on a different server, you may receive the following error on the Applying connection rights step.
System.Data.SqlClient.SqlException (0x80131904): Windows NT user or group '(null)' not found. Check the name again.
To work around the error, you can change the service account to either Network Service or a domain account. Changing the service account to Network Service applies rights in the context of the machine account for the report server.
For more information, see Configure the report server service account .
Windows Service
A Windows service is created as part of the installation. It's displayed as SQL Server Reporting Services . The service name is SQLServerReportingServices .
Default URL reservations
URL reservations are composed of a prefix, host name, port, and virtual directory:
An example of the complete URL string might be as follows:
https://+:80/reportserver , provides access to the report server.
https://+:80/reports , provides access to the web portal.
If you're accessing the report server from a remote machine, you want to make sure you've configured any firewall rules if there's a firewall present.
You need to open up the TCP port that you've configured for your Web Service URL and Web Portal URL. By default, these are configured on TCP port 80.
Additional configuration
To configure integration with the Power BI service so you can pin report items to a Power BI dashboard, see Integrate with the Power BI service .
To configure email for subscriptions processing, see E-Mail settings and E-Mail delivery in a report server .
To configure the web portal so you can access it on a remote computer to view and manage reports, see Configure a firewall for report server access and Configure a report server for remote administration .
Related information
For information on how to install SQL Server Reporting Services native mode, see Install Reporting Services native mode report server .
For information on how to install SQL Server 2016 Reporting Services (and earlier) in SharePoint integration mode, see Install the first Report Server in SharePoint mode .
With your report server installed, begin to create reports and deploy those to your report server. For information on how to start with Report Builder, see Install Report Builder .
To create reports using SQL Server Data Tools, download SQL Server Data Tools .
More questions? Try asking the Reporting Services forum
Additional resources
IMAGES
VIDEO
COMMENTS
The last third of the book focuses entirely on advanced techniques and extending the framework. Download Free PDF View PDF · Professional Microsoft SQL Server
know that Packt offers eBook versions of every book published, with PDF and ePub.
Learn to design more effective and sophisticated business reports While most users of SQL Server Reporting Services are now comfortable designing and
Name already in use · Free-Database-Books/book/SQL Server 2016 Reporting Services Cookbook.pdf · Footer.
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
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.
microsoft-sql-server-reporting-services-recipes-for-designing-expert-reports. 1/1. Downloaded from uniport.edu.ng on. December 29, 2022 by guest.
Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports [Turley, Paul, Bruckner, Robert M.] on Amazon.com.
Three editions later, I teamed up with Robert Bruckner, one of the lead developers on the SSRS product team at Microsoft, to write a different kind of book.
The default path is C:\Program Files\Microsoft SQL Server Reporting Services. After a successful setup, select Configure Report Server to launch
That book was Microsoft SQL Server Reporting. Services Recipes for Designing Expert Reports by Paul Turley and Robert Bruckner. For those few.