Custom Reports

What is it and what is it for?

A Custom Report is a definition you create to query your own data, which you can then run as many times as you want, just like any other AdmCloud report. There are two types:

• Visual Report: you pick a main table, add relationships to other tables, select the fields to show, set filters and sorting — all with dropdowns and configuration tables, without writing SQL. It's the simplest option for a non-technical user.
• SQL Report: you write the SQL query that fetches the data yourself, with access to an editor with syntax highlighting, autocomplete, a built-in Data Dictionary, and three AI tools that help you write, understand and optimize the SQL. For security, the system only allows read-only queries: you cannot insert, update or delete data, nor change the database's structure.

They're useful for turning any business information the native reports don't cover into a reusable format — for example, a specific cross-tab of sales by rep and item line, a customer list with a particular criteria, or an indicator you want to check every week. Besides running it on demand, a Custom Report can also:

• Show up as a card on your Dashboard.
• Show up as a Reminder (alert) on the dashboard.
• Be charted instead of (or in addition to) shown as a table.
• Be exported to PDF, in portrait or landscape orientation.
• Be shared publicly or restricted to specific user groups.
• Be automatically emailed to a list of users on a scheduled frequency (SQL Reports only).

How to use it

Create a Visual Report

1. Open the Custom Reports list and, from the New button, choose Visual Editor.
2. Fill in the Name, Module and Owner of the report. The Name must be unique within your company.
3. On the Data Source tab, pick the main table and, if you need information from other tables, add the matching relationships (JOINs): the table to link, the connecting field, and the join type.
4. On the Fields tab, add the columns you want to show, with their visible caption and, if it applies, a totals function (sum, average, count, minimum or maximum).
5. Add Internal Filters (fixed ones the user doesn't see or change) and Visible Filters (ones the user running the report can adjust each time), and set the row Sort order.
6. If the report isn't Public, go to the Permissions tab and add the user groups that will be able to see it.
7. Save and run the report from the list, or from the sidebar menu if you flagged it to show up there.

Create a SQL Report

1. Open the Custom Reports list and, from the New button, choose SQL Editor.
2. Fill in the Name, Module and Owner of the report.
3. Write your SQL query on the Data Source tab. You can start from one of the SQL Samples grouped by module, look up the built-in Data Dictionary to find tables and fields, or lean on the AI tools (see below) to draft, explain or improve it.
4. Click Preview to confirm the query runs and check the result, then use Create Columns to have AdmCloud automatically add a row on the Fields tab for each column your SQL returns.
5. On the Fields tab, adjust the caption, alignment, data type and, if you need to link to another screen, the link page and link field for each column.
6. On the Visible Filters tab, define the parameters the user will be able to fill in when running the report (text, number, date, yes/no, or a reference to a customer, vendor, item or another entity).
7. If the report isn't Public, assign the user groups on the Permissions tab.
8. If you want it emailed automatically, turn that on from the Mass Mailing tab (see the automatic mailing section below).
9. Save and run the report from the list, or from the sidebar menu.

AI to write and understand SQL

The SQL Report editor includes three AI assistants:

• Generate SQL with AtIna (beta): describe in plain language what you need (for example, "monthly sales grouped by sales rep") and an AI agent drafts the SQL for you; you can review it and accept or discard it before it's placed in the editor.
• Explain SQL Command: if you already have a query (your own or inherited) and don't fully understand it, this button gives you an explanation in your language of what it does.
• Performance Suggestions: analyzes your query and proposes syntax changes to make it run faster, without touching indexes or server configuration.

Data Dictionary

From both the Visual Editor and the SQL Editor you can open the Data Dictionary, a quick lookup of every table in your company (with its technical name and description) and, for each table, the list of its fields with their data type. It's how you find out what information exists and what it's called, without having to guess or ask someone else.

Inside the SQL Report editor, the "Available Variables" tab also shows you the system variables you can use directly in your query:

The report details screen

When you open a Custom Report's details (without editing it) you see its complete definition in read-only mode, organized into the same tabs as the editor:

• Data Source: the SQL command on SQL Reports; the main table and its relations on Visual Editor reports.
• Fields: the report columns with their label, data type, alignment and whether they total.
• Visible Filters: the parameters the user fills in when running the report, with their label, data type and associated custom list.
• Permissions: the user groups that have access to the report.
• Mass Mailing: the automatic email delivery settings, the recipient users and the history of the last 50 deliveries.

The Mass Mailing tab only appears if your plan includes automatic report delivery.

Key capabilities

• Two ways to build the report: Visual Editor (no code) or SQL Editor (your own query).
• Results shown as a table, with export to PDF (portrait or landscape) and to Excel.
• Chart visualization, instead of or alongside the table.
• Availability on the Dashboard as a report card, and as a Reminder (alert).
• Public reports (visible to the whole company) or restricted by user groups.
• Owner protection: when "Owner-only access" is checked, no one else can edit its structure.
• Duplicate an existing report to create a variant without starting from scratch.
• Transfer a report to another company of the same Partner (requires being the owner and the permission to create Plugins).
• Automatic scheduled email delivery, exclusive to SQL Reports (see below).
• A security log of who created, edited or deleted each report.

Automatic email delivery

SQL Reports can be emailed automatically and on a recurring schedule to a list of users, with no one needing to log in and run them by hand. It's configured on the "Mass Mailing" tab of the SQL Report:

Every delivery attempt (successful or with an error) is logged, and you can review the history of the last 50 deliveries from the report's detail screen.

Key fields

Validations on save

• Name is required and can't repeat another report's name in your company.
• The main Table is required on a Visual Report, and SQL is required on a SQL Report.
• Owner is required and must be an active user.
• If the report is Protected, only the owner can save changes to its structure.
• Module is required.
• SQL must be read-only: it must start with SELECT, WITH or DECLARE, and can't contain commands that modify data, permissions or the database structure.
• At least one field must have a caption defined to show on the report.
• If the report shows as a Chart, at least one field must have the Total function turned on.
• Field formulas also can't contain forbidden SQL commands.
• A Public report can't have user groups assigned; remove them before marking it Public.
• For automatic email delivery: at least one recipient user, a Start Date and an Execution Time are required; if you set an End Date, it can't be earlier than the Start Date; and your company can't exceed 20 reports with automatic mailing active.

Availability and permissions

• Browsing, creating, editing and deleting Custom Reports requires the "Customization" permission on your user group, with separate levels for each action.
• Visual Reports (with charts) are available from the Basic plan and up.
• SQL Reports, their availability on the Dashboard or as a Reminder, and automatic email delivery, require the Enterprise plan. If your company doesn't have the Enterprise plan but needs the SQL Editor, it can ask AdmCloud support to enable it specifically for your account.
• Grouping the report under a Custom Menu Option requires the Enterprise plan.
• Transferring a report to another company also requires the permission to create Plugins, and isn't available on accounts in their trial period.
• A report installed from AdmCloud Connect (flagged as a "store asset") can't be edited directly; it can only be viewed, duplicated or deleted.

Tips and common mistakes