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.
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:
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
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.