Creating and Modifying SQL Statements

  • Article

The principal element of a report is its SQL statement. The SQL statement determines which records and fields are returned each time that a user runs the report. The SQL statement accesses read-only SQL Server views, rather than your SMS site database tables. The reporting interface supports most SQL keywords and clauses that can be used for the read-only views. The primary clause that is used for creating SQL statements is the SELECT clause.

You can also create SQL statements to use for a report prompt. When a user runs a report with an SQL statement for a prompt, the SQL statement returns a list of values from which the user can choose. You can create reports prompts that do not use an SQL statement. For more information, see the "Report Prompts" section earlier in this chapter.

To create your own reports requires a working knowledge of SQL. It is not within the scope of this chapter to teach you SQL. However, this section does provide information about how the reporting interface can help you create SQL statements. Although the interface can help you, it does not automatically create complete SQL statements, nor does it validate them. However, SMS 2003 does perform limited syntax checks of the SQL statement.

You can use Microsoft SQL Server SQL Query Analyzer or another SQL query builder to create SQL statements, and then copy and paste the statements into reports. This might be helpful if you want to create longer or more complex statements.

Important

  • You must write case-sensitive queries for reports when they will be run against a case-sensitive SQL Server. Otherwise, the report will not run correctly and the SQL Server will generate errors.

To create an SQL statement, you need an understanding of the SQL Server views that expose data from your SMS site database. Before creating SQL statements, see the "SQL Server Views" section later in this chapter.

The process for creating or modifying an SQL statement in a report is the same.

To create or modify an SQL statement for a report

  1. In the SMS Administrator console, right-click Reports, point to New, and then click Report.

    -Or-

    In the SMS Administrator console, right-click a report and click Properties.

  2. On the General tab, click Edit SQL Statement.

  3. In the SQL statement box, enter a valid SQL statement.

To create or modify an SQL statement for a prompt

  1. In the SMS Administrator console, right-click Reports, point to New, and then click Report.

    -Or-

    In the SMS Administrator console, right-click a report, and then click Properties.

  2. On the General tab, click Edit SQL Statement.

  3. In the Report SQL Statement dialog box, click Prompts.

  4. In the Prompts dialog box, click New (gold star).

    - Or -

    In the Prompts dialog box, click a prompt, and then click Properties.

  5. In the Prompt Properties dialog box, select the Provide a SQL statement check box, and then click Edit SQL Statement.

  6. In the SQL statement box, enter a valid SQL statement for the prompt.

Note

  • If you modify or delete a prompt in a report, links to that report from other reports might be broken. This includes modifying an SQL statement that is used for a prompt.
For More Information

Did you find this information useful? Please send your suggestions and comments about the documentation to smsdocs@microsoft.com