O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Learning SQL Server Reporting Services 2012

Learning SQL Server Reporting Services 2012

by Jayaram Krishnaswamy
Publisher: Packt Publishing
Release Date: June 2013
ISBN: 9781849689922
View table of contents
Start reading

Book Description

For a newcomer to SQL Reporting Services 2012, things can seem a bit daunting, but this book will ease your path to becoming an expert. Clear instructions and screenshots will have you generating reports in no time.

  • Build applications using the latest Microsoft technologies: SSIS 2012, SSDT, WPF, and SharePoint 2010
  • Reach out to the cloud and master Windows Azure Reporting Services
  • Learn the ins and outs of SQL Server Reporting Services 2012 for Native and SharePoint Integrated modes
  • Step-by-step learning, guided by a large number of screenshots in every chapter makes it a simple

In Detail

In the present day information-centric world we live in, the success of an enterprise depends enormously on how its information is generated, handled, and disseminated. With ample screenshots and over 65 hands-on exercises, this book will guide you effortlessly through creating cutting edge reports using SQL Server Reporting Services 2012, native and fully integrated with SharePoint Server 2010 including new features like Power View and Data Alerts.

"Learning SQL Server Reporting Services 2012" will get you started right from installation and then move on to configuration of SQL Server Reporting Services 2012. Start learning right from Chapter 1 and build your skill to create reports using the latest tools and deploy them to the latest servers, both Native and SharePoint. No guessing; you will learn all—Power View, Data Alerts, SQL Azure Reporting,the latest Reporting Services Gadgets, and more.

"Learning SQL Server Reporting Services 2012" is a definitive guide to a step-by-step, metered approach to get a jump start on the latest features in SQL Server Reporting Services.Reporting Services in Microsoft SQL Server 2012 is more tightly integrated not only with the Microsoft Business Stack but also with SharePoint, Windows Azure, and the Visual Studio suite of products, with many new features.

You will install and configure the toolset to work with SSRS 2012 integrated with SharePoint 2010. Using SQL Server Data Tools, you will author reports and deploy. You will learn about security to work with Report Manager and Central Administration in SharePoint. Using Report Builder, you will learn about authoring cutting edge reports. You will create Tabular Models and begin experiencing the thrill of creating Power View Reports.

