How do I chart the total time for DCR Formal Review?

For each Document Change Request (DCR), Grand Avenue records the date that Formal Review begins and ends. If a DCR is rejected and goes through multiple iterations of Formal Review, the total time is measured from the date the first Formal Review begins until the date the last Formal Review completes.

The approval start and end dates are recorded on each DCR as shown in this example.

There are three ways to chart DCR Formal Review duration.

Click the expand (>) controls below to display an example of each method.

Grand Avenue has a built-in dashboard chart that shows the time in days for DCR approval and DCR rollout charted over time by the average number of days per month.

In the Document Control menu in the navigation menu, click Dashboard. Look for the chart titled “Trend of Time (in Days) to Process Change Requests by Month”. Below is an example.

The chart above shows the average number of days needed for the DCR Formal Review step per month for the past 12 months.

Users may not alter any parameters of Dashboard charts. If more control over what’s displayed in the chart is needed, proceed to the next option.

Click Search for Change Requests and enter the search criteria. Depending on the number of change requests returned, the value in Max # of Items to Display may need to be cleared.
Click Generate Chart for Results to generate a chart using the internal charting tool.
Enter the chart parameters.

The chart above shows that 71 DCRs completed Formal Review within 30 days and 3 DCRs completed formal review in 31-60 days. The chart type and “bins” used for this chart (e.g. “0-30 days”, “31-60 days”) cannot be changed in Grand Avenue.

To generate charts that are more configurable, proceed to the next option.

Click Search for Change Requests and enter the search criteria. Depending on the number of change requests returned, the value in Max # of Items to Display may need to be cleared.
Click Export Results to Excel to initiate the data export.
At the bottom of the next page, click Export Results to Excel to export the raw data to a CSV file. Open the exported CSV file and save it as an Excel Workbook (.xlsx).
Create a new column for the number of days to complete Formal Review. Subtract the Approval Start Date from the Approval End Date to populate the new column with values.
One way to process the Excel data is with PivotTables and PivotCharts. Below are 2 examples of charts that can be generated from this data set.

Example 1: The total number of days to approve DCRs shown in a Pareto chart

Use the exported data to generate a Pivot Table in Excel.
Group the rows in the PivotTable to define the Pareto chart bins.
PivotTable is grouped into bins of 5 days each.
Create a PivotChart showing the distribution of DCR approval days.

Example 2: The average number of days to approve DCRs shown in a trend chart

Use the exported data to generate a Pivot Table in Excel.
Create a PivotChart showing the average number of days to approve DCRs per month over a 12-month period.