TFS Reports–Expose through TFS Web

If you are allowing TFS access over internet, reports will be one thing that you would find difficult to expose over internet as this might sit in your SQL server.

What about the web access? Where is report page on team foundation web access? There is none!

So, What you could do is, move the SSRS service to the Web Server or a new server hosted outside.

Both requires IT’s involvement and support. No one would be ready to move the DB server out or even SSRS server out of the firewall if it’s a shared server. If you have your other reports than the TFS reports hosted on this server, this might not be received well from the IT.

So to solve this, I created a new page in TFS and exposed all the reports from the TFS web page. Which just embeds the report viewer control and renders the report on the TFS webpage.

Here are list of changes you will have to do for implementing this reporting plug-in.

• Open C:\Program Files\Microsoft Team Foundation Server 2010\Application Tier\Web Access\Web\UI\Controls\PageHeader.ascx. And insert a new row

<table cellspacing="0" cellpadding="0" class="mainTab">
    <tr>
       <td class="<%=(Request.Url.AbsolutePath.Contains("/UI/Pages/Reports/Reports.aspx") || Request.Url.AbsolutePath.Contains("/UI/Pages/Reports/Viewer.aspx") ? "att" : "ptt")%>">
            <a class="<%=(Request.Url.AbsolutePath.Contains("/UI/Pages/Reports/Reports.aspx") || Request.Url.AbsolutePath.Contains("/UI/Pages/Reports/Viewer.aspx") ? "att" : "ptt")%>"
            href="/tfs/web/UI/Pages/Reports/Reports.aspx?pguid=<%=this.Locator.ProjectUri.Segments[3] %>"><span class="ptt">Reports</span></a>
       </td>
       <td></td>
     </tr>                                     
 </table>

• Create a project visual studio, ASP.Net Web page project. Create a new page and call it as Reports.aspx. In this page we will list all the reports and give a link to access all the reports.



• Open the reports.aspx and add a data list control to it. Your mark up should look like
  
  
  

  <asp:Content ID="c" ContentPlaceHolderID="c" runat="server">
      <asp:DataList ID="ReportList" runat="server">
          <itemtemplate> 
          <td>
          <asp:HyperLink ID="reportLink" runat="server">
          <%
     1: # DataBinder.Eval(Container.DataItem,"Name") 
  %> 
          </asp:HyperLink>
          </td>
             </itemtemplate>
      </asp:DataList>
  </asp:Content>

  
  
  
  



• 
  
  Change the base page to WebAccessPage, this is how you access the TFS connection details that are buried in the base page.
  
  



• 
  
  
  

  public partial class Reports : WebAccessPage

  
  
  
  



• 
  
  In the code behind, In page initialization set the current tab and subscribe to list binding event.
  
  



• 
  
  
  

  
  

  
  

  protected override void OnInit(EventArgs e)
  {
      base.OnInit(e);
      ActiveTab = "Reports";
      ReportList.ItemCreated += new DataListItemEventHandler(ReportListItemCreated);
  }

  
  

  
  

  
  
  
  



• 
  
  
  

  
  

  On Load method, connect to the reports server and fetch all the reports using reporting services. Before adding the code, add a service reference to the  SSRS service by Project->Add Service Reference –> URL as http://sqlserver:5050/ReportServer/ReportService2010.asmx. I have given the name for the service reference as ReportingService2010.
  
  

  
  

  protected void Page_Load(object sender, EventArgs e)
  {
      ReportList.DataSource = GetProjectReports(this.Connection.CollectionName + "/" + this.CurrentProject.Name); ;
      ReportList.DataBind();
  }
  
  
  private ICollection<CatalogItem> GetProjectReports(string path)
  {
      WindowsImpersonationContext ctx = WindowsIdentity.Impersonate(IntPtr.Zero);
      CatalogItem[] reports = null;
      try
      {
          ReportingService2010 reportService = new ReportingService2010();
          // get the URL from the config http://sqlserver:5050/ReportServer
          reportService.Url = config.ReportServerURL + "//ReportService2010.asmx";
          reportService.UseDefaultCredentials = true;
          reports = reportService.ListChildren(path, true);
      }
      finally
      {
          ctx.Undo();
      }
      return reports;
  }
  

  
  

  
  

  
  

  
  
  
  



• 
  
  
  

  
  

  
  

  Assumption above is, workerprocess is running under the identity which has access to reporting server. So we are impersonating to default and reverting back while connecting to SSRS.

  
  

  
  

  
  
  
  



