Summary

A trend report organizes data along a time line or other progress indicator. Managers use trend reports to understand their business processes: for instance, a manager may want to see a report of the number of sales made per month over the past year.

The report definition rule simplifies the creation of trend reports, and allows a manager to quickly attach a chart to the report to make analysis even easier.

In this example, you want to know how the average time it takes to complete a work item has changed over the preceding months.

Suggested Approach

Here are the steps of the suggested approach:

1. Create a new report and associate data with it
2. Get the data you want to track in the report
3. Get data for grouping work items
4. Review the report
5. Add a chart

a. Create a new report

You can create a Report Definition report starting from the Report Browser in a portal, or from the rule form. For a description of the creation steps in each case, see How to create a Report Definition rule.

The new report's rule should have as its Applies To class the class of the work items or data you want it to report on.

b. Get the data you want to track in the report

In this report you want to see how long it takes to resolve each work item, so you are going to subtract each work item's creation date from the date when the item was resolved.

1. Click the function icon ( ) to the right of the Column Name field for a column in the report, to display the SQL Function Builder. The Function Builder makes available a large collection of standard SQL functions, as well as any custom SQL functions developers have created and made available.
2. Start typing in the Select a function field, and then click the AutoComplete arrow () at the right end of the field to see a list of all functions that are possible matches for what you have typed so far. In this case, type "difference" to see the date-related functions:

3. Select the "difference in days..." function. Two fields appear where where you enter the parameters the function will compare, providing the values for "Earlier Date Value" and "Later Date Value". Again, you can type a few characters and click the AutoComplete arrow () to see the available properties containing the string you typed. For the first field select .pxCreateDateTime, and for the second field select .pyResolvedTimeStamp:

..

4. Click Submit to save your selections and dismiss the function builder.
5. The column now displays the name of the SQL function in the Column Name field. Adjust the Column Heading entry, if you wish, to a user-friendly text. The data in this column will only be two or three characters wide, so you can set the Column Width to 50 pixels or even less.
   
6. Select AVG for the Summary Function property, to summarize the time to resolve of all the work items in a particular group.

c. Get data for grouping work items

Since the report is to cover events occurring over a period of time, the report needs a column holding a time value for each work item--in this case, the month when the work item was created.

1. As you did in step b, click the function icon ( ) to the right of the Column Name field for the column that holds this data, to display the SQL Function Builder.
2. Start typing "month" in the Select a function field, and then click the down arrow to see a list of all functions that are possible matches for what you have typed:

3. Select the function that you want: in this case, you want to group the work items by the month of their creation, so select The first day of the month of [Date Value].
4. A Function Input field appears where you specify the property the function works with. Select pxCreateDateTime:
   
5. Click Submit to save your selections and dismiss the Function Builder.
6. The column you have been working with now displays the name of the SQL function in the Column Name field. Adjust the Column Heading entry, if you wish, to a user-friendly text. Adjust the column width to an appropriate setting. Do not select a Summary Function for this column, and accept the Format Values provided by the Function Builder. Set the Sort Order to 1, if it is not already set:
   

Save the report.

d. Review the report

Run the report and review the data it displays.

The information is there, but it is hard to detect trends in a grid-style report. The next step is to add a chart that displays the data along a time line.

e. Add a chart

If a report contains at least one column where the data is grouped by a summary function (MAX, MIN, or COUNT), you can add a chart to the report. To do that,

1. In the Report Viewer, click the Add Or Edit Chart icon:
   
2. In the Report Definition rule, click Include a Chart.

The Chart Definition form that appears lets you specify both what data the chart displays, and the format to display it in. To start, on the Type tab select a chart type. For this example, select Line and Curve for the chart type:

The Data tab is where you specify the data for the chart to display. Since this simple report has only one Group By column and one Summarized column, the form automatically selects them. If there were more columns available, you could select here which values to include in the chart.

On the Axes tab, specify what the unit of measurement for the chart is, and whether grid lines and labels appear, and whether the labels for the grid lines appear at the top or bottom of the chart.

Similarly you can specify whether and where labels appear, and whether grid lines appear, for the summarized column axis, the count of how many data objects were created for each month covered by the chart:

The Options tab lets you customize chart settings by specifying whether the chart appears within the report or in its own window, whether various buttons and data tips appear, and so on.

The Labels tab lets you specify text and style for a chart title, and for labels for the X and Y axes.

Click OK to save your specifications, and review the chart:

The slider at the top of the chart allows the user to zoom in on a particular date range within the chart's values, to get a closer view of how the trend changes.