SMS Reporting | Professional MOM 2005, SMS 2003, and WSUS

Before you start reporting data collected by SMS, it is important to know the different ways of viewing that data. SMS 2003 uses queries and reports to extract data from the SMS database and make it available to SMS administrators and operators.

It is important to understand the differences between queries and reports so that you can better select between the two when trying to view SMS data. The main differences that we can point out right now are:

Language: Queries are written using WQL (WMI Query Language), while reports are written using T-SQL (Transact Structured Query Language).
Access: Queries can be accessed only from the SMS Administrator Console, whereas reports need to reside in a Reporting Point and are accessed through a web application called the Report Viewer. Reports can be accessed from the Reporting node in the SMS Administrator Console. However, once the report is selected for viewing, the Report Viewer web application is opened to display the results.

You will notice other differences as you advance in this chapter. The question you should ask yourself when deciding to create a report or a query is: How are users going to access and use this data? If the answer is that users can view the data through the SMS Administrator Console, you should create a query. However, if the users do not have access to the SMS Administrator Console, or need to be able to save the report (as HTML), you should create a report.

Queries

A query is a set criteria used to extract data from the SMS site database. A query searches the database for objects that match the criteria established by the user and returns that data to the SMS Administrator Console in the form of a list.

There are two types of queries in SMS 2003: Data Queries and Status Message Queries. Data Queries are used to view discovery and inventory information collected by SMS, while Status Message Queries are used to view status messages from the different SMS components. Status Message Queries are used for debugging and maintenance tasks. In this chapter we will cover Data Queries. Therefore, the use of the word "query" in this section refers only to Data Queries.

Queries can be used to return information related to different SMS objects, such as packages, advertisements, sites, inventory data, and so on.

Queries are written using a special language called WQL. WQL is the WMI Query Language. The fact that WQL is used means that the query is not executed directly against the SQL site database. WMI is used as an interface between the query and the site database.

A basic knowledge of WMI is required in order to better understand how queries are created. Detailed information on WMI is beyond the scope of this book, but you can find information on it at http://www.microsoft.com/whdc/system/pnppwr/wmi/default.mspx.

WMI

For the purpose of this chapter, you can think of Windows Management Instrumentation (WMI) as an interface used as a single point of access to obtain management information about any computer. Typically, management information is stored in different places, based on what type of information it is. For instance, an application such as Internet Information Services (IIS) may use its own database to store metadata required for IIS to run, whereas hardware-related information may be maintained by the operating system in the computer registry, and performance-related data may be obtained through System Monitor.

Therefore, if you need to view how much memory is in use and how many hard drives are available in a given machine, you would have to query the registry and system monitor. WMI provides a single point of querying when data from different providers are needed. This way, you can issue a single WMI query and get data from the different providers.

In WMI, information is made available through classes, objects, and properties.

A class is an abstract definition of the type of information that can be accessed for different types of artifacts found in a computer. For example, the Win32_Printer class defines what information is available through WMI when viewing data related to a printer. This class describes that a printer may contain data such as the printer name, paper sizes supported, number of print jobs, and so on. Classes define the state and behavior of an object. For a complete list of WMI classes, go to http://www.msdn.microsoft.com/library/default.asp?url=/library/en-us/wmisdk/wmi/computer_system_hardware_classes.asp.

State is defined by a collection of properties. Properties are used to specify what information is available for a given class. For instance, printer name, paper sizes supported, and number of print jobs are some of the properties defined by the Win32_Printer class.

Behavior is defined by a collection of methods. Methods are actions that can be performed on an object that belongs to a WMI class. For instance, the Win32_Printer class contains the PrintTestPage method. When this method is called from a specific printer, a test page is printed.

An object is referred to as an instance of a class. To better understand the concept, imagine that your computer has two printers connected to it. Each printer is a different object, but they both are from the same class. They share the same definition. They both have a name, paper sizes that they support, and a number of jobs executed. A class defines what information will be available for objects of its type. A class is a type definition, while an object is the actual physical artifact of that type.

You can think of a blueprint for a house as a class. It defines how many stories a house will have, where the rooms will be located, that there will be outside walls, and so on. From the same blueprint we can build multiple houses. A house would be an object. Each house will have its own values for the properties defined in the blueprint. One house may be blue on the outside, while another house may be white. Both have the color property, but they each have different values.

WMI can be used to collect and change settings for many operating system components, hardware types, and applications through the use of providers. A provider allows WMI to access disparate artifacts within a computer. Here is a list of systems for which WMI providers are available:

