Jayaram Krishnaswamy - Learning SQL Server Reporting Services 2012 [Учебник, 2013, PDF][ENG]

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