Building Performance Dashboards and Balanced Scorecards with SQL Server Reporting Services

Christopher Price is a Senior Business Intelligence Consultant with Pragmatic Works. He specializes in data integration, ETL, and data warehouse architecture and design. Adam Jorgensen is President of Pragmatic Works, Director at Large for the Professional Association of SQL Server, and a Microsoft SQL Server MVP. Devin Knight is a Senior BI consultant with Pragmatic Works, a coauthor of six Knight's 24-Hour Trainer books for Wrox, and a conference speaker.

Introduction xix Part I Foundations of Dashboards and Balanced Scorecards 1 Chapter 1 Introduction to Balanced Scorecards 3 Origins of Strategic Management Systems 3 Tools of Today s Management 4 Balanced Scorecards 4 Strategy Maps 5 Dashboards 7 Benefits of Today s Scorecards 7 Team Strategy 8 Information Technology Alignment 8 Data Strategy 8 Organizational Alignment 8 Strategy to Action Conversion 9 Choosing the Right Technology for the Task 9 Designing for Audience Usability 10 Executives 10 Sales and Marketing 10 Field and Global Workforce 11 Operations and Production 11 Organizing Your Balanced Scorecard 11 Defining Your Strategic Function 12 Why is the Strategy Map so Important? 17 How Do You Know It s Working? 19 Moving Forward with your Scorecard Initiative 19 Summary 19 Chapter 2 Planning Your Dashboard and Scorecard Initiative 21 Defining Scope and Gathering Requirements 22 Executive Involvement 22 Defining Measures 25 Selecting the Right Performance Metrics 28 Planning Development and Implementation 29 Managing Expectations and Scope 30 Summary 31 Part II Building and Extending Dashboards and Balanced Scorecards 33 Chapter 3 Building Your First Dashboard and Balanced Scorecard 35 Anatomy of a Reporting Services Project 36 Introduction to Data Sources, Datasets, and Controls 37 Design and Preview 41 Building Your First Dashboard 46 Scoping and Requirements for Your Dashboard 46 Considerations Before You Get Started 47 Identifying Metrics and Gathering Data 47 Working with Metrics and KPIs 48 Designing the Dashboard 49 Summary 50 Chapter 4 Enhancing Your Dashboard and Balanced Scorecard 51 Organizing and Achieving Visual Consistency 51 Placement and Layouts 52 Dashboard Headers and Footers 53 Reports, Subreports, and Paging 54 Formatting Dashboard Elements 55 Fonts and Text Boxes 55 Borders, Lines, Color, and Images 56 Text, Numbers, and Dates 58 Conditional Formatting 60 Expressions 60 Conditional Formatting Step-by-step 61 Custom and Data-driven Labels and Titles 62 Creating Custom Labels 63 Working with Dates and Times 64 Summary 66 Chapter 5 Interactive Dashboards and Balanced Scorecards 67 Common Interactive Features 67 Drillthrough 68 Expand / Collapse Groups 69 Interactive Sorting 71 Bookmarks 72 Parameterization 74 Introduction to Reporting Services Parameters 75 Data-driven Parameters 79 Working with Cascading Parameters 82 Step-by-step Drillthrough Report 83 Summary 88 Chapter 6 Visual Dashboards and Balanced Scorecards 89 General Guidelines for Data Visualizations 89 Know your Audience 90 Select the Right Medium 90 Kill Cute 90 Use Colors Carefully 91 Working with Charts and Graphs 92 Getting Started with Charts 93 Extending the Reporting Services Chart 102 Working with Sparklines and Data Bars 107 Sparklines 107 Data Bars 109 Working with Gauges and Bullet Graphs 110 Gauges 110 Bullet Graphs 112 Working with Indicators 115 Working with Maps 118 Summary 123 Chapter 7 Mobile Dashboards and Balanced Scorecards 125 Planning for the Right Audience 125 Browser or Native? 126 Choosing a Mobile Platform 127 The Pros and Cons of Reporting Services 127 The Pros and Cons of PerformancePoint 128 General Considerations for Mobile Dashboards 134 Performance 135 Security 136 Design Considerations 137 Summary 137 Chapter 8 Extending Dashboards and Balanced Scorecards with Custom Code 139 Choosing to Embed Custom Code 140 Direct Inputting Code 141 Writing a Function 142 Creating Assemblies for Reusability 146 Building a .NET Assembly 147 Deploying an Assembly 150 Using an Assembly in a Report 151 Summary 153 Chapter 9 Publishing Dashboards and Balanced Scorecards 155 Choosing a Deployment Location 155 Configuring and Publishing to a Native Report Server 156 Setting Up and Configuring Reporting Services 156 Planning for Growth and Scalability 158 Deploying to SQL Server Reporting Services 161 Managing Security 162 Configuring and Publishing to SharePoint 165 Configuring Reporting Services for SharePoint 166 Deploying to SharePoint 170 Data Alerts 170 Summary 172 Part III Dashboards and Balanced Scorecards in Action 173 Chapter 10 Dashboards 175 Case Study: Adventure Works 175 What to Measure and When 176 Developing the Dashboard 176 Summary 194 Chapter 11 Strategy and Process Maps 195 Strategy Maps 195 Perspectives 197 Strategic Themes 198 Objectives and Casual Links 200 Designing a Strategy Map 201 Process Maps 202 Process Flow Map 202 SIPOC Map 206 Value Stream Map 207 Case Study: Contoso Plastics 210 Background 211 The Situation 211 Process Improvement 212 Design Sessions 212 Anecdotal Results 213 Implementing Strategy and Process Maps 214 Working in Microsoft Visio 214 Alternative Implementation Options 217 Summary 218 Chapter 12 Balanced Scorecards Case Study 219 Designing a Balanced Scorecard for a Marketing Firm 220 Defining Objectives 220 Selecting Metrics 222 Choosing Appropriate Targets 223 Bringing the Balanced Scorecard Together 225 Wrangling the Data 226 Building the Balanced Scorecard 226 Summary 231 Chapter 13 Decision-making Charts 233 Box-and-whisker Charts 233 Box-and-whisker by Example 234 Building a Box-and-whisker Chart 235 Pareto Charts 240 80-20 Rule 240 Pareto Chart 240 Building a Pareto Chart 241 Variance Charts 244 Gantt Charts 248 Summary 251 Chapter 14 Forecasting Trends and Data Smoothing 253 Smoothing Data 253 Simple Moving Average 254 Weighted Moving Average 256 Exponential Weighted Moving Average 257 Forecasting with Moving Averages 259 Forecasting 259 Forecasting with Microsoft Excel 259 Forecasting with SQL Server Analysis Services 262 Case Study: Health Care Forecasting 273 Gathering and Preparing the Data 273 Building the Time Series Model 274 Making the Forecast 276 Summary 276 Index 277