Table of Contents

  1. Learning SQL Server Reporting Services 2012
    1. Table of Contents
    2. Learning SQL Server Reporting Services 2012
    3. Credits
    4. About the Author
    5. About the Reviewers
    6. www.PacktPub.com
      1. Support files, eBooks, discount offers and more
        1. Why Subscribe?
        2. Free Access for Packt account holders
        3. Instant Updates on New Packt Books
    7. Preface
      1. What this book covers
      2. What you need for this book
      3. Who this book is for
      4. Conventions
      5. Reader feedback
      6. Customer support
      7. Downloading the example code
        1. Errata
        2. Piracy
        3. Questions
    8. 1. Overview and Installation – SQL Server Reporting Services 2012
      1. SQL Server 2012 – mission statement by Microsoft
      2. Overview of Reporting Services 2012
      3. Installing software used in the book
        1. SQL Server 2012 installation requirements
          1. Operating system requirements (64-bit)
          2. Hardware requirements
          3. Software requirements
        2. Configuring the hardware used for the book
        3. Hands-on exercise 1.1 – installing SQL Server 2012
          1. Downloading the source file
          2. Installation steps
        4. Hands-on exercise 1.2 – verifying the installation
          1. Installation choices and notes
        5. Configuring SQL Server Reporting Services
        6. Hands-on exercise 1.3 – configuring SQL Server 2012 Reporting Services in Native mode
          1. Report Server – configuration options
        7. Installing sample databases
          1. Downloading files and running scripts
        8. SharePoint Server 2010 Enterprise Edition
          1. Installing SharePoint Server 2010 Enterprise Edition on Windows 7 (64-bit)
        9. Installing Reporting Services 2012 in SharePoint Integrated mode
          1. Reporting Services add-in for SharePoint 2010
          2. Installing and starting the Reporting Services SharePoint Service
        10. Hands-on exercise 1.4 – installing the Reporting Services SharePoint Service
          1. Starting the service
          2. Creating the Reporting Services Service application
          3. Activating the Power View site collection feature
      4. Summary
    9. 2. SQL Server Reporting Services 2012 Projects with Visual Studio 2012
      1. What is business intelligence?
      2. Introduction to SQL Server Data Tools
        1. BI Projects on SSDT
        2. Report Server Project
        3. Report Server Project Wizard
      3. Creating reports using SSDT
        1. Hands-on exercise 2.1 – creating a report using the Report Server Project Wizard
        2. Hands-on exercise 2.2 – deploying the report to the Report Server
        3. Hands-on exercise 2.3 – creating a report using the Report wizard in SSDT
        4. Hands-on exercise 2.4 – modifying the report in Project RSPW2012 using an expression
          1. Report formats supported in Report Server Reports
      4. Report-related projects in Visual Studio 2012
      5. Creating reports using Visual Studio 2012
        1. Software and hardware requirements
        2. Hands-on exercise 2.5 – creating a report for a Windows Form application using Report Viewer Control
        3. Hands-on exercise 2.6 – creating a report using Report Viewer Control for the Web
        4. Hands-on exercise 2.7 – using Report Viewer Control in the remote mode
        5. Hands-on exercise 2.8 – converting an RDLC to a RDL file
      6. Microsoft Report Viewer Runtime
      7. Summary
    10. 3. Overview of SQL Server Reporting Services 2012 Architecture, Features, and Tools
      1. Structural design of SQL servers and SharePoint environment
        1. Native mode
        2. SharePoint Integrated mode
      2. Reporting Services configuration
        1. Native mode
        2. SharePoint Integrated mode
        3. Hands-on exercise 3.1 – modifying the configuration file in Native mode
          1. Turn on/off the Report Server Web Service
          2. Turn on/off the scheduled events and delivery
          3. Turn on/off the Report Manager
        4. Hands-on exercise 3.2 – turn the Reporting Service on/off in SSMS
      3. Salient features of Reporting Services 2012
        1. Report definition
          1. XML-based report definition (the .rdl file)
          2. Power View report contents
        2. Creating reports
          1. Report data
          2. New toolbox items in Report Builder 3
        3. Report parts and their reusability
        4. Customizing reports
        5. Saving and deploying reports
        6. Report validation
        7. Viewing reports
        8. Managing reports
          1. Report scheduling
          2. Subscriptions and delivery
        9. Features new in RS2012 SharePoint Integrated
          1. Power View
          2. Data Alerts
        10. Implementing security – authentication and authorization
          1. Authentication
            1. Authentication types
          2. Authorization
        11. URL access
        12. Reporting Services extensions
        13. Reporting Services tools
        14. Tools for Report Server administration
        15. Tools for report authoring
        16. Tools for report content management
      4. Summary
    11. 4. Working with Report Manager
      1. Tasks performed using Report Manager
      2. Starting Report Manager
      3. Starting Report Manager for the URL
      4. User access to Report Server (Report Manager)
        1. Considerations for giving user access to the Report Server
      5. First step – deploying reports
      6. Report Manager user interface
        1. Customizing Report Manager
        2. Hands-on exercise 4.1 – creating, modifying, moving, and deleting folders
          1. Navigating through the folders
          2. Creating a folder and a subfolder
          3. Moving an item into a folder
        3. Configuring permissions from Report Manager
        4. Configuring role-based security
        5. Hands-on exercise 4.2 – assigning a Windows user to the System Administrator role
          1. Creating a Windows user
          2. Assigning RSMax to the RS System Administrator role
        6. Assigning users to item-level roles
        7. Review users on a Reporting Services database
        8. Hands-on exercise 4.3 – assigning a user to a Custom role
        9. Hands-on exercise 4.4 – creating a permission to a specific report
      7. Report data sources
        1. An embedded data source
        2. A shared data source
        3. Hands-on exercise 4.5 – creating a shared data source on Report Manager
        4. Hands-on exercise 4.6 – creating a data model from a data source
      8. Viewing, searching, and printing reports
        1. Hand-on exercise 4.7 – view, print, and search on Report Manager
          1. Viewing reports
          2. Printing reports
            1. Change the report format
            2. Data feed format
          3. Search
            1. Searching the Report Manager site
            2. Search within a report
      9. Uploading and downloading files from the Report Server to the filesystem
        1. Uploading a report on the computer to the Report Server
        2. Hands-on exercise 4.8 – uploading a report to the Report Server
        3. Hands-on exercise 4.9 – downloading and reviewing a report definition file from the Report Server
      10. Report subscription and delivery
        1. Hands-on exercise 4.10 – creating an event-driven report subscription for delivery by an e-mail
        2. Hands-on exercise 4.11 – creating an event-driven report subscription for delivery to a file share
        3. Hands-on exercise 4.12 – creating data-driven report subscription for delivery to a file share
          1. Create a Subscribers database in SQL Server
            1. Populate a table in the Subscribers database
          2. Creating the data-driven Subscription and testing it
      11. Report caching
        1. Report processing options
        2. Cache refresh options
        3. Hands-on exercise 4.13 – creating a cache refresh plan by preloading the cache
      12. Snapshot and snapshot history
        1. Hands-on exercise 4.14 – creating a snapshot and snapshot history
      13. Summary
    12. 5. Working with Report Builder 3.0
      1. Report authoring with Report Builder
      2. Downloading and installing Report Builder
      3. Report Builder 3.0 user interface
      4. Report authoring
        1. Hands-on exercise 5.1 – creating a report with an embedded data source
        2. Hands-on exercise 5.2 – creating a report from a shared data source
          1. Creating a List report
        3. Parameterized reports
        4. Subreports
        5. Hands-on exercise 5.3 – creating a report that has a subreport
          1. Creating the subreport
            1. Making background-color of alternate rows different
          2. Creating the main report
            1. Adding a freeform report (List)
          3. Embedding the subreport in the main report
        6. Report with groups
        7. Hands-on exercise 5.4 – setting up a group and creating a document map
          1. Bring up the report from the previous hands-on exercise and remove a parameter
          2. Adding a group to the data
          3. Adding a Document Map to the report
          4. Configuring page breaks
          5. Adding interactive sorting
        8. Drill-through and drill-down reports
        9. Drill-through reports
        10. Hands-on exercise 5.5 – Creating a drill-through report
          1. Source report
          2. Destination report
          3. Set up the drill-through action
        11. Drill-down report
        12. Hands-on exercise 5.6 – Creating a drill-down report
      5. Linked reports
        1. Hands-on exercise 5.7 – creating linked reports
          1. Customizing the linked report in the France folder
      6. Creating a report with XML data sources
        1. Creating well-formed XML data
        2. Displaying data with sparklines, maps, data bars, and indicators
        3. Hands-on exercise 5.8 – creating a report and highlighting data with data bars
        4. Displaying data with sparklines
        5. Hands-on exercise 5.9 – creating reports with sparklines
          1. Creating a table
          2. Creating a report and inserting sparklines
        6. Indicators
        7. Hands-on exercise 5.10 – creating reports using indicators
        8. Reports with maps
        9. Hands-on exercise 5.11 – creating reports with embedded maps
        10. Report parts
        11. Hands-on exercise 5.12 – creating report parts and reusing an item
          1. Saving a report as report parts
          2. Reusing the report parts items
      7. Summary
    13. 6. Power View and Reporting Services
      1. What is Power View?
        1. Helpful resources
      2. What do you need to author a Power View report?
      3. Creating a tabular model
        1. Hands-on exercise 6.1 – creating a tabular model
          1. Brief review of an SSDT ribbon
          2. Creating a connection to the Northwind database
          3. Getting tables from the database
          4. Default field set and table behavior
          5. Adding measures
        2. Deploying the model
        3. Hands-on exercise 6.2 – deploying the model
        4. Tabular model permissions
        5. Hands-on exercise 6.3 – creating a role in SQL Server Data Tools
      4. Creating a Power View
        1. Hands-on exercise 6.4 – connecting to the model from SharePoint Server 2010
        2. Connecting to the model
        3. Creating a data source using the model as the source
        4. Creating a Power View report using the data source
        5. Hands-on exercise 6.5 – exploring a Power View report
          1. Creating the first view of the Power View report
          2. Creating a chart showing sales orders from NW Employees shipped from cities
          3. Adding a second view to the Power View report
          4. Highlighting of data
          5. Displaying data as a card
          6. Using tiles
          7. Slicing the data
          8. Advanced filtering
          9. Scatter and bubble charts
          10. Animation with Power View
          11. Navigating through the views of the Power View report
          12. Save, do, undo, and refresh
          13. Saving to PowerPoint
      5. Summary
    14. 7. Self-service Data Alerts in SSRS 2012
      1. Getting ready for Data Alerts
        1. Granting permissions to work with Data Alerts
      2. Creating a report and saving to the Report Server
      3. Creating a report in Report Builder and saving it to the Documents library
        1. Hands-on exercise 7.1 – creating a report in Report Builder and saving it to the SharePoint site
      4. Giving permission to a report
        1. Hands-on exercise 7.2 – giving full control of a report to a user
      5. Creating a Data Alert
        1. Hands-on exercise 7.3 – creating Data Alert in Data Alert Designer
        2. Editing Data Alerts
      6. Troubleshooting
        1. Using PowerShell to review the logfile
        2. Alert logs and alerting database
      7. Summary
    15. 8. Reporting Services and Programming
      1. Overview of programming interfaces and utilities
      2. URL access
        1. Hands-on exercise 8.1 – URL access, Native mode Report Server
          1. Accessing the Report Server
          2. Listing contents of a folder
          3. Accessing a component in report parts
          4. Rendering a report
          5. Accessing the contents of a data source
          6. Exporting to supported formats
          7. Rendering a report with the report parameter
        2. Hands-on exercise 8.2 – URL access and SharePoint Integrated mode Report Server
          1. Accessing the Report Server in SP-integrated implementation
          2. Accessing a report on the Report Server in SP-integrated implementation
      3. ReportViewer control
        1. Hands-on exercise 8.3 – using URL access and ReportViewer controls with Web applications
      4. Report Server Web Services API
        1. Hands-on exercise 8.4 – rendering a report on the Native mode Report Server into different formats
        2. Reporting Services in SharePoint Integrated mode
        3. Hands-on exercise 8.5 – accessing SharePoint management endpoints
      5. PowerShell
        1. Hands-on exercise 8.6 – a quick review of basics
        2. PowerShell and reporting services with SharePoint Integration
        3. Hands-on exercise 8.7 – exploring reporting services in SharePoint Integrated mode
          1. Getting help about help
          2. Application server of the Reporting Services Service Application
          3. Finding all cmdlets related to Reporting Services SharePoint Integration
          4. Proxy URL of the Report Server
          5. SP service application pool
        4. PowerShell and Native mode Reporting Services 2010
        5. Hands-on exercise 8.8 – exploring Native mode Reporting Services
          1. The Native mode Report Server
          2. Native mode Report Server configuration
          3. Extensions supported on a Windows Forms ReportViewer
        6. Windows Management Instrumentation
        7. Hands-on exercise 8.9 – exploring the Native mode Report Server programmatically
          1. Providing access permission to WMI
        8. Report Server properties using WMI
      6. Reporting Services command prompt utilities
        1. The RSS utility
          1. What can rs.exe do?
        2. Hands-on exercise 8.10 – creating a data source on the Report Server using rs.exe and a script file
          1. The Rskeymgmt utility
          2. Rsconfig
      7. Incorporating custom code into reports
        1. Hands-on 8.11 – inserting custom code into a report
      8. Summary
    16. 9. Windows Azure SQL Reporting
      1. What is Windows Azure SQL Reporting?
        1. Hands-on exercise 9.1 – accessing the Windows Azure portal
      2. Creating content for reports and viewing them
        1. Preparing to author reports
        2. Hands-on exercise 9.2 – creating a report using SSDT and deploying it to the Windows Azure Reporting on the Cloud
          1. Creating a SQL database on Windows Azure
          2. Creating a table for the report
          3. Populating the table in SSMS
          4. Creating a SQL Reporting Service in Windows Azure
          5. Creating a report based on the Skyblue database on the Windows Azure Platform
          6. Deploying the report to the SQL Reporting Services on Windows Azure
          7. Viewing the report on the SQL Reporting Web server
        3. Hands-on exercise 9.3 – using the Windows Azure SQL Reporting Services to create folders, share data sources, and upload reports
          1. Creating a shared data source in the portal
        4. Managing your reports and users is easy
        5. Managing users
        6. Managing reports
        7. Managing the report Dashboard
        8. Status of activities
        9. Managing permissions
        10. Report viewing
        11. Using Report Builder to view reports
          1. Making changes to the report and placing it on the Report Server
        12. URL access to reports on the Report Server
          1. Accessing the server and running a report
          2. Do gadgets like indicators and data bars work?
          3. SQL Server 2012 Reporting Services – Known Issues
        13. Hands-on exercise 9.4 – migrating a table on an on-premise SQL Server 2012 to the Windows Azure SQL database
      3. Summary
    17. 10. Applications Accessing Report Servers
      1. Hands-on exercise 10.1 – accessing the Native mode Report Server using SSIS
        1. Creating the Report Server Web Service WSDL file
        2. Providing the WSDL file to the Web Service Task
        3. Adding a File System Task to the Control Flow page
      2. Accessing reports from a Windows Presentation Foundations classes project
        1. Hands-on exercise 10.2 – accessing Report Server URLs in a WPF project
      3. Accessing Native Report Server reports from SharePoint Web parts
        1. Hands-on exercise 10.3 – viewing reports on the Native mode Report Server using SharePoint Web parts
      4. Accessing the Web parts
      5. Summary
    18. Index