Overview
The Report Generator 2 allows users to build custom reports using a wide range of filters and data sources.
It is setup in such a way that you can add fields to the report with a single click and the report will create the data links in the background for you.
Important: It's important to note that the report generator 2 only has access to Transactions and their linked Invoices.
This means it cannot be used to report on active jobs, or pull through details for accounts, addresses or suppliers etc.
Accessing the Report Generator screen
Click on the Reports button at the top of the screen.
In the top-right corner, click on Generator 2.
Navigating the Screen
Left-Hand Side
Report - This allows to you to select and load a previously saved report.
Dataset - This allows you to switch the available data fields between Transaction and Invoice.
Columns - These are the columns that are available to be added to the report
Pre Set Column - These are pre-loaded columns allow you to easily add summaries (for example adding the "movements" column will provide a total number of jobs for the given filters.
Top Rows
Export to CSV - Clicking on this will export all the results for the current report to a new CSV (excel) file.
Export to Pivot - Clicking on this will export all the results for the current report to a new CSV (excel) file.
Report - Clicking on this will make the main section of the screen display the report columns and filters.
Options (top) - Clicking on this will display the options for exporting and emailing the report.
Schedule (top right) - This allows you to schedule the report to be emailed out on a regular basis.
For more details on scheduling reports please refer to the Scheduling A Report knowledge article.
Switch Mode - This allows you to switch between the main "Report Builder" mode which shows the columns and the "Filter" mode which will only show the filters and allow them to be changed
Main Screen (centre)
Column - The name of the field that is pulling through data. Each entry here will be displayed as a column on the report and its exports.
Or - Allows to select multiple set filters and make report grab data that means, for example, filter 1 or filter 2 rathe than both at once.
For example - have the report display results where the account is "001" or the ProductCode is "Waste"
Where - This is where filters can be added, edited or removed from a column.
Select - Unticking this box will hide the column from the report while still applying any filters that are in place. This is used if you are looking to group entries
For example if you want to group all jobs in a date range product you would set a ate range filter but not show the actual date itself as that would make the jobs be split on the report.
Aggregate - this allows you to set how the results of a column are shown when the report is using grouping.
For example if you wanted the report to show the total Quantity across a group of jobs you would add the Quantity column and set this "SUM"
Sorting - This allows you to display the results of that particular column in Ascending or Descending order.
Format - This allows you to change the format of the date displayed on the report for any column.
Delete button - Clicking on this will delete the column (and any filters set against it) from the report.
Main Screen (lower section)
Grid View (Top 10) - This section displays the top 10 results of the report based on the columns and filters you have set up.
There is no way to view the entire report on-screen - they have to be exported using the CSV or Pivot buttons.
SQL View - This screen is the code behind the report. It cannot be automatically edited but will updated as the columns filters are amended.
This is mostly for support use.
Options (bottom) - Displays further options for the report formatting
By default these are all enabled.
Open an existing Report
Go to the generator 2 screen as normal, then click in the "Report" drop-down menu.
You will see a list of Generator 2 report to which you have access
Note: You will only be able to see reports to which you have access. See the "managing Reports Access" section of this article for more details.
Once you click on the report it will be loaded on your screen.
Important: Reports are shared by all users so be aware that is you make changes to the report and hit Save those changes will be enabled for every other user of the system.
Starting a New Report
When you go to the Report Generator screen you will be presented with a new blank report containing just a "Date" field.
You can edit this immediately and then save it as a new report.
Editing the Contents of a Report
Adding columns to the report
The screen will start with the "Transactions" dataset pre-loaded in the top-right corner, this controls the fields available for selection. You can switch the dataset to "invoices" by selecting it from the drop-down and clicking on "Load". The available fields will automatically update.
To add a column to the report you just need to click on it on the left-hand list.
For example, if you want the report to include the ProductCode then locate the item in the "Columns" section an click on it. The selected columns will be added to the main screen (new columns always appear at the bottom of the list) and the grid View at the bottom of the screen.
Note: As mentioned above, the available fields are drawn from the Transaction and Invoice areas of the system. If there are data fields from those areas missing that you would like to have added then please reach out to support for it to be looked into for you.
Moving the Fields
You can reorder the columns by clicking on the up or down arrows to the left of the items.
Add a filter to a column
The "Where" column allows you to add filters to a column. For example if you wanted to only show results for a specific account you would add a filter to the Account column.
In this example we will be setting a filter to only show results for a specific account.
Click on the "Add" link in the Where column for the field.
Set the Type to "Equals"
Click in the next box and select the required account
Click Add to apply the filter
You can also add multiple filters to the any column. This is useful for adding a date range where you date the date column to be "equal to or greater than" a start date and also "less than or equal to" an end date.
Saving the Report
Once you have added the fields and set the layout you require, ensure you have put a name for the report in the Name field and click on "Save As" at the top of the screen.
That will save the current report as a new file.
Important:
New Report: If you are saving a new report for the first time first enter a name for the report in the "Name" field and then click on "Save As". The report will be saved under than name.
Existing Report: If you are editing an existing report then just hit "Save" otherwise you will end up with duplicate reports all under the same name.
Deleting a Report
If a report is no longer required you can delete it via the below steps:
Open the report as normal
Click on the Delete button in the top-right
Click "Ok" in the confirmation prompt that appears
The report will then be deleted from the system.
Important: This will delete the report from the system for all users.
Managing Report Access
When you create a new report it will, by default, only be available to your user.
To add or remove report access to other users you can click on the "Manage reports" link in the top-right corner.
In the screen select the report you want to share or restrict from the drop down menu.
The lower screens will then display the users who do not currently have access in the left-hand box, and the users who do have access in the right-hand box.
To add or remove a user tick the box against their username and click the "Change" box at the bottom. That user will then be moved to the opposite box.
Tips & Tricks
Adding a Dynamic Date Range
Rather that setting a specific start and end date it is possible to add a "dynamic" date range. This is especially useful if you intend to schedule the report to be emailed out as the dynamic values allow the system to determine things like "last month" or "this week" when you set up a schedule.
To set the dynamic date range you need to add filters to the Date column as normal an use the below options.
From Date
Type = Date From
Date = @fromdate
To Date
Type = Date Less Than
Date = @todate
The filters will end up looking like this when viewed:
Example Reports
Below are screenshots of some examples of reports that are commonly requested.
Total Sales and Job Count by Product
This report looks through the data and grabs all transactions within a date range set by yourself, then displays the total number of jobs and total sales value within that rage for each product.
