Building an SQL Statement
The reporting interface has features that can help you build SQL statements for reports that run against the SQL Server views.
Note
- While the features of the Report SQL Statement dialog box can assist you in building an SQL statement, the interface does not automatically create complete SQL statements, nor does it validate them. However, SMS 2003 does perform limited syntax checks of the SQL statement.
When you initially open the Report SQL Statement dialog box for a new report, the SQL statement box contains the following sample SQL statement:
SELECT * FROM V_R_System where V_R_System.Name0 = 'computer_name'
A SELECT statement specifies the columns to be returned by the statement. It retrieves the data from the SQL Server views and presents it to the user in one or more result sets.
Note
- SQL statements are not case-sensitive.
You can leave the asterisk (*) that follows the SELECT keyword to return all columns or replace it with the specific column names that you want the report to return (for example, User_Domain0 or User_Name0). The FROM clause indicates the SQL Server view from which the data is retrieved and always follows the SELECT keyword. For more information, see the "SQL Server Views" section earlier in this chapter.
You can create multiple SELECT statements within an SQL statement for a report, which returns multiple result sets. The following is an example:
SELECT * FROM v_StatMsgModuleNames SELECT * FROM v_SoftwareProduct
Note
- If you use multiple SELECT statements for a report, you should test each statement individually to ensure that it runs successfully. When a report fails, it returns an error code indicating the failure. When you use multiple SELECT statements, they are treated as a single request; if any statement fails, only one error code is returned and the report fails.
The Report SQL Statement dialog box has controls that you can use to help you build SQL statements. You can use the Views and Columns lists to insert view and column names and the Values button to insert column values into the SQL statement.
Note
- The Report SQL Statement dialog box controls insert data in the SQL statement at the position of the cursor. When you first open the Report SQL Statement dialog box, the cursor is positioned at the beginning of the statement. You should position the cursor before inserting data.
To insert a view name
In the SQL statement box, position your cursor in the SQL statement where you want to insert a view name.
In the Views list, click a view name, and then click Insert.
To insert a column name
In the SQL statement box, position the cursor in the SQL statement where you want to insert a column name.
In the Views list, select the view that contains the column or columns that you want to add.
In the Columns list, click a column name, and then click Insert.
To insert a column value
In the SQL statement box, position your cursor in the SQL statement where you want to insert a column value.
In the Columns list, select a column, and then click Values.
In the Values shown area, click the Previous and Next buttons to scroll through the values.
To apply a filter to limit the number of values that is returned, click Set.
In the Set Filter dialog box, specify the filter criterion, and then click OK.
In the Values list, click the value that you want to add, and then click OK.
For more information about using the Report SQL Statement dialog box, see SMS Help.
SQL keywords and clauses
The following are some other commonly used SQL keywords and clauses that you might find helpful for creating reports:
AS Specifies an alias for a column name. An alias replaces the column display name in the result set. Therefore, when displaying the result set, Report Viewer uses aliases as the column headings, rather than the default column display names. You can uses aliases to create column headings that might be more understandable to report users. In the following example, User_Name0 is assigned the display name User Name. You can also use an alias in place of the column name in an ORDER BY clause, but not in a WHERE clause.
WHERE Specifies a search condition that restricts the rows that are returned. This condition can be based on a specified value from one of the selected columns, as compared to a variable or a string.
ORDER BY Specifies that the result set be sorted in ascending sequence based on the value in a specified column. In the following example, the SQL statement sorts the result set by data in the column User Name, and then by the data in the column Comp Name.
COMPUTE Generates totals that appear as additional summary columns at the end of the result set. Using a COMPUTE clause returns a report with multiple result sets.
The following sample statement provides examples of these keywords and clauses.
SELECT User?Name0 AS 'User Name', Name0 AS 'Comp Name' FROM v_R_System WHERE User?Name0 LIKE @variable2 ORDER BY User?Name0, Name0 COMPUTE COUNT (User?Name0) BY User?Name0, Name0
SQL statement variables
You use variables to integrate report prompts into the SQL statement for a report. Report prompts provide a means for the user to enter a dynamic value each time that the user runs a report. Report Viewer uses that value as a variable value in the SQL statement to target or limit the data that is returned. For example, in a report that returns data about a client, the user might be prompted to enter a computer name.
When you create a report prompt, you assign it a prompt name. To integrate the prompt into the SQL statement, you define the prompt name as a variable at the appropriate place in your SQL statement. You can create more than one prompt, each with its own variable; however, the name for each prompt must be unique within a report. For more information, see the "Integrating Report Prompts" section earlier in this chapter.
Converting Coordinated Universal Time (Greenwich Mean Time) to local time
By default, time data is stored in the SMS database in the local time of the system that generated the data. However, some time data is stored in Coordinated Universal Time, specifically status messages stored in the v_StatusMessage and v_ClientAdvertisementStatus views and in the software metering data and summarization views. In addition, some time data might be stored in Coordinated Universal Time, depending on which time format that you selected when creating the data, such as the ExpirationTime in the v_Advertisements view.
When you create an SQL statement for a report that includes a column with Coordinated Universal Time data, the data appears in the report in Coordinated Universal Time. If you prefer to have local time appear in the report, you can use the implicit variable @__timezoneofffset in your SQL statement. When you use this variable, SMS returns the offset from Coordinated Universal Time in seconds. To convert to local time, use the following syntax:
DATEADD(ss,@__timezoneoffset,< time column name>).
SQL statement examples
The following examples show how to use the SQL Server views to create useful SQL statements for reports:
To return the list of all available views, which can be helpful for creating other reports, use the following statement:
SELECT Type, ViewName AS 'View Name' FROM v_SchemaViews
To return the list of available inventory views, use the following statement:
SELECT Type, ViewName AS 'View Name' FROM v_SchemaViews WHERE Type='Inventory'
To return the display name of resources based on the resource type number (5 = System), use the following statement:
SELECT DisplayName AS 'Display Name' FROM v_ResourceMap WHERE ResourceType=5
To determine discovery properties for a particular resource type, use the following statement:
SELECT * FROM v_ResourceAttributeMap WHERE ResourceType=5
To list the inventory groups for a particular resource type, use the following statement:
SELECT InvClassName FROM v_GroupMap WHERE ResourceType=5
For More Information
Did you find this information useful? Please send your suggestions and comments about the documentation to smsdocs@microsoft.com.