Learning SQL Server 2008 reporting services : a step-by-step guide to getting the most of Microsoft SQL Server reporting services 2008

A step-by-step book and eBook to getting the most of Microsoft SQL Server Reporting Services 2008 Cover 1 Table of Contents 10 Preface 22 Chapter 1: Overview of SQL Server Reporting Services 2008 30 Overview of enterprise reporting 30 Some highlights of SQL Server Reporting Services 2008 32 Scalability 32 New Tablix design feature 32 Enhancement to Report Design 32 Data visualization 33 Installing SQL Server 2008 33 Version of SQL Server 2008 to be installed 33 Hardware requirements 33 System and software requirements 34 Hardware and software used for working with the book 35 Hands-on exercise 1.3: Installing a test database 67 Getting sample databases into the server 73 Hands-on exercise 1.2: Reviewing the installation 60 Program shortcuts 61 Reviewing installed services 63 Starting/stopping Reporting Services 64 Accessing installed services from the SQL Server Management Studio 65 Post installation checks 66 Hands-on exercise 1.1: Installing a named instance of SQL Server 2008 35 Getting ready 35 Follow the steps 37 Installation choices and notes 59 Summary 90 Hands-on exercise 1.4: Configuring the Report Services 74 Report Server configuration options 87 Chapter 2: Overview of SSRS 2008 Architecture and Tools 92 Architectural details and components 92 Report Server 94 Report Server and HTTP 94 Report processing 94 Report Server backend 95 Reporting Services and the database engine 95 Report Manager 98 Starting Report Manager 99 Model designer 101 Report Builder 102 Report Builder 1.0 103 Report Builder 2.0 105 Report Builder features 105 Extension components 107 Data processing 107 Rendering 107 Scheduling and delivery 107 Support for reporting in Visual Studio 2008 108 ReportViewer controls 108 ReportViewer control for Windows applications 109 ReportViewer control for Web applications 114 Business intelligence support 115 Reporting Server configuration file 117 Summary of Extensions 126 Summary 127 Chapter 3: Report Integration with Microsoft ReportViewer Controls 128 ReportViewer features 129 Toolbar 130 Difference between web server and Windows forms versions 132 Hands-on 3.1: Using ReportViewer control for Windows 133 Getting ready 133 Follow the steps 134 Create a Windows project 134 Add a ReportViewer control 136 Configure the report using the Report Wizard 136 Report details of the RDLC file 156 Features of report binding to form 159 Hands-on 3.2: Modifying the previous report 161 Follow the steps 161 Modifying an existing report 161 Hands-on 3.3: Adding a graphic to the report 164 Follow the steps 164 Hands-on 3.4: Using ReportViewer control for a web application 167 Getting ready 167 Follow the steps 167 Creating an ASP.NET web site project and adding a dataset using the Query Builder Tool 167 Adding a report template 175 Summary 180 Chapter 4: Visual Studio 2008 Business Intelligence Template Projects 182 Visual Studio 2008 Business Intelligence Projects 183 Using the Report Server project template 183 Using the Report Server wizard project template 184 Using the Report Model project template 184 Report authoring 184 Data sources used in reports 184 Embedded data sources 185 Shared data sources 186 Report Data and Query Designer 186 Report designer 187 Hands-on exercise 4.1: Creating a Report Server project using the Report Server Wizard project 191 Connecting to a data source 193 Building a query 197 Designing the report 200 Deploying the report to the Report Server 203 Methods of deploying reports to the Report Server 203 Hands-on exercise 4.2: Deploying and viewing the report designed in Hands-on 4.1 203 Report Model 206 Using the Report Model project template 207 Hands-on exercise 4.3: Creating a Report Model using the Visual Studio 2008 template 207 Getting ready 207 Follow the steps 208 Creating a new Report Model Project 208 Defining a Data Source for the Report Model 208 Defining a Data Source view for the Report Model 213 Defining the Report Model 217 Deploying the Report Model to the Report Server 223 Hands-on exercise 4.4: Deploying the Report Model 224 Hands-on exercise 4.5: Importing reports from MS Access 2003 226 Getting ready 226 Follow the steps 226 Summary 227 Chapter 5: Working with the Report Manager 228 Report Manager components 230 Folder structure and hierarchy 232 Sub-items in folder hierarchy 232 Hands-on exercise 5.1: Creating, deleting, and modifying folders 232 Creating a folder 233 Deleting a folder 234 Moving a folder 235 Modifying a folder 236 Report management 236 Permissions in Reporting Services 237 Hands-on exercise 5.2: Assigning users (or groups) to roles and permissions to access reports 240 Getting ready 241 Hands-on 5.2.1: Assigning a Windows user to System Administrator role 241 Creating a Windows user 241 Assigning a user to the System Administrator role 242 Hands-on 5.2.2: Assigning users to item-level roles 244 Creating a Windows user 244 Assigning users to roles 245 Hands-on 5.2.3: Assigning a user to a custom role 247 Hands-on 5.2.4: Creating a permission to a specific report 248 Permitting only the user Navika to this report 249 Report Data Sources 251 Report viewing and printing 252 Hands-on exercise 5.3: View, print, and search 253 Viewing reports 253 Printing reports 256 Printing report from the Print button on report 257 Changing report format for printing 258 Search for reports 259 Find text in a report 259 Managing data source connections with Report Manager 260 Connecting to report data sources 261 Connect using user supplied credentials 262 Connect using stored credentials 263 Connect using Windows integrated security 263 Connection that does not require credentials 264 Hands-on exercise 5.4: Windows user access to a report on the Report Manager 264 Getting ready 264 Deploying reports 268 Hands-on exercise 5.5: Deploying reports 268 Deploying a single report 268 Deploying a report with a shared data source 271 Uploading a report using the Report Manager 274 Creating a new data source in Report Manager and generating a model from the data source 278 Hands-on exercise 5.6: Creating a new data source and generating a model using Report Manager 278 Create new data source 278 Generating a model using Report Manager 281 Modifying reports on a Report Server and creating linked reports with Report Manager 285 Modifying reports 285 Linked reports 286 Hands-on exercise 5.7: Downloading a report definition file from the Report Server 286 Report delivery (new or cached) 287 On demand 287 Report caching 287 Hands-on Exercise 5.8: Working with the report cache 288 Turn on cache and cache a report 288 Schedule a report cache 290 Create a cache from execution snapshot 291 Report subscription 291 Hands-on exercise 5.9: Working with standard email, fileshare and data-driven subscriptions 292 Creating an email subscription 292 Creating a file share subscription 296 Creating a data-driven subscription 298 Summary 303 Chapter 6: Working with the Report Builder 304 Report Builder overview 304 Report Builder 2.0 user interface description 306 The menu for file operations 306 The ribbon 311 Home 311 Insert 312 View 329 Report Data, the Report Designer, and the properties 329 Report data 330 Report Designer pane 331 Properties 335 Server status and tools 336 Hands-on exercise 6.1: Enabling and reviewing My Reports 336 Getting ready 336 Hands-on exercise 6.2: Modifying a basic report 341 Getting ready 342 Follow the steps 342 Open Report Builder and open the ByOrders.rdl report 342 Review the imported MS Access report 343 Hands-on exercise 6.3: Creating reports with charts and gauges 348 Getting ready 349 Follow on 349 Creating a Microsoft Excel spreadsheet with some data 349 Create an ODBC DSN to access the data 350 Create a datasource using a DSN in Report Builder 2.0 351 Create a Dataset based on the data in the Excel file 354 Design a report to display the data 355 Create a chart to display the data 358 Create a bookmark and jump to it 366 Summary 368 Chapter 7: Report Authoring with Report Builder 2.0 370 Report authoring 371 Hands-on exercise 7.1: Filtering data at source using a query parameter 371 Parameterized reports 372 Follow on 372 Creating a data source 372 Query design 373 Parameter design 376 Viewing the Report 383 Hands-on exercise 7.2: Working with a column group and setting up a document map 385 Follow on 385 Removing the parameter from the previous hands-on 385 Group the results by "City" 386 Adding a Document Map for the report 388 Add interactive sort 390 Hands-on exercise 7.3: Working with a subreport 392 Follow on 393 Create a data source for the subreport 393 Add a dataset to the subreport 393 Add a table to the subreport 394 Changing background color of alternate rows 394 Create a data source and a dataset for the main report 395 Add a list to the main report 396 Create a second dataset 396 Get parameter list from this query 397 Add the subreport to the report 398 Making the subreport a drill-down 400 Hands-on exercise 7.4: Creating a linked report 401 Follow on 401 Creating named folders for reports 401 Customizing the linked report for London Office 402 Customizing the linked report for Buenos Aires 403 Hands-on exercise 7.5: Creating a drillthrough report 403 Follow on 404 Create a main report 405 Verifying drillthrough action 406 Hands-on exercise 7.6: Creating a report with XML data 407 Follow on 407 Create the dataset for the report 407 Set image properties 409 Hands-on exercise 7.7: Ad hoc 1: Creating a tabular report with a Report Model 410 Follow on 410 Create a data source 411 Creating a dataset 413 Fashioning a query 413 Design and run the report 417 Hands-on exercise 7.8: Ad hoc 2: Creating a matrix report with a Report Model 417 Follow on 418 Add customer details and product information to the query 418 Apply filter to the report 421 Creating a report based on the Analysis Services cube 422 Summary 424 Chapter 8: Programming Interfaces to Reporting Services 426 Overview of programming interfaces 426 URL access 426 ReportViewer control 427 The Reporting web services API 428 Windows Management Instrumentation 429 Reporting services utilities 429 Miscellaneous 429 Programming using URL access 430 Hands-on exercise 8.1: Displaying a report on the Report Server with an ASP.NET Web application using URL access 430 Follow on 430 Using a link 431 Using an 432 Hands-on exercise 8.2: Integrating a report on the Report Server with a Windows application using URL access 433 Follow on 434 Programming the ReportViewer control 435 Hands-on exercise 8.3: Integrating a report on the Report Server with an ASP.NET Web application 435 Follow on 435 Hands-on exercise 8.4: Displaying a report on the Report Server in a Windows application 438 Follow on 438 Publishing to the local web server 439 Programming with the Report Server web services API 442 Hands-on exercise 8.5: Rendering a report on the Report Server with an ASP.NET Web application to other formats 442 Hands-on exercise 8.6: Rendering a report on the Report Server with an ASP.NET Web application to other formats. 449 Follow on 449 Windows Management Instrumentation 453 Hands-on exercise 8.7: Identifying the Report Server instance and modifying some of the properties 454 Setting up access permission to WMI namespace 454 Accessing information about Report Server instances using WMI 455 Miscellaneous 458 Hands-on exercise 8.8: Creating a SQL Server Integration Services Package to display a report from the Report Server 458 Follow on 458 Hands-on exercise 8.9: Using custom code in an expression 459 Follow on 460 Summary 461 Chapter 9: Crystal Reports 2008 in Visual Studio 2008 462 Crystal Reports 2008 462 Hands-on exercise 9.1: Integrating a saved Crystal Report into an ASP.NET application in Visual Studio 2008 463 Follow on 464 Review source and customize options 466 Exporting the page using code 470 Hands-on exercise 9.2: Creating a Crystal Report and integrating it into a Windows forms application 470 Follow on 471 Creating a typed dataset 471 Add a blank Crystal Report and add META data 473 Add the report to the CrystalReportViewer 475 Hands-on exercise 9.3: Creating a Crystal Report and populating it with data at runtime 476 Follow on 476 Create project and add references 476 Add a Crystal Report and apply fields 477 Add code, build, and run 479 Hands-on exercise 9.4: Creating a Crystal Report and populating it with data from a stored procedure at run time 480 Follow on 480 Create a stored procedure and test it 480 Add Crystal Report and configure the field source 481 Add fields to the Crystal Report 483 Add code for data binding 484 Hands-on exercise 9.5: Creating a Crystal Report and populating it with XML data. 485 Follow on 485 Summary 487 Chapter 10: On Programmatically Creating an SSRS Report 488 Introduction 488 The XMLTextWriter Class 490 Hands-on exercise 10.1: Generating a Report Definition Language file using Visual Studio 2008 491 Follow on 491 Create project and add reference 491 Copy and paste code 492 Build and execute 497 Notes on adding style 498 Summary 498 Appendix A: Queries and Datasets in SSRS 2008 500 Reporting Services data 500 Queries 500 SQL 501 MDX 502 Semantic queries 503 Query Designer 504 Datasets 504 Summary 504 Appendix B: Converting Reports between RDL and RDLC 506 Conversion of report files with extensions RDL and RDLC 506 Status of RDL to RDLC conversion 507 RDLC to RDL conversion 507 Hands-on exercise B.1: RDLC to RDL conversion 508 Follow on 508 Summary 510 Appendix C: Command line utilities 512 Command line utilities 512 rs.exe 513 Syntax for using rs.exe 513 Hands-on exercise C.1: Creating a data source and deploying to a folder on the server which uses the Pubsx database in SQL Express 514 Follow on 514 Summary 516 Appendix D: Notes and References 518 Looking beyond Visual Studio 2008 518 SQL Server bugs and remediation 518 Best practices 519 Report definition and security 519 Report authoring 519 Troubleshooting 520 ReportViewer control 520 Moving and migrating 521 Free Microsoft software 521 Source control 521 White papers 522 Forums 522 Blogs 522 Miscellaneous 522 Index 524

