How to use with BIDS AKA How to get at the TimeSheet Data with a Query to the Database

Oct 22, 2012 at 5:47 PM
Edited Oct 22, 2012 at 10:48 PM

Is  there any way to use BIDS to access the TimeSheetRawData?

I can not find a way to access this data from within bids using the TfsOlapReportDS. 

It is quite ok if all it returns is XML... I just need to be able to get at that XML somehow.

 

Any help to achieve this would be greatly appreciated.

 

Thanks,

 

Dave

Oct 23, 2012 at 8:20 PM
Edited Oct 23, 2012 at 8:20 PM

Within your SQL Server you will find 4 Tfs_   databases  one of which was named by your company.

So I have:

Tfs_CompanyName

Tfs_Configuration

Tfs_Content

Tfs_Warehouse

 

if you run the following

SELECT *
  FROM [Tfs_CompanyName].[dbo].[Fields]
  where Name like '%timesheet%'

you will find one row that has TimesheetRawData  in the [Name] field.   Look at the [ColName] field and there will be something like  Fld10110

Take that and put it into this query

SELECT *
  FROM [Tfs_CompanyName].[dbo].[WorkItemLongTexts]
  where FldID = 10110

This will allow you access to ALL timesheet entries.

I have not related these back to projects yet but I expect to soon.

Nov 20, 2012 at 12:27 PM

Recently I run into this.. may be some views from my DB will help - remember to change FldID (it is different for every project DB)

 

create view [dbo].[tfs_timesheet_bugs_effort]
as

select w.[Changed Date],
       w.Fld10008 as effort,
       c.DisplayPart as name,
       w.Title,
       l.Words as [description]
  from [dbo].[WorkItemsAre] w
  join dbo.Constants c
    on w.[Assigned To] = c.ConstID
  join dbo.WorkItemLongTexts l
    on l.ID = w.ID
   and l.WordsDocumentType = '.htm'
   and l.FldID = 10007
 where w.[Work Item Type] = 'Bug'
   and w.Fld10008 > 0

go


create view [dbo].[tfs_timesheet_data]
as

select addeddate, 
       fldid, 
	   id, 
	   words, 
	   [changed order], 
	   rev, 
	   cast(words as xml) as xmlwords
  from dbo.workitemlongtexts as wilt
 where (fldid = 11032) 
   and (rev =
       (
		   select max(rev) as rev
			 from dbo.workitemlongtexts
			where (id = wilt.id) and (fldid = 11032 )) 
		)
		



GO




CREATE view [dbo].[tfs_timesheet_raport_all]
as
     
      select t.TimeSheetEntryCreatedDate as [date],
             t.TimeSheetEntryCreatedBy as name,
             t.Title,
             
             t.TimeSheetEntryComments,
             t.TimeSheetEntryMinutes / 60.0 as effort,
             'tasks' as [type],
             t.[description]
        from [dbo].[tfs_timesheet_raport] t
   union all
      select be.[Changed Date] as [date],
             be.name as name,
             be.Title,
             
             '',
             be.effort as effort,
             'bug' as [type],
             be.[description]
        from [dbo].[tfs_timesheet_bugs_effort] be


go



CREATE view [dbo].[tfs_timesheet_raport_sprint]
as

    select top 100
           s.name,
           s.effort,
           s.bug_effort,
           s.tasks_effort,
           s.day_effort,
           t.effort as last_week_effort,

           t.bug_effort as last_week_bug_effort,
           t.tasks_effort as last_week_tasks_effort,

           t.day_effort as last_week_day_effort
      from
          (
                  select t.name,
                         sum(t.effort) as effort,
                         sum(case when t.type = 'bug' then t.effort else 0 end) as bug_effort,
                         sum(case when t.type = 'tasks' then t.effort else 0 end) as tasks_effort,
                         sum(t.effort) / DATEDIFF(day, [dbo].[tfs_actual_sprint_start](), [dbo].[tfs_actual_sprint_end]()) as day_effort
                    from [dbo].[tfs_timesheet_raport_all] t
                   where t.date >= [dbo].[tfs_actual_sprint_start]()
                     and t.date <= [dbo].[tfs_actual_sprint_end]()
                group by t.name
          ) s
 left  join
          (
                  select 
                         t.name,
                         sum(t.effort) as effort,
                         sum(case when t.type = 'bug' then t.effort else 0 end) as bug_effort,
                         sum(case when t.type = 'tasks' then t.effort else 0 end) as tasks_effort,
                         sum(t.effort) / DATEDIFF(day, [dbo].[tfs_actual_sprint_start](), [dbo].[tfs_actual_sprint_end]()) as day_effort
                    from [dbo].[tfs_timesheet_raport_all] t
                   where datediff(day, getdate(), t.date) > -8
                group by t.name
          ) t
       on s.name = t.name
order by s.effort desc



GO

create view [dbo].[tfs_timesheet_raport_sprint_before]
as

  select top 100
         t.name,
         sum(t.effort) as effort,
         sum(t.effort) / 5 as day_effort
    from [dbo].[tfs_timesheet_raport_all] t
   where t.date >= [dbo].[tfs_last_sprint_start]()
     and t.date <= [dbo].[tfs_last_sprint_end]()
group by t.name
order by sum(t.effort) desc


go


create view [dbo].[tfs_timesheet_task_alone]
as




     select w.[Changed Date],
                  w.Fld10000 as effort,
                  c.DisplayPart as name
            from [dbo].[WorkItemsAre] w
            join dbo.Constants c
            on w.[Assigned To] = c.ConstID
        left join dbo.LinksAre l
            on l.TargetID = w.ID
            where w.[Work Item Type] = 'Task'
            and l.TargetID is null
            and w.Fld10000 > 0



ALTER function [dbo].[tfs_actual_sprint_start]() returns datetime
as
begin

declare @date datetime

select @date = w.start_date from
(
    select row_number() over(partition by structure_type order by finish_date desc) as lp,
            name,
            start_date,
            finish_date
        from [dbo].[tbl_nodes]
    where charindex('\eMatura\Iteration\Release', [path]) > 0
        and finish_date is not null
) w
where w.lp = 1 

return @date

end

ALTER function [dbo].[tfs_actual_sprint_start]() returns datetime
as
begin

declare @date datetime

select @date = w.start_date from
(
    select row_number() over(partition by structure_type order by finish_date desc) as lp,
            name,
            start_date,
            finish_date
        from [dbo].[tbl_nodes]
    where charindex('\eMatura\Iteration\Release', [path]) > 0
        and finish_date is not null
) w
where w.lp = 1 

return @date

end


ALTER function [dbo].[tfs_last_sprint_end]() returns datetime
as
begin

declare @date datetime

select @date = w.finish_date from
(
    select row_number() over(partition by structure_type order by finish_date desc) as lp,
            name,
            start_date,
            finish_date
        from [dbo].[tbl_nodes]
    where charindex('\eMatura\Iteration\Release', [path]) > 0
        and finish_date is not null
) w
where w.lp = 2

return @date

end

Jan 29, 2013 at 12:51 PM

Thanks for the queries. Where tfs_timesheet_raport table/view comes from?

Dec 7, 2015 at 8:23 PM
Hey,Do you have any process about getting the data into the query designer for reports?