Using Query Designer to Create SQL Statements for Reports
Applies To: System Center Configuration Manager 2007, System Center Configuration Manager 2007 R2, System Center Configuration Manager 2007 R3, System Center Configuration Manager 2007 SP1, System Center Configuration Manager 2007 SP2
When creating Configuration Manager 2007 reports, queries can be created while in the Report SQL Statement dialog box in the Configuration Manager console. The Configuration Manager SQL views and associated columns are both listed, but you still need to know how to write query statements. For some administrators, it is much easier to use Query Designer in Microsoft SQL Server 2005 to create the query for the Configuration Manager report. This tool has a variety of features that help in designing and testing queries.
Using Query Designer to Create Report Queries
Writing queries in the Query Designer component of the Microsoft SQL Server 2005 Management Studio provides a graphical interface for writing queries. You can create a new query or copy a query from an existing Configuration Manager report, paste the query into the SQL pane and easily add views, create joins, select columns to display, add criteria, sort data, and so on. For more information, see How to Use Query Designer to Create or Modify Report Queries. Query Designer provides the following panes:
Diagram pane: Provides the ability to join the views on specific columns and select the columns to display as part of the query results.
Criteria pane: Provides the ability to create aliases for columns, configure the sort order for the query results, configure filters, and so on.
SQL pane: Provides the ability to manipulate the SQL statement.
Results pane: Provides the query results when the Execute SQL action is initiated.
Query Designer Considerations
When using Query Designer, you should be aware of the following points so that your queries and reports work as expected.
Report Prompt Query Variables
Many predefined Configuration Manager reports have report prompts. These report prompts require the user to enter a value for a specified view column. The value is stored in a variable, and the variable is then used to filter the query result set. These variables will not work in Query Designer, so you must change the variable to a static value or the query will fail. The following example shows a query from a Configuration Manager report that contains a variable representing a specific collection ID and how this variable is modified so that Query Designer can be used:
Query from a Configuration Manager report:
WHERE CollectionID = @collid
Change the variable to the desired static value:
WHERE CollectionID = 'SMS00001'
After the query has been modified in Query Designer and is ready to be used in a Configuration Manager report, the query can be copied into the Report SQL Statement dialog box for the report and modified so that the original report prompt variable replaces the static value entered above.
If you change the column order by modifying the query in a predefined report, and the report has a link to another report that requires a column number, the link can pass data from the wrong column to the target report. To prevent this, verify that the correct column numbers are specified in the link.
For additional information, see Configuration Manager 2007 Information and Support.
To contact the documentation team, email SMSdocs@microsoft.com.