A prompt in Configuration Manager 2007 is a report property that can be configured when a report is created or modified. Prompts are created to limit or target the data that a report retrieves. A report can contain more than one prompt as long as the prompt names are unique and contain only alphanumeric characters that conform to the SQL rules for identifiers.

When you are running a report, the prompt will request a value for a required parameter and, based on the value, retrieve the report data. For example, the Computer information for a specific computer report retrieves the computer information for a specific computer and prompts the user for a computer name. Report Viewer then passes the specified value to a variable that is defined in the SQL statement for the report. Provided that the SQL statement is properly configured, the report returns computer information data for only the specified computer.

Prompt Properties

A default value for the prompt can be configured so that when the prompt displays it is populated with the default value. The prompt can also be configured to display a list of appropriate values from which the user can choose by using a SQL statement, which is separate from the report's primary SQL statement. For example, if a report prompts the user for a computer name and you want users to be able to select from a list rather than typing in a computer name from memory, a SQL statement can be configured. When the user clicks Values in the Prompt dialog box, a list displays the items returned from the SQL statement that the user can choose from. To allow the use of the percent (%) symbol wildcard to limit the values returned, the @_filterwildcard variable must be used in the SQL statement.

Prompt SQL Statement Example

The following is an example of a SQL statement for a prompt that supports the use of the % wildcard character and returns a list of computer names that are either filtered or unfiltered, depending on the prompt value that is entered:

  Copy Code 
begin
 if (@__filterwildcard = '')
  SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS ORDER By SYS.Netbios_Name0
 else
  SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS 
  WHERE SYS.Netbios_Name0 like @__filterwildcard
  ORDER By SYS.Netbios_Name0
end

The SQL statement provides two main sections. When the user clicks Values without typing anything into the text box, the if (@__filterwildcard = '') statement is true and all computer names are returned. Otherwise, the computer that matches the prompt value is retrieved, or when a wildcard is specified, the computers that contain the text from the prompt value are retrieved and populate the values list.

Using Wildcards for Prompt Values

Wildcards can be used to reduce the number of values that are displayed when you click Values in the Prompt dialog box. Use the percent (%) symbol to substitute for any number of characters, the underscore (_) symbol to substitute for a single character, and the bracket ([ ]) symbols to search for literals. Although wildcards help reduce the number of values that are displayed when you click Values, you cannot use wildcards to reduce the number of results that are returned when you actually run a report by clicking Display. If you enter a wildcard and then click Display, the report searches for the wildcard as a literal value. For example, if you enter %m% when prompted for a computer name and then click Display, the report searches for computers that have the literal name %m%.

Maximum Rows Returned for Prompt Values

The number of values that might be returned when you click Values can be very large and is limited by default to 1,000. For information about how you can change the default, see How to Configure the Maximum Rows Returned in the Values List.

See Also