Parameters are how a one-time query becomes a reusable report. A placeholder in the SQL becomes a value the user supplies when the report runs, or a value the report fills in for itself from a default.
Parameter Syntax
Write the parameter in your SQL as <<$Name>>, wrapped in single quotes. The quotes are part of the SQL you write, for every parameter type, so the value is always substituted as a string literal and then cast where needed.
Example placeholder:
'<<$StartDate>>'
Example typed usage:
CAST('<<$CedingCommissionRate>>' AS DECIMAL(5,4)) AS ceding_commission_rateExample in a filter:
WHERE c.date_of_loss BETWEEN '<<$StartDate>>' AND '<<$EndDate>>'SQL Editor detects every <<$Name>> placeholder in the query and lists it in the Parameters panel. You do not define parameters by hand first; add the placeholder to the SQL and then configure it in the panel.
Note: Some materialized views require parameters. Views whose measures are "as of" a date require
AsOfDate; views that cover a period requireStartDateandEndDate. If your SQL uses one of these views without declaring the parameter, the save is rejected with a message naming the parameters you need to add.
Parameter Settings
For each detected parameter, the Parameters panel lets you set:
| Setting | What it does |
|---|---|
| Name | The identifier used in the SQL placeholder, for example EndDate. |
| Label | The business-friendly text shown to the person running the report, for example "As of Date". |
| Type | The data type of the value: Number, Text, Date, or a list of allowed values (an enumeration the user picks from a dropdown). |
| Default Type and Default Value | How the default is produced when the user does not enter a value. See the next section. |
| Hidden | Whether the parameter is shown when the report is run. See Visible and Hidden Parameters. |
Default Value Types
A default can be produced in three ways. Choose the Default Type first, then enter the Default Value in the matching form.
Static Value
A fixed literal that is used as-is. Examples: 1900-01-01 for a hidden StartDate, TX for a state, 0 for a minimum amount. Enter the value without surrounding quotes; the single quotes in your SQL placeholder supply the quoting.
SQL Expression
A MySQL expression that is evaluated each time the report runs, including scheduled runs, so the default is always current. This is the usual way to produce relative dates.
| Default you want | SQL Expression |
|---|---|
| Today | CURDATE() |
| Last day of the previous month | LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) |
| First day of the previous month | DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01') |
| First day of the current year | MAKEDATE(YEAR(CURDATE()), 1) |
| Same day one year ago | DATE_SUB(CURDATE(), INTERVAL 1 YEAR) |
Keep SQL expression defaults simple and fast. They run unattended before the report itself runs.
Value of Another Parameter
The default is copied from another parameter of the same report at run time. Pick the source parameter from the list. The most common use is a hidden AsOfDate whose default is the value of EndDate: the user enters one date and both parameters receive it. The referenced parameter must exist in the same report, or the save is rejected.
Visible and Hidden Parameters
- Visible parameters are shown to the user in the run dialog in SQL Editor, in the run sidebar on the Report List, and in the scheduler. The default is pre-filled and the user can change it. A visible parameter does not need a default; the user supplies the value when the report runs.
- Hidden parameters are never shown. They take their default on every run, so a hidden parameter must have a default value; the report cannot be saved without one. Use hidden parameters for values that should not change from run to run, such as a StartDate of 1900-01-01 or an AsOfDate that mirrors EndDate.
Parameters at Run Time
- In SQL Editor, select Run. Visible parameters are prompted with their defaults filled in.
- From the Report List, open the published report and the run sidebar lists the visible parameters with their labels and defaults. Enter values and run.
- On the Report Status page, each completed run records the parameter values that were used, so you can confirm which date range or filter produced a given file.
- On a schedule, the scheduler resolves defaults automatically. A schedule can also override a parameter with its own static value or SQL expression, so one report can serve several schedules (for example, a month-end run and a year-to-date run).
Best Practices
- Use clear, business-friendly names and labels.
EndDatewith the label "As of Date" is easier to understand thandt2. - Cast parameters to the expected SQL type in your query, such as DATE, DECIMAL, or INTEGER, rather than relying on implicit conversion.
- Give every parameter a sensible default, even visible ones, so the report runs correctly with no input.
- Prefer SQL expression defaults for dates so scheduled runs always use the right period.
- Validate default behavior and realistic ranges before publishing. Run the report once with defaults only and once with edge-case values.
- Document parameter meaning and intended usage in the report description or in SQL comments.
Benefits
Typed, well-named parameters reduce report sprawl and prevent accidental logic drift across copied queries. A single report with clear parameter contracts is usually easier to govern than many near-duplicate reports.
