Microsoft System Center Configuration Manager’s SQL Server database contains valuable information about your users, computers, hardware, operating systems, applications, compliance status, and much more. Microsoft has provided excellent tools for extracting this information in meaningful ways, including SQL Server Reporting Services (SSRS) and SQL Server Data Tools Business Intelligence (SSDT-BI). System Center Configuration Manager Reporting Unleashed shows you how to make the most of these tools. World-renowned System Center reporting guru Garth Jones and his expert coauthors guide you through all facets of custom reporting with System Center. You’ll walk through installing and configuring SSRS, using SQL views to find the data you need, writing SQL queries, creating basic and advanced reports, and using role-based administration to securely deliver those reports to the correct individuals. Jones brings together reliable, comprehensive, and up-to-date System Center reporting techniques you’ll find in no other book or website. Using this guide, you can consistently retrieve the right information to solve immediate problems and quickly respond to management concerns. Detailed information on how to… • Install and configure SQL SSRS for optimal System Center reporting and easier troubleshooting • Understand the data stored in the ConfigMgr site database • Efficiently retrieve ConfigMgr data by writing SQL queries in SQL Server Management Studio • Learn best practices for developing and designing System Center reports • Create report templates, customize content with report parameters, and embed charts • Customize logos, color palettes, and other report elements for your own organization • Construct advanced drillthroughs to provide deeper understanding • Strengthen report security by integrating ConfigMgr role-based administration into SQL queries • Leverage reporting to measure KPIs and gain a better understanding of your environment • Tailor your reports to the needs of end-users or management • Foreword by Wally Mead, Principal Program Manager, Cireson The only book entirely dedicated to Configuration Manager reporting, this guide complements Meyler's System Center 2012 Configuration Manager Unleashed, offering far more in-depth coverage of reporting than the single chapter in that book. Most of the content in this new guide will be equally valuable in both System Center 2016 and 2012 environments.

Table of Contents:
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.5×11in)   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