Microsoft SQL Server
Microsoft Exchange Server
Domain Name Service (DNS)
Systems Management Server (SMS)
The Windows operating system
Internet Information Services (IIS)
Windows Drivers (WDM)

For a more complete list, check http://www.microsoft.com/technet/prodtechnol/sms/sms2003/opsguide/default.mspx.

SMS and WMI

Now that you have a better understanding of WMI, you can learn how SMS uses it. As you know, WMI acts as a single point of access to view and change management information. SMS exposes information gathered by inventory and SMS objects through WMI classes.

SMS data is exposed through WMI by dozens of SMS-related classes. For better organization, these SMS classes are grouped in Object types. There are nine object types in SMS:

Advertisement: Allows access to advertisement objects within SMS.
Package: Allows access to package objects within SMS.
Program: Allows access to program objects within SMS.
Site: Allows access to site objects within SMS.
Software Metering Rule: Allows access to software metering rule objects within SMS.
Software Product Compliance: Allows access to software product compliance objects within SMS.
User Group Resource: Allows access to user group objects within SMS.
User Resource: Allows access to user objects within SMS.
System Resource: Allows access to discovery and inventory objects within SMS, such as logical disk, printer, and so on.

Object types are used to group attribute classes. An attribute class, in SMS, represents a single WMI class. All object types above contain a single attribute class, except for System Resource, which contains a collection of attribute classes used to describe discovery and inventory data.

A class can be used as a base to create a new class. For instance, a disk drive class can be created to collect all information any disk drive would have, such as capacity, file system, and so on. Then, a new class can be created based on that to represent hard disks. In this class, some specific properties for a hard disk can be added, such as number of partitions, number of spindles, and so on. The same based class can be used to create the CD Drive class. The ability to extend classes this way is referred to as inheritance.

SMS uses two parent (or base) classes for all its attribute classes. These classes are:

SMS_R_System: Parent class for all discovery classes, such as User Resource (SMS_R_User) and User Group Resource (SMS_R_UserGroup).
SMS_G_System: Parent class for all inventory classes, such as Logical Disk (SMS_G_System_LOGICAL_DISK) and Operating System (SMS_G_System_OPERATING_SYSTEM).

Running Queries

Now that you have a basic understanding of WMI, which is the basis for all SMS queries, you can start to use some queries and create queries of your own. Out of the box, SMS 2003 provides about 20 queries, as listed below:

All Client Systems
All Non-Client Systems
All Systems
All Systems with Hardware Inventory Collected
All Systems with Specified Software File Name and File Size
All Systems with Specified Software Product Name and Version
All Systems with Specified Software File Name and File Size
All User Groups
All Users
All Windows 2000 Professional Systems
All Windows 2000 Server Systems
All Windows 98 Systems
All Windows NT 4.0 Systems
All Windows NT 4.0 Systems with Service Pack 3
All Windows NT Systems
All Windows Server 2003 Systems
All Windows XP Systems
Computers recommended for Advanced Client upgrade
SMS Clients that have not been upgraded to SMS 2003
Systems by Last Logged On User
This Site and Its Subsites

To run a query, perform the following steps:

In the Start menu, point to All Programs Systems Management Server and click SMS Administrator Console.
In the SMS Administrator Console, expand the Site Database node.
Expand the Queries node. The SMS Administrator Console appears, as shown in Figure 15-1.

Figure 15-1
Right-click the query to be executed, and click Run Query. Figure 15-2 shows the result of the All Systems query.

Figure 15-2

Some SMS queries require one or more parameters. One good example is All Systems with Specified Software File Name and File Size. To view this report using parameters, perform the following steps:

Right-click the All Systems with Specified File Name and File Size and click Run Query. The Input Query Value dialog box appears, as shown in Figure 15-3.

Figure 15-3
In the Software Files.File Name box, type WINNT.EXE, and click OK. The Input Query Value dialog box appears, as shown in Figure 15-4.

Figure 15-4
In the Software Files.File Size box, type 84797. (This is the size of the winnt.exe file for Windows Server 2003.) Click OK. The results for the All Systems with Software File Name and File Size query are displayed, as shown in Figure 15-5.

Figure 15-5

Creating Queries

Now that you can run a query to view its results, it's time to learn how to create your own queries. To begin this lesson, we show you how to create a simple query without using any criteria or parameters. Then we will expand your knowledge by adding criteria, using existing queries, and finally using parameters.

