- Using Data Classes and SQL Views
- Using Tools for Creating Reports
- Demonstrating SQL Server Management Studio
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
Using Tools for Creating Reports
This section of the chapter explores the SQL Server Management Studio tool. This is the first of three major tools you can use to create reports for ConfigMgr. Chapter 1, “Installing SQL Server Reporting Services,” detailed the steps for installing SQL Server Management Studio 2014.
Chapter 6, “Building a Basic Report,” explains the two remaining tools:
SQL Server Data Tools Business Intelligence (SSDT-BI) for Visual Studio 2013
Report Builder
These tools are used to design SSRS reports for ConfigMgr as well as other System Center products, such as Operation Manager and Service Manager.
Introducing SQL Server Management Studio
Using the SQL Server Management Studio tool is the recommended way to create the queries required to build and design ConfigMgr reports. SQL Server Management Studio allows you to access, configure, manage, administer, and develop SQL Server components. It combines the features of Enterprise Manager, Query Analyzer, and Analysis Manager with script editors to provide access to SQL Server to administrators and developers of all skill levels. SQL Server Management Studio allows you to easily find SQL views and their columns in the ConfigMgr database. Using this tool, you can also write and execute queries as well as confirm results before creating a custom ConfigMgr report. Chapter 4, “Transact-SQL Primer,” can help you become familiar with this core tool as well as how to write proper SQL queries.
Connecting to the ConfigMgr Database Server
Once the SQL Server Management Studio tool is installed on your workstation, launch it and follow these steps:
Connect to the database server used for ConfigMgr by entering the server name and clicking Connect (see Figure 3.10).
FIGURE 3.10 SQL Server Management Studio Connect to Server dialog.
Once connected, you see the main interface of SQL Server Management Studio, as shown in Figure 3.11.
FIGURE 3.11 SQL Server Management Studio Object Explorer.
)
)
Executing a Query in SQL Server Management Studio
To create and execute a query, open a new query by clicking New Query in the SQL Server Management Studio menu bar. After the query page opens, it is important to make sure you change the database source to the ConfigMgr database. To do this, select the database from the dropdown menu, as shown in Figure 3.12.
)
FIGURE 3.12 SQL Server Management Studio database dropdown menu.
Enter your query in the New Query page and click Execute. When you execute your query, the Results section appears at the bottom of your query page. Figure 3.13 shows an example of this section.
)
FIGURE 3.13 SQL Server Management Studio executed query with results.
Using the Object Explorer
The Object Explorer, displayed on the left side of SQL Server Management Studio, provides the ability to drill down into the ConfigMgr database and get a list of all ConfigMgr views as well as the available columns that can be used within your queries. Notice that the expanded dbo.v_R_System_Valid view in Figure 3.14 contains many columns.
)
FIGURE 3.14 SQL Server Management Studio Object Explorer, showing columns.
TIP: Invalid Object Name Error Message
When executing a query, if you receive an “Invalid object name” error message (see Figure 3.15), it usually means that you forgot to change the target database to the ConfigMgr database (as shown in Figure 3.12).
)
FIGURE 3.15 SQL Server Management Studio “invalid object name” error message.