You are previewing Business Intelligence with SQL Server Reporting Services.
O'Reilly logo
Business Intelligence with SQL Server Reporting Services

Book Description

Business Intelligence with SQL Server Reporting Services helps you deliver business intelligence with panache. Harness the power of the Reporting Services toolkit to combine charts, gauges, sparklines, indicators, and maps into compelling dashboards and scorecards. Create compelling visualizations that seize your audience’s attention and help business users identify and react swiftly to changing business conditions. Best of all, you'll do all these things by creating new value from software that is already installed and paid for – SQL Server and the included SQL Server Reporting Services.

Businesses run on numbers, and good business intelligence systems make the critical numbers immediately and conveniently accessible. Business users want access to key performance indicators in the office, at the beach, and while riding the subway home after a day's work. Business Intelligence with SQL Server Reporting Services helps you meet these need for anywhere/anytime access by including chapters specifically showing how to deliver on modern devices such as smart phones and tablets. You'll learn to deliver the same information, with similar look-and-feel, across the entire range of devices used in business today.

  • Key performance indicators give fast notification of business unit performance
  • Polished dashboards deliver essential metrics and strategic comparisons
  • Visually arresting output on multiple devices focuses attention
  • Table of Contents

    1. Cover
    2. Title
    3. Copyright
    4. Dedication
    5. Contents at a Glance
    6. Contents
    7. About the Author
    8. About the Technical Reviewers
    9. Acknowledgments
    10. Introduction
    11. Chapter 1: SQL Server Reporting Services as a Business Intelligence Platform
      1. Business Intelligence Concepts
        1. Key Performance Indicators
        2. Scorecards
        3. Dashboards
      2. SSRS for Business Intelligence, Practically
      3. Getting the Data Right
        1. Prepare the Data
        2. Use Views and Stored Procedures
        3. Some Ideas on Source Data Definition
      4. Real-World Data
      5. Designing SQL Server Reports for Business Intelligence
        1. Presentation
        2. Design
        3. Layout
        4. Interface and Interactivity
      6. Multi-Purposing
      7. The Sample Database
      8. Preparing Your Environment
        1. Creating an SSDT Project
        2. Adding a Shared Datasource
        3. Add Shared Datasets
        4. Configuring Parameters
        5. Code and Stored Procedures
      9. Reusability
      10. The CarSales_Reports Database
      11. Book Audience
      12. How Best to Use This Book
      13. Conclusion
    12. Chapter 2: KPIs and Scorecards
      1. What Are Key Performance Indicators?
        1. KPI Value
        2. KPI Goal
        3. KPI Status
        4. KPI Trends
      2. A Simple KPI
        1. The Source Data
        2. How the Code Works
        3. Building the KPI
        4. How It Works
      3. A More Complex KPI Using Sparklines
        1. The Source Data
        2. How the Code Works
        3. Building the KPI
        4. How It Works
      4. Gauge-Based KPIs
        1. The Source Data
        2. How the Code Works
        3. Building the Gauge-Based KPI
        4. How It Works
        5. Hints and Tips
      5. Text-Based KPIs
        1. A Simple Text-Based KPI
        2. The Source Data
        3. How the Code Works
        4. Building a Text-Based KPI
        5. How It Works
        6. A Complex Text-Based KPI
        7. The Source Data
        8. How the Code Works
        9. Building a Complex Text-Based KPI
        10. How It Works
      6. Conclusion
    13. Chapter 3: Gauges for Business Intelligence
      1. Gauge Elements
      2. Data for Gauges
        1. Gauge Development
      3. Classic Gauges
        1. The Source Data
        2. How the Code Works
        3. Building the Gauge
        4. How It Works
        5. Hints and Tips
      4. Using Multiple Pointers in Gauges
        1. The Source Data
        2. How the Code Works
        3. Creating the Gauge
        4. How It Works
      5. Varying the Pointer Types and Assembling Gauges
        1. The Source Data
        2. How the Code Works
        3. Building the Gauge
        4. How It Works
        5. Hints and Tips
      6. Gauges with Multiple Elements
        1. The Source Data
        2. Creating the Gauge
        3. How It Works
        4. Hints and Tips
      7. Less Traditional Gauges
        1. Thermometers
        2. The Source Data
        3. How the Code Works
        4. Building the Thermometer
        5. How It Works
        6. Hints and Tips
        7. Sub-Gauges
        8. The Source Data
        9. How the Code Works
        10. Creating Gauges with Sub-Gauges
        11. How It Works
        12. Hints and Tips
      8. Interesting Tricks with Gauges
        1. The Source Data
        2. How the Code Works
        3. Creating and Assembling the Gauges
        4. How It Works
        5. Hints and Tips
      9. Conclusion
    14. Chapter 4: Charts for Business Intelligence
      1. Some Chart Presentation Ideas
      2. Charts to Compare Metrics with Targets
        1. Basic Target Comparison Charts
        2. The Source Data
        3. How the Code Works
        4. Building the Chart
        5. An Advanced KPI Chart
        6. The Source Data
        7. How the Code Works
        8. Building the Chart
        9. How It Works
      3. Ordering Chart Elements
        1. The Source Data
        2. How the Code Works
        3. Building the Chart
        4. How It Works
      4. Superposed Bar Charts
        1. The Source Data
        2. Creating the Chart
        3. How It Works
        4. Hints and Tips
      5. Radar Charts
        1. The Source Data
        2. How the Code Works
        3. Building the Chart
        4. How It Works
      6. Hints and Tips
      7. Bubble Charts
        1. The Source Data
        2. How the Code Works
        3. Building the Chart
        4. How It Works
      8. Waterfall Charts
        1. The Source Data
        2. How the Code Works
        3. Creating a Waterfall Chart
        4. How It Works
        5. Hints and Tips
      9. Trellis Charts
        1. The Source Data
        2. How the Code Works
        3. Building a Trellis Chart
        4. How It Works
        5. Hints and Tips
      10. A Pyramid Chart
        1. The Source Data
        2. How the Code Works
        3. Building a Pyramid Chart
        4. How It Works
      11. A Few Ideas on Using Charts for Business Intelligence
        1. Keep Charts Simple
        2. Use Multiple Charts of the Same Type
        3. Minimize Titles
        4. Avoid Extraneous Elements
        5. Vary Chart Types
      12. Conclusion
    15. Chapter 5: Maps in Business Intelligence
      1. Geographical Data
        1. Loading Geographical Data into SQL Server from Shapefiles
      2. A Simple Map of US States with Sales
        1. The Source Data
        2. Building the Map
        3. How It Works
        4. Hints and Tips
      3. A Heat Map of European Sales by Country
        1. Simplifying the Geographical Data
        2. The Source Data
        3. How the Code Works
        4. Creating the Map
        5. How It Works
      4. A Bubble Chart Map of English Regions
        1. The Source Data
        2. How the Code Works
        3. Creating the Map
        4. How It Works
      5. Conclusion
    16. Chapter 6: Images in Business Intelligence
      1. Image Types
      2. Image Editing Software
        1. Image Size and Resolution
      3. Image Location
        1. Exporting Embedded Images from Reports
      4. Image Backgrounds for Text Boxes
        1. The Source Data
        2. How the Code Works
        3. Building the Visualization
        4. How It Works
      5. Report and Table Backgrounds
        1. The Source Data
        2. How the Code Works
        3. Building the Scorecard
        4. How It Works
        5. Using Background Repeat
      6. Rectangle Borders
        1. Using Chart Borders for Gauges
        2. How It Works
        3. Using a Custom Image
      7. Borders for Tables
        1. Fixed-Size Table Borders
        2. How It Works
        3. Dynamic Table Borders
        4. How It Works
      8. Gauge Backgrounds
        1. The Source Data
        2. How the Code Works
        3. Building the Visualization
        4. How It Works
      9. Chart Columns and Bars
        1. The Source Data
        2. Building the Chart
        3. How It Works
      10. Images as Titles
      11. Conclusion
    17. Chapter 7: Assembling Dashboards and Presentations
      1. A First Dashboard
        1. How It Works
        2. Hints and Tips
      2. Grouping Objects
        1. How It Works
        2. Hints and Tips
      3. A More Complex Dashboard
        1. Creating the Basic Dashboard
        2. Grouping in Tables
        3. Grouping Inside a Rectangle
        4. Adding Custom Elements
        5. Adding the Radar Chart
        6. Grouping Two Components
        7. Finishing the Dashboard
        8. Hints and Tips
      4. Creating a Legend
      5. Hints and Tips
      6. A Few Random Tips
      7. Conclusion
    18. Chapter 8: Interface Enhancements for Business Intelligence Delivery
      1. Report Refresh and Parameter Postback
      2. Interactive Parameter Selection
        1. The Source Data
        2. How the Code Works
        3. Building the Report
        4. How It Works
        5. Hints and Tips
      3. Creating a Year and Month Selector Widget
      4. Creating Interactive Menus to Select Filter Elements
        1. The Source Data
        2. Building the Report
        3. How It Works
      5. Using Slicers
        1. Single Slicers
        2. The Source Data
        3. How the Code Works
        4. Building the Report
        5. How It Works
      6. Multiple Slicers
        1. The Source Data
        2. How the Code Works
        3. Building the Report
        4. How It Works
      7. Conclusion
    19. Chapter 9: Interface Enhancements
      1. Highlighting for Data Selection
        1. The Source Data
        2. How the Code Works
        3. Building the Report
        4. How It Works
      2. Creating Tiles to Subset Data
        1. The Source Data
        2. How the Code Works
        3. Building the Report
        4. How It Works
      3. Adding a Carousel to Filter Data
        1. The Source Data
        2. How the Code Works
        3. Building the Report
        4. How It Works
      4. Adding Paged Recordsets
        1. The Source Data
        2. How the Code Works
        3. Building the Report
        4. How It Works
      5. Conclusion
    20. Chapter 10: BI for SSRS on Tablets and Smartphones
      1. Designing Mobile Reports
      2. Delivering Mobile Reports
      3. Tablet Reports
      4. Multi-Page Reports
        1. The Source Data
        2. Building the Report
        3. How It Works
      5. Creating Tabbed Reports
        1. The Source Data
        2. How the Code Works
        3. Building the Report
        4. How It Works
        5. Other Techniques for Tablet Reports
      6. Smartphone Reports
        1. Multiple Gauges
        2. The Source Data
        3. Building the Gauges
        4. How It Works
        5. Slider Gauges
        6. The Source Data
        7. How the Code Works
        8. Building the Report
        9. How It Works
        10. Text-Based Metrics
        11. The Source Data
        12. How the Code Works
        13. Building the Display
        14. How It Works
        15. Multiple Charts
        16. Smartphone and Tablet Report Hierarchy
        17. Access to a Report Hierarchy
        18. How It Works
        19. Structuring the Report Hierarchy
      7. Conclusion
    21. Chapter 11: Standardizing BI Report Suites
      1. Planning for Reuse
      2. Shared Datasets
      3. Adding Multiple Data Sources, Datasets, or Image Files
        1. Copying Images Between Reports
        2. Hints and Tips
        3. Copying Datasets Between Reports
        4. Copying Datasets Between Reports
      4. Images in BI Projects
        1. Loading and Importing Database Images
        2. Using Images from a SQL Server Table
        3. Adding a Dataset for Images to a Report
        4. Hints and Tips
      5. Centralized Style Information
        1. Applying Style Information from a Database Table
        2. Hints and Tips
      6. Conclusion
    22. Chapter 12: Optimizing SSRS for Business Intelligence
      1. Normalized Data Sources
        1. Reporting Tables
        2. Aggregated Reporting Tables
        3. Indexes
        4. Covering Indexes
        5. Filtered Indexes
        6. Indexed Views
      2. Partitioned Data Sources
        1. Vertical Partitioning
        2. Horizontal Partitioning
      3. Columnstore Indexes
      4. Dimensional Data Sources
      5. Dataset Caching
        1. Data Source Credentials
        2. Enabling Shared Datasets for Caching
      6. Report Caching
      7. Using Parameters andFilterswhen Caching Reports and Datasets
        1. The Source Data
        2. Building the Report
      8. Cache Schedules
        1. Creating a Shared Schedule
        2. Applying a Shared Schedule
      9. Pre-Caching Reports and Data Sets
        1. Cache Refreshing
        2. Subscriptions to Load the Cache
      10. In-Memory Data Warehouses
      11. Conclusion
    23. Appendix A: Sample Data
      1. Creating the Sample Data Directory Structure
      2. Downloading the Sample Data
      3. Sample Database
    24. Index