Creating a Simple Query

To create a query folder, perform the following steps:

Open the SMS Administrator Console, and expand the site database and the Queries node.
Right-click Queries, point to New, and click Folder. The Folder Properties dialog box appears, as shown in Figure 15-6.

Figure 15-6
In the Name box, type the folder name (in our example, Custom Queries) and click OK.

To create a simple query within the new folder, perform the following steps:

Right-click the new folder, point to New, and click Query. The Query Properties dialog box appears, as shown in Figure 15-7.
In the Name box, type the query name. For our example, type Discovered Computers.
In the Comment box, type a comment, if desired.
In the Object type list, select the desired SMS object type to be used. For our example, select System Resource.

Figure 15-7
Click the Edit Query Statement button. The General tab in the Discovered Computers Query Statement Properties dialog box displaying the Results list appears, as shown in Figure 15-8.

Figure 15-8
Click the New query result button (displayed as an asterisk). The Result Properties dialog box appears, as shown in Figure 15-9.

Figure 15-9
Click the Select button to choose an attribute. The Select Attribute dialog box appears, as shown in Figure 15-10.

Figure 15-10
In the Attribute class list, select a class from which an attribute will be used. For our example, select Computer System.
In the Alias as list, type an alias to be used instead of the attribute name. For our example, type Computer.
In the Attribute list, select the attribute to be displayed. For our example, select Name. Click OK.
In the Sort list, select Ascending to order the query result list by name. Click OK.
Repeat Steps 6 to 11 to add other attributes. For our example, add the User Name attribute from the Computer System attribute class. After adding the attributes, the Query Statement Properties dialog box appears, as shown in Figure 15-11.

Figure 15-11
Click Show Query Language to view the WQL statement generated for this query. The Query Language tab showing the Discovered Computers query is displayed, as shown in Figure 15-12. Click OK.
In the Collection Limiting section, select one of the following options:
- Not collection limited: This option does not limit the query to the existing results of any given collection; for our example, select this option.
- Limit to collection: This option allows the section of a collection to limit the query to the computers, groups, or users in the selected collection.
- Prompt for collection: This option allows the collection to be selected when a user runs the query, as a parameter.
Click OK.

Figure 15-12
Right-click the new query and click Run Query. The query results are displayed, as shown in Figure 15-13.

Figure 15-13

Using Criteria in a Query

In this lesson, you learn how to create a query based on an existing query, how to add criteria to a query, and how to link a criterion to a prompted value (parameter). To create a new query using criteria, perform the following steps:

Right-click the folder where the query will be created (for our example, Custom Queries), point to New, and click Query.
In the Name box, type the name for the new query. For our example, type Computers with more than 128 Mb of RAM.
Click the Import Query Statement button. The Browse Query dialog box appears, as shown in Figure 15-14.

Figure 15-14
Select the query you want to copy from. For our example, select the All Client Systems query. Click OK.
Click the Edit Query Statement button. The General tab for the dialog box showing query properties is displayed, as shown in Figure 15-15.

Figure 15-15
Click the Criteria tab. The Query Properties dialog box is displayed showing the Criteria tab for the new query in Figure 15-16.

Figure 15-16
Click the New Criterion button (displayed as a sunburst). The Criterion Properties dialog box is displayed, as shown in Figure 15-17.

Figure 15-17
Click the Select button. The Select attribute dialog box appears.
In the Attribute class list, select the class you want to select an attribute from. For our example, select Memory.
In the Alias as list, type an alias to be used instead of the attribute name. For our example, leave it as <No Alias>.
In the Attribute list, select the attribute to be used for filtering. For our example, select Total Physical Memory (Kbytes).
In the Criterion type list, select one of the following options:
- Null Value: Compares the selected attribute to NULL (no value).
  
  The Criterion Properties dialog box with the Null value criterion type is shown in Figure 15-18.
  
  Figure 15-18
  
  Once this option is selected, the Operator list will be populated with the options is NULL and is not NULL.
- Simple Value: Compares the selected attribute to a simple value.
  
  The Criterion Properties dialog box with Simple value criterion type is shown in Figure 15-19.
  
  Figure 15-19
- Once this option is selected, the Operator list will be populated with the options is equal to, is greater than, is greater than or equal to, is less than, is less than or equal to, and is not equal to. The Value box should be populated with the value to be compared against. For our example, use this option with "is greater than" as an operator and 128000 as the value.
- Prompted Value: Compares the selected attribute to a value entered by the user when the query is executed.
  
  The Criterion Properties dialog box with the Prompted value criterion type is shown in Figure 15-20.
  
  Figure 15-20
