Visual Studio Team System 2008 : Creating new report (part 2)

6/6/2012 5:34:43 PM
We have the dataset and parameters ready for the report. Now is the time to design the UI for the report and place the fields for the report. To begin this, select the Layout tab in the report designer. Keep the Datasets explorer and the Toolbox open. The layout shows the report surface to drop the fields from the dataset. To design the report:
  1. 1. Select the format from the Toolbox such as Table, Matrix, or List and drop it on the report surface. There are other options like Subreport or Chart.

  2. 2. Now drag-and-drop the fields from the Datasets onto the report. Place the controls from the toolbox and design the report as required. The report layout should be similar to the one shown as follows:

The dataset is ready and report layout is ready. Now, we can check the preview and finalize the report to see if it is as per expectations. We can go back to the layout or data tabs anytime and modify the report. The Preview tab shows the parameters for selection. This is based on the Parameters checkbox selection against the filter expression in the Data tab. Once you select the parameters and click on View report, you can see the actual report generated. The final report preview would be similar to the one shown next. The preview also has the option to print, export to a file in different formats, or refresh the report.

The report is now complete and verified, and is a standalone report now. It has to be shared with the other users. For this, the report should be published and made available along with the other reports. Before deploying the report, the report project properties for deployment should be set with the team project and the report server URL as shown in the following screenshot:

Build the report and deploy it using the Deploy option under the Build menu. Wait for the deployment succeeded message and then open the URL to which the report is deployed. As per the above example, it should be at the URL as follows:

When you click the report name SampleDefectReport, it will open the report, which is the same as the one we saw in the Preview tab of the report designer.

We can add the report to the Team Foundation Process template so that the other team projects can make use of the new report. This can be done by following the steps as shown:

  1. 1. Select the menu option Process Template Manager under Team | Team Foundation Server Settings menu in Visual Studio and select the process you would like to add to the report. Download the process template to your local machine.

  2. 2. Open the downloaded process template file using Visual Studio menu option Tools | Process Editor | Process Templates | Open Process Template and select the Reports from the Methodology tree view. Upload the SampleDefectReport.rdl file that we created and save that file after providing the name and the data sources.

  1. 3. Open the Process Template Manager and upload the modified Process template file using the Upload option in the Process Template manager. Now the process template is uploaded to the TFS.

  2. 4. Create a new team project and check the reports. The list should have the new report SampleDefectReport in the list as shown here with the highlight report:

Create a new report in Excel

In the above section, we have seen how to create reports using Visual Studio and SQL Server Business Intelligence Studio. Here, we will look at how to create reports using Microsoft Excel. Reporting is not limited to Excel and Visual Studio, but we can use any tool that can create the report and access the SQL Server database. Using Excel, we can create Pivot Table and Pivot Chart and pull the data from the TFS data warehouse. Once we create the Pivot Table, we can customize the report based on the columns present in the data warehouse, organize the table and the calculations that the table should perform, and we can even manipulate the columns and the rows in the Pivot Table that we have created.

To get connected to the SQL Server database and the TFS data warehouse, the user must have enough access to read the data from the warehouse to use the Excel report. The user should be a member of the TfsWarehouseDataReaders security role in the Analysis Services, and the member will have server-wide privileges and can perform any task within the instance of the Analysis Services.

The prerequisites are:

  • Microsoft Excel

  • Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider

  • User should be member of the TfsWareHouseDataReaders security role

Creating the Pivot Table and placing the field is very simple in Excel. The following are the steps to create report in MS Excel 2007:

  1. 1. Open the Excel workbook for creating the report and select the Data menu and select Get External Data.

  2. 2. Select From Other Sources | From Analysis Services, which will display the Data Connection Wizard. In the Data Connection Wizard, Connect to Server, enter the name of the SQL Server name used by the TFS in the Server Name text box.

  3. 3. Click Next to get the dialog Data Connection Wizard, Select Database and Table. Select the TfsWareHouse database from the drop-down and select the Team System cube from the list.

  1. 4. Click Next and select finish in the dialog window Data Connection Wizard Save Data Connection File and Finish, which opens the Import Data dialog window. Select Pivot Table Report and click OK, which displays the Pivot Table fields list.

  2. 5. Select a measure group and then select a measure for that particular measure group. From the Pivot Table fields list pane, select necessary fields and drag it to the row labels box and column labels box. To filter the report, select the fields and drag it to the Report Filter box. Repeat this step until we have selected all the required fields in the corresponding columns and rows.

Now the report is ready in Excel. It can be saved in normal XLS, XML, or in any other supported file format.

  •  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)