Topic Last Updated—August 2008

This step-by-step guide contains procedures to guide you through the process of using SQL Reporting Services in Configuration Manager 2007 R2 to create a SQL-based report.

This example report displays the name, processor type, and Configuration Manager 2007 client version for each computer in a specified collection. Additionally, this report links to the Computer information for a specific computer report to provide further information about any computer you select.

Note
The information in this topic applies only to Configuration Manager 2007 R2 and Configuration Manager 2007 R3.

To create the SQL-based report

  1. In the Configuration Manager console, navigate to System Center Configuration Manager / Site Database / Computer Management / Reporting / Reporting Services / <Report Server> / Report Folders and expand the node.

  2. Under Report Folders, right-click the report folder in which you want the report to be created, and then click Create Report.

  3. On the Information page of the Create Report Wizard, specify the following:

    • Select the check box for SQL-based Report.

    • In the Name field, type Example SQL-based Report.

    • In the Description field, type This is an example SQL-based report..

  4. Click Next.

  5. On the Create datasets page of the Create Report Wizard, click the New button to create a new dataset, which will be called DataSet1. This dataset will contain the SQL code for the report.

  6. Ensure that Dataset1 is selected in the Dataset Name drop-down list, and then click the Edit Command Text button.

  7. Enter the following SQL statement in the Command Text window of the Command Text dialog box:

    SELECT Netbios_Name0, CPUType0, Client_Version0 FROM V_R_SYSTEM

  8. Click OK to close the Command Text dialog box.

  9. Click Next.

  10. On the Summary page of the Create Report Wizard, review the actions to be taken and then click Next.

  11. On the Wizard Completed page, review the actions taken and then click Finish to close the wizard.

To create a parameter for the SQL-based report to restrict it to a specified collection

  1. In the Configuration Manager console, navigate to System Center Configuration Manager / Site Database / Computer Management / Reporting / Reporting Services / <Report Server> / Report Folders and expand the node.

  2. Right-click the report Example SQL-based Report, and then click Properties.

  3. On the Datasets tab of the Example SQL-based Report Properties dialog box, click the New button to create a new dataset, which will be called DataSet2. This dataset will contain the SQL code for the report prompt parameter.

  4. Ensure that Dataset2 is selected in the Dataset Name drop-down list, and then click the Edit Command Text button.

  5. In the Command Text dialog box, enter the following SQL statement in the Command Text window:

  6. begin if (@filterwildcard = '') SELECT DISTINCT CollectionID, Name FROM v_Collection ORDER BY Name else SELECT DISTINCT CollectionID, Name FROM v_Collection WHERE CollectionID like @filterwildcard ORDER BY Name end

  7. Click OK to close the Command Text dialog box.

  8. Select Dataset1 from the Dataset Name drop-down list, and then click the Report Parameters button.

  9. In the Report Parameters dialog box, click Add and then specify the following values:

    • Parameter Name Specify the name ID.

    • Prompt Text Specify the prompt text Specify a collection name.

    • Valid Values Select this check box to provide a query containing the valid values for this report parameter.

    • Allow Blank Clear this check box to prevent a bank value being specified for the collection parameter.

    • Valid Values Select Query Based.

    • Dataset Name From the drop-down list, select Dataset2.

    • Value field From the drop-down list, select CollectionID.

    • Label field From the drop-down list, select Name.

  10. Click OK to close the Report Parameters dialog box.

  11. On the Datasets tab of the Example SQL-based Report Properties dialog box, select DataSet1 from the Dataset Name drop-down list and then click the Edit Command Text button.

  12. In the Command Text dialog box, append the following SQL code to the query in the Command Text window:

    WHERE CollectionID = @ID

  13. Click OK to close the Command Text dialog box.

To link the SQL-based report to another report

  1. In the Configuration Manager console, navigate to System Center Configuration Manager / Site Database / Computer Management / Reporting / Reporting Services / <Report Server> / Report Folders and expand the node.

  2. Right-click the report Example SQL-based Report and then click Properties.

  3. On the Datasets tab of the Example SQL-based Report Properties dialog box, select DataSet1 from the Dataset Name drop-down list.

  4. In the Fields window, locate the Name row and click … to specify a new link.

  5. In the Report Link Dialog dialog box, click … to open the Browse Reports dialog box. Select the report Computer information for a specific computer (in the folder Hardware – General), and then click OK.

  6. In the Report Link Dialog dialog box, select Name from the Parameter drop-down list.

  7. Click OK to close the Report Link Dialog dialog box.

  8. Click OK to close the Example SQL-based Report Properties dialog box.

See Also