In this example I explain how to use the prompt function. I anticipate that you already by now know how to create the report in Configuration Manager and how to start SQL Server Management Studio. This report will list some basic hardware details with a prompt on the Bios Serial number. I am creating the first part of the report in SQL server 2008 Studio Manager, and the prompt in the Config Mgr. console.

When you are working with prompts in reports there is a few things you need to know first.

  • To use variables use the @ symbol eg. WHERE V_GS_PC_BIOS.SerialNumber0 = @BiosSerialNumber

  • Variables in SQL statement stores the values from the prompt

The following views are used in this example:

V_R_SYSTEM
image
V_GS_PC_BIOS
image
V_GS_COMPUTER_SYSTEM
image

 

I’ve started SQL Server Management Studio, navigated to the SMS_Sitecode database, and created a new View. For information about those steps please refer to Part II of this post.
image

Select V_GS_Computer_System, V_R_SYSTEM, V_GS_PC_BIOS click Add and Close.
image

From V_R_System drag the column RessourceID onto the RessourceID column onto the  V_GS_Computer_System view.

image

From V_R_System drag the column RessourceID onto the RessourceID column onto the  V_GS_PC_BIOS view.

image

From V_R_SYSTEM select, Name()
From V_GS_PC_BIOS select, Name(), Releasedate(), SerialNumber()
From V_GS_COMPUTER_SYSTEM select, Manufacturer0, Model0, NumberOfProcessors0

In the V_GS_PC_BIOS.Name()  Alias. column type Bios Name
image The SQL statement:

SELECT     dbo.v_R_System.Name0, dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0, dbo.v_GS_COMPUTER_SYSTEM.Model0,
                      dbo.v_GS_COMPUTER_SYSTEM.NumberOfProcessors0, dbo.v_GS_PC_BIOS.Name0 AS [Bios Name], dbo.v_GS_PC_BIOS.ReleaseDate0,
                      dbo.v_GS_PC_BIOS.SerialNumber0
FROM         dbo.v_R_System INNER JOIN
                      dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID INNER JOIN
                      dbo.v_GS_PC_BIOS ON dbo.v_R_System.ResourceID = dbo.v_GS_PC_BIOS.ResourceID
image

The Result
image Copy the SQL statement, open the Config Mgr. console, create a new report and paste the SQL statement as explained in post II.

If you finish the report wizard, you will have a useful report with basic hardware information.

image Now you need to create the prompt and the select statement. We start by creating the prompt, click on the Prompts button.

In the Prompts dialog box click on the yellow starburst icon (to create a new prompt)

image In the Name column type the variable name (this is the name we will use later in the Where statement). In my example I type BiosSerialNumber

In prompt text type the text that will be displayed for the end user. I typed Select bios serial number

image Next we will provide a SQL statement which will fill the list of bios serial numbers.
Click Provide a SQL statement and click the Edit SQL Statement button
image In the SQL statement box type:
Select distinct SerialNumber0 From v_GS_PC_BIOS

Click OK

image Click OK
image Click OK
image Click Yes
image By now you should be back in the SQL statement, navigate to the end and type
WHERE     (dbo.v_GS_PC_BIOS.SerialNumber0 = @BiosSerialNumber)

Notice: You could also type @BiosSerialNumber  in the criteria column in SQL Server Management Studio.

Click OK and save the report.
image When you run the report it should look like this.
image When you click Values you will run the SQL statement from the prompt
image Click Display
image Nice report 🙂