The Tabular Reports are the simplest ones you can create in Operate. They list your data in the form of columns containing the information you need to see.
This article will show you how to build one.
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
Log into your account by going to operate.essensys.tech.
Go to the Reports section.
Click New.
You will now be asked to select the type of report that you want to create. Please select Tabular.
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 your 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 Calendar Bookings. We start by selecting the 'Accounts' module, then continue with 'Calendars', then 'Booking Charges'. 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 bookings and these bookings have been charged for (there is an associated Charge Sheet).
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
It is now time to design your report. Operate will show you a first version of it, based on the source modules you have previously selected. Your Report builder 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
As mentioned, your report will contain some preset fields, depending on the modules you selected as the data source. This should be a good starting point from which you can customize further.
This report type only contains Columns and the field labels will be shown as the column headers. The Columns will add the data that corresponds to the modules you have selected as data sources.
To add a new field:
Click the "+" sign next to the Columns 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 then list the fields that correspond to your keyword. Please note that some fields can be associated to multiple modules (e.g. Location). In this case, they will all be listed and you can choose one or all.
Now tick the box next to the field you want to add.
Some fields come with 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.).
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.
If a field has help text added to it, then you will see a tooltip next to it. Hover over that to see the help text.
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 Columns area at the top of your report builder page. You can also use the same red 'x' icon to remove columns from the report preview area - you will find it on the column header, right next to the title.
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. If your column contains number based fields, you can select the number of decimal places by entering the corresponding number (e.g.: 2). All of these options can be labeled so you can easily distinguish them onto the report. In addition, you can style the column by making the font bold or by selecting a background colour. 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 is coming 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
The options you can use to best view your report 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. The filtering process discussed below is the same for all of these areas. We use Organizations as an example.
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 you 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 field selected.
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 at the end of your tabular 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.
