Foreword xiii Introduction 1 PART I: INSTALLING AND CONFIGURING SSRS FOR CONFIGURATION MANAGER Chapter 1 Installing SQL Server Reporting Services 7 Installing the SQL SSRS Component 8 Configuring SSRS 16 Configuring the SSRS Installation 16 Configuring Email 24 Enabling Remote Errors 26 Installing Client Tools for SSRS Reporting 31 Installing Client Tools 31 Installing SQL Server Data Tools Business Intelligence 38 Summary 41 Chapter 2 Installing and Configuring Configuration Manager Reporting 43 Creating the Reporting Services Point 44 Using ConfigMgr Security Reporting Roles 52 Creating a Security Reporting Role 52 Restricting Access to Reports 57 Removing a Security Role 58 Exporting and Importing Security Roles 58 Working with Security Roles 62 Applying a Security Role to a Security Group 62 Removing Permissions 70 Accessing ConfigMgr Reports 71 Changing the ConfigMgr Report Logo 72 Demonstrating Creating and Assigning ConfigMgr Security Roles 75 Working with the Report Reader Security Role 75 Working with the Software Updates Report Reader Security Role 76 Working with the Inventory Report Reader Security Role 76 Summary 77 PART II: ABOUT DATA AND RETRIEVAL Chapter 3 Understanding Configuration Manager Data 81 Using Data Classes and SQL Views 81 Using Discovery Classes 82 Using Hardware Inventory Classes 84 Using Software Inventory Classes 86 Using Software Update Inventory Classes 87 Using Software Metering Inventory Classes 89 Using Status Message Classes 90 Using State Messages 91 Using Collection Data Classes 92 Using Tools for Creating Reports 93 Introducing SQL Server Management Studio 94 Connecting to the ConfigMgr Database Server 94 Executing a Query in SQL Server Management Studio 95 Using the Object Explorer 95 Demonstrating SQL Server Management Studio 97 Creating and Executing a Query 98 Looking at Views 98 Summary 98 Chapter 4 Transact-SQL Primer 99 Understanding the Basic SQL Sections in a Query 100 Using the SELECT Statement 101 Using the FROM Statement 101 Defining Criteria with the WHERE Statement 102 Using ORDER BY 102 Using Secondary Operators 103 Using the DISTINCT Keyword 103 The GROUP BY Operator 104 Using SQL View Aliases 104 Using Operators 105 Column Naming in Query Results 106 Understanding Aggregate Functions 107 Using the COUNT Function 108 Using the MIN (Minimum) Function 108 Using the AVG (Average) Function 108 Using the MAX (Maximum) Function 108 Using the SUM Function 108 Understanding Date and Time Functions 109 Understanding Date Parts 109 Using Common Date and Time Functions 109 Transforming Data Stored in the ConfigMgr Database 111 Using the CASE Function 111 Converting Data with the CONVERT and CAST Functions 112 Using the ISNULL Function 114 Understanding the SQL JOIN Statement 115 What Is a JOIN? 116 Using the ResourceID Column 116 Using the v_R_System_Valid View 117 Explaining the JOIN Statements 118 Demonstrating SQL Operators 125 Running the Sample SQL File 125 Editing the Sample Query 126 Creating a Query with Aliases 127 Demonstrating Aggregate Functions 127 Creating a New Query 127 Using the COUNT Function 128 Demonstrating Date and Time Functions 128 Demonstrating Data Transforms 129 Applying the CASE Function 129 Applying the CAST and CONVERT Functions 130 Applying the ISNULL Function 130 Demonstrating JOIN Statements 130 Executing an Inefficient Query 131 Writing an Efficient Query 131 Working with the Different JOIN Statements 131 Summary 131 PART III: USING SSRS FOR CONFIGURATION MANAGER REPORTING Chapter 5 Basic Report Design 135 Understanding the Purpose of a Report Series 136 Using a Summarized Data Report 136 Using a List Report 138 Using a Detailed PC Report 139 Understanding Report Design Considerations 140 Providing a Consistent Look and Feel 140 Printed Versus Interactive Reports 142 The Benefits of Using Report Templates 142 The Ability to Export Reports 144 Using Custom Report Requests 146 Gathering Report Information 146 Questioning Report Requirements 156 Report Verification and Delivery 157 Report Life Cycle and Maintenance 159 Summary 161 Chapter 6 Building a Basic Report 163 Creating a Project 165 Creating a Basic Report 168 Creating a Data Source 170 Creating a Dataset 171 Setting the Size of a Report 173 Accessing Report Items 175 Adding a Table to a Report 177 Previewing a Report 180 Customizing a Report 181 Adding Background Color to Rows 181 Alternating Data Row Colors 183 Adding Interactive Sorting 184 Setting the Table Header Row to Repeat on All Pages 187 Adding a Page Header to a Report 188 Adding a Page Footer to a Report 191 Adding an Image to a Report 193 Demonstrating Basic Report Creation 195 Creating a New Project 195 Creating a Patch Compliance Progression Report 196 Creating a Computer Hardware Information Report 196 Summary 197 Chapter 7 Intermediate Reporting Concepts 199 Creating Report Templates 200 Creating a Report Template (8.5x11in) 200 Creating a New Report from a Template 204 Exporting Reports from SSDT-BI 206 Modifying the Page Size of an Existing Report 207 Using Report Parameters 209 Understanding the Value and Label Fields 209 Using Default Values 209 Understanding Cascade Prompts 210 Using Multi-Value Parameters 210 Adding a Simple Prompt 210 Adding a Dropdown Prompt 213 Adding a Multi-Value Parameter 217 Adding a Chart Item to a Report 220 Using Column Charts 220 Using Bar Charts 220 Using Line Charts 220 Using Pie Charts 221 Using Doughnut Charts 221 Using Other Chart Types 221 Adding a Chart to a Report 221 Customizing Chart Items 225 Adding an Action to a Chart 233 Demonstrating Template Creation 236 Creating Template Reports 236 Modifying Page Sizes for Existing Reports 237 Demonstrating Report Parameters 237 Updating the Patch Compliance Progression Report 237 Updating the Computer Hardware Information Report 238 Demonstrating Adding Chart Items 238 Adding a Chart to the Patch Compliance Progression Report 238 Adding a Chart to the Computer Hardware Information Report 239 Summary 240 Chapter 8 SSRS Reporting Features 241 Using Report Drillthroughs 242 Adding an Action on a Table Cell's Value 242 Adding Hyperlinks to a Report Item 246 Adding a Subreport Item 247 Creating Custom Color Palettes 258 Available Out-of-the-Box Color Palettes 259 Setting Custom Color Codes 259 Setting Custom Color Codes by Values 263 Adding Reports to SSRS 268 Manually Adding a Report to SSRS 268 Publishing Reports from SSDT-BI to the SSRS Website 273 Creating Report Subscriptions 277 Demonstrating Report Drillthroughs 282 Patch Compliance Progression Drillthrough Series 282 Computer Hardware Information Drillthrough Series 283 Demonstrating Custom Color Palettes 284 Applying a Custom Color Palette to Cycle Through Color Codes 284 Applying a Custom Color Palette Based on Values 284 Demonstrating Adding Reports to SSRS 285 Manually Adding Reports to the SSRS Website 285 Publishing Reports to SSRS 285 Creating a Subscription 286 Summary 286 Chapter 9 Role-Based Administration and Reporting 287 How Role-Based Administration and Reporting Work 288 Using SQL Queries with RBA 289 Converting a SQL Query to a ConfigMgr RBA SQL Query 289 Testing a ConfigMgr RBA SQL Query 292 Using Reports and RBA 295 Updating SSDT-BI for RBA 295 Adding the DLL to a Report 296 Adding an RBA Dataset 298 Adding RBA Prompts to a Report 300 RBA Tips and Troubleshooting 305 RBA SQL Query Performance 305 Troubleshooting and Errors 309 Demonstrating Creating an RBA Report 315 Converting the Patch Compliance Progression Query to an RBA Query and Testing It 315 Converting the Patch Compliance Progression Report to an RBA Report 315 Converting the Computer Hardware Information Query to an RBA Query and Testing It 316 Adding a Chart to the Computer Hardware Information Report 316 Summary 316 PART IV: APPENDIXES Appendix A Glossary 319 Glossary of Terms 319 Appendix B Demonstration Outcomes 323 Chapter 2 Demonstration Outcomes 323 Working with the Report Reader Security Role 323 Working with the Software Updates Report Reader Security Role 324 Working with the Inventory Report Reader Security Role 325 Chapter 3 Demonstration Outcomes 326 Creating and Executing a Query 326 Looking at Views 327 Chapter 4 Demonstration Outcomes 328 Demonstrating SQL Operators 328 Working with Aggregate Functions 330 Working with Date and Time Functions 332 Working with Data Transforms 333 Executing an Inefficient Query 336 Writing an Efficient Query 336 Chapter 6 Demonstration Outcomes 338 Creating a New Project 338 Creating a Patch Compliance Progression Report 338 Creating a Computer Hardware Information Report 338 Chapter 7 Demonstration Outcomes 340 Creating Template Reports 340 Modifying Page Sizes for Existing Reports 340 Updating the Patch Compliance Progression Report 341 Updating the Computer Hardware Information Report 341 Adding a Chart to the Patch Compliance Progression Report 341 Adding a Chart to the Computer Hardware Information Report 343 Chapter 8 Demonstration Outcomes 343 Adding a Patch Compliance Progression Drillthrough Series 343 Adding a Computer Hardware Information Drillthrough Series 344 Applying a Custom Color Palette to Cycle Through Color Codes 345 Applying a Custom Color Palette Based on Values 345 Manually Adding Reports to the SSRS Website 345 Publishing Reports to SSRS 347 Creating a Subscription 348 Chapter 9 Demonstration Outcomes 348 Appendix C Available Online 351 Creating and Assigning ConfigMgr Security Roles 351 Understanding Configuration Manager Data 352 Transact-SQL Primer 353 Basic Report Design 355 Building a Basic Report 355 Intermediate Reporting Concepts 355 SSRS Reporting Features 357 Role-Based Administration and Reporting 358 Index 361
Garth Jones, System Center Enterprise Client Manager MVP, is chief architect at Enhansoft, an Ottawa-based company that develops products and services to extend the value of System Center Configuration Manager. Garth started working with the product in 1996, when it was known as SMS. He is the founder of the Ottawa Windows Server User Group and its associated study group. In addition to being an active participant in Microsoft's forums, Garth also participates on OWSUG.ca, SMSug.ca, FAQshop.com, and myITForum.com. He has presented at OWSUG, MMS, EnergizeIT, ITProTeach, and Techdays. Garth also creates webcasts for SMSUG.ca and myITForum.com. Dan Toll, a System Center Configuration Manager administrator, has worked with different versions of the product starting with SMS 2003. Dan specializes in OS deployment for workstations and servers using Microsoft Deployment Toolkit (MDT) with ConfigMgr, as well as Configuration Manager reporting. In his current job, Dan is the SME for Configuration Manager as well as the client computing environment, which includes deployment, functionality, and maintenance of systems; their operating systems; applications; patching compliancy; and reporting. Kerrie Meyler, System Center Cloud and Datacenter Management MVP, is the lead author of numerous System Center books in the Unleashed series, including System Center 2012 Configuration Manager Unleashed (2012), System Center 2012 R2 Configuration Manager Unleashed Supplement (2014), System Center 2012 Service Manager Unleashed (2014), System Center 2012 Operations Manager Unleashed (2013), and System Center 2012 Orchestrator Unleashed (2013). She is an independent consultant with more than 17 years of information technology experience. Kerrie was responsible for evangelizing SMS while a senior technology specialist at Microsoft and has presented on System Center technologies at TechEd and MMS.