All Collections
USING OPERATE
New Report Writer
Creating a Matrix Report in Operate
Creating a Matrix Report in Operate

View complex data using Operate's Matrix Reports

Stuart avatar
Written by Stuart
Updated over a week ago

Matrix reports list summaries of your data in a grid against both horizontal and vertical criteria.

This article will show you how to build them. 

Please note that you need to either be an Administrator or have editor access to the Reports module to be able to create your own reports. Please see Operate User Access and Security Groups for further information.

Step 1: Get Started

  • You will now be asked to select the type of report that you want to create. Please select Matrix

Step 2: Select Your Data Source Modules

  • On the next screen, you can select the data that you would like to include in your report. You can select up to four connected modules. Please note that only some modules connect to each other. If there is no module connection, then it means that the data cannot be put together in a report, so Operate will not list any further module. On this screen, simply select where you need to pull data from, under the Module 1 column. Operate will then reveal the connected modules, under the Module 2 column. If there are further modules you can connect, then you will see them listed under the Module 3 and Module 4 columns.  

  • Simply click on a module to select it. If there are connected modules, you will see them displayed in the next column. Click again to select, and do the same until you no longer see connected modules or you reach the fourth. The broader your first module is, the more likely it is for you to see further connections. For example, if you start by selecting :'Locations' under 'Module 1', then 'Accounts' under 'Module 2', your report will be much broader than if you selected 'Accounts' under 'Module 1'. You can stop at one, two or three modules - the connected modules are optional, but selecting them will allow you to narrow down the data.

  • If you want your report to only include a module if it contains records, please enable 'Tick if module must contains records'. If there are no records, then your report will not list it at all. If you leave this option unticked, modules with no records will be displayed with no data.

  • When you are ready, click Save to continue.  

Example report scenario, with module explanations:

In the example screenshot below, we show a report on an organization's Actual Income Report. We start by selecting the 'Accounts' module, then continue with 'Invoices' then 'Invoice Lines'. For each column, we tick the option to only include a module if it contains records. This means that our report will only include Accounts that have associated invoices.

Note on Invoice Lines

Under the 'Invoices and Credit Notes' module, you will find the 'Lines' module, where you have the option to prorate the charged amount by ticking the box next to this module. This option only works for charges across multiple months, as follows: 

If you have a line on an invoice where the start date of the charge is 15/01/2020 and the end date is 14/02/2020 for a Total Price of £30, then:

  • The report will show the full £30 Total price if you don't tick this box.

  • The report will show £15 in January and £15 in February, thus prorating the charge. 

At the moment, the 'Lines' module is the only one with this option. When it is enabled, the prorate option will allow you to select an extra field on the report, called 'Line - Break Month', which lets you select the months separately as report filters and displays the charge corresponding to each respective month.

Step 3: Design Your Report

While designing your Report, the screen will look similarly to the screenshot below, with your filters and data source selections at the top and a report preview at the bottom.

Please note that the report preview will only use 50 records. The full report will be available when you run it. Please keep this in mind while designing your report, because it might not reflect your data selection criteria. Moreover, when you enable/disable a report option, such as Show Hidden Columns, Show Report Title, etc., the report might pick up a different sample, which could make it seem incorrect. To get a correct view of your data, please run the report.

Please also note that reports are limited to a maximum of 10.000 records when running, emailing or exporting them. If any of your reports contains more records, you can get the full data by scheduling it.

3.1. Select the Date Range

This is where you can select what date range should be considered by your report. The first dropdown allows you to select the module upon which your dates are based. For example, you might want to consider Account related dates, main Invoice dates or Invoice Line dates. Then you can also select the exact period that the report should consider. There are several available options, including the ability to select 'All time' or to customize your date range. 

3.2. Select Fields

You can add fields to the report as

  • Values - These fields will pull the main data that you want to show in your report. You will be able to group this data using the rows and columns.

  • Row Groups - The fields you select here will create row groups of data within your report. These will create a column for each field and separate the report into groups. When you add a Row Group, a Column will be added as well. You can remove it if you like - Please see the section on Customizing Report Fields (below) for further information.

  • Column Groups - These will be the columns of your report and the field labels will be shown as the column headers. Note: When grouping by a date field, any date fields that contains a time stamp will not be able to be grouped by date format 'day'

Note: Pick list fields are not able to be added to matrix reports

To add a new field:

  • Click the "+" sign next to the Column fields area: 

  • Next, you can either search for the field you want to add or click the "+" sign next to the module that you want to add the field from. This will expand the module and show you a list of available fields. 

  • Your search will list results from all modules which might contain a particular field corresponding to your search term. Please note that a particular field might be associated to more than one module: e.g.: Account Name

  • Now tick the box next to the field you want to add. You have several options: If your fields are numerical, you can choose to display a sum total of values, the minimum or maximum value; or an average. If they are string fields (containing both letters and numbers), then you may choose the Min/Max options to get the smallest or largest value alphabetically. (e.g. if you have products from A to Z and choose to display 'Min', the report will list the first product record alphabetically. The 'Max' option will do the reverse, displaying the last record alphabetically.) 

  • If a field has help text added to it, you will see this as a tooltip next to the field, as shown below. 

  • You can add more than one field in one go, without having to save them separately. Your selections will be kept even if you search for other fields.

  • You can also create your own custom calculated field, by clicking 'Add Calculated Field'. Learn more about this by reading the dedicated article.

  • Click Save when you are ready and your new column fields will be added to the report.

  • You can add the same field twice if you like, only that you will need to add and save it before adding it again.

