Chapter 1: Overview and Installation – SQL Server Reporting Services 2012 9
SQL Server 2012 – mission statement by Microsoft 9
Overview of Reporting Services 2012 11
Installing software used in the book 14
SQL Server 2012 installation requirements 15
Operating system requirements (64-bit) 15
Hardware requirements 16
Software requirements 16
Configuring the hardware used for the book 17
Hands-on exercise 1.1 – installing SQL Server 2012 18
Downloading the source file 18
Installation steps 18
Hands-on exercise 1.2 – verifying the installation 33
Installation choices and notes 37
Configuring SQL Server Reporting Services 37
Hands-on exercise 1.3 – configuring SQL Server 2012 Reporting Services in Native mode 38
Report Server – configuration options 50
Installing sample databases 55
Downloading files and running scripts 55
SharePoint Server 2010 Enterprise Edition 56
Installing SharePoint Server 2010 Enterprise Edition on Windows 7 (64-bit) 56
Installing Reporting Services 2012 in SharePoint Integrated mode 60
Reporting Services add-in for SharePoint 2010 61
Installing and starting the Reporting Services SharePoint Service 62
Table of Contents
[ ii ]
Hands-on exercise 1.4 – installing the Reporting Services
SharePoint Service 63
Creating the Reporting Services Service application 65
Activating the Power View site collection feature 71
Summary 72
Chapter 2: SQL Server Reporting Services 2012 Projects
with Visual Studio 2012 73
What is business intelligence? 73
Introduction to SQL Server Data Tools 74
BI Projects on SSDT 77
Report Server Project 78
Report Server Project Wizard 78
Creating reports using SSDT 79
Hands-on exercise 2.1 – creating a report using the Report Server
Project Wizard 79
Hands-on exercise 2.2 – deploying the report to the Report Server 87
Hands-on exercise 2.3 – creating a report using the Report wizard
in SSDT 90
Hands-on exercise 2.4 – modifying the report in Project RSPW2012
using an expression 96
Report formats supported in Report Server Reports 98
Report-related projects in Visual Studio 2012 99
Creating reports using Visual Studio 2012 100
Software and hardware requirements 100
Hands-on exercise 2.5 – creating a report for a Windows Form
application using Report Viewer Control 101
Hands-on exercise 2.6 – creating a report using Report Viewer
Control for the Web 115
Hands-on exercise 2.7 – using Report Viewer Control in the
remote mode 121
Hands-on exercise 2.8 – converting an RDLC to a RDL file 123
Microsoft Report Viewer Runtime 129
Summary 130
Chapter 3: Overview of SQL Server Reporting Services 2012
Architecture, Features, and Tools 131
Structural design of SQL servers and SharePoint environment 132
Native mode 134
SharePoint Integrated mode 136
Reporting Services configuration 139
Native mode 139
Table of Contents
[ iii ]
SharePoint Integrated mode 142
Hands-on exercise 3.1 – modifying the configuration file
in Native mode 143
Turn on/off the Report Server Web Service 144
Turn on/off the scheduled events and delivery 145
Turn on/off the Report Manager 145
Hands-on exercise 3.2 – turn the Reporting Service on/off in SSMS 146
Salient features of Reporting Services 2012 147
Report definition 148
XML-based report definition (the .rdl file) 148
Power View report contents 150
Creating reports 150
Report data 151
New toolbox items in Report Builder 3 153
Report parts and their reusability 153
Customizing reports 154
Saving and deploying reports 155
Report validation 156
Viewing reports 157
Managing reports 158
Report scheduling 158
Subscriptions and delivery 160
Features new in RS2012 SharePoint Integrated 162
Power View 163
Data Alerts 164
Implementing security – authentication and authorization 166
Authentication 166
Authorization 170
URL access 171
Reporting Services extensions 172
Reporting Services tools 173
Tools for Report Server administration 173
Tools for report authoring 174
Tools for report content management 174
Summary 175
Chapter 4: Working with Report Manager 177
Tasks performed using Report Manager 178
Starting Report Manager 179
Starting Report Manager for the URL 180
User access to Report Server (Report Manager) 180
Considerations for giving user access to the Report Server 180
First step – deploying reports 181
Table of Contents
[ iv ]
Report Manager user interface 182
Customizing Report Manager 185
Hands-on exercise 4.1 – creating, modifying, moving,
and deleting folders 185
Navigating through the folders 186
Creating a folder and a subfolder 187
Moving an item into a folder 190
Configuring permissions from Report Manager 191
Configuring role-based security 192
Hands-on exercise 4.2 – assigning a Windows user to the
System Administrator role 193
Creating a Windows user 193
Assigning RSMax to the RS System Administrator role 195
Assigning users to item-level roles 196
Review users on a Reporting Services database 198
Hands-on exercise 4.3 – assigning a user to a Custom role 198
Hands-on exercise 4.4 – creating a permission to a specific report 200
Report data sources 202
An embedded data source 202
A shared data source 203
Hands-on exercise 4.5 – creating a shared data source on
Report Manager 203
Hands-on exercise 4.6 – creating a data model from a data source 205
Viewing, searching, and printing reports 211
Hand-on exercise 4.7 – view, print, and search on Report Manager 212
Viewing reports 212
Printing reports 213
Search 215
Uploading and downloading files from the Report Server to
the filesystem 217
Uploading a report on the computer to the Report Server 217
Hands-on exercise 4.8 – uploading a report
to the Report Server 217
Hands-on execise 4.9 – downloading and reviewing a report
definition file from the Report Server 218
Report subscription and delivery 220
Hands-on exercise 4.10 – creating an event-driven report
subscription for delivery by an e-mail 222
Hands-on exercise 4.11 – creating an event-driven report
subscription for delivery to a file share 227
Hands-on exercise 4.12 – creating data-driven report subscription
for delivery to a file share 229
Table of Contents
[ v ]
Create a Subscribers database in SQL Server 230
Creating the data-driven Subscription and testing it 231
Report caching 237
Report processing options 238
Cache refresh options 239
Hands-on exercise 4.13 – creating a cache refresh plan by
preloading the cache 240
Snapshot and snapshot history 242
Hands-on exercise 4.14 – creating a snapshot and snapshot history 242
Summary 244
Chapter 5: Working with Report Builder 3.0 245
Report authoring with Report Builder 246
Downloading and installing Report Builder 246
Report Builder 3.0 user interface 248
Report authoring 248
Hands-on exercise 5.1 – creating a report with an embedded
data source 249
Hands-on exercise 5.2 – creating a report from a shared
data source 259
Creating a List report 263
Parameterized reports 267
Subreports 267
Hands-on exercise 5.3 – creating a report that has a subreport 268
Creating the subreport 268
Creating the main report 270
Embedding the subreport in the main report 272
Report with groups 275
Hands-on exercise 5.4 – setting up a group and creating
a document map 276
Bring up the report from the previous hands-on exercise and remove parameter 276
Adding a group to the data 277
Adding a Document Map to the report 280
Configuring page breaks 281
Adding interactive sorting 282
Drill-through and drill-down reports 284
Drill-through reports 285
Hands-on exercise 5.5 – Creating a drill-through report 285
Source report 285
Destination report 286
Set up the drill-through action 287
Drill-down report 288
Hands-on exercise 5.6 – Creating a drill-down report 289
Table of Contents
[ vi ]
Linked reports 291
Hands-on exercise 5.7 – creating linked reports 291
Customizing the linked report in the France folder 292
Creating a report with XML data sources 293
Creating a well-formed XML data 294
Displaying data with sparklines, maps, data bars, and indicators 297
Hands-on exercise 5.8 – creating a report and highlighting data
with data bars 297
Displaying data with sparklines 300
Hands-on exercise 5.9 – creating reports with sparklines 301
Creating a table 301
Creating a report and inserting sparklines 302
Indicators 304
Hands-on exercise 5.10 – creating reports using indicators 304
Reports with map 307
Hands-on exercise 5.11 – creating reports with embedded maps 307
Report parts 314
Hands-on exercise 5.12 – creating report parts and reusing an item 314
Saving report as report parts 315
Reusing the report parts items 319
Summary 323
Chapter 6: Power View and Reporting Services 325
What is Power View? 325
Helpful resources 326
What do you need to author a Power View report? 327
Creating a tabular model 328
Hands-on exercise 6.1 – creating a tabular model 328
Brief review of SSDT ribbon 330
Creating a connection to the Northwind database 331
Getting tables from the database 332
Default field set and table behavior 337
Adding measures 339
Deploying the model 340
Hands-on exercise 6.2 – deploying the model 340
Tabular model permissions 341
Hands-on exercise 6.3 – creating a role in SQL Server Data Tools 341
Creating a Power View 344
Hands-on exercise 6.4 – connecting to model from SharePoint
Server 2010 344
Connecting to the model 347
Creating a data source using the model as the source 347
Creating a Power View report using the data source 349
Table of Contents
[ vii ]
Hands-on exercise 6.5 – exploring a Power View report 350
Creating the first view of the Power View report 351
Creating a chart showing sales orders from NW Employees shipped from cities 355
Adding a second view to the Power View report 357
Highlighting of data 360
Displaying data as a card 360
Using tiles 362
Slicing the data 363
Advanced filtering 365
Scatter and bubble charts 366
Animation with Power View 368
Navigating the views of the Power View report 370
Save, do, undo, and refresh 370
Saving to PowerPoint 371
Summary 372
Chapter 7: Self-service Data Alerts in SSRS 2012 373
Getting ready for data alerts 374
Granting permissions to work with data alerts 377
Creating a report and saving to the Report Server 385
Creating a report in Report Builder and saving it to the
Documents library 386
Hands-on exercise 7.1 – creating a report in Report Builder
and saving it to the SharePoint site 387
Giving permission to a report 389
Hands-on exercise 7.2 – giving full control of a report to a user 390
Creating a data alert 393
Hands-on exercise 7.3 – creating a data alert in Data Alert Designer 394
Editing data alerts 398
Troubleshooting 402
Using PowerShell to review the log file 404
Alert logs and alerting database 406
Summary 408
Chapter 8: Reporting Services and Programming 409
Overview of programming interfaces and utilities 409
URL access 410
Hands-on exercise 8.1 – URL access, Native mode Report Server 411
Accessing the Report Server 411
Listing contents of a folder 412
Accessing a component in report parts 412
Rendering a report 413
Accessing the contents of a data source 413
Exporting to supported formats 414
Rendering a report with the report parameter 414
Table of Contents
[ viii ]
Hands-on exercise 8.2 – URL access and SharePoint Integrated
mode Report Server 415
Accessing the Report Server in SP-integrated implementation 415
Accessing a report on the Report Server in SP-integrated implementation 416
ReportViewer control 417
Hands-on exercise 8.3 – using URL access and ReportViewer
controls with Web applications 418
Report Server Web Services API 422
Hands-on exercise 8.4 – rendering a report
on the native mode Report Server into different formats 423
Reporting Services in SharePoint Integrated mode 430
Hands-on exercise 8.5 – accessing SharePoint management
endpoints 431
PowerShell 434
Hands-on exercise 8.6 – a quick review of basics 435
PowerShell and reporting services with SharePoint Integration 437
Hands-on exercise 8.7 – exploring reporting services in SharePoint
Integrated mode 438
Getting help about help 438
Application server of the Reporting Services Service Application 438
Finding all cmdlets related to Reporting Services SharePoint Integration 439
Proxy URL of the Report Server 440
SP service application pool 440
PowerShell and Native mode Reporting Services 2010 441
Hands-on exercise 8.8 – exploring Native mode Reporting Services 441
The Native mode Report Server 441
Native mode Report Server configuration 442
Extensions supported on a Windows Forms ReportViewer 444
Windows Management Instrumentation 445
Hands-on exercise 8.9 – exploring the native mode Report
Server programmatically 446
Providing access permission to WMI 446
Report Server properties using WMI 448
Reporting Services command prompt utilities 451
The RSS utility 452
What can rs.exe do? 452
Hands-on exercise 8.10 – creating a data source on the report
server using rs.exe and a script file 453
The Rskeymgmt utility 456
Rsconfig 457
Table of Contents
[ ix ]
Incorporating custom code into reports 458
Hands-on 8.11 – inserting custom code into a report 458
Summary 460
Chapter 9: Windows Azure SQL Reporting 461
What is Windows Azure SQL Reporting? 462
Hands-on exercise 9.1 – accessing the Windows Azure portal 463
Creating content for reports and viewing them 468
Preparing to author reports 468
Hands-on exercise 9.2 – creating a report using SSDT and
deploying it to the Windows Azure Reporting on the Cloud 469
Creating a SQL database on Windows Azure 469
Creating a table for the report 474
Populating the table in SSMS 476
Creating a SQL Reporting Service in Windows Azure 478
Creating a report based on the Skyblue database on the Windows Azure Platform 480
Deploying the report to the SQL Reporting Services on Windows Azure 484
Viewing the report on the SQL Reporting Web server 486
Hands-on exercise 9.3 – using the Windows Azure SQL Reporting
Services to create folders, share data sources, and upload reports 488
Creating a shared data source in the portal 489
Managing your reports and users is easy 491
Managing users 492
Managing reports 493
Managing the report Dashboard 494
Status of activities 495
Managing permissions 496
Report viewing 496
Using Report Builder to view reports 497
Making changes to the report and placing it on the Report Server 500
URL access to reports on the Report Server 502
Accessing the server and running a report 502
Do gadgets like indicators and data bars work? 502
SQL Server 2012 Reporting Services – Known Issues 503
Hands-on exercise 9.4 – migrating a table on an on-premise
SQL Server 2012 to the Windows Azure SQL database 505
Summary 506
Chapter 10: Applications Accessing Report Servers 507
Hands-on exercise 10.1 – accessing the Native mode Report
Server using SSIS 508
Creating the Report Server Web Service WSDL file 513
Table of Contents
[ x ]
Providing the WSDL file to the Web Service Task 513
Adding a File System Task to the Control Flow page 515
Accessing reports from a Windows Presentation Foundations
classes project 517
Hands-on exercise 10.2 – accessing Report Server URLs in a
WPF project 518
Accessing Native Report Server reports from SharePoint
Web parts 524
Hands-on exercise 10.3 – viewing reports on the Native mode
Report Server using SharePoint Web parts 524
Accessing the Web parts 525
Summary 532
Index 533
