The first step when creating a custom report in SIX ERP is selecting the data source - the table that contains the information you want to report on.
In the dialog, you will find a list of available tables. Simply pick the one that best fits your reporting needs. For example:
STAFF: for reports about employees.
CLIENTS: for customer-related data.
INVOICES: to report on your billing.
PROJECTS: for project tracking.
…and many more. You can scroll through the list and select what matches the type of data you'd like to include in your report.
Note: We are continuously working on adding even more tables, so this list will keep growing.
After selecting your first table, you can include additional tables to combine related information. For example, if you start with INVOICES, you may want to add CLIENTS to include customer details in the same report.
When adding a table, you'll choose how it connects to the others through a Join:
Left Join – This means you want to keep all data from the first table you selected, even if there’s no matching data in the second table.
Right Join – This keeps all data from the second table (the one you are adding), even if there’s no match in the first.
👉 In simple terms:
Think of Left Join as saying, “Always show all my invoices, even if there’s no matching client.”
And Right Join as, “Always show all my clients, even if they don’t have any invoices.”
After selecting the type of join and choosing which fields should connect the tables, press the checkmark on the right side of the row to confirm the link.
You can repeat this as many times as needed to add more tables, building up your report with all the necessary details.
Once you’ve added your tables and defined any joins you want, press SAVE to store your table selections and continue building the report.
This process is designed to be simple and flexible, allowing you to combine exactly the data you need without complex steps.
Please make sure you are saving your work on each single step!
Once you've chosen your tables and set up any joins, the next essential step is adding Filters to narrow down the data shown in your report. Filters help you focus on only the information that matters most for your needs.
Filters allow you to apply conditions that limit which records are included in your report. Instead of showing all data from the selected tables, filters help you display only the data that matches specific criteria.
For example:
Only show invoices over $1,000.
Show projects created this year.
List clients with names starting with "A".
Filters are applied to the tables you selected in the Data Source step and use the available fields in those tables (like amount, date, name, etc.).
Go to the "Filters" tab.
You'll see a list of existing filters (if any).
To add a new filter, click the “Add Filter” button at the top of the screen.
Choose:
The Table (from your selected data sources).
The Field (the column you want to filter).
The Operator (how the filter works).
The Value you want to filter for.
When creating custom reports in SIX ERP, filters help narrow down your results by applying specific conditions to your data. Filters work by comparing values from your selected tables using different operators, each with its own purpose. Below is an analytical explanation of each available filter operator and how they work, including the types of data they are best suited for:
The EQUAL operator is used when you want to include only records that exactly match a specific value.
For example:
For numbers: "Amount = 1000" will only show records where the amount is exactly 1000.
For text: "Name = John" will only show records where the name is exactly "John".
For dates: "Date = 01-01-2024" will only show records from that exact date.
The NOT EQUAL operator is the opposite of EQUAL. It excludes records that match the specified value.
For example:
Excluding invoices where the status is "Closed".
Ignoring entries where the name is "Test".
This works with numbers, text, and dates alike.
The GREATER THAN operator is used to find records where the value is higher than the specified number or date.
For example:
For numbers: "Amount > 500" finds records with amounts above 500.
For dates: "Date > 01-01-2024" shows records after January 1st, 2024.
This is particularly useful for numeric and date fields.
The LESS THAN operator finds records with values lower than the specified number or date.
For example:
"Amount < 1000" includes records below 1000.
"Date < 12-31-2023" includes dates before the end of 2023.
Again, this works for numbers and dates.
This operator combines GREATER THAN and EQUAL, including records that are either exactly equal to the given value or higher.
For example:
"Quantity ≥ 10" includes records with quantities of 10 or more.
"Date ≥ 01-01-2024" includes all records from January 1st, 2024, onwards.
Used on numeric and date fields.
This works similarly but in the opposite direction. It includes records that are either equal to or less than the specified value.
For example:
"Date ≤ 12-31-2023" covers everything up to and including the end of 2023.
Also ideal for numeric and date fields.
The BETWEEN operator is used to filter values that fall within a specific range, such as dates or amounts.
For example:
"Date BETWEEN 01-01-2024 and 12-31-2024" will only show records within that year.
"Amount BETWEEN 500 and 1500" shows records within that price range.
Works well with numbers and dates.
The LIKE operator is used to search for text that contains specific characters or words.
For example:
"Name LIKE '%John%'" finds names that include "John" anywhere, such as "Johnathan" or "Big John".
This is used only on text fields.
This is the opposite of LIKE. It excludes records that contain the specified text.
For example:
"Name NOT LIKE '%Test%'" will remove all records where "Test" appears in the name.
Only applies to text fields.
The BEGIN WITH operator filters text values that start with certain characters.
For example:
"Name BEGIN WITH 'A'" includes all names starting with "A", such as "Alice" or "Andrew".
This is used specifically for text fields.
All these filter types can also be made dynamic using the "Ask User" option. When this option is enabled on a filter, users will be prompted to provide the filter value themselves at the time the report is run.
For instance, if you create a filter for Date BETWEEN with "Ask User" enabled, the person running the report will be able to enter the date range they want each time they generate the report.
When you add multiple filters to a report, you may want them to work together in specific ways. This is where Group Conditions come into play.
Group conditions follow basic logic:
AND – All filters must be true for a record to appear.
(Example: "Status = Active" AND "Amount > 1000" – only records meeting both conditions are shown.)
OR – At least one filter must be true for a record to appear.
(Example: "Status = Active" OR "Status = Pending" – records with either status are included.)
This gives you the flexibility to create very specific or very broad filters depending on your reporting needs, all while keeping the setup straightforward and user-friendly.
Once you are finished setting up a filter, press SAVE at the bottom right of your screen. You may add additional filters for further narrowed search results.
When creating custom reports in SIX ERP, using Group Conditions like AND and OR in your filters is very powerful—but it’s important to understand how they can greatly influence the results of your report.
AND means all filter conditions must be true for a record to appear.
➤ If one condition isn’t met, the record is excluded.
For example:
"Show only invoices where the amount is over 1000 AND the status is Paid."
→ If an invoice is over 1000 but not marked as Paid, it won’t be shown.
OR means any filter condition can be true for a record to appear.
➤ If at least one condition is met, the record will be included.
For example:
"Show invoices where the status is Paid OR the amount is over 1000."
→ Even unpaid invoices over 1000 would appear.
Incorrectly setting up AND/OR conditions may lead to:
Reports that exclude important data you expected to see.
Reports that include too much data, making them less useful.
Misinterpretation of results if filters are not combined as intended.
Before finalizing your report, double-check your filter logic.
Think carefully about how your conditions should work together.
If unsure, test your report with sample data to verify that the results match your expectations.
Clear understanding and careful use of Group Conditions ensure your reports are accurate, reliable, and helpful.
Now that Data Sources and Filters are defined press NEXT at the bottom of the screen to proceed to Step 2 - Grouping and Sorting.
Read more:
Base Reporting in SIX ERP
Dashboards in SIX ERP
First Steps to define Custom Reports
Step 2 - Grouping and Sorting
Step 3 - Defining Subtotals
Step 4 - Columns
Step 5 - Conditional Cell Formatting
Step 6 - Final Setup and Publishing
Custom Report Settings for Administrators