In Detail

Microsoft SQL Server Reporting Services 2008 is the third generation Microsoft reporting software that is more tightly integrated with SQL Server 2008 than ever before. It provides a complete server-based platform that is designed to support a wide variety of reporting needs, including managed enterprise reporting, special purpose reporting, embedded reporting, and web-based reporting.

Ideal for beginners who need to create reports as quickly as possible, this book will lead you through practical examples to teach you a full range of skills for reporting with SQL Server 2008 Reporting Services. A series of carefully picked exercises help you practice the most important aspects of report creation. By the end of the book, you will have the confidence to take on the most complex of reports, and be producing reports that will make you the envy of your organization.

Learn to create and deliver attractive, data-rich reports with SQL Server 2008 Reporting Services

Approach

With clear instructions and plenty of screenshots, this book provides all the support and guidance you will need as you begin to generate reports with SQL Server 2008 Reporting Services.

Who this book is for

This book is for anyone who is new to SQL Server 2008 Reporting Services and needs to create and deploy reports. The book is suitable for report developers, data analysts, and report server database administrators.

You will need to know the basic concepts of SQL Server, but not necessarily be very familiar with SQL Server 2008.

A step-by-step guide to getting the most of Microsoft SQL Server Reporting Services 2008 In Detail Microsoft SQL Server Reporting Services 2008 is the third generation Microsoft reporting software that is more tightly integrated with SQL Server 2008 than ever before. It provides a complete server-based platform that is designed to support a wide variety of reporting needs, including managed enterprise reporting, special purpose reporting, embedded reporting, and web-based reporting. Ideal for beginners who need to create reports as quickly as possible, this book will lead you through practical examples to teach you a full range of skills for reporting with SQL Server 2008 Reporting Services. A series of carefully picked exercises help you practice the most important aspects of report creation. By the end of the book, you will have the confidence to take on the most complex of reports, and be producing reports that will make you the envy of your organization. What you will learn from this book? Approach With clear instructions and plenty of screenshots, this book provides all the support and guidance you will need as you begin to generate reports with SQL Server 2008 Reporting Services. Who this book is written for? This book is for anyone who is new to SQL Server 2008 Reporting Services and needs to create and deploy reports. The book is suitable for report developers, data analysts, and report server database administrators. You will need to know the basic concepts of SQL Server, but not necessarily be very familiar with SQL Server 2008.