- Attribute Reference: Compares the selected attribute to the value of another attribute.
  
  The Criterion Properties dialog box with the Attribute reference criterion type is shown in Figure 15-21.
  
  Figure 15-21
  
  When this option is selected, a second attribute has to be selected.
- Subselected Values: Compares the selected attribute to a list of values obtained through another query.
  
  The Criterion Properties dialog box with the Subselected values criterion type is shown in Figure 15-22.
  
  Figure 15-22
  
  When this option is selected, the Operator list is populated with the options is in and is not in. The Browse button should be used to select the queries from which a list of values will be created.
- List of Values: Compares the selected attribute to a list of predefined values.
  
  The Criterion Properties dialog box with the List of values criterion type is shown in Figure 15-23.
  
  Figure 15-23
  
  When this option is selected, the Operator list is populated with the options is in and is not in. Use the Values button to select values to be added to the list, or type the desired values in the Value to add list and use the Add and Remove buttons to populate the Values to match list.
Click OK. The Criteria tab for the dialog box displays, as shown in Figure 15-24.
Click OK twice and run the query. The query result is displayed, as shown in Figure 15-25.

image from book
Figure 15-24

image from book
Figure 15-25

Exporting and Importing Queries

Queries created in SMS 2003 can be shared among multiple environments by exporting and importing the queries. This is rather useful when you are dealing with multiple SMS organizations that are not in the same hierarchy or when creating queries in a child site that need to be copied to a parent site, or a sibling site.

To export a query, perform the following steps:

Right-click the Queries container, point to All Tasks, and click Export Objects. The Export Object Wizard dialog box appears, as shown in Figure 15-26.

Figure 15-26
Click Next. The Objects to Export page of the Export Object Wizard is displayed, as shown in Figure 15-27.

Figure 15-27
Click the Select All button to select all queries, or select the queries you want to export.
Click Next. The MOF File page of the Export Object Wizard is displayed, as shown in Figure 15-28.

Figure 15-28
In the MOF path and file name box, type the path to the file that will be created or use the Browse button to select a path.
In the Comment box, type a comment if desired.
Click Next. The fourth step of the Export Object Wizard appears, as shown in Figure 15-29.
Click the Finish button.

image from book
Figure 15-29

If you want, you can verify the file that was created with the preceding procedure. Figure 15-30 shows a sample MOF file containing two queries opened in Notepad.

image from book
Figure 15-30

Once a MOF file is created, it can be sent to a different server and imported. To import a query, perform the following steps:

Right-click the Queries container, point to All Tasks, and click Import Objects. The first step of the Import Object Wizard dialog box appears, as shown in Figure 15-31.

Figure 15-31
Click Next. The second step of the Import Object Wizard appears, as shown in Figure 15-32.

Figure 15-32
In the MOF path and file name box, type the path to the MOF file to be imported, or click the Browse button to navigate to the file.
Click Next. The third step of the Import Object Wizard appears, as shown in Figure 15-33.

Figure 15-33
Verify the list of objects and click Next. The fourth step of the Import Object Wizard appears, as shown in Figure 15-34.

Figure 15-34
Observe the comments and click Next. The fifth step of the Import Object Wizard appears, as shown in Figure 15-35.

Figure 15-35
Click Finish. Verify the list of queries. The imported query should be displayed.

Security

Like most Windows server applications, SMS 2003 grants access to queries, reports and other objects based on security rights managed through Access Control Lists. ACLs in SMS are made up of three components:

Account: User or group account that will be granted access.
Permission set: Rights granted to the account, such as create, delete, or read.
Class or instance: Individual object (instance) or type of object (class) that the ACL entry will apply to.

When applying security settings to queries and reports, if you add an account to the class section, the permission set will be applied to all queries or reports. However, if you add the account to the instance section, the permission set will be applied to the individual query or report selected.

To apply security settings to a query or report, perform the following steps:

Right-click a query or report and click Properties. In our example, we used the All Systems query.
Click the Security tab. The All Systems Query Properties dialog box showing security settings is displayed, as shown in Figure 15-36.

Figure 15-36
In the Instance security rights section, click the asterisk button. The Object Instance Security Right Properties dialog box is displayed, as shown in Figure 15-37.

Figure 15-37
In the User name box, type the user or group to be added to the ACL. Notice that this is a list, and all users that have been used in SMS can be selected.
In the Permissions list, enable all rights to associate with this ACL. Figure 15-38 shows a sample security setting.

