Matrix Reports

When selected, this feature creates a report where ExtraView will display a results grid with one or more selected fields across the page and one or more selected fields down the page. When used with the aggregation method to count issues, this report will produce results identical to running a summary report with two selected columns, but rather than the results being displayed in a column, they are displayed in a grid form. Examples of useful matrix reports include:

  • A report of statues of issues by customer
  • Break down the number of issues by priority, by product
  • Breakdown the number of reported issue by customer for the last month
  • Display a count of the issues created by country, by region over each month for the last two years.

You are able to select from any fields to which you have read permission, and which are able to be summarized and counted. For example, you can typically summarize on different values in a list, but cannot summarize on text fields.

To prepare a new Matrix report, choose the Create New Matrix Report option. The screen presented to you will be similar to the following:

Creating a new matrix report

Selecting columns to summarize and display

  • There are two basic counting mechanisms for matrix reports.  You can simply count the number of issues that are at the intersection of the two axes you select and filtered by your selection, or you can sum the values within a specific field that are at the intersection of the two axes.  These produce quite different results.
  • Select the columns that you require on your report, by selecting up to ten field names from the Field across (X axis) list, and then select up to ten fields from the Field down (Y axis) list
  • You can use the options Include columns with no data and Include rows with no data to display columns and rows on the report that have zero values. The default is that if all values for a column or all values for a row are zero, then that column or row will not be displayed. If the field you are using for the X axis or the Y axis is not of a list type, then this option is ignored. For example, if you are summarizing on a text field, you cannot include rows or columns with no data. This is because you may be selecting a huge number of values with no data, a circumstance that will inevitably lead to a report that does not display reasonable data.

Columns on the report output may be sorted by clicking on the column title.  When there is more than one field on the Y axis, and you have selected an option to display sub-totals, then the sort takes place within each group of results.

The following two screenshots show a matrix report with the Assigned To and Category fields selected for the axes.  The first report shows a count of the issues within each Category that are Assigned To each user.  The second report is summing a field within the issues by each of the Assigned To personnel within each Category.

Sample matrix report counting issues


Sample matrix report summing issues

Note that by clicking on the titles in each column, you can resort the results.

Column Width on the Report Output

By default, the width of columns on browser output are not set, and the user's browser determines the width of each column using its own algorithms.  Most of the time this works well, but there are times when you might want to fix the width of the Y axis column titles being displayed on the output.  Right-click on the red button by the field name you selected for the report, and one of the fields allows you to set the width of the column, measured in characters.  This number is approximate, as browsers will sometimes override the number you set, particularly if you are displaying a large number of columns on the screen, relative to the width of the screen.  Microsoft Word and PDF column width output are set using the same option.  Note that you may also use the report option titled Wrap Titles on Output to assist in controlling the width of columns on the output.  When you both set the width of a column and set the Wrap Titles on Output option, the column width setting takes precedence.

Multiple Field Breakdowns on Matrix Reports

You may select up to four fields on each axis of a matrix report, and ExtraView will automatically categorize the results.  As an example, here is a report with two fields on each of the axes.  The X axis is displaying Status within Assigned To, and the Y axis is displaying Category within the Business Area.


Sample matrix report with 2 fields selected for each axis

Again, you may click on columns to resort the results.  When more than one field selected for the Y axis, you can use the Expand All and Contract All buttons to view more or fewer rows in the result.s.  You may also click on any of the values within the titles to view more or less of the results.  As an example, if you click on the Contract All button in the above screen, the results displayed become:

Matrix Reports and Date Fields

Date fields have special properties when used on matrix reports.  This helps you produce a variety of time-based reports, with the date fields being used on the X axis as column headings.  The key properties are:

  • You can group all the results for a time period.  For example, you can group by week, or group by month, or group by year.  Right-click the small red button at the left of the field name to popup a panel with this option
  • You can select the same date field more than once for the X axis, then group the results.  For example, you can group results by month within years, or by weeks within months within years  Right-click the small red button at the left of the field name to popup a panel with this option
  • The groupings may be made on calendar or fiscal time periods.  There are also other groupings basing results on days of the month or week and on month and year starting and ending dates  Right-click the small red button at the left of the field name to popup a panel with this option
  • You can select up to four levels for groupings
  • You can sub-total the results at any or all the grouping levels  Right-click the small red button at the left of the field name to popup a panel with this option
  • You can alter the format of the date field on the column heading.  Select between Short, Medium and Long.  As an example, the month of January will appear as J, Jan or January, depending on which format is selected  Right-click the small red button at the left of the field name to popup a panel with this option
  • You may check the box on the report editor to Include columns with no data.  This will fill in periods with no data.  For example, if you have a report where you want to display all the months of a year, but only some columns have data, you will still see a column header for each month of the year

Example

The objective is to prepare a report for last year, displaying sub-totals by quarter.  The report will show the number of issues assigned to each person in each time period.

