Building Performance Dashboards and Balanced Scorecards with SQL Server Reporting Services
Buy Rights Online Buy Rights

Rights Contact Login For More Details

More About This Title Building Performance Dashboards and Balanced Scorecards with SQL Server Reporting Services


Discover how to maintain and update balanced scorecards and performance dashboards with SQL Server Reporting Services

Complementing the bestselling Balanced Scorecards and Operational Dashboards with Microsoft Excel (9780470386811), this indispensable book shows you how to create maintainable and dynamically updated scorecards and performance dashboards using Microsoft's premier reporting tool for the enterprise: SQL Server Reporting Services. The book begins with coverage of performance management methods and metrics and then moves on to introduce you to techniques for developing and delivering both a dashboard and balanced scorecard using Reporting Services. The authors assume very limited knowledge of Reporting Services, allowing you to gradually progress through more advanced topics and solutions.

Shows you how to build a simple dashboard and balanced scorecard utilizing helpful examplesDiscusses formatting controls and labels, paging, and conditional formattingIntroduces the skills necessary to make Reporting Services dashboards and scorecards interactiveCovers the visual aspect of dashboards and scorecards and shows how to design and implement dashboards and scorecards in Reporting Services for mobile devicesExplains how to apply the techniques covered earlier in the book for specific business applications

This book sets itself apart from other titles with its comprehensive and invaluable coverage of key strategies and techniques for effectively using SQL Reporting Services to create scorecards and dashboards.


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 andBalanced 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