Using the Microsoft Excel SMSVIEW Sample

This sample spreadsheet has several worksheets that analyze the SMS data. Most of the worksheets use pivot tables to analyze the data. Pivot tables are a multidimensional way to look at data. You can put any of the fields you want to analyze across the rows at the top, or drag them to the side to become the fields down the left side of the table. You can also drag fields to the left corner to become filters. You can change any of this at any time.

You can use Microsoft Excel to do any number of other analytical functions. You can collect cost information (from a MIF file) for example, then use Microsoft Excel to calculate depreciation cost for the PC inventory already in the SMS database by department.

There are worksheets that show software packages, video, BIOS, network cards, jobs, and so on. There are also some charts that show CPU and operating system distributions.

You can also use the Pivot Table Wizard to create your own worksheets, using the view tables created by SMSVIEW.

    To set up Microsoft Excel for the Microsoft Excel samples

Make sure you have completed the ODBC setup as described in General Setup for Using the SMSVIEW Samples.

    To use the Microsoft Excel sample

1. Copy SMSSDK.XLS from the SDK directory on the SMS CD-ROM to the directory on your hard disk where you want to place the sample.
2. Start Microsoft Excel, and open SMSSDK.XLS.
3. Use the tabs at the bottom of the window to go to the individual worksheets.
4. Try the pivot tables:
   1. Use the filters by using the list boxes on the spreadsheet.
   2. To see more detail on an item, double-click the cell.
   3. Try moving the fields to organize the data in different ways.
5. The pivot tables store a local copy of the data. If you want to refresh the data from the site database:
   1. Select the cells where you want to refresh data.
   2. Use the right mouse button to click the selection.
   3. Select Refresh Data.

      A message box tells you that the SQL Server login failed. This occurs because your password is different from the one used to create the pivot table.

   4. Click OK.

      The SQL Data Sources dialog box appears.

   5. From the Select Data Source dialog box, select sms data.
   6. Click OK.

      The SQL Server Login dialog box appears.

   7. In the Login ID box, type a login identifier with access to the site database.
   8. In the Password box, type the password for the login identifier.
   9. Click OK.