• 
  
  
  

  
  

  
  

  Now we need to make all the report list as hyper links with proper navigation set, in the item created event of the list we would implement this.
  

  
  

  void ReportListItemCreated(object sender, DataListItemEventArgs e)
  {
    CatalogItem currItem = (CatalogItem)e.Item.DataItem;
  
    HyperLink link = (HyperLink)e.Item.FindControl("reportLink");
  
    if (currItem.TypeName == "Folder")
    {
        link.Attributes.Add("style", " color:Gray; font-size:16px");
        link.Enabled = false;
    }
    else if (currItem.TypeName == "Report")
    {
        e.Item.Enabled = true;
        link.Attributes.Add("style", "font-size:13px; padding-left:30px;");
        link.NavigateUrl = base.ResolveUrl("~/UI/Pages/Reports/Viewer.aspx") + "?pguid=" + this.Locator.ProjectUri.Segments[3] + "&rep=" + currItem.Path;
    }
    else
    {
        link.Visible = false;
        e.Item.Visible = false;
    }
    link.Text = currItem.Name;
  }

  
  

  
  

  
  

  
  

  
  
  
  



• 
  
  
  

  
  

  
  

  
  

  We don’t have the report viewer implemented yet, so lets create a new page called viewer.aspx and add report viewer control to it. Mark up would look like
  

  
  

  <%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
      Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>
  
  <asp:Content ID="c" ContentPlaceHolderID="c" runat="server">
      
      <rsweb:ReportViewer EnableViewState="true" AsyncRendering="true" ID="ReportViewer" runat="server"
       Width="100%" Height="100%" 
       BackColor="#f8f8f8" ForeColor="#555555" InternalBorderColor="#cccccc"
       BorderColor="#dddddd" BorderStyle="Solid" BorderWidth="1px"
       ProcessingMode="Remote">
      
      </rsweb:ReportViewer>
  </asp:Content>

  
  

  
  

  
  

  
  

  
  

  
  
  
  



• 
  
  
  

  
  

  
  

  
  

  
  

  In  the code behind,  binding to the control is straight forward once we have the path
  

  
  

  public partial class Viewer : WebAccessPage
  {
      protected override void OnInit(EventArgs e)
      {
          base.OnInit(e);
          ActiveTab = "Reports";
          Page.EnableViewState = true;
          Master.EnableViewState = true;
          ReportViewer.ReportError += new Microsoft.Reporting.WebForms.ReportErrorEventHandler(ReportViewerReportError);
      }
  
      void ReportViewerReportError(object sender, ReportErrorEventArgs e)
      {
          if (TimeSheetHelper.IsUserAdministrator(Request.LogonUserIdentity.Name))
          {
              Response.Write(e.Exception.ToString());
          }
          else
          throw new Exception("Report exception", e.Exception);
      }
  
      protected void Page_Load(object sender, EventArgs e)
      {
          if (!IsPostBack)
              LoadReports(Request.QueryString["rep"]);
      }
  
      private void LoadReports(string reportPath)
      {
          ReportViewer.ServerReport.ReportServerCredentials = new ReportServerCredentials(CredentialCache.DefaultCredentials);
          ReportViewer.ServerReport.ReportPath = reportPath;
          ReportViewer.ServerReport.ReportServerUrl = new Uri(config.ReportServerURL );
          ReportViewer.ServerReport.Refresh();
      }
  }

  
  

  
  

  
  

  
  

  
  

  
  

  
  
  
  



• 
  
  
  

  
  

  
  

  
  

  
  

  That’s it! You should be able to see the pages something like this..
  
  
  

  
  

  
  

  
  

  
  

  
  
  
  
  
  
  
  
  
  

Timesheets with TFS Continued…

So what were those screen shots?

Lets look at them again.

Screen shot is for TFS 2010, and what is that link saying manage timesheet? and I don’t get the reports tab!

Ok, Lets just talk about Timesheet tab, I will write about reports later. Its one more plugin. I will talk about the problem and solution that tab is catering to in next post.

Back to timesheet.

I wouldn’t want do discuss the steps to make it 2010 compatible, overall this requires some fixes in the master pages, content pages and javascript object model to make it to work. 

For the plug-in to work with multiple collections and projects, I created a table for collections tblCollections. And one more table for Projects. Here are some of the columns

• ID -  called projectcollectionid as this is the combination of project id and collection id.
• Project Name – Same as the TFS Project Name
• Project Manager -  TFS project can have multiple admins, but in most of the cases there is a single manager who manages the tasks and can approve the timesheets.
• Team Size – just a metadata information
• CollectionId – so that you could map with collections
• PGUID – project GUID that TFS maintains, can be handy in reports.

