Visual Studio Team System 2008 : Creating new report (part 1) - Report server project

6/6/2012 5:33:25 PM
Creating or customizing a new report for the project is always based on the project reporting requirement. In some cases, the existing reports may not be suitable or will not provide enough information required for the project. In TFS, all the information is stored in MS SQL Server, and it uses the SQL Reporting Service and Analysis Services for reporting purposes. With the existing installation of Visual Studio, we cannot create a new report or customize the existing report. It requires an additional tool called the Business Intelligence Development Studio, which comes along with the SQL Server 2005. There is another option in MS EXCEL, which uses the pivot tables to get data from the SQL Server databases and present it in spreadsheet. However, the following are possible if we are planning to use Visual Studio for creating reports:
  • Write your own SQL queries to get the data

  • Publish and share the report through Team Explorer

  • User can customize the report

  • High performance report

Creating a new report involves understanding the database structure and designing. Before getting into the actual design of the new report, let's look at the different databases and how the SQL Server databases are structured by TFS for storing the data. It is divided into three different stores each having its own purpose:

  • OLTP Database: The Online Transaction Processing store contains multiple databases to maintain all the transactions. It has got sets of databases for maintaining the build, version control, work item tracking, and activity logging. These are the databases tuned for high performance to save and retrieve the transactions online.

  • Relational Warehouse: This store is built for queries instead of online transactions. This database is optimized for queries and reporting instead of transactions. Data is transferred into these databases periodically using adapters collecting data from the tools like work item tracking, build, and other tools.

  • OLAP Cube: The third one is the Online Analysis Processing Database, which stores the historical data for future reference. It has its own query language. This can be maintained and accessed using the SQL Server Analysis Services.

We will be using the above database for building and designing our new report. The warehouse database is broken down into dimensions and facts, which is more important for the reports. The dimensions are the parameters, and facts are the actual values. Parameters are used for controlling the data to be fetched from the store while the facts are the actual values like the count of defects or build number or work item ID. You can explore more on these databases and tables using the SQL Server Management Studio. The TfsWareHouse is the name of the warehouse database in SQL Server.

We will start designing a new report, so let's not go into the details of the database. To get started with reports, we have to make sure we have the required tools. The following are the list of tools required:

  • The TFS installed and is available for the clients

  • SQL Server client tools containing the Business Intelligence Development Studio, which is mandatory

  • VSTS

Report server project

After installing the required tools, Visual Studio will have the Report Server Project in the Templates of the Project types list. Otherwise, we can also launch the Visual Studio using the SQL Server Business Intelligence Development Studio menu option available under the Windows Programs menu.

Select the Report Server Project, name it and select OK. The solution and project get created and the project contains two folders, Shared Data Sources and Reports. For creating the report, we need to connect to the OLTP and warehouse databases. Create or add the data sources by following these steps:

  1. 1. Select the option Add New Data Source by right-clicking the Shared Data Sources.

  2. 2. Name the data source as TfsReportDS. The name is important here as most of the reports in Team Foundation Server are using this data source name.

  3. 3. Select Microsoft SQL Server as the Type of the data source.

  4. 4. Click on the Edit... option next to the Connection String box and in the new window, select the server name from the list or enter the server name used by the TFS.

  5. 5. Select the TfsWareHouse database from the list and click on OK.

Now select the same New Data Source option for adding another data source for Online Analytical Processing (OLAP). Name the data source as TfsOlapReportDS and select the Microsoft SQL Server Analysis Services as the type of the data source. Select the same database TfsWareHouse and click OK. Now there should be two data sources, TfsReportDS.rds and TfsOlapReportDS.rds, under the Shared Data Sources folder in the solution explorer.

We have created the reporting project in Visual Studio and created the required data sources. Now we can proceed towards creating our report. Follow these steps below to create a new report:

  1. 1. Open the Solution Explorer if not opened.

  2. 2. Right click the Reports folder and select Add | New Item.

  3. 3. Select the Report template from the available templates.

  4. 4. Enter the name for the report and click OK.

Now the template is ready in the designer, but we have to edit the report and redesign it based on our requirement. There are three tabs in the designer: Data, Layout, and Preview.

  • Data: This tab is used for selecting the dimensions and the facts for the report. It also helps select the parameters and formats.

  • Layout: This is the surface for designing the report. Using the dataset built in the data tab, we can place the fields on the surface. This is also used for selecting the report format such as Tabular, Matrix, or List.

  • Preview: This tab is used to see the preview of the report we have designed using the data tab and the layout tab. This is the exact report that will be deployed or used by the user.

Let us see the steps involved in modifying the report to meet our needs in the Data tab:

  1. 1. Create a new dataset to get data from the OLAP cube. Select the<New Dataset…> option from the Dataset drop-down list.

  2. 2. Enter the name for the data set as DataSet1.

  3. 3. Select tfsOlapReportDS (shared) and then click OK.

Under the Dataset selection option we can see the Build Cube selected by default. Select the option in the cube box to open the cube selection window and select the Team System Cube, which contains all the cubes. The cube is totally based on the type of data we need for the report design. Let us select the Team System cube.

The Data tab now contains four different sections—Metadata, Calculated members, Query Results, and Dimensions or filters. The Metadata section contains all the dimensions and facts from the cube, which we will select for the report.

