When creating Configuration Manager 2007 reports, you can write SQL statements in the Report SQL Statement dialog box in the Configuration Manager console. The Configuration Manager SQL views and associated columns are listed in the dialog box, but you still need to know how to write SQL statements. For some administrators, it is much easier to use Query Designer in Microsoft SQL Server 2005 to create the SQL statement for the Configuration Manager report. This tool has a variety of features that help in designing and testing queries. For more information about how to use Query Designer to create or modify a report SQL statement, see How to Create or Modify a Report SQL Statement Using Query Designer.
Using Query Designer to Create Report Queries
Writing SQL statements 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 SQL statement into the SQL pane of the Query Designer, and easily add views, create joins, select columns to display, add criteria, sort data, and so on. 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:
SELECT Name
FROM v_FullCollectionMembership
WHERE CollectionID = @collid
Change the variable to the desired static value:
SELECT Name
FROM v_FullCollectionMembership
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.
Report Links
If you change the column order by modifying the query in a predefined report and if 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.