Using Parameters in ACE SQL Designer

In ACE SQL Designer, you can build reports that prompt users for input—such as a date range, minimum balance, or client ID—before the report runs. These dynamic inputs are called parameters, and they allow users to filter the report results at runtime.

In the SELECT clause, a function prefix (String:, Date:, or Currency:) included in the alias followed by a colon, may be used to describe and/or alter the displayed format of the fields being reported.

What are Parameters?

Parameters are placeholders in your SQL query that accept user input at runtime. When the report runs, ACE shows a field based on the parameter's type and label.

You define a parameter using this syntax:

${ParamaterType: Parameter Label}

• Parameter Type = the kind of input (e.g., DateRange, Currency)

• Parameter Label = what the user sees in the input field when running the report

Function Prefixes in SELECT Clauses

In your SELECT clause, you can control how data appears when exported. The AS clause includes a prefix before the colon which describes the desired function and a label which describes the column header to be used.

field_name AS "Function:Column Header"

Example 1: Filter by Payment Date

To create a report that prompts for a date range, use this parameter:

${DateTimeRange: Payment Date}

• Date Time Range is the parameter type

• “Payment Date” is the parameter label shown to the user when the report runs.

SQL Query Example:

SELECT
    payment.payment_trans_id AS "String:Transaction ID",
    payment.trans_date AS "Date:Payment Date",
    payment.amount AS "Currency:Amount"
FROM
    payment
WHERE
    payment.trans_date BETWEEN ${DateTimeRange: Payment Date}
LIMIT 10;

When the report runs, the user is prompted to select a start and end date and time. The report returns transactions that fall within that range, with column headers in the output.

Example 2: Filter by Minimum Balance

To create a report that prompts the user for a minimum balance, use this parameter:

${Currency: Minimum Balance}

• Currency is the parameter type (which formats input as a dollar amount).

• "Minimum Balance" is the label shown to the user when the report runs.

SQL Query Example

SELECT
    debt.debt_id AS "String:Reference Number",
    debt.owing_balance AS "Currency:Owing Balance"
FROM
    debt
WHERE
    debt.owing_balance >= ${Currency: Minimum Balance}
LIMIT 10;

Explanation:

• The Currency parameter prompts the user for a dollar amount

• The field labels in the SELECT clause control how the values are formatted in the output

The user inputs a minimum balance, and the report returns matching debts. The results display formatted currency and readable labels.

Example 3: Combine Multiple Prompts in One Report

You can include more than one parameter in a single SQL report to give users flexible filtering options.

${DateRange: Created On}
${DateRange: Updated On}
${UserID: Creating User}
${UserID: Updating User}

• DateRange is the parameter type (which prompts the user for a start and end date).

• UserID is the parameter type (which lets the user select a specific ACE user).

• The text after the colon is the label that will appear on the report prompt.

SELECT
    debt.debt_id AS "String:Account",
    debt.create_date AS "Date:Created On",
    debt.last_update AS "Date:Updated On",
    debt.created_by AS "String:Creating User",
    debt.last_updated_by AS "String:Updating User"
FROM
    debt
WHERE
    debt.create_date BETWEEN ${DateRange: Created On}
      AND debt.last_update BETWEEN ${DateRange: Updated On}
      AND (
          debt.created_by = ${UserID: Creating User}
          OR debt.last_updated_by = ${UserID: Updating User}
      )
LIMIT 10;

When the report runs, users will be prompted to:

• Select a Created On date range

• Select an Updated On date range

• Choose a Creating User or Updating User

The report will return debts that match all of these inputs.

Parameters You Can Use

Here are common parameters you can include in your SQL query:

Tips for Building Reports

• Use clear labels after the colon in each parameter so users understand what input is expected.

• Always test your SQL to verify parameters and formatting work as expected.

• Use LIMIT while building or testing to avoid long queries and large result sets.

• Format SELECT fields using "DataType:Label" to control how data is displayed and exported.