3.3. Customize Report Fields

You can make the following changes to report fields: 

  • Remove by clicking the red "x" icon next to either the field entry under the Values, Columns or Row Groups areas at the top of your report builder page. 

  • Edit the properties by clicking the gear icon on the field entry. This will take you to a screen that lets you edit several aspects related to that field's labeling and behavior.  

  • In the properties of a column, you can change the field label, choose to hide it, choose a sort order, select to show a summary, the minimum, maximum or average value. All of these options can be labeled so you can easily distinguish them onto the report. Please make sure to save any changes you make, if you would like to enable them!

Please note: If you update a field label, you will not lose track of where the data comes from. Simply hover over the tooltip next to the field label and you will see the actual field name and the module it belongs to. 

Step 4: View Options

  • Other report options include the ability to show grand totals or record counts, show any columns you may have hidden in the field properties; as well as to show the report title. The report preview as well as the printable views (pdf or excel) will update according to your selection here. 

In addition, the 'Display Filter Criteria' option will add information about the filters that have been applied to the report upon creation; so that your staff get a more accurate representation of what data is included or not. This also takes effect on the preview and print views (pdf or excel).

'Allow Drill Downs' lets you click into data that represents a record in Operate and see where the report information has been retrieved from. For example, if you have a report on Accounts, then you will have the option to click and see the Account record that generated a particular set of data.

All of these options apply both when running and when editing a report.

Step 5: Customize

5.1. Apply Quick Filters

Quick Filters allow you to narrow down the data in your report. These filters will be made available on the report in View Mode, to help your employees filter data as well. However, they cannot be edited if access is not granted.

To apply Quick Filters, click the Quick button at the top right of your report screen.  

  • Limit results to user's allocated location - Ticking this box will limit the data in the report to what belongs to the Locations to which the user is associated. This is useful for situations when you need to allow your employees to run reports, but limit access. Please note that this filter will only work if you apply at least one of the filters below. For example, if you select a particular Organization, then enabling the Location limit will only show e.g. Accounts which have a Client Type associated to your selected Organization and assigned to the user's Location. Location access can be set from the User Profile

  • Below, you have the option to filter by: Organization, Client Type, Account, Product Group or Product. The latter two are only available if your report contains products. There will be filters available for each module included in your report and will restrict the data that it displays to that which fulfills your filtering criteria.

  • If you choose to use a particular filter, for example 'Organization', then you will have the option to choose a particular module and filter among the Organizations where it is being used. Once you select the module, you can choose to show 'All Organizations' or 'Selected Organizations' - in which case a 'Select Organizations' button will be displayed.

  • Click 'Select Organizations' if you want to do so. This takes you to a screen where you can choose from the Organizations that your business is using in Operate. 

  • Use the Search box on the right to find the Organization you need. Tick the box next to it to select.

  • Click Save to save your changes; or Cancel to exit the screen without saving. 

  • Click Clear Organization Filter if you would like to start over. 

5.2. Advanced Filters

Advanced Filters allow you to narrow down your data even further, using virtually any field from within Operate. These filters are similar to those in your list views, for example. 

Advanced Filters are not available to Operate users who do not have Reports Editing permission. 

To use Advanced Filters, please click the Advanced button at the top of your report.

This screen will allow you to select from a variety of fields to filter by, as well as operation criteria and values. 

  • The Field Name will allow you to select from all fields included within your report's selected modules.

  • The Operation includes a number of criteria that the field should fulfill, such as: equals/not equal to, starts with/contains, less than/greater than; etc. 

  • The Value is the criterion that your Operation will work against. When selecting values, you will be asked to enter it in a text box, select from a dropdown or tick a box, depending on the type of your selected field. 

Use the Add New button to add more filters. 

Use the Remove button to remove a filter. 

Use the Add Logic button to specify AND and OR conditions for your filters, which change the default AND relationship between each filter. 

  • AND ties two fields together, so that your report contains both or all fields (e.g.: 1 AND 2 AND 3).

  • OR will make your report display either one field or the other; depending on your selection. (e.g.: 1 OR 2 OR 3).

  • You can combine AND and OR into more complex expressions; in which case you can use parentheses.

NOTE on date related filters: 

When you select a date related field such as: Creation Date, Enquiry Date, etc; the filter will display a Value textbox which needs to be populated with one of the following values: 

  • NOW

  • TODAY

  • TOMORROW

  • YESTERDAY

  • LAST WEEK

  • LAST MONTH

  • LAST QUARTER

  • LAST YEAR

  • THIS WEEK

  • THIS MONTH

  • THIS QUARTER

  • THIS YEAR

  • NEXT WEEK

  • NEXT MONTH

  • NEXT QUARTER

  • NEXT YEAR

  • DATEADD

Simply enter the text in uppercase and the filter will apply.

You can also use a specific date if you like, by entering the date in this format: yyyy-mm-dd. 

Click Save to save the filters or Cancel to return to the report editor without saving. 

When you have applied Advanced Filters, the button will show how many there are, so you know at a glance that your report data is filtered. 

There is no limit to the number of Advanced Filters you can apply. 

5.3. Insert Charts

You can attach a chart after each row group or at the end of your matrix reports. Please see Attaching a Chart to other Report Types for details.

Step 6. Save or Run Your Report

Once all your fields are added and your options set, you are ready to either Save or Run your report.

Name your report and select the folder where you'd like to store it, then click on Save.

Did this answer your question?