Note the following:

  • The Date Created field was selected 3 times for the X axis
  • Use the popup accessed by right-clicking the red button by the field to group the first selection to Year, the second to Quarter and the third to Month
  • On the Date Created - Month popup, select the Display sub-totals checkbox
  • Select the Category field for the Y axis
  • Use a query filter of Date Created during Last year or similar filter to define the time period
  • When you right-click the small red button, the popup allows you to refine the report output with additional options
    • Ability to display sub-totals on any grouping
    • Ability to set alternate titles to the fields
    • Ability to override the user's data format and define the format of the dates on the report output for all users
    • Ability to set the grouping as described in the above bullets
    • The button with the title Set Output Time Span allows you to enter a beginning and end date for the columns that are to be displayed on the report.  These dates may or may not coincide with the data values contained within the report.  All columns following the Start Date will be displayed whether or not they contain data.  All columns before the End Date will be displayed,  whether or not they contain data.  For this to work correctly and to include all the empty columns on the report, you must also check the box Include columns with no data
    • The format option allows you to shorten the titles for the months of the year and the days of the week.  There are 3 options, short, medium and long.

This produces a report similar to the output below.

Adding Statistics to Matrix Report Output

There are several options with the Add Statistics to Report checkboxes.  You can elect to add Totals, the Percentage, the mean, the minimum, or the maximum to the report output.  This may look similar to this:


Using the Report Editor to Define the Statistics to be Added to the Report Output

You may click on each of the statistical methods, bringing up a popup window where you may alter the title of the column and set the decimal precision of the displayed results for the statistic that is being generated.  Note that this precision is independent of the precision of any decimal number that might be displayed within the body of the report.  You may also set whether to display the statistics on the X axis, the Y axis or both axes.

Also, note the select list directly beneath the Fields Down Rows (Y axis) field box.  This offers the ability to provide an initial sort on the columns displaying the statistical output.  You may sort any of the columns in an ascending or descending order when the report is first displayed.  Once displayed, you may click on the column titles to resort the report.


Formatting the Statistical Output on the Report


Statistics on the Y Axis of a Matrix Report

Adding Conditional Formatting to the Output of Matrix Reports

This is fully explained here.  Conditional formatting may be added to the cells within the matrix report, to sub-totals and totals and to any of the other statistics rows / columns that appear on the report output.

Aggregation Method

The default option is that the summary report produces a count of issues within each cell of the matrix.  If you select the option to sum the issues, you are asked to select a numeric field.  ExtraView will then sum the values within each cell, for the count of issues.

Display on Mobile

Check the option if you want the report to be available within mobile clients.

Output Report Definition

Checking this option displays the report selected options and fields along with the filters used at the end of the report.

Comparisons with Prior Periods

The report editor screen has an option that allows you to make a comparison of the results on your matrix report with either the prior period you have selected, or with the same period in the previous year.  The output will then show three sections side-by-side; the results selected, the prior results and the difference.  The time period against which the comparison is made is identified with a query filter.  For example, if you set a query filter of Date Created During Last Year and select Comparison equals Prior period, then the report will display last year's data, plus the previous year's data plus a comparison of the two years.

Notice that when making prior period comparisons, the report option to Include rows with no data is purposely enabled.  This is to ensure that the same set of rows are displayed in the results and the prior results.  If this did not happen, then it is entirely possible that the rows of data would not line up correctly when you view the report.

Comparisons with prior periods are not supported on matrix reports that have statistics added, on anything other than totals.

Displaying the Row Values within the Cells of the Output

There is a checkbox option titled Render Y field values in cells.  When you check this option, and choose a single field within the row (Y axis) list.

Note that each cell of the matrix report has the value of the field - Assigned To in the above example.  The count of issues is also displayed.

The example also shows how conditional formatting was applied to highlight the values that were greater than a count of 5.

Hierarchical Summary Reports

If your administrator has defined hierarchies on which you can report, you will see an additional prompt on the report editor screen:

Reporting on hierarchies within matrix reports is similar to the way they are defined for column reports. The key difference is that you are only required to select the filters for each level in the hierarchy. These filters will be applied to each level of the hierarchy for the preparation of the data to be placed on the report. The report output will look very similar to a standard matrix report, except that the additional filters are applied.

Grouping on Multiple Levels of Hierarchical Filters

This feature produces a standard matrix report for the output, but allows you to group the results using filters at each level of the report hierarchies.

  • As an example, we will utilize a hierarchy with 3 levels representing an organization that tests software.  The organization creates Test Plans and Test Cases.  Some subset of the available Test Cases are placed within a Test Plan, in a parent-child relationship.   When Test Cases are executed, a Test Result is created as a child relationship of the Test Case
  • To represent this, we have the following reporting hierarchies defined
    • Tests, which uses the relationship Test Plans --> Test Cases at the topmost level and Test Cases --> Test Results at the second level
    • Test Plans --> Test Cases as a single-level reporting hierarchy
    • Test Cases --> Test Results as a single-level reporting hierarchy

To prepare and run a matrix report that groups on multiple levels of hierarchical filters, perform the following steps:

  • Create a new matrix report
  • Select Condensed Query Filters
  • Select Advanced Query Screen
  • Select the report hierarchy titled Tests
  • Select Assigned To as a field to display and group by.  You group by this field by right-clicking on the red button by the field and selecting Test Plan -->Test Case as the hierarchy level
  • Beneath the field list for the X axis, you see the Perform count on select list.  Again, select the Test Plan --> Test Case entry.  The important point is that this is the same hierarchy level that you chose in the last step
  • Choose the base level query filters.  E.g. select the filter PROJECT = Test Plans for the base level
  • For the Test Plan --> Test Case level of the hierarchical filters, select the filter PROJECT = Test Cases
  • For the Test Case --> Test Result level of the hierarchical filters, select the filter PROJECT = Test Results
  • Run the report.