Using Fields for Reporting

  • Article

This topic describes how to make work item fields available in the data warehouse so that they can be used to create reports.

Reportable Attribute Values

Some field values are especially useful for reporting. By using the work item type definition language, you can specify an optional attribute, reportable, for fields that should be used for reporting. The reportable attribute has the following effects:

  • Data from fields that have the reportable attribute will be exported to the data warehouse and can be included in reports.

  • If you do not specify the reportable attribute for a newly defined field, the field data will not be exported to the data warehouse.

  • If you do not specify the reportable attribute for an existing field, the field’s report setting remains unchanged from its existing setting.

  • Once the reportable attribute is specified for a field, it cannot be changed.

The reportable attribute takes one of three values, dimension, detail, and measure, as described in the following sections.

Note

You can make a field reportable after it has been used for a work item. After setting the reportable value, new revisions of the work item that are copied to the warehouse will contain the field value. However, the revisions already in the warehouse will not be backfilled with the existing values.

Dimension

Use the dimension type only for Integer, Double, String, or DateTime fields. The data in this field enters the relational warehouse database and the cube as an attribute of the Work Item dimension so that the data can be used to filter reports. Use this for fields that have lists of valid values. Work Item Type and State are good examples of a dimension.

Example

<FIELD refname="MyCorp.Category" name="Category" type="String" reportable="dimension">

Detail

Use the detail type only for Integer, Double, String, or DateTime fields. The data in this field is moved into the relational warehouse database in the Work Item History and Current Work Item tables, but not into the cube. This is a good choice for unrestricted text fields because it lets you use them in reports, but avoids making the cube significantly larger. Any reports that you build using these fields, however, will have to use the relational database instead of the cube. Summary, a string field that summarizes the work item, is a good example of a field that should be a detail when it is reported.

Example

<FIELD refname="MyCorp.Summary" name="Summary" type="String" reportable="detail">

Measure

Use the measure type only for Integer and Double fields. Measures are the numeric values in your reports. Each measure will appear in both the Current Work Item measure group and the Work Item History measure group. Estimated Work is a good example of a measure.

Note

Although SQL Server 2005 Analysis Services supports many forms of aggregation, a field that is used as a Measure is always aggregated as a sum.

Example

<FIELD refname="MyCorp.Cost" name="Cost" type="Integer" reportable="measure">

See Also

Concepts

Understanding the Data Warehouse Architecture

Other Resources

Defining Work Item Type Fields
Understanding the Structure of the Data Warehouse Cube