November 10, 2009

SQL Server Reporting Services Data Models

Surround SCM

Works with TestTrack 2009 and later Works with Surround SCM 2010 and later

Report models organize database tables and views in a way that allows users to create reports without understanding queries or the physical database structure. Report models can also control the available data for reporting. These models are used in conjunction with the SQL Server Reporting Services plug-ins.
Download Models

Installing the TestTrack Models

The following SSRS report models (SMDL files) are provided for reporting against TestTrack projects:
  • TestTrack_CoreItem_Report_Model_dsv.smdl — Used for creating reports that include information about defects, test cases, test runs, requirements, requirement documents, and workflow events.
  • TestTrack_Administration_Report_Model_dsv.smdl — Used for creating reports that include information about workflow, automation rules, field relationships, security groups, and users.
Before the report models can be used, new views must be added to the TestTrack database using the SQLScriptToAddViews.sql script. Note: The provided report models are required only if you are using the sample reports provided by Seapine. Your organization may decide to create new report models or modify the existing models to expand or restrict data access.
  1. Log in to SQL Server.
  2. Add views to the TestTrack database using the SQLScriptToAddViews.sql script.
  3. Open the SQL Server Report Manager in a Web browser.
    Use the following URL: http://<SQL Server name>/<SQL Report Manager virtual directory name>. For example, http://localhost/reports.
    Tip: If you do not know the Report Manager URL, you can look it up in the Reporting Services Configuration Manager. In SQL Server 2005, the URL is displayed in the Report Server Virtual Server Directory category. In SSRS 2008, the URL is displayed in the Web Service URL category.
  4. Click Upload File.
  5. Click Browse.
  6. Browse to the directory that contains the TestTrack_CoreItem_Report_Model_dsv.smdl file, select the file, and click Open.
  7. Optionally enter a different Name.
  8. Click OK to upload the file.
  9. Repeat steps 5 through 8 to upload the TestTrack_Administration_Report_Model_dsv.smdl file.

Configuring Models

The following instructions use the TestTrack data models, the same process can be used to configure the Surround SCM models. Report models must be associated with a data source. Data sources contain the TestTrack SQL Server database connection information.
  1. On the SSRS home page, click New Data Source.
  2. Enter a Name and Description.
  3. Select Microsoft SQL Server as the Connection type.
  4. Enter the following Connection string to the TestTrack database: Data Source=<SQL Server name/instance name>;Initial Catalog=<TestTrack database name>
    For example, if SQL Server is running on a computer named WYSICORPDEV and the TestTrack SQL Server database is named Development, the connection string is: Data Source=WYSICORPDEV;Initial Catalog=Development.
    Note: You do not need to include an instance name if the projects are in the default instance.
  5. Select Credentials stored securely in the report server and enter a User name and Password. The user name and password are stored on the report server. Optionally select Use as Windows credentials when connecting to the data source if the user name and password are Windows authentication credentials.
  6. Click OK to save the data source.
  7. On the SSRS home page, click the TestTrack_CoreItem_Report_Model_dsv report model.
  8. Click Data Sources in the left pane.
  9. Click Browse.
  10. Select the data source you created and click OK to associate it with the report model.
  11. Repeat steps 8 through 10 to associate the TestTrack_Administration_Report_Model_dsv report model with the data source.