5.2Dynamic & cascading parameters
The essentials in 30 seconds
- Dynamic parameter: the value list is fed automatically by the database (real, up-to-date customers, products, regions) — superior to any fixed static list for values that change.
- Cascading parameters: the first choice filters the next one (country → region → city) — reduced lists, guidance from general to specific, zero invalid combinations.
- Cost to watch: each dynamic list means a database query at opening — limit and cache if lists are huge, and design filtering server-side.
Running mini-case — Farès makes budget prompts reliable
Farès, a management controller in Algiers, is tired of empty budget reports: department heads type service codes by hand, with typos, and choose impossible combinations (a cost center from one division with a budget from another). He switches his prompts to dynamic parameters drawn from the database — the real, up-to-date list of divisions, departments and cost centers — organized in cascade: the chosen division filters departments, the department filters cost centers. Continued from case 5.1 — Farès moves from manually typed prompts to guided, reliable prompts.
Continued from case 5.1 — Farès replaces free typing with dynamic cascading lists.
1. Dynamic parameters
Reminder from level 2: a parameter value list can be static (fixed: months, statuses) or dynamic (fed automatically by the database). Farès remembers the simple rule: static for what never changes, dynamic for everything that lives in the database.
A dynamic parameter queries the database to offer the real, up-to-date list of customers, products, regions — instead of a fixed list. The list always reflects current data (a new customer appears on its own, without changing the report) and avoids typing mistakes: you choose from reality instead of typing.
2. Cascading parameters
Cascading parameters: several linked parameters where the first choice filters the second one's values. Farès's example: choosing a division restricts the department list, which restricts the cost center list — like the classic country → region → city.
A guided hierarchy of choices: reduced lists (a few values instead of thousands), guidance from general to specific, no invalid combinations (a cost center outside the chosen division is never offered — no more empty reports).
3. Value and cost
They make prompts smart and up to date (real values), guided (hierarchy) and reliable (fewer wrong entries) — a real plus for reports aimed at other users, like Farès's department heads.
Caution: dynamic lists mean a database query at opening (performance if lists are huge → limit and cache). They plug into record selection like any parameter (design filtering server-side, level 2). Static remains relevant for fixed, few values (months, statuses).
🔎 Concrete example — Division → department → cost center
The cascade in action at Farès's.
“Sales Division” choice → only that division's departments.
“Sales Department” choice → only that department's cost centers.
Dynamic lists (drawn from the database) + up to date.Reading: each level filters the next, from general to specific.
Lesson: fast, guided choices, with no impossible combination.
Section vocabulary
- Dynamic parameter
- A parameter whose value list is fed automatically by the database.
Simple example: Farès offers the real list of departments, up to date without touching the report.
What it is not: a hand-typed list that must be maintained.
Reflex: does this list update itself?
- Static list
- A fixed hand-defined list, with no query — reserved for values that do not change.
Simple example: Farès keeps a static list for months and statuses.
What it is not: a fixed list for customers or products that evolve.
Reflex: will these values ever change?
- Cascade
- Linked parameters where each choice filters the next one's values, from general to specific.
Simple example: Farès links division → department → cost center in Algiers.
What it is not: three independent lists that allow the impossible.
Reflex: does my previous choice restrict the next one?
- Reduced list
- The cascade effect: a few relevant values instead of a giant list.
Simple example: Farès shows only the cost centers of the chosen department.
What it is not: thousands of values to browse at each prompt.
Reflex: does the user choose from a short list?
- Population cost
- The database query needed to fill a dynamic list at opening.
Simple example: Farès limits and caches his cost-center list.
What it is not: a huge list reloaded without precaution at each opening.
Reflex: does my list stay fast at opening?
In practice — Create dynamic and cascading parameters
- Set a parameter as a DYNAMIC list: point to the database field that supplies values (e.g. the customer list) — it will always be up to date.
- Create CASCADING parameters: link country → region → city (or division → department → cost center), so each choice restricts the next list.
- Plug these parameters into record selection (server-side filtering) like any parameter.
- Check the runtime experience (up-to-date, guided, reduced lists) and watch performance if lists are large (limit/cache).
Ready-to-say script — offer guided prompts to users
“No more typing codes by hand: I offer you up-to-date lists drawn directly from the database — a new department will appear on its own — organized in cascade: you choose the division, then the filtered department, then the filtered cost center. No more typos, no more impossible combinations, and the same report serves everyone.”
Say to users to get dynamic cascading prompts adopted.
Copy-paste template — cascading prompts sheet
CASCADING PROMPTS SHEET — report: ___
LEVEL 1 (general): ___ · database source: ___ · dynamic YES / NO
LEVEL 2 (filtered by 1): ___ · database source: ___ · dynamic YES / NO
LEVEL 3 (filtered by 2): ___ · database source: ___ · dynamic YES / NO
PERF: lists limited? YES / NO · cache enabled? YES / NO · server-side filtering? YES / NOSolo / tight budget
Farès alone: a single dynamic parameter (departments) is enough to make his budget report reliable.
Very small business / small team
Two cascade levels shown with an example: everyone understands the hierarchy in one try.
SME in Algiers
Farès in Algiers schedules a demo for department heads: everyone tests the prompt on their own division.
Starting team
Pair: one configures the cascade, the other tests all combinations before go-live.
✓ Best practices
- Dynamic for values that change (customers, products); static for fixed values (months).
- Order the cascade from general to specific (country before city).
- Limit/cache huge dynamic lists.
✗ Common pitfalls
- A fixed static list for values that evolve (it goes stale).
- A cascade hierarchy inconsistent with the data.
- Huge dynamic lists that slow down opening.
Key takeaways
- DYNAMIC parameter: list drawn AUTOMATICALLY from the DATABASE (real and up to date) — far superior to a fixed STATIC list for values that change.
- CASCADING parameters: the first choice FILTERS the second (country → region → city). Guides the user and REDUCES lists.
- Benefits: UP-TO-DATE, GUIDED prompts, reduced lists, fewer errors → better EXPERIENCE and RELIABILITY. Ideal for reports aimed at others.
- Caution: dynamic lists mean a QUERY at opening (limit/cache if huge). They plug into selection (server-side filtering).
48h challenge — apply tomorrow
Fill in the cascading prompts sheet (template above) for 1 real report: are your dynamic lists and your filtering hierarchy in it?
- I can explain dynamic, static and cascade in one sentence.
- My prompts are up to date and hierarchically guided.
- I know how to limit huge lists for opening.
Frequently asked questions
Why use a dynamic parameter instead of a static list?
A STATIC list is hand-defined: simple, with no query, but FIXED — for fixed, few values that do not change (months, statuses). Its drawback: it does not update (a new customer does not appear) and must be maintained manually. A DYNAMIC list is fed automatically by the database: always UP TO DATE (a new customer appears on its own), REAL (true data), with no TYPOS (you choose from the list), no MAINTENANCE. Its only cost: a query at opening (limit/cache if the list is huge). Use static for fixed values; prefer dynamic for values that evolve or come from the database — more reliable and maintenance-free, ideal for lasting reports.
How do cascading parameters improve the user experience?
They link hierarchical choices: each selection FILTERS the next choice (country → region → city, or division → department → cost center). Benefits: (1) REDUCED, relevant lists (only the cities of the chosen region — faster); (2) GUIDANCE from general to specific (natural logic, the user goes down the hierarchy); (3) no INVALID COMBINATIONS (a city outside the region is not offered — avoids empty reports); (4) SPEED. Combined with DYNAMIC lists, values are both up to date AND filtered. Setup: one list (ideally dynamic) holding the hierarchy fields, configured in cascade from general to specific. Caution: the hierarchy must reflect the data structure; watch the population cost.
What does the first parameter choice do in a cascade?