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. 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. 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. 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. 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.
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.
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:
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. Open the Excel workbook for creating the report and select the Data menu and select Get External Data.
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. 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.
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.
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.