The link you see the screenshot above is admin link available only for super administrator who activates the timesheet feature for a project. This is the time when you capture most of the project metadata information. This is how this new screen looks.

I have used entity framework(.Net 4.0) as my ORM Data access tier. So my collection id determination looks like

public static int GetProjectCollectionId(string ProjectName, string collectionName)
     {
         using (TfsTimesheetEntities ctx = new TfsTimesheetEntities())
         {
             var project = ctx.Projects.Where(x => x.Collection.CollectionName == collectionName && x.ProjectName == ProjectName).FirstOrDefault();
             if (project != null)
             {
                 return project.Id;
             }
         }

         return 0;
     }

Similarly, using the project manager id we could determine if the logged in user is Project admin using following code

public static bool IsProjectAdmin(string logon, int projectCollectionId)
        {
            if (IsUserAdministrator(logon))
                return true;

            using(TfsTimesheetEntities ctx = new TfsTimesheetEntities())
            {
                var admin = ctx.Projects.Where(x => x.Id == projectCollectionId).FirstOrDefault();
                if (admin != null)
                {
                    if (String.Compare(logon, admin.ManagerId.Trim(), true) ==0)
                    {
                        return true;
                    }
                }
            }

            return false;
        }

While rendering the timesheet plugin, to determine if we have to enable any of the admin features, you call the above method to see if user is a project manager for this project.

            if (TimeSheetHelper.IsProjectAdmin(Request.LogonUserIdentity.Name, this.ProjectCollectionId))
            {
                toolbar.Items.Add(new Microsoft.TeamFoundation.WebAccess.Controls.MenuItem(true));
                toolbar.Items.Add(new Microsoft.TeamFoundation.WebAccess.Controls.MenuItem("Admin", "admin", ResolveUrl("~/Resources/Images/admin.gif")));
                toolbar.Items[toolbar.Items.Count - 1].Action = "parent.location='Admin.aspx?pguid=" + this.Locator.ProjectUri.Segments[3]+"'";
            }

Now, bigger part of the work remains is in the database side.

• First you will have to include the project collection id in all calls. So that reference to all entries are related to project collection ids and not to project ids.



