In Configuration Manager 2007, reporting is integrated into the Configuration Manager console, and Configuration Manager reports are secured objects that you can create and manage in the console. Like other Configuration Manager objects, you must have the appropriate credentials to create, modify, delete, view, or run reports. You have the option of using predefined reports, creating custom reports, or adding supplemental reports.
Reports are not propagated up or down the Configuration Manager hierarchy; they run only against the site database of the site on which they are created. However, because primary sites contain inventory data from child sites, when a report retrieves data from a primary site's database, it might retrieve data that was forwarded from a child site.
For more information about Configuration Manager 2007 reporting, see Reporting in Configuration Manager in the Configuration Manager 2007 Documentation Library (http://go.microsoft.com/fwlink/?LinkId=120047).
Many predefined reports are provided with Configuration Manager, including reports that provide information about hardware, software, software distribution, software updates, operating system deployment, mobile devices, status messages, and so forth.
Custom reports can be created either by copying and modifying predefined reports or by creating new reports. When modifying the properties of a predefined report, you can no longer use the original report as designed. If you reinstall predefined reports, from an import or as part of a product upgrade, you lose your changes. To keep the original report intact, always make a copy of the predefined report, rename it, and then modify the new report to better meet your needs. To create a new report, you must specify an SQL statement that determines which records are returned when the report is run.
The SQL statements in custom Configuration Manager reports are created in the Report SQL Statement dialog box in the Configuration Manager console, which can be difficult and requires SQL experience, or by using the Query Designer that is built into SQL Server 2005. For more advanced queries or for users who are less experienced in writing SQL statements, the Query Designer provides a simple interface for writing SQL statements. For more information, see Query Design Tools.
For reference information to help you create effective Configuration Manager custom reports, see the following sections:
Queries Using the Configuration Manager SQL
Views Provides sample queries that demonstrate
how Configuration Manager 2007 SQL views can be joined to other
views when creating reports.
for Creating Custom Reports Provides
real-world scenarios for creating, cloning, and modifying
Configuration Manager reports.
for Creating Custom Reports Provides exercises
that demonstrate how to modify and clone predefined reports, and
how to create a new report, using different methods for creating or
modifying the SQL statement in each report.
- SQL Server
Reference Provides information about common
SQL query statements and the query design tools available in
Reports created outside of Configuration Manager can be copied to a designated folder on a reporting point to extend the reporting capabilities of the site. These reports will primarily be Active Server Pages (ASP) pages. However, it can be any file that can display by using Internet Explorer 5.0 or later. Because supplemental reports are not secured Configuration Manager objects, any user can view them—unless you secure them by using Microsoft Internet Information Services (IIS) security.
You can run reports by using Report Viewer, which is a browser-based application that you can start either from within the Configuration Manager console or by using a URL with Internet Explorer, which allows for access to reports by any user with the appropriate security rights. The Report Viewer files are located on a reporting point, which is a Configuration Manager site system role. The reporting point provides access to reports at the site and communicates directly with the site database; therefore, the Report Viewer files can be installed only on a primary site. Multiple reporting points can be created to balance the network load for viewing reports.
If a report does not have prompts, Report Viewer starts directly at the Report Results page for the selected report when it runs. For reports that are likely to return large amounts of data, such as status message reports or client installation reports, it is recommended that you create prompts or linked reports to limit the amount of data that is returned by any one report.
Report Viewer cannot display different languages on a single reporting page. You can create individual reports that contain data in only one language. If double-byte character set (DBCS) information is not displayed correctly, you should configure Internet Explorer as follows: right-click anywhere in Report Viewer, point to Encoding, and then click Auto-Select. This action overrides other encoding selections. Reports that are opened within the Configuration Manager console use the default encoding setting and cannot be modified from the console.
Creating and Modifying Reports
Creating a new report or modifying a predefined report requires a working knowledge of SQL. When you create a new report, you must specify a category. You can choose an existing category or create a new category. When you create a new category, it is added to the category list. Within a given category, report names must be unique. However, you can use duplicate report names in different categories. Configuration Manager assigns each new report a report ID number, which uniquely identifies the report. The category determines which tree branch the report appears in on the main page of Report Viewer.
You can configure a report to refresh its results automatically at a specified interval. This functionality is especially useful for reports that you include in a dashboard or otherwise use to monitor information that changes frequently. You can also configure a report to display its data as a chart. This functionality is useful for reports that return counts, such as a report that provides a count of computers by network protocol. You can specify a chart title, a title and report column to use for the category (x) axis data, and a title and report column to use for the value (y) axis data. For the value (y) axis data, you should select a column that contains integer data. If you select a column that contains string data, some of the data might be truncated on the chart. You can also specify a default chart type, such as a bar chart. A report user can choose to display the data with a different chart type after the report runs. If a report returns multiple result sets, Report Viewer displays only the first result set as a chart.
To display report data as a chart by using Report Viewer, you must have a licensed copy of Microsoft Office XP Web Components, Microsoft Office 2000 Web Components, or Microsoft Office 2003 Web Components installed on the reporting point site system. The Office Web Components are automatically installed with all Office XP editions, and Office 2000 and Office 2003 Professional, Premium, Developer, and Standard editions. They are not installed with Office 2000, Office 2003 Small Business, or the stand-alone version of Microsoft Office Excel 2000. You must also have a licensed copy of at least one Microsoft Office application installed on the reporting point site system.
|Office Web Components is not supported on 64-bit operating systems. If you want to use graphs in reports, use 32-bit operating systems for your reporting points.|
Report SQL Statement
The principal element of a report is a Structured Query Language (SQL) statement that defines which data the report gathers and returns as the result set. A result set is a tabular arrangement of the data in columns and rows. A report can also return multiple result sets. The SQL statement in a report does not run directly against the Configuration Manager site database tables. Instead, the SQL statement runs against a set of Microsoft SQL Server views, which point to records in the Configuration Manager site database tables. Each time that you run a report, the information returned consists of data that is current in the database at the time that you run the report. To create new reports by using the Configuration Manager console, you must have a working knowledge of SQL. However, no knowledge of SQL is required to import new reports.
A report prompt is a report property that you can configure to request a parameter value from the user before running the report. You can include more than one prompt in a report; however, each prompt must have a unique name. You can use prompts to limit or target the data that a report retrieves. For example, you create a report that retrieves hardware inventory data for a given computer and prompts the user for a computer name. Report Viewer then passes the user-specified value to a variable that is defined in the SQL statement for the report. Provided that you have properly configured the SQL statement, the report returns hardware inventory data only for the specified computer.
You can use a link in a source report to provide users with ready access to additional data, such as more detailed information about each of the items in the source report. For example, you might link a report that lists all site codes to another report that lists all recent error messages for a given site code. The source report passes a specific site code to the target report based on which line item in the source report the user chose to obtain more information. A report can be configured with only one link, and that link can connect to only one target report.
If a report has links to a target, link icons appear to the left of each row of data when you run the report in Report Viewer. When you click a link icon, the target opens in the same window. If a report has links to a target and returns multiple result sets, the same target is used for all result sets.
Using Report Data
When you run a report, there are several ways that you can use the report data in another application or offline. You can use the menu bar commands on the Report Results page to perform the following tasks:
- Print the report data.
- Copy the report data to the Clipboard.
- Display the report data as a chart (for reports configured to
- Export the report data as a comma-delimited file.
- Add the report URL to your list of favorites.
- Send the URL for the report by using e-mail. (The recipient
must have Read permission for the report and be a member of the SMS
Reporting Users group to run the report.)
A report can return multiple result sets (for example, when you include more than one SELECT clause or a COMPUTE clause in an SQL statement). If a report is configured to display as a chart and the report returns more than one result set, Report Viewer displays only the first result set as a chart. If you print a report that returns multiple result sets, copy it to the Clipboard or export it to a comma-delimited file. All result sets are included.
You can sort the data within a result set by clicking a column heading. If the report has multiple result sets, you can sort the data in each result set independently. You can sort only by using one column at a time.
Exporting and Importing Reports
Reports in Configuration Manager 2007 can be exported by using the Export Object Wizard, which creates a Managed Object Format (MOF) file that can then be imported at another site. This can be useful when importing reports that have been downloaded from the Internet or created by someone else in the organization.
Report Viewer generates a unique URL for each report that you run. The URL contains the report ID and the variable names that you used to run the report. You can use the URL to schedule a report to run (or to run and export the data to a file) at a specified interval by configuring the Scheduled Tasks feature of your operating system to start Internet Explorer with a URL This functionality can be helpful for reports that can take a long time to run, such as a report that returns a large amount of data. You can schedule such reports to run at a time when your network is less busy.
When a report is deleted in Configuration Manager 2007, the report object is removed from the site database, resulting in the following changes to the report's status:
- It no longer appears in the report list in the Configuration
Manager console or Report Viewer.
- It no longer appears in dashboards in which it was
- It is no longer available to source reports that use the
deleted report as the target for a link. The report link in the
source report no longer works when the target report is deleted,
and the link should be removed or reconfigured.