TFS test Case Step data statistics

Source: Internet
Author: User
Tags rowcount

The TFS System integrates a suite of BI systems, implemented based on the SQL Server Analysis service. Through these years of deep use, can feel how this data warehouse model is excellent, and Microsoft's official Data Warehouse sample AdventureWorksDW a ratio. The system itself from the basic data ETL to the data warehouse into the cube, the entire process seamless, corresponding users and system administrators, almost transparent. and custom extensions for TFS Work items are automatically supported, and users can automatically join and statistic data by adding the relevant dimensions of the work or the properties of the metric metadata in the final cub.

However, in a large customer use process, the statistics of all the test cases in TFS to complete the test steps of the data, this test step in the TFS system is not designed to be a measurable data, directly from the Data warehouse in TFS statistics, it is obviously impossible. By analysis, data for the type of Htmlfieldcontrol in TFS is stored in the Workitemlongtexts table in the Tfs_collection library, and the test steps are stored as text in the XML structure organization. So to count this data also must start from this point, through a few attempts, follow the steps below to finally count the number of test steps for each project.

1, the cross-Library query extracts all the test case-related data into a temporary table.

1

2

3

4

5

select   CWV. Projectnodename,cwv. System_assignedto, CWV. System_createddate, CWV. System_id,cwv. system_title,  wilt.words

  from   workitemlongtexts Wilt

left   join

[tfs_warehouse].[ DBO]. [Currentworkitemview] cwv  on   wilt.id = Cwv. system_id  and   CWV. System_WorkItemType =  ' test case '

where   wilt.fldid = 10181   and   wilt. EndDate =  ' 9999-01-01 '   and    SUBSTRING ( Wilt. words,1,6) =  ' <steps '

  

2, for the temporary table extracted above, created a stored procedure, the XML type of the test step data to parse, extract the step data.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21st

22

23

24

25

26

27

28

29

30

31

32

CREATEPROCEDURE[dbo].[CalcTestSetpProg]

AS

BEGIN

SETNOCOUNT ON

 --Delete illegal record

  delete[TeamProjectTestStep] whereisnull(system_id,0)=0

DECLARE@tempTABLE

(

    sys_id INT,

    word ntext

)

INSERTINTO @temp(sys_id, word ) select [System_Id],words from[dbo].[TeamProjectTestStep];

DECLARE

    @sys_id ASINT,

    @word Asnvarchar(max)

WHILE EXISTS(SELECTsys_id FROM@temp)

        BEGIN

               SETROWCOUNT 1

               SELECT@sys_id= sys_id,@word = word FROM@temp;

               BEGINTry

                UPDATE[TeamProjectTestStep] SET StepCount = Cast(@word as xml).value(‘count(/steps/step)‘,‘int‘)  WHERE[email protected]_id;

               ENDTry

               BeginCatch

               EndCatch  

               SETROWCOUNT 0  

               DELETEFROM @temp WHERE[email protected]_id;

        END

END

  

3, use the report to show

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/28076/201612/28076-20161230220910632-177065943.png "Style=" border:0px; "/>


TFS test Case Step data statistics

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.