Figure 15-38
Click OK. The All Systems Query Properties dialog box showing security settings appears, as shown in Figure 15-39.
Click OK.

image from book
Figure 15-39

Reports

SMS reporting is provided through a site system called the Reporting Point. The Reporting Point site system is composed of a web application. Therefore it requires a server running IIS (Internet Information Services).

Installing a Reporting Point

The Reporting Point site system role can be installed in any computer running the Windows 2000 Server operating system with IIS and Office Web Components (for reports that display charts). Machines connecting to the Reporting Point to use the Report Viewer web application need to have Internet Explorer 5.01 SP2 or later.

To install the Reporting Point site system role, perform the following steps:

In the SMS Administrator Console, expand Systems Management Server, Site Database, Site Hierarchy, the appropriate site, Site Settings, Site System. The SMS Administrator Console is displayed, as shown in Figure 15-40.

Figure 15-40
If you do not see the computer on which you want to install the Reporting Point site system in the list of site systems, right-click Site System, point to New, and click Server. Otherwise, right-click the system and click Properties. The Site System Properties dialog box appears, as shown in Figure 15-41.

Figure 15-41
If you are adding a new site system to the SMS environment, click Set and type the desired computer name.
Click the Reporting tab. The Site System Properties dialog box showing the Reporting Point tab is displayed, as shown in Figure 15-42.

Figure 15-42
Enable the Use this site system as a reporting point check box.
In the Reporting folder box, type the name of the virtual directory to be created in IIS for the Reporting Point application. By default, this folder is http://www.sitesystem/SMSReporting_sitecode.
Click OK.

To verify the Reporting Point installation, you can check the following settings on the computer where you are installing the site system role:

Services: Look for the SMS_REPORTING_POINT service. If this service is not present, check for the BOOTSTRAP service. The presence of the BOOTSTRAP service means that SMS is installing a service on the computer.
Internet Information Service (IIS) Manager: Check for the new virtual directory under the

Default Web Site.
SMS Site Component Manager (SMS Administrator Console): Check status messages for the Reporting Point.
Logfiles: Check the RSetup.log and SMSReportingInstall.log files.
Windows Explorer: Check the SMSReporting_sitecode folder within the IIS folder

(C:\Inetpub\wwwroot, by default).

Note

For security purposes, you may apply the IIS Lockdown 2.1 Template (for Windows 2000) or the URLScan 2.5 Template (for Windows 2003) tool to all site systems that require IIS. These tools are available in the SMS Toolkit. To download the toolkit, visit http://www.microsoft.com/smserver/downloads/2003/tools/toolkit.mspx.

Viewing Reports

SMS 2003 provides a long list of reports that can be used to view information on hardware inventory, software inventory, site system status, software distribution, and software metering data. Although the default reports may provide enough information to most organizations, you may create your reports to display information.

SMS reports are based on SQL queries that access the many tables used by SMS through views in the site database. A working knowledge of such views is required to better understand how to create reports in SMS 2003. Reports may also contain links to other reports and parameters used to filter information.

There are more then 160 reports available in SMS 2003. These reports are grouped in categories to facilitate their localization. SMS 2003 allows us to filter the list of reports in the SMS Administrator Console based on these categories. To filter the list of reports in SMS 2003, perform the following steps:

In the SMS Administrator Console, expand Systems Management Server, Site Database, and Reporting.
Right-click Reports, point to All Tasks, and then click Filter Reports. The Filter Reports dialog box appears, as shown in Figure 15-43.

Figure 15-43
Select one or more categories and click the Display/Hide button to determine whether to display or hide the selected categories. Observe the changes in the Display column of the category list.

Reports filtered to show memory-and processor-related reports are shown in Figure 15-44.

Figure 15-44

Click OK.

Note

The SMS Administrator Console does not show any indication that the report list is being filtered. Therefore, always verify the Filter Reports dialog box if you cannot find the report you are looking for.

Now that you can view the list of reports available in SMS 2003 and filter the list based on report categories you are ready to view your first report in SMS! To view a predefined report, perform the following steps:

In the SMS Administrator Console, expand Systems Management Server, Site Database Reporting, and Reports.
Right-click the report you want to view, point to All Tasks, Run, and click on the name of the Reporting Point from which to run the report. Figure 15-45 shows a sample All Collections report opened in this way.

image from book
Figure 15-45