Now let's try building a simple defect report, which contains only three or four fields to show the details such as the bug title, the bug current status, and the status change count for each defect. We will also add parameters for data range and bug status. Follow these steps to select the dimensions and facts for the query:

  1. 1. In the Metadata tab, under Measures, we can see all the dimensions from the Team System Cube. Scroll down in the list and select the Work Item dimension and select the Work Item.Title and drag that onto the query results surface. Do the same thing for Work Item.State.

  2. 2. Lets us also try to get the history of state change counts for the defects. To do this, Expand Work Item History under Measures and select State Change Count and then drag-and-drop that onto the query results surface. Keep selecting whatever data is needed for the report.

  3. 3. Now we have selected the data to be presented on the report but not the filters. Let us select the date filter and the work item state and type as filters and parameters for filtering the data:

    a. Select the Team Project.Team Project attribute in the Team Project dimension from the list in the Metadata and drag-and-drop that into the Dimensions section. In the Filters area, select the Team Project dimension from the grid and select and choose the team project in the Filter Expressions drop-down. This will filter the displayed results.

    b. Select the Date.Date attribute under the Date dimension and drop that into the Dimension area. In the Hierarchy drop-down, select the format for the date. To have the date range, select the option Range(Inclusive) option from the drop-down under the Operator column. To make the date to be parameterized, select the check boxes against the Date dimension under the column heading Parameters.

    c. Select the Work Item.State attribute from the Work Item dimension and drop that onto the Dimension area.

    d. The next important thing is the Work Item Type. The above selections will display all the work items irrespective of their types. But we need only the bugs, not the all work items. To filter this, select the Work Item.Work Item Type from the Work Item dimension and drop that onto the Dimensions filter section. Under the Filter Expression, select the type as Bug.

  4. 4. On dropping the filters and dropping the attributes on the query results area, we can see that the query gets executed and displays the results. This is to verify whether the selection is providing the expected result.

The final Data tab of the report would look like the one shown here for the sample report:

  •  Visual Studio Team System 2008 : TFS reports for testing - Bugs
  •  Extra Network Hardware Round-Up (Part 3)
  •  Extra Network Hardware Round-Up (Part 2) - NAS Drives, Media Center Extenders & Games Consoles
  •  Extra Network Hardware Round-Up (Part 1)
  •  Networking Jargon Explained (Part 2)
  •  Networking Jargon Explained (Part 1)
  •  The Micro Revolution
  •  Computing Yourself Fit (Part 4)
  •  Computing Yourself Fit (Part 3)
  •  Computing Yourself Fit (Part 2)
  •  Computing Yourself Fit (Part 1)
  •  Touch Interaction - Multi-Touch: An Evolution
  •  Think the Brighter Side to Piracy
  •  These Companies Would Still Be Here In 5 Years
  •  Build Up Your Dream House with PC (Part 4)
  •  Build Up Your Dream House with PC (Part 3)
  •  Build Up Your Dream House with PC (Part 2)
  •  Build Up Your Dream House with PC (Part 1)
  •  2012 - The Year to Come (Part 4)
  •  2012 - The Year to Come (Part 3)
    Top 10
    Nikon 1 J2 With Stylish Design And Dependable Image And Video Quality
    Canon Powershot D20 - Super-Durable Waterproof Camera
    Fujifilm Finepix F800EXR – Another Excellent EXR
    Sony NEX-6 – The Best Compact Camera
    Teufel Cubycon 2 – An Excellent All-In-One For Films
    Dell S2740L - A Beautifully Crafted 27-inch IPS Monitor
    Philips 55PFL6007T With Fantastic Picture Quality
    Philips Gioco 278G4 – An Excellent 27-inch Screen
    Sony VPL-HW50ES – Sony’s Best Home Cinema Projector
    Windows Vista : Installing and Running Applications - Launching Applications
    Most View
    Bamboo Splash - Powerful Specs And Friendly Interface
    Powered By Windows (Part 2) - Toshiba Satellite U840 Series, Philips E248C3 MODA Lightframe Monitor & HP Envy Spectre 14
    MSI X79A-GD65 8D - Power without the Cost
    Canon EOS M With Wonderful Touchscreen Interface (Part 1)
    Windows Server 2003 : Building an Active Directory Structure (part 1) - The First Domain
    Personalize Your iPhone Case
    Speed ​​up browsing with a faster DNS
    Using and Configuring Public Folder Sharing
    Extending the Real-Time Communications Functionality of Exchange Server 2007 : Installing OCS 2007 (part 1)
    Google, privacy & you (Part 1)
    iPhone Application Development : Making Multivalue Choices with Pickers - Understanding Pickers
    Microsoft Surface With Windows RT - Truly A Unique Tablet
    Network Configuration & Troubleshooting (Part 1)
    Panasonic Lumix GH3 – The Fastest Touchscreen-Camera (Part 2)
    Programming Microsoft SQL Server 2005 : FOR XML Commands (part 3) - OPENXML Enhancements in SQL Server 2005
    Exchange Server 2010 : Track Exchange Performance (part 2) - Test the Performance Limitations in a Lab
    Extra Network Hardware Round-Up (Part 2) - NAS Drives, Media Center Extenders & Games Consoles
    Windows Server 2003 : Planning a Host Name Resolution Strategy - Understanding Name Resolution Requirements
    Google’s Data Liberation Front (Part 2)
    Datacolor SpyderLensCal (Part 1)