• Next, you will have to pass the collection name in all the SPs that makes a cross DB joins to connect to appropriate database. For example…

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS(SELECT * 
            FROM sys.objects 
            WHERE object_id = OBJECT_ID(N'[dbo].[usp_RS_AwaitingApproval]') 
                  AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[usp_RS_AwaitingApproval]
GO
CREATE PROCEDURE [dbo].[usp_RS_AwaitingApproval]
     @collectionName nvarchar(100),
    @projectCollectionId int
AS
BEGIN
    SET NOCOUNT ON
        
    DECLARE @statement nvarchar(1000) = N'SELECT tc.sid, DisplayPart AS displayName, weekStarting
    FROM TimesheetCompletions tc WITH(NOLOCK)
    JOIN TFS_'+ @collectionName +'.dbo.Constants WITH(NOLOCK)
    ON tc.sid = Constants.sid
    WHERE completed = 1 AND 
    tc.ProjectCollectionId ='+ CAST(@projectCollectionId as nvarchar(8)) +' AND
    approved IS NULL'

    EXEC(@statement)

END
GO

Above snippet connects to TFS_<CollectionName> db to get he usernames.

This must have given you an idea on how the plugin can be extended to serve multiple collections. Next part you will be interested in is getting report of who has filled timesheet in your organization. I used SSRS report, following is the query that I used to get the result.

SELECT prj.id, 
        MAX( [ProjectName]) AS ProjectName,
        MAX(ManagerName) AS ManagerName, 
        MAX(WeekStarting) AS WeekStarting, 
        dbo.TFSGetUserName(tc.sid) as DisplayPart, -- this is function i created to get the username 
                                                   -- refer to the constants table easily. 
        Completed as EnteredTimeSheet,
        (approved )as Closed,
        MAX(prj.TeamSize) as TeamSize,
        SUM(te.hours) as HoursLogged
        FROM timesheetcompletions tc
JOIN Projects prj ON tc.projectCollectionId=prj.id
JOIN TimesheetEntries te on tc.sid = te.sid and tc.ProjectCollectionId = te.ProjectCollectionId
AND (te.entryDate > tc.weekStarting AND te.entryDate < DATEADD(DAY,7, tc.weekStarting ) )
where DATEADD(dd, 0, DATEDIFF(dd, 0, WeekStarting)) =DATEADD(dd, 0, DATEDIFF(dd, 0, @Startdate))
GROUP BY prj.id
, tc.sid

Report would look something like…

There is another interesting report involved, that is to figure out which contributor hasn’t filed his/her timesheet. Which is bit complex report compared to this one, I will write about it some other time.

Timesheets with TFS

If you haven't taken look at http://tfstimesheet.codeplex.com/ already, please take a look at it. It’s a pretty interesting plugin developed and quite useful. If you are an organization who is worried about daily utilization and derive billing out of it, its pretty handy and solves many of the problems.

If you are thinking why do I need this, when I can update the task directly – here is the answer

• People are lazy – its not possible to expect everyone to update all the tasks they worked on, on a daily basis. If they miss one day; its not present in the TFS Cube daily snapshot.
• If you are working on say 5 generic tasks(say daily standup, code reviews, helping QA etc) on regular basis; its pretty hard to open all talks and update the numbers.
• No approvals – If the time logged is important for your business, like billing; you definitely want to have an step to review and approve the time logged.

All these problems can be addressed by a simple table view of the tasks and days, something like this.

Now its easy, you can enter effort for any day for the week, multiple tasks in one go, one single view.

All right, that answers all the problems we talked about.

But, but, this plugin

• Doesn’t work with TFS 2010 server
• Doesn’t support working with multiple projects and collections
• Doesn’t support approvals by projects(i.e. having one PM for each project)
• Doesn’t come with reports

So how do you solve? Read on…

Using Client Reporting to generate PDFs on the fly

Objective: To create PDF file(or any type supported by RDLC) from an ASP.Net(any .net) application from a in-memory dataset.

First, lets create an ASP.Net application using the new Project creation wizard.

Add a new project item and chose Report(report1.rdlc)

What we need now is a dataset, which we can use in reports. To do this, lets add a new item – dataset.

lets name it as MyDataset.XSD. Lets open this dataset and add a table using the toolbox. Lets name it as DataTable1 and lets add two columns here Email and User. Lets compile this once so that we generate the dataset model.

Next step is to use this in our reports. Lets open the report designer(double click the RDLC added earlier) and add a DataSet to this. On report data panel, say New->Data Set.

Now select the Mydataset as the data source and the default table as dataset. Now we have Email and User as columns. Lets leave the name as DataSet1. Now its ready to be used in the report.

If everything goes right, you can add a tablix and associate the DataSet1 with it. Now you should see the fields available for using in reports.

I have mapped user and Email fields in the report.

Now lets focus back to our code. Open the markup and drop a report viewer control. This should look something like this.

 
    <rsweb:ReportViewer ID="repViewer" runat="server">
    </rsweb:ReportViewer>

Lets prepare the data to fill the report data using the C# code. We can do this in two ways.

1. Use the same dataset(MyDataSet)



2. Create a new dataset on the fly.

Lets try the second approach. On the code behind, lets create a data set and add a table to it and add some data.

            DataSet ds = new DataSet();
            DataTable dt = new DataTable();

            dt.Columns.Add("User");
            dt.Columns.Add("Email");


            dt.Rows.Add("Guru", "Guru@aa.com");
            dt.Rows.Add("Guru", "Guru@aa.com");
            dt.Rows.Add("Guru", "Guru@aa.com");
            dt.Rows.Add("Guru", "Guru@aa.com");
            ds.Tables.Add(dt);

Now, lets bind this to the report.

repViewer.Reset(); // follow the same order, reset and then add the data   repViewer.LocalReport.ReportPath = "Report1.rdlc";
            
reportDataSource rs = new ReportDataSource();
rs.Name = "DataSet1"; // Important: Use the same name as given in the report.
rs.Value = ds.Tables[0];
            
repViewer.LocalReport.DataSources.Add(rs);

Finally, rendering part. Lets return the response as a PDF file

string mimeType;
string encoding;
string extension;
Warning[] warnings;
string[] streamids;

byte[] reportBytes = repViewer.LocalReport.Render("PDF", null,
 out mimeType, out encoding,
 out extension, out streamids,
 out  warnings);

Response.ClearContent(); Response.ClearHeaders();
Response.AddHeader("Content-Disposition", "inline; filename=Test.pdf");
/ Response.AddHeader("Accept-Header", reportBytes.Length.ToString());
Response.ContentType = "application/pdf";
Response.OutputStream.Write(reportBytes, 0, reportBytes.Length);
Response.Flush();
Response.Close();

Only advantage of creating the dataset using XSD(MyDataSet) is that you can use them in the report directly by selecting the element. This will help you a lot while binding. You can also connect to SQL and build your data set and use it for report design. Later you can build your own dataset like we have done and use it by providing the same name.Following SQL statement will do the job for you.

Select 'Guru' as User, select 'Test' as Email

Screen shot below is the output, Pdf displayed in browser embedded. This approach will be a great help if you have specify requirement for printing involving the layout, breaks and header/footer.

SSRS Record & Play testing

In my previous article I wrote about SSRS performance measurement. This time we will take a look at how we can make use of this data for our performance testing itself.

To test we would need the report, report path and the parameters; all of these we can get from the report server itself

Select catalog.name, [Catalog].Path,
ExecutionLog.Parameters
from ExecutionLog with (nolock)
inner join
 Catalog on Catalog.ItemID= ExecutionLog.ReportID

This is like our manual tests being recorded, lets see how we can use this for replaying this rest.

As you know, we can access SSRS reports directly from  the server using reportServer web service URL. Using this URL we can pass query strings too!

<iframe height="100px" width=100%" src="http://172.16.150.29/ReportServer/Pages/ReportViewer.aspx?#ReportPath/ReportName#&amp;rs:Command=Render&amp;#Parameters#"/>

Use excel to import the results and use =CONCATENATE(A2,B2,C2,D2,"""/>")  in excel to generate this HTML code. Now copy these line and place it between HTML tags and your page which can open all reports in one go is ready.

Open this HTML page from multiple machines or tabs; so that it generates sufficient load. Use report server ExecutionLog view to measure the performance of your test.

How bad is your SSRS report performance?

In SSRS, the execution data is stored in a table called ExecutionLogStorage table in ReportServer database. You can view this data using the views provided by SSRS named ExecutionLog3, 2 and ExecutionLog. All these views read the data from same table, with only difference is that they interpret data and give you different views.

Following query will give you data on which report sucks.

use ReportServer
go
-- overall suckers
Select Catalog.Name,
TimeDataRetrieval+TimeProcessing+TimeRendering as TotalTime,
 ExecutionLog.Format, ExecutionLog.[Parameters],  
 ExecutionLog.TimeDataRetrieval,
 ExecutionLog.TimeProcessing, TimeRendering,
 TimeStart, TimeEnd, Status, ByteCount, ExecutionLog.[RowCount]
from  ExecutionLog with (nolock) 
inner join Catalog on Catalog.ItemID= ExecutionLog.ReportID
order by TotalTime DESC

-- Query sucks
Select  Catalog.Name,
TimeDataRetrieval+TimeProcessing+TimeRendering as TotalTime,
 ExecutionLog.Format, ExecutionLog.[Parameters],  
 ExecutionLog.TimeDataRetrieval,
 ExecutionLog.TimeProcessing, TimeRendering,
 TimeStart, TimeEnd, Status, ByteCount, ExecutionLog.[RowCount]
from ExecutionLog with (nolock) 
inner join Catalog on Catalog.ItemID= ExecutionLog.ReportID
order by TimeDataRetrieval DESC

-- SSRS Processing sucks
Select  Catalog.Name,
TimeDataRetrieval+TimeProcessing+TimeRendering as TotalTime,
 ExecutionLog.Format, ExecutionLog.[Parameters],  
 ExecutionLog.TimeDataRetrieval,
 ExecutionLog.TimeProcessing, TimeRendering,
 TimeStart, TimeEnd, Status, ByteCount, ExecutionLog.[RowCount]
from ExecutionLog with (nolock) 
inner join Catalog on Catalog.ItemID= ExecutionLog.ReportID
order by TimeProcessing DESC

-- SSRS Rendering sucks
Select  Catalog.Name,
TimeDataRetrieval+TimeProcessing+TimeRendering as TotalTime,
 ExecutionLog.Format, ExecutionLog.[Parameters],  
 ExecutionLog.TimeDataRetrieval,
 ExecutionLog.TimeProcessing, TimeRendering,
 TimeStart, TimeEnd, Status, ByteCount, ExecutionLog.[RowCount]
from ExecutionLog with (nolock) 
inner join Catalog on Catalog.ItemID= ExecutionLog.ReportID
order by TimeProcessing DESC

Very good UML Video tutorial

A very nice UML Video tutorial to start with.

Tutorial starts with basics and goes all the way. I would recommend you downloading Visual Paradigm for UML along http://www.visual-paradigm.com/product/vpuml/. With this tutor you can learn both UML as well as the tool.  VP can do a lot more, but very good start for both UML and visual paradigm is this.

Special thanks to PatriaLivreBrasil.

*don’t go by the first few videos, it might look slow and boring. Rest of the tutorials are really good.