The preceding steps describe how to access reports from the SMS Administrator Console. However, not all users that may need access to reports will have access to the SMS Administrator Console in a production environment. Such users may still access reports using Internet Explorer. To do so, perform the following steps:

Open Internet Explorer from any computer on the network.
At the address bar, type http://www.reportserver/SMSReporting_sitecode and hit Enter. The Report Viewer web application is displayed, as shown in Figure 15-46.

Figure 15-46
Notice the list of reports on the left side of the window displays reports grouped by category. Expand the "SMS-Site Discovery and Inventory Information" group and click the Computers discovered by a specific site report. Because this report requires a parameter (site code), the Report Viewer web page is displayed, as shown in Figure 15-47.

Figure 15-47
You can either type the site code in the Site Code box, or click the Values button to select from a list of valid values. For this example, click Values. The list of values is displayed, as shown in Figure 15-48.

Figure 15-48
Select a site from the list and click the Display link. Computers discovered by a specific site report are displayed, as shown in Figure 15-49.

image from book
Figure 15-49

Creating a Report

As previously discussed, SMS 2003 provides more then 160 predefined reports. However, you may need access to information that goes beyond the predefined reports. In such scenarios, new reports can be created, and exported for backup and copy purposes.

SMS reports are based in SQL queries. These queries access data made available through views in the SMS site database. Therefore, an understanding of such views is necessary in order to create more reports efficiently. Figure 15-50 displays a database schema with the Advertisement views used by SMS 2003. As you can see, a few views are related to the table v_Advertisement. Over 180 views in the SMS site database can be used by reporting.

image from book
Figure 15-50

Four types of reports are available in SMS 2003:

Predefined reports: Reports available in SMS 2003 out-of-the-box.
Custom reports: Reports created by SMS administrators using the SMS Administrator Console.
Supplemental reports: Reports created outside SMS 2003 as ASP pages. Because such reports are not SMS objects, they cannot be controlled through SMS security. You have to use IIS to secure access to these reports.
Dashboards: Grid displaying multiple reports in Report Viewer. Dashboards can display only those reports that do not require parameters.

In this chapter, you learn how to create custom reports and dashboards. Custom reports can be created by copying an existing report or from scratch. They may also contain parameters and links to other reports.

Copying an Existing Report

The first step for creating your own report is to copy and modify an existing report. To copy a report, perform the following steps:

In the SMS Administrator Console, navigate to Systems Management Server, Site Database, Reporting, Reports.
Right-click a report, such as the Computers discovered recently by discovery method, point to All Tasks, and click Clone. The Clone Report dialog box is displayed, as shown in Figure 15-51.

Figure 15-51
Type a name for the new report in the New report name box (we used Discovered Computers for our example) and click OK. The new report is displayed in the list of reports, as shown in Figure 15-52.

Figure 15-52

Working with Parameters

The Discovered Computers report created in the previous section contains two parameters that specify what collection the computers belong to, and how long it has been since they were discovered. You will now see how to change this report and work with parameters. To do so, perform the following steps:

Right-click the newly created report and click Properties. The Report Properties dialog box is displayed in the list of reports, as shown in Figure 15-53.

Figure 15-53
To view the SQL statement used to generate the report, click Edit SQL Statement. The Report SQL Statement dialog box is displayed in the list of reports, as shown in Figure 15-54.

Figure 15-54
Scroll down to see the WHERE clause for the selected SQL statement. You will notice the two parameters, or prompts as they are called in SMS, as shown in Figure 15-55.

Figure 15-55
To concentrate on the prompts, click Prompts. The Prompts dialog box appears, as shown in Figure 15-56.

Figure 15-56
Select the CollID prompt and click the Exclude (X) button to remove it from the SQL statement.
Click OK. The Linked Report Prompts dialog box is displayed, as shown in Figure 15-57.

Figure 15-57
This dialog box displays a list of reports that have a link to the report you are changing based on the prompt changed. As you can see, no reports are affected. Therefore, click Yes.
Click OK. The Report SQL Statement dialog box displaying error message appears, as shown in Figure 15-58.

Figure 15-58
The error message refers to the fact that although we deleted a prompt, a reference to it remains in the SQL statement. Change the SQL statement by removing the text selected in Figure 15-59, and click OK twice to save your report.

Figure 15-59
Right-click the report, point to All Tasks, Run, and click the Reporting Point from which to run the report. You will notice only one value is now prompted, as shown in Figure 15-60.

image from book
Figure 15-60